Reasons for Prepared Statements with Bind Parameters over Interpolated Statements with Escaped / Quoted Parameters

To protect against SQL injection one is advised to use prepared statements with bind values. This ensures, that the database can distinguish between the actual logic in the SQL (which has to be parsed, interpreted and optimized) and the data (which doesn't need interpretation) and therefore will not interpret and execute commands which are found in data.

Another method accomplishing some protection is using an escaping library, which disarms significant chars in the data, so that they will not be interpreted.

It seems to me that in general it is advised to prefer prepared statements with bind parameters over escaping the input. Prepared statements with bind values do for example have some performance benefits in loops.

My question: is there any security reason to prefer prepared statements with bind values over escaping? And if yes, what are the exact reasons?

One reason I might think of is that "escaping is tricky" and the escaping library needs to match exactly the database features... anything else?

Solution

One reason is that escaping only works to protect quoted string literals. For example (I'll use pseudocode since you didn't reference any particular programming language):

$escapedName = EscapeString("O'Reilly")

$sql = "SELECT * FROM MyTable WHERE name = '$escapedName'"

In the above example, the apostrophe should be escaped, so it will become WHERE name = 'O\'Reilly' and therefore be safe to interpolate into the SQL query without causing any error.

However, numbers don't need to be quoted in SQL, and escaping a string that contains an apostrophe won't do the right thing:

$escapedId = EscapeString("123'456")

$sql = "SELECT * FROM MyTable WHERE id = $escapedId"

This will result in WHERE id = 123\'456 which is still an error.

You might say, "well put the number in single-quotes" but this isn't always possible, for example the LIMIT clause in MySQL required real integers, not a quoted string containing digits.

Besides the above issue, it's just easier to write code using parameters instead of using escaping!

For example, you could write code like the following:

$sql = "INSERT INTO mytable (col1, col2, col3, col4, col5, col6) 
  VALUES ('" . mysqli_real_escape_string($_POST['col1']) . "', " 
  . $mysqli->real_escape_string($_POST['col2']) . "', '" 
  . $mysqli->real_escape_string($_POST['col3']) . "', '" 
  . $mysqli->real_escape_string($_POST['col4']) . ", '" 
  . $mysqli->real_escape_string($_POST['col5']) . "', '" 
  . $mysqli->real_escape_string($_POST['col6']) . "')";

Can you spot the mistakes? With enough time, I’m sure you can. But it will slow down your coding and may give you eyestrain as you look for missing quote characters and other mistakes.

But it’s so much easier to write this, and easier to read it afterwards:

$sql = "INSERT INTO mytable (col1, col2, col3, col4, col5, col6) 
  VALUES (?, ?, ?, ?, ?, ?)";

Query parameters are safe for more data types, and they help you write code more quickly, with fewer mistakes. That's a big win.