Escaping ORDER BY clause using mysqli_real_escape_string() function

Question

I am using different values in ORDER BY clause of SQL queries based upon user selection. How do I escape this selected value using mysqli_real_escape_string() function?

For example, the url is as following:

localhost/person/person_listing.php?sort_by=date_of_birth

Based on this I am using:

if (isset($_GET['sort_by'])) {
    $sort_by = trim($_GET['sort_by']);
    if (!empty($sort_by)) {
        $order_by_sql = " ORDER BY $sort_by";
    }
}

The question is, what is the best way to escape this type of add-on to SQL? Can the entire ORDER BY clause be escaped at once, or each value has to be escaped individually?

score 0 · Accepted Answer · answered Jan 09 '18 at 08:45

The best way to do this would be to use a prepared statement. Your code would look kind of as follows: (grabbed from here.

Basically, you add a question mark wherever you have a variable you would want to pass. And then you pass it with the mysqli_stmt_bind_param function. ss here means that you want to pass 2 strings.

if ($stmt = mysqli_prepare($link, "SELECT * FROM users WHERE Name=? ORDER BY ?")) {

    /* bind parameters for markers */
    mysqli_stmt_bind_param($stmt, "ss", $name, $sort_by);
}

The code has to be altered everywhere then. Thanks anyway. – head_scratcher Jan 10 '18 at 08:46 — head_scratcher, Jan 10 '18 at 08:46

Escaping ORDER BY clause using mysqli_real_escape_string() function

1 Answers1