[ACCEPTED]-PDO quote method-pdo

Accepted answer
Score: 15

When using Prepared Statements with PDO::prepare() and 7 PDOStatement::execute(), you don't have any quoting to do : this will 6 be done automatically.

But, sometimes, you 5 will not (or cannot) use prepared statements, and will 4 have to write full SQL queries and execute 3 them with PDO::exec() ; in those cases, you will have 2 to make sure strings are quoted properly 1 -- this is when the PDO::quote() method is useful.

Score: 0

While this may not be the only use-case 5 it's the only one I've needed quote for. You 4 can only pass values using PDO_Stmt::execute, so for example 3 this query wouldn't work:

SELECT * FROM tbl WHERE :field = :value

quote comes in so that 2 you can do this:

// Example: filter by a specific column
$columns = array("name", "location");
$column = isset($columns[$_GET["col"]]) ? $columns[$_GET["col"]] : $defaultCol;

$stmt = $pdo->prepare("SELECT * FROM tbl WHERE " . $pdo->quote($column) . " = :value");
$stmt->execute(array(":value" => $value));

$stmt = $pdo->prepare("SELECT * FROM tbl ORDER BY " . $pdo->quote($column) . " ASC");

and still expect $column to be 1 filtered safely in the query.

Score: 0

The PDO system does not have (as far as 25 I can find) any mechanism to bind an array 24 variable in PHP into a set in SQL. That's 23 a limitation of SQL prepared statements 22 as well... thus you are left with the task 21 of stitching together your own function 20 for this purpose. For example, you have 19 this:

$a = array(123, 'xyz', 789);

You want to end up with this:

$sql = "SELECT * FROM mytable WHERE item IN (123, 'xyz', 789)";

Using 18 PDO::prepare() does not work because there's 17 no method to bind the array variable $a 16 into the set. You end up needing a loop 15 where you individually quote each item in 14 the array, then glue them together. In which 13 case PDO::quote() is probably better than 12 nothing, at least you get the character 11 set details right.

Would be excellent if 10 PDO supported a cleaner way to handle this. Don't 9 forget, the empty set in SQL is a disgusting 8 special case... which means any function 7 you build for this purpose becomes more 6 complex than you want it to be. Something 5 like PDO::PARAM_SET as an option on the 4 binding, with the individual driver deciding 3 how to handle the empty set. Of course, that's 2 no longer compatible with SQL prepared statements.

Happy 1 if someone knows a way to avoid this difficulty.

More Related questions