Useless Intern Useless Intern - 4 months ago 20
MySQL Question

What can I do to get actual prepared statements in Wordpress

My company wants to use WordPress for their Internet site and my main concern is the use of prepared statements. According to this, and I read the source code myself in disbelief, WordPress sanitizes string but is not prepared at the database.

Is there anything I can do to get the real thing? Or are my concerns unfounded?

Answer

History

Understand that the wpdb class, originally a fork of Justin Vincent's ezSQL library, was introduced way back in 2003, only a couple of months after the MySQL prepared statement protocol was introduced and more than a year before PHP had any API support for accessing it: WordPress thus did the only thing it then could, and required any variables to be concatenated directly into dynamic SQL.

Back then, ezSQL (and thus WordPress) used PHP's mysql_escape_string() function to escape such literals—and were therefore ignorant of the database connection's actual encoding (a threat that was only addressed in PHP itself just a few months earlier).

This remained WordPress's (only) defence against SQL injection for nearly two years, whereupon it switched to using (the even less safe) addslashes() for escaping—a situation that then remained in place for over four years until mysql_real_escape_string() was finally utilised (albeit wpdb::prepare() had been introduced in the interim)!

It then wasn't for another five years until WordPress even began using a driver capable of native statement preparation (i.e. MySQLi): this now just under two years ago and, as @naththedeveloper already pointed out, the discussion in ticket #21663 suggests that using native prepared statements may not even be on a to-do list.

Status Quo

Unfortunately, since the MySQLi object is a protected member of the wpdb class for which there's no getter (and is assigned internally during instantiation), there's no way that you can use native prepared statements with WordPress's database connection (at least, not without modifying/replacing wpdb with your own class).

Is there anything I can do to get the real thing?

You can, of course, open your own/separate connection to the database over which you have complete flexibility to do as you wish.

Or are my concerns unfounded?

As great as native statement preparation is (and I agree that it really should be the default defence strategy against SQL injection in any new project), it does also have its downsides. Careful escaping of one's variables for use as SQL literals (as done by PDO's prepared statement emulation, for example; and which is what wpdb::prepare() these days attempts to deliver) should be resistant to all known attacks...