Skip to content
Patchstack Academy

SQL Injection (SQLi)

Introduction

This article covers ways to secure the code from SQL Injection vulnerability. This includes applying a proper function to check for the user’s input.

Learn more about SQL Injection

Prepared Statement

One of the most standard ways to secure code from possible SQL Injection is using a query-prepared statement. A prepared statement is a parameterized and reusable SQL query that forces the developer to write the SQL command and the user-provided data separately. The SQL command is executed safely, preventing SQL Injection vulnerabilities.

WordPress has a built-in function for a prepared statement, which uses wpdb::prepare function. The following placeholders can be used in the query string:

  • %d (integer)
  • %f (float)
  • %s (string)
  • %i (identifier, e.g. table/field names)

According to the official documentation, all placeholders MUST be left unquoted in the query string. A corresponding argument MUST be passed for each placeholder:

add_action("wp_ajax_fetch_post_count", "fetch_post_count");


function fetch_post_count(){
    global $wpdb;


    $search_title = wp_unslash($_GET["search_title"]);
    $search_content = wp_unslash($_GET["search_content"]);


    $query = $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts} WHERE `post_title` = %1$s AND `post_content` = %2$s AND `post_status`='publish'",
        $search_title,
        $search_content
    );


    $result = $wpdb->get_results($query);
    echo count($result);
}

esc_sql

This function could also be used as an alternative to the prepared statement. However, most of the time it’s better to use a prepared statement for the query execution. This function will escape data for use in a SQL query.

According to the official documentation, users must be careful when using this function. It will only escape values used in strings in the query. That is, it only provides escaping for values within quotes in the SQL (as in field = '{$escaped_value}'). If your value is not within quotes, your code will still be vulnerable to SQL injection:

ORDER BY {$escaped_value}

This implementation is vulnerable because the escaped value is not surrounded by quotes in the SQL query. As such, this function does not escape unquoted numeric values, field names, or SQL keywords. Additional note that this function was formerly just an alias for $wpdb->escape(), but that function has now been deprecated:

add_action("wp_ajax_fetch_post_by_title", "fetch_post_by_title");


function fetch_post_by_title(){
    global $wpdb;
    $titles = $_GET["search_title"];


    $in_clause = "('" . join(",'", array_map('esc_sql', $titles)) . "')"


    $res = $wpdb->get_results(
        "SELECT post_content FROM {$wpdb->posts} WHERE `post_title` IN $in_clause AND `post_status`='publish'"
    );


    echo json_encode($res);
}

sanitize_sql_orderby

We can use the sanitize_sql_orderby function for values in the ORDER-related query, especially on an order by clause. This function ensures a string is a valid SQL order by clause.

According to the official documentation, this function accepts one or more columns, with or without a sort order (ASC / DESC). e.g. column_1, column_1, column_2, column_1 ASC, column_2 DESC etc. It also accepts ‘RAND()’:

add_action("wp_ajax_fetch_post_by_order", "fetch_post_by_order");


function fetch_post_by_order(){
    global $wpdb;


    $allowed_orders    = array("ID", "post_content", "post_title");
    $query[]           = "SELECT post_content FROM {$wpdb->posts} WHERE `post_status`='publish'";
    $orderby           = in_array( $_GET["orderby"], $allowed_orders, true ) ? $_GET["orderby"] : 'ID';
    $order             = 'DESC' === strtoupper( $_GET["order"] ) ? 'DESC' : 'ASC';
    $orderby_sql       = sanitize_sql_orderby( "{$orderby} {$order}" );
    $query[]           = "ORDER BY {$orderby_sql}";


    $res = $wpdb->get_results( implode( ' ', $query ) );


    echo json_encode($res);
}

Contributors

rafiem