PHP PDO MySQL IN (?,?,?

Question

I want to write a MySQL statement, for example:

SELECT * FROM someTable WHERE someId IN (value1, value2, value3, ...) 

The trick is that I don’t know in advance how many values ​​will be in IN ().

Obviously, I know that I can generate a query on the go using string manipulations, however, since this will work in a loop, I was wondering if I could do this using PDO PreparedStatement.

Sort of:

 $query = $PDO->prepare('SELECT * FROM someTable WHERE someId IN (:idList)'); $query->bindValue(':idList', implode(',', $idArray)); 

Is it possible?

Answer 1

It is not possible how you try it. You should have a separate placeholder for each parameter that you want to pass, everything else will ignore the purpose of the parameters (which separate the code from the data).

 $ids = array(2, 4, 6, 8); // prepare a string that contains ":id_0,..,:id_n" and include it in the SQL $plist = ':id_'.implode(',:id_', array_keys($ids)); $sql = "SELECT * FROM someTable WHERE someId IN ($plist)"; // prepare & execute the actual statement $parms = array_combine(explode(",", $plist), $ids); $stmt = $PDO->prepare($sql); $rows = $stmt->execute($parms); 

If you manage to pass an array of values ​​to one parameter during the binding, you will be allowed to modify the SQL statement. That would be a loophole for SQL injection - nothing could guarantee that all array values ​​would be generally innocent.

Answer 2

 $sql = 'SELECT * FROM someTABLE WHERE someID IN(:ids)'; $sth = $PDO->prepare($sql); $sth->execute(array(':ids' => implode(',', $idArray))); $rows = $sth->fetchAll();