4

In php, what is the difference and which is better and why, using mysqli.

I have a whole project written with real escape string, is it necessary to convert to object oriented prepared statements?

shane
  • 1,742
  • 2
  • 19
  • 36
  • 1
    [This 'not constructive' question has some good discussion](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons) – ahruss Jul 21 '14 at 02:33
  • Thanks for the article but it does not explain the differences – shane Jul 21 '14 at 02:50

1 Answers1

5

From the programmers point of view, the difference between escaping the values manually and parameterized/prepared statements as implemented by PDO is the degree of separation, automation, and a shift of responsibility.

With *_escape_string the developer has to ensure that all values:

  • are passed through the corresponding *_escape_string function and
  • are placed within SQL string literals, as the *_escape_string functions are intended to be used for escaping string literal values only.

This can work perfectly if the developer has the discipline to keep track of each parameter’s processing. But it tends to become more complicated and thus error prone the more complex a statement gets.

And if there’s just one parameter missing proper processing or handling, the statement is at stake being vulnerable to SQL injection. And to be honest, there are actually many cases here on Stack Overflow which show that this manual technique is more error prone as it’s easy to miss one of the aforementioned points.

In opposite to that, PDO provides a layer of abstraction by having just placeholders in the statement. The parameter values are passed separately and PDO takes care of proper processing and handling. All the developer has to do is prepare the statement with the placeholders and then execute the prepared statement with the actual values.

Now which one is better? Obviously the latter PDO variant as it is less error prone and way more cleaner. There are less things for the developer to consider and take care of because it’s now done by PDO.

Gumbo
  • 643,351
  • 109
  • 780
  • 844
  • Thanks so there is no fundamental difference other than it's interface. Could you elaborate 'are placed within SQL string literals, as the *_escape_string functions are intended to be used for escaping string literal values only.' I don't quite understand this phrase – shane Jul 21 '14 at 05:48
  • @user3667450 The `*_escape_string` functions don’t do magic. They just escape certain characters which are critical in string literals. For example, [`mysql_real_escape_string`](http://php.net/mysql_real_escape_string) escapes the characters NULL (`\0`), LINE FEED (`\n`), CARRIAGE RETURN (`\r`), BACKSLASH (```\```), APOSTROPHE (`'`), QUOTATION MARK (`"`) and SUBSTITUTE (`\x1a`). Some of these are critical within [MySQL string literals](http://dev.mysql.com/doc/en/string-literals.html) as they denote the end of the string literal or introduce an escape sequence. – Gumbo Jul 21 '14 at 06:22
  • @user3667450 However, if you would use the value outside a string literal, there would be no need for an attacker to escape from the string literal to inject arbitrary SQL code. – Gumbo Jul 21 '14 at 06:23