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

Ernest Marcinko

WordPress developer, javascript enthusiast, photoshop clicker and the founder of wp-dreams.com

Stay connected

Related Posts
Filter by
Post Page
Tutorials Wordpress Hosting Blog Testing Commercial Plugins Wordpress
Sort by

Saving space automatically – Beginners guide to Digital Ocean

After a while, you will notice an exponential growth of used space, without even copying anything to it. How
2016-05-06 09:24:05
admin

8

From XAMPP to Vagrant WordPress

2016-01-05 19:58:17
admin

8

Swap file – Beginners guide to Digital Ocean

If you reached this step, your server is up and running flawlessly. However after gaining some traffic it will run out
2015-01-21 15:48:02
admin

8

Mailing – Beginners guide to Digital Ocean

Sending mails properly is probably the hardest task to achieve with Digital Ocean. I will present two ways of doing
2015-01-20 21:38:53
admin

8

FTP & SFTP – Beginners guide to Digital Ocean

So far we have a running web server with a working database, but we don't have FTP access. SFTP However
2015-01-16 17:08:19
admin

8

Leave a Reply

Your email address will not be published. Required fields are marked *