PDO::quote

Description

PDO::quote() places quotes around the input string (if
required) and escapes special characters within the input string, using a
quoting style appropriate to the underlying driver.

If you are using this function to build SQL statements, you are
strongly recommended to use
PDO::prepare() to prepare SQL statements with bound
parameters instead of using PDO::quote() to interpolate
user input into an SQL statement. Prepared statements with bound parameters
are not only more portable, more convenient, immune to SQL injection, but
are often much faster to execute than interpolated queries, as both the
server and client side can cache a compiled form of the query.

Security: the default character set

The character set must be set either on the server level, or within the
database connection itself (depending on the driver) for it to affect
PDO::quote(). See the driver-specific
documentation for more information.

Parameters

string

The string to be quoted.

parameter_type

Provides a data type hint for drivers that have alternate quoting styles.

Return Values

Returns a quoted string that is theoretically safe to pass into an
SQL statement. Returns FALSE if the driver does not support quoting in
this way.

User Contributed Notes 4 notes

When converting from the old mysql_ functions to PDO, note that the quote function isn't exactly the same as the old mysql_real_escape_string function. It escapes, but also adds quotes; hence the name I guess :-)

After I replaced mysql_real_escape_string with $pdo->quote, it took me a bit to figure out why my strings were turning up in results with quotes around them. I felt like a fool when I realized all I needed to do was change ...\"".$pdo->quote($foo)."\"... to ...".$pdo->quote($foo)."...

One have to understand that string formatting has nothing to do with identifiers.And thus string formatting should NEVER ever be used to format an identifier ( table of field name).To quote an identifier, you have to format it as identifier, not as string.To do so you have to

So, the code would be:<?phpfunction quoteIdent($field) { return "`".str_replace("`","``",$field)."`";}?>this will make your identifier properly formatted and thus invulnerable to injection.

However, there is another possible attack vector - using dynamical identifiers in the query may give an outsider control over fields the aren't allowed to:Say, a field user_role in the users table and a dynamically built INSERT query based on a $_POST array may allow a privilege escalation with easily forged $_POST array. Or a select query which let a user to choose fields to display may reveal some sensitive information to attacker.

To prevent this kind of attack yet keep queries dynamic, one ought to use WHITELISTING approach.

Every dynamical identifier have to be checked against a hardcoded whitelist like this:<?php$allowed = array("name","price","qty");$key = array_search($_GET['field'], $allowed));if ($key == false) { throw new Exception('Wrong field name');}$field = $db->quoteIdent($allowed[$key]);$query = "SELECT $field FROM t"; //value is safe?>(Personally I wouldn't use a query like this, but that's just an example of using a dynamical identifier in the query).

And similar approach have to be used when filtering dynamical arrays for insert and update: