PDO quote method

Question

Where and when do you use the quote method in PDO? I'm asking this in the light of the fact that in PDO, all quoting is done by the PDO object therefore no user input should be escaped/quoted etc. This makes one wonder why worry about a quote method if it's not gonna get used in a prepared statement anyway?

Answer 1

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

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

Answer 2

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

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

You want to end up with this:

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

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

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

Happy if someone knows a way to avoid this difficulty.

Answer 3

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

SELECT * FROM tbl WHERE :field = :value

quote comes in so that 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 filtered safely in the query.

Answer 4

A bit late anwser, but one situation where its useful is if you get a load of data out of your table which you're going to put back in later.

for example, i have a function which gets a load of text out of a table and writes it to a file. that text might later be inserted into another table. the quote() method makes all the quotes safe.

it's real easy:

$safeTextToFile = $DBH->quote($textFromDataBase);