Using PDO::quote with Parameterized Queries

I spent some time last week investigating a puzzling issue raised in the SQL Server Driver for PHP forums: Need help with PDO::quote() and PDOStatement::bindValue and PDO::execute using new SQLSRVR 2.0 driver. At the heart of the issue was this question: Should you use the PDO::quote method to quote a parameter if you are also using the PDOStatement::bindValue or the PDOStatement::bindParam method to bind the parameter? My answer is no, you shouldn’t. I’ll explain why not, but I wonder if I’m potentially missing some use cases where it does make sense…I’d be very interested to learn those cases if I am.

The PDO::quote method is intended to appropriately quote a string and escape special characters (appropriate to the underlying driver, that is) for use in a query. So, in the (hopefully rare) cases where you concatenate a query string with a parameter value, the PDO::quote method can be helpful. For example, in this code…

$param = "Brian's data";
$param = $conn->quote($param);
$sql = "INSERT INTO testtable (data) VALUES (".$param.")";
$stmt = $conn->query($sql);

…the PDO::quote method would be helpful because your parameter value would be properly quoted and escaped (‘Brian’’s data’ in the case of the PDO_SQLSRV driver and ‘Brian\’s data’ in the case of the PDO_MYSQL driver).

Note: Unless you are 100% positive that a SQL injection attack is impossible (i.e. you are not working with user input data), the approach above for inserting data is a bad idea – see What’s the Right Way to Prevent SQL Injection in PHP Scripts? to understand why.

The beauty (and power) of using parameterized queries is that the underlying driver takes care of quoting and escaping for you (while also helping to protect against SQL injection). So, in code like this…

$param = "Brian's data";
$sql = "INSERT INTO testtable (data) VALUES (?)";
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $param);

…the underlying driver will appropriately quote and escape the parameter value so that the correct data is inserted (i.e. Brian’s data).

However, if you use the PDO::quote method and a parameterized query, the data that is inserted may not be what you expected. For example, in this code…

$param = "Brian's data";
$param = $conn->quote($param);
$sql = "INSERT INTO testtable (data) VALUES (?)";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $param);

…the value that is inserted into the database would include the quotes and escape characters (i.e. ‘Brian’’s data’ in the case of the PDO_SQLSRV driver and ‘Brian\’s data’ in the case of the PDO_MYSQL driver). If, as in the case of the forum post I mentioned at the beginning, you are passing parameter values to a stored procedure that is in turn generating a SQL string to be executed, these unexpected values can cause problems.

As I said in the beginning, I’d be interested in understanding if there are, in fact, common scenarios in which it does make sense to use both the PDO::quote method and a parameterized queries.



Share this on Twitter

Comments (2)
  1. Jerome says:

    You're my savior ! Thanks

  2. Darth Killer says:

    Hello, i found this one while searching for something else, but i see a misunderstanding i feel i need to explain.

    You're talking about the PDOStatement object created by PDO::prepare(), calling it a "parameterized statement". That's you're mistake here. These are prepared statements, not mearly parameterized statements.

    The difference is that prepared statement are pre-compiled on the database side, so they can be re-executed repeatedly with new parameter values without suffering from slow communication. But if you only intend to execute your statement once, it's a total overkill. You'll end up with a slower execution than if you write your query manually using PDO::quote().

    In other languages, both parameterized statements and prepared statements exist, but in PHP as of today only prepared statements exist, and if you want to have parameterized statements you have to implement them yourself, OR write the query to execute the old fashion way.

Comments are closed.

Skip to main content