SELECT statment in PDO with php generated array

Question

my current code is this:

$class = explode("-", $_REQUEST['class']);
$statement1 = $db->prepare("SELECT * FROM Medication WHERE class = :class OR class =:class1 OR class =:class2 OR class =:class3 OR class =:class4 ORDER BY :queryorderby :queryorder ");
$statement1->execute(array(':class' => $class[0], ':class1' => $class[1], ':class2' => $class[2], ':class3' => $class[3], ':class4' => $class[4],':queryorderby' => $queryorderby,  ':queryorder' => $queryorder));
$excutereally = $statement1->fetchall();

As you can see I have been entering $class[0] then $class[1] ... and so on into my prepare statement which i think is very time consuming and there should be another option of obtaining all arrays and use them in PDO prepare statement.

What is a better way of going around this?

Duplicate with http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition You know that a=1 or a=2 or a=3 and so on is similar as a in (1,2,3,...) — Todor, Sep 27 '14 at 20:06
@Todor the array in the question that you linked here has 5 values.. mine can change depending on $_REQUEST['class']. Also, the previous question contains IN() while mine uses OR — syrkull, Sep 27 '14 at 23:14
What you're doing by using OR is the same as using IN(), in this particular case. — joshstrike, Sep 28 '14 at 00:02
@joshstrike I started wondering if people are smart enough to answer the question. Look at the answers provided on this question. — syrkull, Sep 28 '14 at 05:23
`ORDER BY :queryorderby`: the fact is, unless queryorderby is a int indicating the index of the output column of the SELECT query, you can't bind a column name (or a SQL identifier in general). This query can't really be prepared: the SQL is not known before, this doesn't stick to their principle. Even if you prepare it with an ugly `str_repeat('?', count($class))` or whatever, you'll get more code for nothing: it won't be executed multiples times (no efficiency gain) ; a non-prepared statement is not necessarily less safe (all datas escaped). Try an injection... — julp, Sep 28 '14 at 09:34

score -1 · Accepted Answer · edited Jun 20 '20 at 09:12

Don't use a prepared statement:

$statement1 = $db->query('SELECT * FROM Medication WHERE class IN(' . implode(',', array_map(array($db, 'quote'), $class)) .  ') ORDER BY `' . str_replace('`', '``', $queryorderby) . '` ' . (0 == strcasecmp($queryorder, 'DESC') ? 'DESC' : 'ASC'));
$excutereally = $statement1->fetchAll();

This is safe:
- datas in IN part are escaped by PDO::quote (just take care to indicate your charset in the DSN)
- the SQL identifier is "escaped" in the MySQL way *
more correct than your prepared statement:
- you can't bind a SQL identifier on :queryorderby
- you can't bind a keyword (ASC/DESC) on :queryorder

* I quote MySQL documentation:

The identifier quote character is the backtick (“`”):

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

[...]

Identifier quote characters can be included within an identifier if you quote the identifier. If the character to be included within the identifier is the same as that used to quote the identifier itself, then you need to double the character. The following statement creates a table named a`b that contains a column named c"d:

mysql> CREATE TABLE `a``b` (`c"d` INT);

SELECT statment in PDO with php generated array

1 Answers1