Bulletproof queries in WordPress – preventing SQL injections

Ernest Marcinko Blog, Tutorials, Wordpress Leave a Comment

When developing WordPress plugins, securing potential SQL injections is extremely important. With a well written SQL injection the attacker might gain access to your database, your blog back-end and steal your data, or make modifications you won’t even notice.

What is an SQL injection?

SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input.

Injected SQL commands can alter SQL statement and compromise the security of a web application.

Source: http://www.w3schools.com/sql/sql_injection.asp

The source of an SQL injection attack is almost always the incorrect or missing input sanitation. A correctly sanitized data will prevent any kind of known SQL injections, and WordPress even offers nice tools to make it easier for you.

Query parametrization – The recommended way

Latest versions of WordPress offer a so called prepare() statement to construct your queries safely. Before sending your query to the database, you run it through the prepare() function of the $wpdb object, passing the input variables as well. This method is very similar to php vsprintf(). Let’s look at an example:


$results = $wpdb->query( $wpdb->prepare(
  "SELECT * FROM $wpdb->posts WHERE
    post_title LIKE '%s' AND
    (ID BETWEEN %d AND %d)",
  $phrase,
  $id_low,
  $id_high
));

This query selects posts where the title matches the $prase and it’s ID is between $id_low and $id_high. Look how these variables are passed as parameters to the $wpdb->prepare(..) statement, rather than directly putting them to the query string.

The %s and the two %d variables in the query string will be replaced with the $phrase, $id_low and $id_high parameters in order. This technique is called query parametrization.

There are 3 different types of parameters you can use in the query:

  • %s – a string type
  • %d – an integer “whole” number
  • %f – a floating-point “real” number

If you can’t use Query parametrization

There are however rare cases, when you are unable to use the upper mentioned technique. The reasons are often very complex or dynamically generated queries, where you can hardly predict the correct number and order of parameters.

In this case we have to distinguish string and numeric values for maximum safety. String values are used between single quotes (‘) in the query, while numbers are not. Using the same escaping method for both may still lead to vulnerabilities. The query parameters will be escaped separately.

If you look up the WordPress codex about custom escaping methods, you will find like_escape() and $wpdb->esc_like() methods. The first one works on WordPress versions before 4.0 and the second after and with 4.0

Two functions I use

I’ve created two handy functions to use with escaping query parameters. One is for string escaping, second is for numeric values.

Both functions work with single values or arrays as well.


/**
 * Performs a full escape
 *
 * @param $string
 * @return array|mixed
 */
function x123safe_escape( $string ) {
    global $wpdb;

    // Recursively go through if it is an array
    if ( is_array($string) ) {
        foreach ($string as $k => $v) {
            $string[$k] = x123safe_escape($v);
        }
        return $string;
    }

    if ( is_float( $string ) )
        return $string;

    // Escape for 4.0 >=
    if ( method_exists( $wpdb, 'esc_like' ) )
        return $wpdb->esc_like( $string );

    // Escape support for WP < 4.0
    return like_escape($string);
}

..and the one to make sure a numeric value is passed:


/**
 * Converts a string to number, array of strings to array of numbers
 *
 * Since esc_like() does not escape numeric values, casting them is the easiest way to go
 *
 * @param $number string or array of strings
 * @return mixed number or array of numbers
 */
function x123force_numeric ( $number ) {
    if ( is_array($number) )
        foreach ($number as $k => $v)
            $number[$k] = $v + 0;
    else
        $number = $number + 0;

    return $number;
}

The numeric casting function is the more interesting. Since it is not known if the input parameter is a floating or integer (or maybe a string) value, we must either check and cast accordingly, or use this very simple trick – adding 0 to the parameter. By adding 0 it is automatically converted to the correct type – it does not matter what type it was before.

So the above example would look something like:


$string = x123safe_escape( $_POST['string'] );
$from   = x123force_numeric( $_POST['from'] );
$to     = x123force_numeric( $_POST['to'] );

$results = $wpdb->query( 
  "SELECT * FROM $wpdb->posts WHERE
    post_title LIKE '$string' AND
    (ID BETWEEN $from AND $to)"
);

Of course you can implement more accurate parameter validation methods based on your needs, but these two very basic functions can already prevent any known basic attack methods.

Sources

Leave a Reply

Your email address will not be published.