Using PDO::quote with Parameterized Queries

[アーティクル]
05/10/2011

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);
$stmt->execute();

…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);
$stmt->execute();

…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.

次の方法で共有

Using PDO::quote with Parameterized Queries

Comments

その他のリソース