2.2: Understanding SQL Injection Attacks

Based on our analysis of 1599 WordPress plugin vulnerabilities reported over 14 months, SQL Injection vulnerabilities are the second most common vulnerabilities found in WordPress. If you’re able to avoid writing XSS and SQL injection vulnerabilities, you will have removed the risk of writing 65% of all vulnerabilities you might ever accidentally create.

It is worth investing the time in learning about SQL injection vulnerabilities and how to avoid them.

What is a SQL injection vulnerability?

Most useful WordPress plugins have some kind of interaction with the database. User input is frequently sent to the database, either because it needs to be stored in the DB, it needs to modify something in the DB, or because it is being used as part of a SELECT statement. If user input is not properly validated and escaped, an attacker can replace that user input with commands they can send directly to the database.

There are two kinds of SQL injection. A ‘classic’ SQL injection vulnerability is one where unfiltered user input lets an attacker send commands to the database and the output is sent back to the attacker. A ‘blind’ SQL injection vulnerability is when the attacker can send commands to the database but they don’t actually see the database output.

What is the impact of a SQL injection vulnerability?

In the following video, we create a WordPress plugin that contains a SQL injection vulnerability. Then we demonstrate how to attack our test website and exploit the vulnerability. We get a list of databases we have access to, view the tables in the database and download sensitive personally identifiable information (PII).

This is a clear demonstration of the devastating impact that SQLi vulnerabilities can have on a business. We encourage you to watch the video and pause where necessary to view source code or command details.

How a Classic SQL Injection Vulnerability Works

To understand how a classic SQL injection vulnerability works, lets look at a WordPress example:

global $wpdb;
$title = $wpdb->get_var("select post_title from " . $wpdb->posts . " where ID=" . $_GET['id']);
echo $title;

The above code is an example of a SQL injection (SQLi) vulnerability. It is an SQLi vulnerability because the user input in $_GET[‘id’] is sent directly to the database without sanitization or escaping. This allows an attacker to send commands directly to the database.

The database output is then sent directly back to the user’s browser. Because the output is sent to the browser, this makes the vulnerability a classic SQLi vulnerability, as opposed to a blind SQL injection vulnerability, which is discussed below.

Using this vulnerability an attacker can send commands directly to the database. These include SELECT commands to download your entire database including any user personally identifiable information (PII). In some cases it also includes INSERT and UPDATE commands to create new user accounts or modify existing user accounts.

To fix the above vulnerability is relatively easy. In WordPress you simply need to use the prepare method which will automatically sanitize and escape any data you send to the database. The above code can be modified as follows to remove the SQLi vulnerability:

global $wpdb;
$title = $wpdb->get_var($wpdb->prepare("select post_title from " . $wpdb->posts . " where ID=%d", $_GET['id']));
echo $title;

Note that we use the $wpdb->prepare() method to escape the data we are sending the database. It has a syntax that is similar to the sprintf() function which allows you to use placeholders. A %d is an integer, %f is a float (or decimal) and %s is a string (or text). If you are using %s as a placeholder, you don’t need to include quotes as these are added automatically.

How a Blind SQL Injection Works

A blind SQL injection vulnerability looks like the following:

global $wpdb;
$title = $wpdb->get_var("select post_title from " . $wpdb->posts . " where ID=" . $_GET['id']);
//Do something with title, but don't echo.

In the above example, raw unsanitized user input is sent directly to the database by concatenating the $_GET[‘id’] variable directly to the SQL query. To fix this vulnerability, you would simply use the prepare() method as above to sanitize and escape any database input.

The difference here is that the output is never sent to the browser. A blind SQLi vulnerability is just as serious as a regular SQLi vulnerability because an attacker can in some cases easily insert or update data in your database. The difference is that it becomes more difficult to extract data from the database because the attacker can’t see the output of the database because it is not written to the web browser.

Time based blind SQL attacks

There are generally two ways an attacker extracts data from a database using a blind SQL injection attack. The first is using a time based attack. Lets assume that, using the above SQLi vulnerability an attacker can send any command to the database, but they can’t see the output. They can only see the resulting web page.

An attacker might ask the database a question like “Does the first letter of the first admin account start with ‘a’? If it does, then sleep for 5 seconds and if it does not, don’t sleep at all. If it takes less than 5 seconds for the web page to be generated and return to the web browser, they know that the admin account does not start with the letter ‘a’ and they move on the the next letter, ‘b’ and ask the same question.

Using this technique, an attacker can launch a time based attack on a website and determine the names of admin accounts and they can extract hashed user passwords.

The actual SQL sent to the database might look like the following:

select post_title from wp_posts where ID=1 
  union select IF(
    substring(wp_users.user_login,1,1)='a', 
    BENCHMARK(5000000,ENCODE('blah','asdf')),
    null)
  from wp_users where ID=1

What this SQL says is “select the post_title where the post ID is 1, but merge in a query that will take a lot of time if we guess that the user account with ID 1 (which is usually an admin account) has the letter ‘a’ as the first letter of the username.”

When this query is run, if the page takes a long time to load, the attacker has correctly guessed the first letter of the admin username. They can then move on to letter two and three until they have your admin username. Once they have that, they can extract your admin hashed password, your admin email, any user email or any data they want, provided they take enough time to run the attack.

Remember, these attacks are automated and incorrect guesses take no time at all, so data can be extracted relatively quickly using this technique.

Content based blind SQL injection attacks

A content based blind SQL injection attack is another way for an attacker to extract data from a database when they can’t see the database output.

If the query generating the content is the following (remember, the query output is not sent to the user)

select post_status from wp_posts where ID=1

Lets assume that the value ‘1’ above is an unfiltered query parameter appended to the database query as in our above example. Thus an attacker can control all text after ‘ID=’.

An attacker can append the following to the query to verify that if they include a false condition, they will see unusual content generated:

select post_status from wp_posts where ID=1 and 1=2

1 is obviously not equal to 2 so in the above query the database will return an empty result set. The attacker will examine the resulting page and if it is a page with no content or an error message saying something like ‘no content’, they will know what a response from an empty query with a false condition looks like.

The attacker can then include something like the following:

select post_status from wp_posts where ID=1 
  and (select ID from wp_users where 
  user_login='admin' and ID=1)

The above query will be empty if the user in the database with ID 1 does not have a username of ‘admin’. It will however return the a non-empty normal result to the browser if the user with ID 1 does have a username of ‘admin’. Using this technique an attacker can extract data from a database by checking for non-empty and empty responses from the application.

Another example of a content based blind SQL injection query is:

select post_status from wp_posts where 
  ID=1 and (select 1 from wp_users where 
  substring(user_pass,1,1) = 'a' and ID=1)

The above query will check if the first letter of the hashed password for user with ID 1 is an ‘a’. Using this technique, an attacker can go through every character and extract the hashed password for admin accounts.

Conclusion

We have explained in detail how SQL injection vulnerabilities and blind SQL injection vulnerabilities work. The key to avoiding these vulnerabilities is to sanitize and escape anything you send to the database. In WordPress the easiest way to do this is by using the prepare() method and using placeholders in your SQL.

Now that you have an understanding of how attackers exploit these vulnerabilities, you will be able to better protect your own websites and those of your customers.