PDO vs MYSQLI, Prepared Statemens and Binding Parameters

Question

I have this very question to clear things up. I read some documentation and comments around but still somethings are just not clear enough.

• I understand PDO offers more drivers which would certainly is a plus if you would ever change your database type.
• As said on another post, PDO doesnt offer true prepared statements but mysqli does so it would be safer to use MYSQLI
• Benchmarks looks similar, (did not test it myself but checked around on the web for a few benchmarks)
• Being object oriented is not an issue for me since mysqli is catching up. But would be nice to benchmark procedural mysqli vs PDO since procedural is supposed to be slightly faster.

But here is my question, with prepared statement, do we have to use parameter binding with the data we use in our statement? good practice or have to? I understand prepared statements are good perfermance-wise if you run the same query multiple times but it is enough to secure the query itself? or binding parameters is a must? What exactly do the binding parameters and how it works to protect the data from sql injection? Also would be appreciated if you point our any misunderstanding about the statements I made above.

Answer 1

In short,

• Binding is a must, being a cornerstone of protection, no matter if it is supported by a native driver or not. It's the idea of substitution that matters.
• The difference is negligible in either safety and performance.
• Performance is the last thing to consider. There is NO API that is considerable slower than other. It is not a class or a function that may cause whatever performance problem but a data manipulation or a bad algorithm. Optimize your queries, not mere functions to call them.
• If you are going to use a raw bare API, then PDO is the only choice. While wrapped in a higher level class, mysqli seems more preferable for mysql.
• Both mysqli and PDO lack bindings for the identifiers and keywords. In this case a whitelist-based protection must be implemented. Here is my article with the ready made example, Adding a field name to the SQL query dynamically