This is an archived copy of the forum for reference purposes

fDatabase and MySQL, why are prepared statement "disabled" ?

posted by cnicodeme 7 years ago

Hello!

Looking at the code, I saw that Flourish uses PDO for Mysql queries (at least for MySQL), but disable prepared statement (MYSQL_ATTR_DIRECT_QUERY to 1).

I'm aware that MySQL prepared statement were a "theoretical performance hit and source of annoyance" (http://stackoverflow.com/a/13239868/330867), but it seems to evolve in the good way now (quote: "Starting with MySQL 5.1.17, some but not all prepared statements can now use the query cache. The other minor concerns are still present.").

But in Flourish, queries generated with fDatabase, and managed through fStatement are simply used as a query storage. Even more, PDOStatement::bindParam/bindValue escape quotes which prevent some sql injections attacks, but Flourish don't do that at all : https://github.com/flourishlib/flourish-classes/blob/master/fStatement.php#L741

Why this choice ? Is it planned in the future to use prepared statement for MySQL ?

Thanks for the reply.

Flourish will use any of three database extensions for MySQL: mysqli, pdo_mysql and mysql. The mysql extension is old and "bad" in that it lacks features and has a pretty gross API.

For pdo_mysql, Flourish disables automatically translating all queries into prepared statements, which is what MYSQL_ATTR_DIRECT_QUERY does. This was a performance decision - things may have changed since then. You can always call fDatabase::getConnection() and change this attribute to 0 to see how it will affect your site.

Queries through fStatement do end up using real mysql prepared statements, if the extension on your server supports them. The mysql extension does not. mysqli and pdo_mysql do. fDatabase will pick the best one of those available on your server.

The line you referenced does not mean that values are not escaped to prevent against sql injection, instead it is showing that no extension-specific code is required for those listed database extensions. Since the mysql extension doesn't support real prepared statements, we just end up doing a regular query. If you look at https://github.com/flourishlib/flourish-classes/blob/master/fStatement.php#L349, you'll see that the fDatabase::escape() method is used when running the query, thus preventing SQL injection attacks.

posted by wbond 7 years ago

Ok! I was a bit confused about the "$type" and "$extensions" you use, differing PDO and MySQL. Now it's clearer.

Thanks :)

posted by cnicodeme 7 years ago