Database Security ($wpdb)

We’ve already covered XSS (sanitizing and escaping) and CSRF (nonces). It’s time we tackle the third type of vulnerabilities mentioned at the beginning of this chapter – SQL injections.

The first and most important rule of keeping your database secure against SQL injections is to use built-in WordPress functions if you can. Don’t write a query to update a meta value, use update_post_meta(). Don’t write a query to read an option, use get_option(). Don’t write a query to get posts, use WP_Query. Most core WordPress functions are safe and handle sanitizing for you.

That being said, there are legitimate situations where you have to write your queries by hand, e.g., if you use custom tables or need maximum performance. To do that, you should use the global $wpdb object. It’s an object of the wpdb class (yes, the name of the class is the same as the object).

wpdb is WordPress’s abstraction over a database connection. It has many useful methods which we’ll talk about soon. Every wpdb object (you can instantiate your own) is connected to exactly one database. The global $wpdb object is a connection to the main database configured in wp-config.php. By the way, this object is used internally by WP_Query to query the database.

Most methods in the wpdb class expect an SQL query as a string. Some of these methods are:

• query() – a general “run this query” method (returns the number of affected rows).
• get_results() – a general “run this query and return the results” method.
• get_var() – return just one value.
• get_row() – return a selected row from the query.
• and more…

You can hard-code these queries and they will be safe. The problem starts when you need to include user-provided data. You should remember the danger of that from our discussion on SQL injections. The user passing “abc;DROP TABLE wp_posts” would drop your posts table if you just concatenated the data with your query.

To make the query secure, you need to escape the untrusted data. In WordPress (and PHP), this process is called “preparing” the query.  Here’s how you do it with wpdb:

$prepared = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE ID = %d", $post_id );

$prepared = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE ID = %d", $post_id );

As you can see, you write the query using sprintf-like syntax (with placeholders, like %s, %d, and %f). You then pass the data you want escaped as arguments. They are then inserted in place of those placeholders.

The prepare() method makes sure the data is in the correct format and that it doesn’t break out of the current query with a rogue semicolon. If you used %d but passed in 1.1 (a float), the escaped value will be 1 (an int) and will not be wrapped in quotes. If you use %s (a string), the value in the final query will be wrapped in quotes. All unsafe characters in the variables will be properly escaped. This helps ensure data validity and prevents SQL injections.

It’s important to remember that prepare() returns the prepared query. In our case, the query is assigned to the $prepared variable. It’s just a string you pass to the method you’re calling. By the way, the $wpdb->posts property is the name of the posts table with the correct table prefix (configured when installing WordPress).

Sometimes you may need to create a query where you don’t know the number of variables. Like a query with an IN clause and a user-provided list of IDs. Thankfully, prepare() lets you pass an array as the second argument. Note that, in this case, you have to dynamically create the query with a correct number of placeholders (equal to the number of values in your array) and pass this query as the first argument to prepare().

One more method you might find useful is esc_like(). It’s used for escaping the contents passed to the LIKE clause. It’s job is escaping the percentage sign (%) and underscore (%) so that they don’t get interpreted as wildcards but as the content. In reality, this function just adds a backslash (\) before them. Use it on the value of the like (without the wildcards) before you pass it through prepare(), like so:

$search = '43% of planets'; // This might be what a user inputs in a search
$like = '%' . $wpdb->esc_like( $search ) . '%';
$sql = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_title LIKE %s", $like );

$search = '43% of planets'; // This might be what a user inputs in a search
$like = '%' . $wpdb->esc_like( $search ) . '%';
$sql = $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_title LIKE %s", $like );

$wpdb->esc_like( $search ) will return “43\% of planets”. If you didn’t do that, your actual LIKE clause would be: “%43% of planets%”. See the problem? This query would return all posts whose post_title contains “43”, not “43% of planets”, because the first percentage sign is interpreted as a wildcard (assuming this query won’t crash, which it probably will).