Hi
I have been told that using PDO together with PHP (I am using v 8) and MySQL any prepared statements default to emulated prepared statements. I have heard that emulated prepared statements are less secure.
Now I understood that PDO is the recommended method as opposed to mysqli. Some say because of additional security and some say compatibility with other databases. I have also been told that prepared statements is a good start to preventing injection.
If PDO defaults to emulated prepared statements or if I have to allow emulation for some databases it seems that PDO could actually be less secure than mysqli.
Could someone please explain or clarify for me
Thank you
Correct. The current default setting is TRUE, i.e. to use emulated prepared queries, for all database types.
They can be if you hadn’t set the character set of the connection to match your database table’s character set.
No. The PDO extension is simpler and is more consistent than the mysqli extension, especially when dealing with prepared queries. The mysqli extension has a completely different programing interface for non-prepared and prepared queries, requiring you to learn two different sets of statements. The PDO extension treats the result of a non-prepared and a prepared query identically.
This is correct in any case. The PDO extension has drivers for 12 different database types. You use the same fundamental PDO statements for all the database types, rather than learning a completely different set of php statements for each database type. You only have to account for sql differences between the different database types.
Prepared queries provide protection against sql special characters in a value breaking the sql query syntax (for all datatypes, not just strings), which is how sql injection is accomplished. They also eliminate a bunch of php code, dealing with making each different datatype safe and they also simplify the building of the sql query syntax, since you are no longer trying to put php variables into the query, and are just putting simple ? place-holders in for each value.
No. Again, the key to securing emulated prepared queries is to set the character set properly.
Lastly, only a true prepared query provides a performance benefit when executing the same query more than once. An emulated prepared query, since it builds the actual sql query statement internally and sends it to the database server at each ->execute() call (which also affects the error handling, since all errors are detected at the execute call), does not benefit from separating the parsing and planning the query from the execution of the query and it cannot make use of the binary transfer protocol to speed up the transfer of data values between php and the database server.
Yes, PDO::ATTR_EMULATE_PREPARES defaults to true. Presumably because not all databases supported by PDO support native prepared statements. Just set it to false for mysql and move on.
There is another more subtle reason (besides security) that emulates should not be used. With emulation on, integer database columns are returned as strings. Back when PDO was released this was not big deal but with today’s emphasis on strict typing, having integers be integers is nice.
One final slightly off-topic note. There is a PDO wrapper library called Doctrine Database Abstraction Layer (DBAL). The DBAL offers a number of useful tools such as query builders, schema detection and even allows sending arrays to IN clauses. If you are just starting out then I’d suggest considering using DBAL. It’s as close to standard as you can get.
