A beginner’s guide to using SQL to query the WordPress database

There aren’t too many occasions in my career as a web developer where I’ve needed to query the core WordPress database tables directly using SQL. For the most part, WordPress’ various query mechanisms such as WP_Query, WP_Term_Query, WP_User_Query, etc., save us the need to write our own SQL statements.

You may, however, be faced with situations where you need to write your own SQL in order to get the data you need — perhaps it’s more performant to use a custom SQL query or perhaps you are dealing with custom database tables, as may well be the case if you are using our ACF Custom Database Tables plugin to organise your Advanced Custom Fields data.

The following information is presented as a beginner’s guide to writing SQL within the context of WordPress to query data. It’s not an extensive resource but will provide you with a good foundation to writing your own queries to suit your application’s requirements. Note that this is aimed at fetching data from the database so if you are looking to update your tables via SQL statements, you should refer to WordPress.org’s wpdb documentation which provides some example queries for tasks such as: inserting new rows, replacing existing rows, updating existing rows, and deleting existing rows.

A note on the structure of this article

We are going to start off with a brief rundown of the most common SQL statements you might use to retrieve data from your database — this section will be raw SQL and won’t run in PHP by itself. We’ll then move on to using WordPress’ built in database access class to execute SQL statements and then dive into how to ensure your SQL statements remain safe from SQL injection attacks.

All of the code examples you’ll see in the first section don’t have a PHP component as they are just SQL statements. To run them you either need to fire up MySQL on your command line or open up an SQL command area in a database client such as TablePlus, Sequel Pro, MySQL Workbench, or phpMyAdmin.

WARNING! Much danger…

Please avoid executing SQL statements immediately against your production database. You can do a lot of damage in that environment so I can’t stress this enough — always develop/test in a non-production environment.

Section 1: Basic SQL statements

We’re going to take a quick look at some simple SQL statements for selecting data from custom database tables. As this is a beginner’s guide, we’re just going to look at the use cases that will apply to those of you using the ACF Custom Database Tables.

We’ll start by looking at basic SELECT queries then take a look at using the SQL LIKE operator for matching patterns (string starts with, ends with, contains, etc). We’ll touch on using WHERE clauses in conjunction with AND, OR and NOT operators and also how to sort and order our data using ORDER BY, ASC, and DESC.

At this stage, I won’t dive into joins or other topics beyond what I’ve mentioned here as there is plenty of information available online to show you how to take your basic SQL skills further — W3Schools is a great place to start. I really just want to get you familiar with the basics and get you started. Should enough people ask, however, I’ll add more to the article.

Basic SELECT statements

The simplest SQL SELECT query is to simply return all data rows from a desired table as follows:

It’s OK, but it does return absolutely everything which isn’t ideal. You can take this a little further and select only specific columns. e.g;

This adds specificity in the data we wish to see in our result-set. i.e; instead of returning all columns for all rows, MySQL only returns three columns — name, profession, and hourly_rate — from the table. We are still selecting all rows at this stage which generally isn’t our goal so let’s look at how to control which rows are returned using a WHERE clause.

Using the WHERE clause to query data conditionally

The WHERE clause allows us to limit the rows of data that are returned to only those matching a specific set of conditions. A simple example could look as follows:

In the above example, we will get all columns but only of rows where the hourly_rate column is 90. Let’s take a look at another example matching a string:

Notice that when working with strings, we need to delineate the string with quotes. These are very basic examples of data matching an exact value but there are a few other handy operators you will likely want to use at some point. Let’s say, for example, you need to find all rows matching a few possible values:

In the above example, we’ll get any rows where the profession column is either of the specified values. Moving on, we can also use comparison operators to match various requirements. e.g;

We can even use the BETWEEN operator to select rows with a value that fits within a given range:

Multiple conditions using the AND & OR operators

A handy language feature is the ability to compound our WHERE clauses using the AND and OR keywords:

These keywords can also be combined and organised into logical combinations using parentheses where necessary:

Negating a condition using the NOT operator

The NOT operator basically gives you the opposite of a condition. For example:

Using LIKE and ‘wildcards’ to match patterns

You are likely to come up against situations where you need to find all rows where a column either starts with, ends with, or contains a piece of text. For this situation, you can use the LIKE operator which we’ll touch on a little further down. First, we should get familiar with the % and _ symbols which can be used to match variable values — these symbols are called ‘wildcards’ and can match zero, one, or multiple characters of any type.

