Patchstack Weekly, Week 06: Preparing for SQL Injection

Welcome back to the Patchstack Weekly security update! This update is for week 6 of 2022.

Last week, two high severity vulnerabilities were patched by the developers of WP Spell Check and Revolut Gateway for WooCommerce.

Both of these plugins patched unauthenticated SQL injection vulnerabilities, so that will be the topic of this week’s knowledge share.

Vulnerability news

The WP Spell Check plugin and Revolut Gateway for WooCommerce plugin developers both patched SQL injection vulnerabilities this week.

Users should update to the most recent release of these plugins as soon as possible, as attackers would require no authentication to perform this attack.

Reviewing the patches on both of these plugins, the developers did a great job and used the same defensive coding method of preparing the SQL query before sending it to the SQL server. This could use a little more explanation though, so protecting against SQL injection attacks will be the topic for this week’s knowledge share.

Weekly knowledge about SQL injection

Let’s learn how to code defensively and prevent SQL injection bugs in code this week. Luckily it is simple, you just need to prepare the query before sending it to the SQL server. But, we need to know what SQL is in order to learn how to prepare a query.

What is SQL?

If a website or app interacts with a database, that interaction is done using a language called “SQL”, sometimes pronounced “Sequel” but it is an acronym SQL which stands for “Structured Query Language”.

This is a boring name and leads to the entirely acceptable and repeatedly repetitive term “SQL Query” which when expanded is “Structured Query Language Query” … anyways, I’m off track.

Most websites rely on a database at some point, and many websites use user-supplied inputs in the SQL queries they send. So, developers should learn how to code defensively and secure their applications using prepared statements.

What are prepared statements?

When writing code that will retrieve data from the database, you will inevitably be building a string that will be sent to the database server this is called the “query string”.

Preparing the query string before sending it, ensures that any variables used in it are properly sanitized, formatted, and will not result in executing additional SQL commands. If you are developing a WordPress plugin or theme, and wish to code defensively and securely.

The function you will use to prepare your SQL query string is called “prepare”. The prepare function is part of the global $wpdb class and is well documented in the WordPress developer guides.

I will give a short and simple explanation of $wpdb->prepare for you here though. The prepare function accepts a string and set of variables, within the string you will use placeholders, like %d, to ensure “this variable should always be an integer” and bail out if not.

You can also use another placeholder %s for strings. With the string placeholder, $wpdb->prepare will make sure to escape any special characters in the string which may interfere with or make the SQL query behave unexpectedly. It is a good rule of thumb to always prepare your SQL query strings before sending them to the database, but knowing how SQL injection attacks work will help this make a lot more sense.

What causes SQL injection?

When code sends a query string to the database with an un-escaped user-supplied variable in it, that variable could manipulate the SQL query string to do really unexpected things. Let me try to explain through an example.

Let’s say we have a query that checks if a value is stored in the WordPress options table. For some reason, we’re allowing the user to select which option we’re looking up because it is tied to a unique ID the user knows.

The insecure SQL query code would look a little like this:

global $wpdb;
$results = $wpdb->get_results("SELECT option_value from wp_options where option_name = 'user_options_id_".$_POST[user_id].""'");

This query will return the option_value for the option specified in option_name and store that in $results. In normal circumstances, the POST user_id value would be an integer and create a properly formatted query string like

SELECT option_value from wp_options where option_name = 'user_options_id_1234'

And the database will respond with:

+--------------+
| option_value |
+--------------+
| 1            |
+--------------+

But, since the POST user_id value is a user-controlled variable and this statement is not prepared. The browser can append more than just a number into that SQL query. The browser can even over-write the expected response with a UNION statement. This is done by simply setting the POST user_id variable to “1234blah’ UNION SELECT 0 as option_value”.

What the DB server sees with an SQLi attack

SELECT option_value from wp_options where option_name = 'user_options_id_1234blah' UNION SELECT 0 as option_value

And the database will respond with:

+--------------+
| option_value |
+--------------+
| 0            |
+--------------+

It only responds with one result because the option name ending with 1234blah does not exist, and so the only row returned is from the UNION SELECT statement.

The fix for this, is to remember to use $wpdb->prepare.

global $wpdb;
$results = $wpdb->get_results($wpdb->prepare("SELECT option_value from wp_options where option_name = 'user_options_id_%d'", $_POST[user_id]));

This is only one short and simple explanation of SQL injection attack and defense, but I hope it was helpful.

There are many, many ways to alter a program’s logic via manipulating SQL queries, however, all the developer needs to remember is that the fix is easy: always prepare your SQL query strings before sending them to the database server.

Thanks and appreciation

This week’s thanks go out to the developers of WP Spell Check and Revolut Gateway for WooCommerce plugins for their patches addressing SQL injection risks. And for the inspiration for the weekly knowledge share.

Additional thanks are due to the WordPress core developers who worked on and implemented the prepare function as part of WordPress core. This function puts secure code at the fingertips of every WordPress developer, they just need to know it’s there and how to use it.

I will be back next week with more security tips, tricks, opinions, and news on the Patchstack Weekly security update.