PDO vs MYSQLI, Prepared Statemens and Binding Para

2019-02-09 19:31发布

问题:

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.

thanks

回答1:

In short,

  • Binding is a must, being the cornerstone of protection, no matter if it is supported by native driver or not. It's the very idea of substitution that matters.
  • The difference is negligible in either safety and performance, save for the few edge cases
  • Performance is the last thing to consider. There is NO API that is considerable slower than other. It is not a class or function that may cause whatever performance problem but data manipulation or bad algorithm. Optimize your queries - there is a WAY to go - not mere functions to call them.
  • If you are going to use raw bare API, then PDO is the only choice. Being wrapped in a higher level class, mysqli seems more preferable for mysql.
  • Both mysqli and PDO lacks binding for the important literals which developer have to write oneself, see safeMysql for the example.