Real escape string vs bind param

Question

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?

Answer 1

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.