The % wildcard

The % character will match zero, one, or multiple of any characters so if your match pattern was draft%, the query would return rows where the column’s value contains:

• draft
• draftsperson
• draftswoman
• draftsman
• draft beer brewer
• draftfkerjsdofgsdfgzsdfgsdfg

Some examples of the wildcard in use include:

The _ wildcard

The _ wildcard is similar to the % wildcard but instead will only match a single character. So, if your match pattern was arch_tect, the query would return rows where the column’s value contains:

• architect
• archatect
• arch1tect
• archotect

Note that that the pattern will not match empty or multiple characters. e.g; archtect and archiitect.

Some examples of the wildcard in use include:

A slightly more complicated LIKE operator example

Now that we understand the % and _ wildcards, we can use them in conjunction with the LIKE operator inside a WHERE clause. In the previous code blocks we’ve actually looked at some basic statements using LIKE so let’s take a look at an example that is a little bit more complex:

Note that in the example above, we’ve used two LIKE conditions, each with the % wildcard combined with an OR operator. Also note that we’ve spread the statement over multiple lines and used indents to make it a little more readable — this is totally acceptable when writing your SQL and I recommend it because it will make your SQL code so much easier to read.

Sorting the results using ORDER BY

Using the ORDER BY keyword, we can also sort our result-sets in ascending or descending order. An example of sorting a result-set in ascending order is as follows:

Be aware that ORDER BY will sort records in ascending order by default so the above can actually be shortened slightly to the same effect by removing the ASC keyword. e.g;

If you need to sort in descending order, simply use the DESC keyword. e.g;

It is also possible to sort by multiple columns. When doing so, the result-set will be sorted by the first column and where there are multiple rows with the same value in the first column, they’ll then be further sorted by the second column and so on. Sorting by multiple columns is done as so:

By adding ASC or DESC, we can control the sort order for each column. e.g;

Section 2: Executing SQL using wpdb — the core database access class in WordPress

The wpdb class is WordPress’ built-in database abstraction class. It’s used to access the database throughout WordPress and provides developers with convenient methods for preparing raw SQL queries for safe execution.

Fortunately, WordPress gives us a readily available instance of the wpdb class via the global variable $wpdb. By declaring the $wpdb variable as global in you own functions, method, and scripts, you gain access to the same instance used by WordPress. e.g;

What is the $wpdb->prefix?

Note in the above example the use of $wpdb->prefix — this is a common prefix used for all core database tables in WordPress as well as any custom DB tables that use it to build the full table name. Generally, it’s wise to use a database table prefix in WordPress as it will help mitigate compatibility issues with plugins and can obfuscate your full table names making it hard for attackers to inject functional SQL queries when attempting to attack your site. It also helps to keep separation between websites if the same database is used for multiple sites, provided the prefix is unique. This is actually how WordPress multi-site maintains many sub sites and the core site in one database.

Using it ensures your queries will work regardless of the database prefix on the current website. Always use $wpdb->prefix in place of hard-coding the wp_ default table prefix as it is very common for the default prefix to change between sites. If you assume the table prefix will always be wp_ and release a plugin that has database queries, you might be in for a bit of support shock shortly after people start installing your plugin.

Dynamic data is a risk — handle with care

If you are writing your own SQL, it is highly likely your SQL statements will depend on data provided either through user input — i.e; a form — or as the result of executing some PHP code. In this situation, the value you are going to be using is dynamic.

Dynamic data is often unpredictable and should generally be considered unsafe and in need of preparation before being executed by MySQL. With this in mind, we’ll look at exactly how to handle user input data in a safe way that protects your queries from SQL injection attacks.

Protecting against SQL injection attacks

The main risk with dynamic data is that your application could be vulnerable to SQL injection; a technique used to inject malicious SQL statements into existing SQL code. At its simplest, a hacker could enter some SQL code into a form that, when submitted, has the potential to modify any associated SQL statements that process the form input.

The goal of such an attack is to modify the intent of a database query, often maliciously, as a means to gain access to private or sensitive information, take control of a web application, or perhaps even delete entire databases!

Have a read of the OWASP definition of SQL Injection:

“A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.”

— OWASP.org

We can protect against SQL injection attacks through the use of prepared statements which utilise query parameterization is a means of preparing input data for safe use in an SQL query. Correctly prepared statements remove the attacker’s ability to modify the intent of an SQL query which means your queries will do only what you wrote them to do.

