Do I Need to Escape My Variables If I Use MySQLi Prepared Statements

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.

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.

mySQLi Prepared Statement Select with Escape Characters

you should validate/escape user input before sending it to the db.

checkout this mysql-real-escape-string()

mysqli prepared statements and mysqli_real_escape_string

If you correctly bind all your variables you can dramatically reduce the risk of SQL injection. It is still possible to get an SQL injection if you create SQL dynamically for example:

'SELECT * FROM ' . $tablename . ' WHERE id = ?'

But if you avoid things like this it is unlikely you will have problems.

Properly Escaping with MySQLI | query over prepared statements

1. http://php.net/manual/en/mysqli-stmt.get-result.php
2. Yes, but it is very bad practice:
   
   • it will help you in this case but only in this case and deceive with anything else
   • manual escaping is just silly, better let driver to do it for you
3. YES, because there is no such thing like SQL injection but improper formatting ONLY

is that using $mysqli->real_escape_string($Var); does not provide protection against SQL Injection?

I didn't change my mind: sure, it doesn't.
It will do only if you enclose the resulting value in quotes (and set proper encoding using mysqli_set_charset() to be strict).

Look, SQL injection not something essential, existing on it's own, but it's rather mere a consequence. A consequence of improperly formatted query.

When creating a query, you have to properly format every part of it. Not because of whatever "injection" but for the sake of it. When you're going to insert a string into query, you HAVE to put it into quotes, or you will get a syntax error. When you're going to insert a string into query, you HAVE to escape these quotes were used to delimit this string, or you will get a syntax error. And so on. It is proper formatting that should be your concern, not scaring tales about injection. And as long as you have every dynamic query part properly formatted according to it's type - no injection ever could be possible

So, the source of variable or it's value should never be your concern. But only it's place in the query:

• strings have to be enclosed in quotes and have these quotes escaped.
• numbers have to be cast to it's type.
• identifiers have to be enclosed in backticks and have these backticks doubled

When it's going for the static part of the query, hardcoded in the script, we don't use such strict standards - say, we're not enclosing every identifier in backticks.

But when it's going for the dynamical part of the query, applying formatting rules should be strict rule, as we cannot know variable content for sure.

By the way, there is another way to format your strings and numbers - prepared statements. It is not as convenient as it should be, but because it is using placeholders to represent your data in the query, it it recommended to use over silly manual formatting.

Why is using a mysql prepared statement more secure than using the common escape functions?

An important point that I think people here are missing is that with a database that supports parameterized queries, there is no 'escaping' to worry about. The database engine doesn't combine the bound variables into the SQL statement and then parse the whole thing; The bound variables are kept separate and never parsed as a generic SQL statement.

That's where the security and speed comes from. The database engine knows the placeholder contains data only, so it is never parsed as a full SQL statement. The speedup comes when you prepare a statement once and then execute it many times; the canonical example being inserting multiple records into the same table. In this case, the database engine needs to parse, optimize, etc. only once.

Now, one gotcha is with database abstraction libraries. They sometimes fake it by just inserting the bound variables into the SQL statement with the proper escaping. Still, that is better than doing it yourself.

Should I use mysqli_real_escape_string or should I use prepared statements?

Prepared statements only. Because nowhere escaping is the same thing. In fact, escaping has absolutely nothing to do with whatever injections, and shouldn't be used for protection.

While prepared statements offer the 100% security when applicable.

mysqli_real_escape_string, should I use it?

You should use prepared statements and pass string data as a parameter but you should not escape it.

This example is taken from the documentation:

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $city);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($district);

    /* fetch value */
    $stmt->fetch();

    printf("%s is in district %s\n", $city, $district);

    /* close statement */
    $stmt->close();
}

Note that the example does not call mysqli_real_escape_string. You would only need to use mysqli_real_escape_string if you were embedding the string directly in the query, but I would advise you to never do this. Always use parameters whenever possible.

Related

• How can I prevent SQL injection in PHP?

Do i need to sanitize input if using prepared PHP/MySQL queries?

No you don't have to escape it or sanitize it for injection protection. For other app specific things you may sanitize it though.

I had a similar question a while back:

mysqli_stmt_bind_param SQL Injection

---

Related Topics