WordPress and SQL Injection

An SQL injection vulnerability occurs when you use a variable in your SQL statement and someone uses the variable to add unexpected code.

You can read more about what an SQL Injection vulnerability is or you can jump to Preventing SQL Injection in WordPress.

Vulnerable to SQL Injection

If we created a line of SQL code using the variable $id

$sql = "SELECT display_name FROM wp_users WHERE id={$id}";

where our expectation is $id is an integer. Then our resulting SQL would look something like

$sql = "SELECT display_name FROM wp_users WHERE id=4";

SQL Injection Exploit

The classic SQL Injection Exploit is to add an additional SQL command to your variable. e.g.

$id = '4; DROP TABLE wp_users';

now our resulting SQL would be

$sql = "SELECT display_name FROM wp_users WHERE id=4; DROP TABLE wp_users";

If our database engine ran this, it would do two things:

1. Select the value we were looking for
2. Delete the wp_users database table

XKCD Comic About SQL Injection

Image from XKCD 327 Exploits of a Mom

This StackOverflow Answer does a nice job summarizing how this code works.

WordPress and SQL Injection

The good news is WordPress won’t let you combine two SQL commands into one line, which means a name like Robert'); DROP TABLE Students; -- won’t cause your tables to drop. However, there are other exploits that can be performed using this same idea. For example by taking advantage of a UNION clause, you can retrieve otherwise protected information.

Setting

$id = '4 UNION SELECT user_email as display_name from is_users LIMIT 100 OFFSET 1';

causes our resulting SQL to be

$sql = "SELECT display_name FROM wp_users WHERE id=4 UNION SELECT user_email as display_name from is_users LIMIT 100 OFFSET 1";

which would expose the email address of one of our users. Using this technique we could extract all of the information from the wp_users database table.

Other Resources

• SQL Vulnerable Footer an example WordPress plugin with an SQL Injection Vulnerability
• WordFence’s Understanding SQL Injection Attacks article has a great video exploiting a WordPress SQL Injection vulnerability using automated tools

Preventing SQL Injection in WordPress

Typically, the best way to secure your code from an SQL injection is by avoiding querying the database directly. Instead, WordPress provides us with a number of functions we can use, which have security precautions built in.

Avoid SQL Entirely

The following will echo the display_name for the user with ID $id. This code is not vulnerable to SQL injection.

$userdata = get_userdata( $id );
echo $userdata->display_name;

When You Need SQL

Sometimes we still need to run SQL commands directly. The basis of an SQL injection vulnerability is exploiting our variable by adding unexpected information to it. WordPress provides us with $wpdb->prepare() to ensure we are getting the information we want (and only the information we want).

In the following line we’ve replaced $id with %d indicating we want an integer.

"SELECT display_name FROM wp_users WHERE id=%d;"

The $wpdb->prepare() function will take this string and allow us to insert the value we want.

$sql = $wpdb->prepare(
    "SELECT display_name from {$wpdb->users} WHERE id=%d",
    $id
);

Placeholders

The $wpdb->prepare() function supports three placeholders

• %d integer (
• %f float
• %s string

The beauty of these placeholders is even if we use a placeholder like %s, when the substitution occurs it will be added in such a way as to prevent an SQL injection attack.

Tips to Avoid SQL Injection Vulnerabilities in WordPress

• Use WordPress functions instead of directly querying the database whenever possible
• If you still need to query the database, use $wpdb->prepare()

Image Credit

pixnio

Sal Ferrarello (@salcode)

Sal is a PHP developer with a focus on the WordPress platform. He is a conference speaker with a background including Piano Player, Radio DJ, Magician/Juggler, Beach Photographer, and High School Math Teacher. Sal can be found professionally at WebDevStudios, where he works as a senior backend engineer.