Do PHP Pdo Prepared Statements Need to Be Escaped

Do PHP PDO prepared statements need to be escaped?

PDO does not escape the variables. The variables and the SQL command are transferred independently over the MySQL connection. And the SQL tokenizer (parser) never looks at the values. Values are just copied verbatim into the database storage without the possibility of ever causing any harm. That's why there is no need to marshall the data with prepared statements.

Note that this is mostly a speed advantage. With mysql_real_escape_string() you first marshall your variables in PHP, then send an inefficient SQL command to the server, which has to costly segregate the actual SQL command from the values again. That's why it's often said that the security advantage is only implicit, not the primary reason for using PDO.

If you concat the SQL command and don't actually use prepared statments (not good!), then yes, there still is an escape function for PDO: $pdo->quote($string)

How to avoid escape character \ in PDO prepared statement?

You are doing ac.id IN (?). This is telling the database that you want to bind one parameter to the query. If you have multiple elements, you need multiple ?s: ac.id IN (?,?,?).

What you can do is dynamically add the ?s and then bind each parameter that you need.

$params = implode(',', array_fill(0, count($values), '?'));
$sql = "SELECT ac.id AS target_id FROM account AS ac
    JOIN address_vod__c AS ad ON (ad.account_vod__c = ac.id AND ad.primary_vod__c = 1)
    WHERE ac.id IN ({$params})";

$stmt = $pdo->prepare($sql);
$stmt->execute($values);
$result = $stmt->fetchAll();

Clarity on PHP Prepared Statement Escaping

NB: This answer uses an overly-simplistic model of what escaping and prepared statements actually do.

SQL is a language. Some characters in it have special meaning. For instance ' delimits the beginning and end of a string.

When you escape data, you put a \ in front of the characters with special meaning. That causes them to mean (for example) "An apostrophe" instead of "The end of the string".

So:

$id = $conn->real_escape_string($_POST['id']);

So now, if there was a ' in the ID, it won't break the SQL.

When you use a bound variable, it will automatically be escaped for you.

$qry->bind_param('iss', $id, $name, $message);

So now, if there was a ' in the ID, it won't break the SQL.

… except you have already done that.

So now you have the ' turned into \' and then in to \\\' because the ' was escaped and then it was escaped again along with the \ from the first escape.

So now the first \ has been treated as data (instead of as a special SQL character) and inserted into the database.

Use prepared statements. Use only prepared statements.

(The exception is when you are doing things with variables where a prepared statement can't go, such as dynamic table names, which shouldn't be too often).

Should you use prepared statements for their escaping only?

The difference considered to be negligible.

Nevertheless, one have to distinguish native prepared statements from the general idea of a prepared statement.

The former is just a form of running queries supported by most of DBMS, explained here. Its usage can be questioned.

The latter is a general idea of substituting actual data with a placeholder, implying further processing of the substituted data. It is widely used in programming, a well-known printf() function is an example. And this latter approach have to be ALWAYS used to run a query against a database, no matter if it is backed by native prepared statements or not. Because:

• prepared statement makes proper formatting (or handling) inevitable.
• prepared statement does proper formatting (or handling) in the only proper place - right before query execution, not somewhere else, so, our safety won't rely on such unreliable sources like
  
  
  • some PHP 'magic' feature which rather spoils the data than make it safe.
  • good will of one (or several) programmers, who can decide to format (or not to format) our variable somewhere in the program flow. That's the point of great importance.
• prepared statement affects the very value that is going into query, but not the source variable, which remains intact and can be used in the further code (to be sent via email or shown on-screen).
• prepared statement can make application code dramatically shorter, doing all the formatting behind the scenes (*only if driver permits).

So, even if you consider not using native prepared statements (which is quite okay), you have to always create your queries using placeholders instead of the actual data. For this purpose you can use PDO, which works exactly as described above - by default it just emulate prepares, means regular SQL query being created out prepared query and data, and then run against database.

However, PDO lacks support for many important data types, such as identifier or an array - thus it makes you unable to always use placeholders and thus makes an injection quite possible. Luckily, safeMysql has placeholders for the every data type and allows you to run queries safely.

Do I need to escape my variables if I use MySQLi prepared statements?

No, if you use prepared statements everywhere in your application you are safe from SQL injection. However, an important "gotcha" is 2nd order injection attacks which happen when some queries use prepared statements and others don't.

According to this answer of a similar question on SO:

prepared statements / parameterized queries are sufficient to prevent 1st order injection on that statement. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

In summary, prepared statements create a separation between the data being sent and the SQL query itself, ensuring that the data can not be misinterpreted as the SQL query. However, an attacker can still enter SQL as data, and although it will not be executed when it is first stored if you are using prepared statements, you must still use caution when retrieving said results. Prepared statements protect your application in that particular place, but because SQL is still allowed to be stored in the database, your application is unsafe if you're later using that data without parameterization.

PDO : Need to escape string or not ?

You need to prepare your statement, try this:

$query = $pdo->prepare('INSERT INTO test(id, name) VALUES (:theid, :thename)');
$query->execute(array(
    'theid' => $id,
    'thename' => $name
));

Escape a pdo query, is that necessary?

The parts of your query that are dynamic are the table name and column names. You can't use bind functions for these parts of the query. Bind functions can be used only for the parts of the query that would otherwise be a simple value in an SQL query. Like a numeric constant, or a quoted string or quoted date literal.

To avoid SQL injection from dynamic table names or column names, you have the following choices:

• Use values that are predefined in your class, or otherwise certain to be safe. Don't use external content from users or any other source.
• Use escaping. Note that the function PDO::quote() doesn't do the kind of escaping you need for table names or column names.
• Create a "allowlist" of known table names and the column names for the respective table, and compare the dynamic input to the allowlist. If it doesn't match the allowlist, raise an error.

Prepared statements and Escaping

the only problem with you query is that you cannot pass the tableName as a paramater. Only values can be parameterized. So the other way is to concatenate the tableName along with your query.

"SELECT * FROM `" . $tableNameHere . "` WHERE `uid`=?"

---

Related Topics