esc_sql Doh! WordPress SQL Injection Vulnerability

Update: This is not about a specific vulnerability, but a series of vulnerabilities due to trusting the use of a sanitizing function in a situation where it should not be used.

WordPress has a number of data sanitizing functions. esc_sql is one of them and it is frequently used, when used the way it was intended it performs perfectly. Unfortunately some of us developers assumed that esc_sql was magic and would sanitize anything related to SQL queries.

Fortunately the codex has been updated to make it more obvious.

Here is a screenshot of the previous codex page taken from the Internet Archive Wayback Machine

Here is the most recent version:

The function was designed to sanitize user data that is used within query strings of SQL statements. It basically escapes quotes in the string so user input cannot break out of the string.

So the string:

username’; DROP TABLES wp_users;

becomes:

username\’; DROP TABLES wp_users;

which makes for a terrible username, but SQL doesn’t execute the command: DROP TABLES wp_users;

D’oh

When the esc_sql function is used to sanitize data that is not inside quoted strings, it’s pretty much useless. Many developers (myself included) used and overlooked it’s use in this fashion. We look at the function name and assume that it is fine for sanitizing any data that is used in a SQL string. $wpdb->prepare should be used in almost every case unless it’s just not possible to do so.

Here is an excerpt from the Pods advisory that I helped with that shows the input and how it ends up getting passed to SQL even though esc_sql() was called on it.

For other examples of how this has been mis-used, check the advisory links below.

In almost all of the cases reported so far, this specific SQL Injection Attack is secured behind the admin interface because the orderby SQL command is used to sort list of things in those places and the admin page trusts the user enough to do no harm.

As always, an administrator should not click links from untrusted sources (or even trusted people). Use an account with the highest credentials as little as possible, you should be posting as a user with lower privileges, and only login as the highest level account when you need to do actual administrator type things.

First Contact

As far as I can tell Ryan Dewhurst of Dewhurst Security and the WPScan Team was the first to discover and document this. Since then numerous people have detected and reported other issues. At this time I have not heard of any of these vulnerabilities being used in the wild.

Plugins with known (and fixed) esc_sql issues

I’d like to keep this list up to date, I’ll add to it as I hear about more issues or send me a message @Pritect

Props to all of the developers for taking these issues seriously and getting fixes out asap. It’s easy to blow the severity of these issues out of proportion, so let’s try not to let it get out of hand.