Why Is Using a MySQL Prepared Statement More Secure Than Using the Common Escape Functions

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.

Can someone clearly explain why mysqli_prepare()/bind_param() is better than real_escape_string()?

What you are reading, that you need to use mysqli_prepare() and mysqli_bind_param() functions to "properly escape your MySQL queries" is wrong.

It is true that if you use mysqli_prepare() and mysqli_bind_param(), you needn't (and shouldn't) "escape" the values supplied as bind parameters. So, in that sense, there's some truth in what you are reading.

It's only when unsafe variables are included in the SQL text (the actual text of the query) that you need to "properly escape" the variables, usually by wrapping the variables in mysqli_real_escape_string() function calls.

(We note that it's possible to make of use of prepared statements and still include un-escaped variables in the SQL text, rather than passing the variable values as bind_parameters. That does sort of defeats the purpose of using prepared statements, but the point is, either way, you can write code that is vulnerable.

MySQL now supports "server side" prepared statements (if the option is enabled in the connection), and that's a performance optimization (in some cases) of repeated executions of identical SQL text. (This has been long supported in other databases, such as Oracle, where making use of prepared statements has been a familiar pattern for, like, since forever.)

Q: Did they implement [prepared statements] so that people wouldn't forget to escape values before sending them in a query?

A: Based on the number of examples of code vulnerable to SQL Injection when not using prepared statements, despite the documentation regarding mysql_real_escape_string() function, you'd think that certainly would be sufficient reason.

I think one big benefit is that when we're reading code, we can see a SQL statement as a single string literal, rather than a concatenation of a bunch of variables, with quotes and dots and calls to mysql_real_escape_string, which isn't too bad with a simple query, but with a more complex query, it is just overly cumbersome. The use of the ? placeholder makes for a more understandable SQL statement,... true, I need to look at other lines of code to figure out what value is getting stuffed there. (I think the Oracle style named parameters :fee, :fi, :fo, :fum is preferable to the positional ?, ?, ?, ? notation.) But having STATIC SQL text is what is really the benefit.

Q: Or is it somehow faster?

As I mentioned before, the use of server side prepared statements can be and advantage in terms of performance. It's not always the case that it's faster, but for repeated execution of the same statement, where the only difference is literal values (as in repeated inserts), it can provide a performance boost.

Q: Or should I use this method when I intend to use the same query repeatedly (since a mysqli_stmt can be reused) and use the traditional method in other cases?

That's up to you. My preference is for using STATIC SQL text. But this really comes from a long history of using Oracle, and using the same pattern with MySQL fits naturally. (Albeit, from Perl using the DBI interface, and Java using JDBC and MyBATIS, or other ORMs (Hibernate, Glassfish JPA, et al.)

Following the same pattern just feels natural in PHP; the introduction of mysqli_ and PDO are a welcome relief from the arcane (and abused) mysql_ interface.

Good code can be written following either pattern. But I challenge you to think ahead, about more complex SQL statements, and whether the choice to use mysqli_real_escape_string() and concatenating together a dynamic string to be executed, rather than using static SQL text and bind parameters, might make reading, and deciphering, the actual SQL being executed more complicated for the soul that finds themselves maintaining code they didn't write.

I think studies have shown that code is read ten times more than it is written, which is why we strive to produce readable, understandable code, even if that means more lines of code. (When each statement is doing a single identifiable thing, that's usually easier for me to understand than reading a jumble of concatenated function calls in one complicated statement.

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 mysqli_string_escape statement to avoid sql injection

No you don't, prepared statements will ensure that your queries are sanitized properly.

In fact, prepared statements are the safest way to prevent injections, escaping is never 100% safe.

This explains why: Why is using a mysql prepared statement more secure than using the common escape functions?

---

• Consult the following links regarding prepared statements, or PDO with prepared statements.

How can prepared statements protect from SQL injection attacks?

The idea is very simple - the query and the data are sent to the database server separately.

That's all.

The root of the SQL injection problem is in the mixing of the code and the data.

In fact, our SQL query is a legitimate program.
And we are creating such a program dynamically, adding some data on the fly. Thus, the data may interfere with the program code and even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

will produce a regular query

SELECT * FROM users where id=1

while this code

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

will produce a malicious sequence

SELECT * FROM users where id=1; DROP TABLE users;

It works because we are adding the data directly to the program body and it becomes a part of the program, so the data may alter the program, and depending on the data passed, we will either have a regular output or a table users deleted.

While in case of prepared statements we don't alter our program, it remains intact

That's the point.

We are sending a program to the server first

$db->prepare("SELECT * FROM users where id=?");

where the data is substituted by some variable called a parameter or a placeholder.

Note that exactly the same query is sent to the server, without any data in it! And then we're sending the data with the second request, essentially separated from the query itself:

$db->execute($data);

so it can't alter our program and do any harm.

Quite simple - isn't it?

The only thing I have to add that always omitted in the every manual:

Prepared statements can protect only data literals, but cannot be used with any other query part.

So, once we have to add, say, a dynamical identifier - a field name, for example - prepared statements can't help us. I've explained the matter recently, so I won't repeat myself.

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

Are dynamic mysql queries with sql escaping just as secure as prepared statements?

Definitely NO.

While question in the title is ambiguous and can be interpreted as "Are dynamic mysql queries with every it's part properly formatted..." and thus have a positive answer, the question in the body is not:

If I ran all data received from the user through mysql real escape would it be just as secure as using mysql prepared statements?

If you look to this question closer, you will understand that this is just a magic quotes incarnation! The very purpose of this disgraced, deprecated and removed feature is exactly to "run all user input through escape".

Everyone knows nowadays that magic quotes are bad. Why positive answer then?

Okay, it seems that it needs to be explained again, why bulk escaping is bad.

The root of the problem is a quite strong delusion, shared by almost every PHP user:

Everyone have a strange belief that escaping do something on "dangerous characters" (what are they?) making them "safe" (how?). Needless to say that it's but a complete rubbish.

The truth is:

• Escaping do not "sanitize" anything.
• Escaping has nothing to do with injections.
• Escaping has nothing to do with user input.

Escaping is merely a string formatting and nothing else.

When you need it - you need it despite of injection possibility.

When you don't need it - it won't help against injection even a little.

Speaking of difference with prepared statements, there is at least one issue (which already mentioned many times under sql-injection tag):

a code like this

$clean = mysql_real_escape_string($_POST['some_dangerous_variable']);
$query = "SELECT * FROM someTable WHERE somevalue = $clean";

will help you NOT against injection.

Beause escaping is just a string formatting facility, not injection preventer by any means.

Go figure.

However, escaping have something in common with prepared statements:

Them both doesn't guarantee you from injection if

• you are using it only against notorious "user input", not as a strict rule for the building ANY query, despite of data source.
• in case you need to insert not data but identifier or a keyword.

To be safe in these circumstances, see my answer explaining FULL sql injection protection how-to

Long story short: you can consider yourself safe only if you make 2 essential corrections and one addition to your initial statement:

If I ran all data received from the user through mysql real escape and always enclose it in quotes (and, as ircmaxell mentioned, mysqli_set_charset() is used to make mysqli_real_escape string() actually do it's work (in such a rare occasion of using some odd encoding like GBK)) would it be just as secure as using mysql prepared statements?

Following these rules - yes, it would be as secure as native prepared statements.

PHP & MySQL: Creating your own Prepared statement without using MySQLi and/or PDO

There are two types of prepared statements, the emulated prepared statements and the native prepared statements. What you are doing is emulating the prepared statements.

---

1.How to create a MySQL prepared statement?

In php level, you could only emulate the prepared statement by replacing the placeholder with secure values. (Ex: quote the string, escape the special char, and so on...)

2.Why it is secure?

Because the prepared statements prevent the sql injection.

---

PS:
PDO has the option of PDO::ATTR_EMULATE_PREPARES, which enables or disables emulation of prepared statements.

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.

---

Related Topics