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_QueryWP_Term_QueryWP_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 rowsreplacing existing rowsupdating 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 TablePlusSequel ProMySQL 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 ANDOR and NOT operators and also how to sort and order our data using ORDER BYASC, 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:

SELECT * FROM some_table_name;

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;

SELECT name, profession, hourly_rate FROM some_table_name;

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 — nameprofession, 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:

SELECT * FROM some_table_name WHERE hourly_rate = 90;

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:

SELECT * FROM some_table_name WHERE profession = 'architect';

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:

SELECT * FROM some_table_name WHERE profession IN ('architect', 'draftsperson', 'builder');

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;

# Find rows with hourly_rate less than 100.
SELECT * FROM some_table_name WHERE hourly_rate < 100;
# Find rows with hourly_rate less than or equal to 100.
SELECT * FROM some_table_name WHERE hourly_rate <= 100;
# Find rows with hourly_rate greater than 50.
SELECT * FROM some_table_name WHERE hourly_rate > 50;
# Find rows with hourly_rate greater than or equal to 50.
SELECT * FROM some_table_name WHERE hourly_rate >= 50;
# Find rows with hourly_rate not equal to 50.
SELECT * FROM some_table_name WHERE hourly_rate <> 50;

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

# Find rows with an hourly rate from 60 to 120.
SELECT * FROM some_table_name WHERE hourly_rate BETWEEN 60 AND 120;

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:

SELECT * FROM some_table_name WHERE profession = 'architect' AND hourly_rate < 100;
SELECT * FROM some_table_name WHERE profession = 'architect' OR hourly_rate > 100;

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

SELECT * FROM some_table_name WHERE profession = 'builder' AND (hourly_rate < 60 OR hourly_rate > 80);

Negating a condition using the NOT operator

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

# Get all rows where the profession is not 'architect'.
SELECT * FROM some_table_name WHERE NOT profession = 'architect';
# Get all rows where the profession is neither 'architect' or 'builder';
SELECT * FROM some_table_name WHERE NOT profession = 'architect' AND NOT profession = 'builder';
# Note, the previous example could also be written as:
SELECT * FROM some_table_name WHERE NOT profession IN ('architect', 'builder');

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:

# Get all rows where the profession begins with 'draft';
SELECT * FROM some_table_name WHERE profession LIKE 'draft%';
# Get all rows where the profession ends with 'draft';
SELECT * FROM some_table_name WHERE profession LIKE '%draft';
# Get all rows where the profession contains 'draft';
SELECT * FROM some_table_name WHERE profession LIKE '%draft%';
# Get all rows where the profession begins with 'dr' and ends with 'aft';
SELECT * FROM some_table_name WHERE profession LIKE 'dr%aft';

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:

# Get all rows where the profession is like 'architect' but the 'i' can be any character;
SELECT * FROM some_table_name WHERE profession LIKE 'arch_tect';
# Get all rows where the profession is like 'architect' but begins with any character;
SELECT * FROM some_table_name WHERE profession LIKE '_architect';
# Get all rows where the profession is like 'architect' but ends with any character;
SELECT * FROM some_table_name WHERE profession LIKE 'architect_';
# Get all rows where the profession is like 'architect' but both the 'i' and 'e' can be any character;
SELECT * FROM some_table_name WHERE profession LIKE 'arch_t_ct';

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:

# In English: Get all rows where profession ends in either 'ologist' or 'iatrist' and the hourly rate is less than 200.
SELECT * FROM some_table_name
WHERE (profession LIKE '%ologist' OR profession LIKE '%iatrist')
AND hourly_rate < 200;

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:

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate ASC;

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;

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate;

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

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate DESC;

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:

# First, sort by profession then by hourly rate, both in ASC order.
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY profession, hourly_rate;

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

# First, sort by profession in descending order, then hourly_rate in ascending order, and then finally name in descending order.
SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY profession DESC, hourly_rate ASC, name DESC;

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;

<?php
function get_some_data(){
global $wpdb;
return $wpdb->get_results("SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = 'some value'");
}

A note on $wpdb->prefix

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.

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:

<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = %s", 'some user submitted string' );

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.

<?php
global $wpdb;
// Pretend these values have been submitted by a user or returned from a function.
$int_val = 1;
$float_val = 3.14159;
$prepared_sql = $wpdb->prepare(
"SELECT * FROM {$wpdb->prefix}some_table WHERE some_column BETWEEN %d AND %f",
$int_val,
$float_val
);

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:

<?php
global $wpdb;
// Pretend these values have been submitted by a user or returned from a function.
$int_val = 1;
$float_val = 3.14159;
$prepared_sql = $wpdb->prepare(
"SELECT * FROM {$wpdb->prefix}some_table WHERE some_column BETWEEN %d AND %f", [
$int_val,
$float_val,
]
);

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.

<?php
function get_some_data( $value ){
global $wpdb;
return $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = '{$value}'" );
}

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.

<?php
function get_some_data( $value ){
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = %s", $value );
return $wpdb->get_results( $sql );
}

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:

<?php
global $wpdb;
$value = 'The value to substitue';
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s AND column_b LIKE '%some_string'", $value );

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;

<?php
global $wpdb;
$value = 'The value to substitue';
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s AND column_b LIKE %s", $value, '%some_string' );

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:

<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", '_value_%' );

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;

<?php
global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", $wpdb->esc_like('_value_%') );

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;

<?php
global $wpdb;
// The $dynamic_input might be coming in from a form or may be the result of a function call.
$value = '%' . $wpdb->esc_like( $dynamic_input ) . '%';
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", $value );

Further reading & resources

About the author

Phil Kurth is a web developer living in Melbourne, Australia. Phil has a long history of WordPress development and enjoys building tools to empower others in their web design/development practice.

When not working with the web, Phil is usually spending time with his two young sons or is out hiking through the Australian bush.

Good dev stuff, delivered.

Product news, tips, and other cool things we make.

We never share your data. Read our Privacy Policy

© 2021 Hookturn Digital. All rights reserved.