06 Aug

WordPress Database Interaction with $wpdb.

mysql

There are a number of ways to interact with the database when developing for WordPress. Wherever possible, you should use the many core WP functions for working with database data, for example: get_pages can return an array of pages that meet criteria you specify.

However sometimes you may need to deal with the db a little more directly to craft queries that go beyond the scope of the standard core WP functions. Unfortunately we still see WP plugin or theme developers doing this in ways that are vulnerable to SQL injections, and it only takes one security hole to put your entire site at risk.

PHP MySQL Options

The old mysql_* functions should never be used – not only is there no prepared statement support meaning you are relied upon to carry out all sanitizations (or risk SQL injection), but these functions are deprecated in newer versions of PHP.

PHP’s newer mysqli_* functions are a vast improvement, with support for prepared statements, multiple statements, transactions, debugging capabilities, and it’s OO (object orientated).

There’s also PDO (PHP Data Objects) – a database abstraction layer, which provides a consistent interface which can be used with a variety of DB drivers (MySQL, MS SQL, Firebird, PostgreSQL and others). This means your PHP application can be run on a wider variety of server configurations in a consistent manner.

Using any of the above options for database interactions in WordPress would see alot of work from the WordPress core developers gone to waste. Here is a little reference guide to get you on the right track to deal with db calls the recommended, safest way – using $wpdb.

Introducing $wpdb

The WordPress core features a WordPress Database Access Abstraction Object class called wpdb(), which is based on ezSQL by Justin Vincent.

Unless we’re wanting to deal with another database than the WP database, we don’t need to create a new instance of wpdb() as we can use the instance that is created by the WP core, $wpdb. Conveniently, it will usually be connected to the site’s db already by the time your plugin or theme code is executed.

Quick example showing use of WP’s $wpdb instance:

WP Table Names

The wpdb() class provides us with the table names of the WordPress database tables. This is useful as WP gives site administrators setting a table name prefix other than the default wp_* (eg the posts table may be named “wpsite2014_posts” rather than the default “wp_posts”), so WP developers should never hardcode table names into sql queries.

Here’s the list of table names available that we can reference in our code:

Prepared Queries with $wpdb

Let’s look at crafting a simple SELECT query to grab the id’s and titles of published posts by a specific author ID. The raw SQL query to do this may look something like:

Let’s prepare this query so that we use the proper table name (as the table name itself can vary depending on table prefix!) and sanitize the post_author ID to ensure only an integer is put into the query. We do this using wpdb::prepare, which also takes care of any escaping of the string to help avoid SQL injections.

You can see the $wpdb->prepare method has a 2nd parameter passed above, the value of the post_author ID we wish to search for. The method supports sprintf() like syntax for supplying parameters to go into the placeholders in a query string. You can have as many placeholders as you like within the query string, just pass additional parameters when calling the method.

The supported placeholder types are:
%d (integer)
%f (float)
%s (string)

Tip:  If your query string should have a % symbol within it (eg %LIKE%), you can escape those symbols using %%

SELECT Queries with $wpdb

The wpdb() class has a number of methods available to use when running SELECT queries. Whenever part of the query is a variable value, always use the prepare() method we covered above to make sure the query string is escaped and sanitized for safe usage. Let’s cover some of these methods:

$wpdb->get_var( ‘query’, column_offset, row_offset );
Retrieves a single value from a query at specified column and row offsets. As an example, lets grab the number (count) of published posts by author ID held in $authID. We will wrap the query string with the prepare() method to ensure the query is clean:

$wpdb->get_row(‘query’, output_type, row_offset);
Returns a whole row from a query at the specified offset. Output_type defaults to OBJECT, and row_offset defaults to 0, so these parameters are optional. If you’d rather work with an associative array, set output_type to the constant ARRAY_A.

As an example, let’s grab the title and post ID of the latest published post with post type held in $postType (eg “post” or “page”) by the author ID held in $authID:

You can see we introduced using an extra placeholder (for the post_type value) in the query string for the prepare() method, with it’s type defined as a string (%s).

$wpdb->get_results( ‘query’, output_type );
Fetches all results of a query (multiple rows if matched), and returns them as the defined output_type (defaults to OBJECT). If you prefer to work with associative arrays for the rows, you can specify output_type as ARRAY_K, with the rows returned in a numerically indexed array (eg $results[0][‘column_name’] ). Additionally, the row count of the query will be stored in $wpdb->num_rows.

