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_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
NOT operators and also how to sort and order our data using
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.
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 —
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 to query data conditionally
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
A handy language feature is the ability to compound our
WHERE clauses using the
These keywords can also be combined and organised into logical combinations using parentheses where necessary:
Negating a condition using the
NOT operator basically gives you the opposite of a condition. For example:
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
_ 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.
% 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 beer brewer
Some examples of the wildcard in use include:
_ 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:
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
_ 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 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
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;
A note on
Note in the above example the use of
$wpdb->prefix — using this ensures your queries will work regardless of the database prefix on the current website. Always use
$wpdb->prefix in place of hard-code 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.
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
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:
%dwhen substituting integers
%fwhen substituting floating-point numbers
%swhen 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
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.
% 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
_ characters using
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:
- What if we have a direct need to use
_as literal characters in a hard-coded query?
- What if we have some user input that we need to use to lookup something like 90%?
- What if we are calling on a third-party function that returns meta keys containing
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() method specifically escapes
_ 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;