If it all sounds a bit heavy, that’s OK — it was a head-bender for me for a while as well. Keep at it and it will all make sense as you gain experience.

Thankfully, WordPress’ wpdb class has a very convenient wpdb::prepare() method which will handle escaping for us and protect our queries against SQL injection attacks. It is well worth your time getting familiar with the wpdb::prepare() method as it can save you all sorts of trouble.

Preparing SQL statements using the wpdb::prepare() method

Using syntax very much like PHP’s sprintf() function, the method will accept (as function arguments) an SQL statement containing any number of data placeholders, along with a matching number of values to escape and substitute into the given statement. The placeholders to know are as follows:

• Use %d when substituting integers
• Use %f when substituting floating-point numbers
• Use %s when substituting strings

Here’s a simple example of preparing an SQL statement using one parameter. Note the use of %s to indicate where a string is to be substituted:

Now consider an example where two dynamic values are needed within an SQL statement. Note that in this example, the prepare() method just continues to accept more variables as function arguments.

Just so you know, it’s also possible to pass the variables along to the method as an array, should you need to. Here’s the same example above, but using an array to pass in the data:

An ‘unsafe’ example

The following example may seem fairly innocuous at first glance but given the basic information we know at this point, we can assume that this query is vulnerable to SQL injection through the $value argument.

Making it safe by preparing your statement

Let’s utilise the wpdb::prepare() method to protect the same SQL statement against injection attacks. Here we are passing the variable data to the method separately so the wpdb object can escape the data as needed before merging it into the SQL statement.

If you take a dive into the source code, you’ll see that the wpdb class is using PHP’s mysql_real_escape_string() to escape special characters and vsprintf() to handle the replacements. There’s more going on of course, and it’s almost always going to be wiser to use this approach that to roll your own, but it doesn’t hurt to get familiar with the tools you are using.

Using the % wildcard in prepared statements

When using percentage wildcards in prepared statements, we need to change things up a little bit as we don’t want to confuse wpdb::prepare() with wildcards that it misinterprets as query parameters. If, for example, wpdb::prepare() sees two % symbols in the SQL statment, it’ll be expecting two pieces of data to prepare and substitute into the query. Consider the following example:

In the example above, the query will fail and you’ll get a PHP notice along the lines of the following:

PHP Notice: wpdb::prepare was called <strong>incorrectly</strong>. The query does not contain the correct number of placeholders (2) for the number of arguments passed (1).

This happens because the string we’re using with our second LIKE operator contains %s. We’re using this with the intention of getting anything that ends with ‘some_string’ but the wpdb::prepare() method is interpreting this is a second parameter which it doesn’t have data for. The better way to handle this is to move all strings containing % wildcards into data values. e.g;

This example will save us from the error as WordPress will now have the exact number of data values to substitute into recognized parameters — in this case, 2.

Special consideration for literal % and _ characters using wpdb::esc_like()

As we’ve seen in the previous section, the % wildcard can sometimes be misinterpreted as a query parameter resulting in errors with our queries. This we fixed by moving the wildcard-containing strings to data variables for substitution.

When using the LIKE operator, we may find ourselves in situations where we may well have wildcard characters used literally and we want to avoid them being read as wildcards. Consider the following what-ifs:

1. What if we have a direct need to use % or _ as literal characters in a hard-coded query?
2. What if we have some user input that we need to use to lookup something like 90%?
3. What if we are calling on a third-party function that returns meta keys containing _ characters?

When substituting these values into a paramaterized statement, wpdb::prepare() will see these as wildcards and treat them as such. Consider the following:

The above query would actually return any row where column_a starts with any single character followed by the word value and then another single character. It would match, for example, avaluezwhatever which isn’t what we would be wanting in this case. What we are really after is anything that literally starts with ‘_value_’. To get the result we are after, we can use another method in the wpdb class — wpdb::esc_like(). e.g;

The wpdb->esc_like() method specifically escapes % and _ characters so they can be used as string literals in the query.

In the situation where you are combining variable data with wildcards, you can simply use concatenation to achieve the result you need. e.g;

Further reading & resources

• How to use ACF Custom Database Tables data with WP_Query objects
• Documentation for the wpdb class
• OWASP’s guide to SQL Injection
• W3Schools’ guide to SQL Injection
• Documentation for the mysqli_real_escape_string() function
• Documentation for the vsprintf() function