This example shows using get_results to grab the titles of all published posts by author ID $authID, and we iterate over the results to echo out all found post titles:

INSERT Queries with $wpdb->insert

The wpdb() class has an insert() method for inserting a row into a table:
$wpdb->insert( $table, $data, $format );

$table = table name, such as $wpdb->posts
$data = associative array of unescaped values ( column_name => value, .. )
$format = array of data types for the values in $data, eg: array(‘%s’, ‘%d’). This is optional so if omitted, it will treat each value as type string. Possible values for format types are %s for string, %d for integers, %f for floats.

The insert() method returns false if it failed to insert the row, but if successful it will store the AUTO_INCREMENT ID of the newly inserted row in $wpdb->insert_id. There is no need to call prepare() method here, or to escape the values ourselves, as the insert() method does this for us.

Here’s an example in which we will insert a new row into the wp_options table, with the option name “peach_option” and the value set to a string held in $peachValue :

Replacing Rows with $wpdb->replace

What if we want to insert this wp_options row and delete any existing table rows for that option, to prevent row duplications for the option we’re saving? Then we use wpdb::replace().

$wpdb->replace( $table, $data, $format );
This method shares the same parameters as insert(), and the first item in the $data array should be the index for the row.
The method returns back the sum of the rows deleted and inserted, so 1 if the row didn’t exist previously but was inserted, and 2 or more if there were rows deleted and inserted, and false if a row wasn’t able to be deleted or inserted.

Here’s our previous insert() example reworked to use replace() instead, so if the option name “peach_option” is already saved, it is deleted first and then our new row inserted, preventing row duplication:

UPDATE Queries with $wpdb->update

Update a row (or multiple rows) that match a where clause, with escaping of values for the placeholders:

$wpdb->update( $table, $data, $where, $format = null, $where_format = null );
$table = name of the table to update
$data = data to update (mapped array column => value (unescaped))
$where = named array of WHERE clauses (in column => value pairs)
$format = array of formats to be mapped to each of the values in $data
$where_format = array of formats to be mapped to each of the values in $where.

Update returns the number of rows affected if successful, or false on error. As an example, let’s update the author ID of all posts belonging to the author ID held in $oldAuthor to the new ID held in $newAuthor:

DELETE Queries with $wpdb->delete

The delete method is much the same format as update(), and again returns the number of rows affected or false on error.

$wpdb->delete( $table, $where, $where_format = null );
$table = table name to delete from
$where = mapped array of where clause(s) to match row(s) for deletion
$where_format = array of value data types for the $where clauses

For an example, let’s delete from the wp_options table, any row(s) with the option_name set to ‘peach_option’:

Custom SQL Queries with $wpdb->query

Although the above wpdb() methods cover the majority of database interactions you’ll need, there will be times when you want to run queries which aren’t covered above. For example, creating or deleting a custom table in the WordPress database.

$wpdb->query( $sql );
This method doesn’t run any escaping, so you should call the prepare() method also on the $sql string to take care of any escaping / data sanitizing.

Returns the number of rows affected/selected, and false on error.

For an example, let’s create a new custom table, which is common for plugins to do upon plugin installation. We want to keep our table name in line with the rest of the table naming in the database, so for our table name we shall get the table name prefix for the site and append “peachy” at the end (eg “wp_peachy”).

The site’s table name prefix is stored in $wpdb->prefix.

Further info

I should note that for the update/replace/delete methods, the data should always be unescaped as those methods will do the escaping for you. So on that note, if you’re using data passed on from $_GET and $_POST, it may be escaped already so just call stripslashes() on that data to avoid additional slashes going into the database.

You can specify whether wpdb() errors should be echoed to the screen, by calling either of these functions:

Links for additional wpdb() info:

I hope you find this article insightful and help you write more secure scripts for WordPress.
We welcome your feedback below, thanks!

  • James Huckabone

    Thanks for the helpful article! FYI, there is a ($) dollar sign in front of your if statement in the success check code example. Peace.

    • Glad you found it helpful James, and appreciate the heads up – will fix it now, cheers :)

  • Raghu Chintakindi

    Excellent tutorial

  • Guillaume Lebret

    Great post. I’m not sure ARRAY_K is the good name : it is OBJECT_K or ARRAY_A no ?

  • Luke Cavanagh

    Thanks for sharing.