5 ms·
You can write a function for it using ?'s being generated inside your code only. Its perfectly safe to do it that want and allows you to bind parameters. The o
by opendais 12y ago
You can write a function for it using ?'s being generated inside your code only. Its perfectly safe to do it that want and allows you to bind parameters.
The only explanation for not doing that is ignorance and/or incompetence. Mistakes happen but to claim its a language problem is incorrect.
http://php.net/manual/en/pdostatement.bindparam.php http://php.net/manual/en/pdostatement.bindparam.php
<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->bindParam(1, $calories, PDO::PARAM_INT);
$sth->bindParam(2, $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>
So what you do is you write a function to convert the array to a series of ?'s for the IN clause/tuple and then iterate through the array to bind the parameters.
- jiggy2011 12y agoI don't think that would work in this case, because there is no way to bind a PHP array to an SQL tuple. Available param types includes string , int , bool but no array or tuple type. http://php.net/manual/en/pdo.constants.php http://php.net/manual/en/pdo.constants.php You can write "SELECT calories FROM fruit WHERE name IN (? , ?, ?)" and then bind the parameters as strings but this will only work in cases where the length of the tuple is known and fixed. If you need to allow for a variable length tuple then you will need to concatenate the query string yourself.
- SapphireSun 12y agoJust use a placeholder like (??) and flatten everything out. Translate (??) and array(1,2,3) into (?,?,?) with 1, 2, 3 as the binds.
- jiggy2011 12y agoI don't think PDO supports this AFAIK.
- SapphireSun 12y agoI had to implement it manually, but it works. FWIW, I'm using a custom wrapper around MySQLi though.
- mappu 12y agoThen what do you do if the passed tuple has zero length? Dynamically rewriting the SQL isn't going to be tractable in all cases - doing `IN (NULL)` might be a valid value - and throwing an exception is poor form for an actually valid case.
- SapphireSun 12y agoCheck for bad inputs? I'm not saying this is a cure all, but it's a lot better than not being able to use arrays at all.
- deleted 12y ago[deleted]
- opendais 12y ago... What part of you needed to write a function was unclear? o.O Counting an array is a safe operation.
- jiggy2011 12y agoYou need to count the array at runtime and convert to a tuple, the only way this can be done using PDO (AFAIK) is by appending the tuple to the string itself because PDO provides no tuple type.
- opendais 12y agoYou count the length of the array. You create the tuple with the appropriate number of ?'s. You then bind parameters. No user input involved.
- __david__ 12y ago> You can write a function for it using ?'s being generated inside your code only. Its perfectly safe to do it that want and allows you to bind parameters. Of course you can. Everyone here is saying you shouldn't have to. This is the perfect type of thing to move into the db library. That makes it much safer because you don't have to hand-roll the same stupid code in 50,000 places (and possibly fat-finger it once).
- opendais 12y agoIt belongs in an ORM, not the language.