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:
- Select the value we were looking for
- 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()
Leave a Reply