CODEWITHSUNDEEP
phpmysqlprepared-statement
Klipp Ohei
Klipp Ohei

Reputation: 385

ORDER BY with prepared statements

I am currently working on a script which can sort a database table by the users input.

So, a user can choose a column (via selectbox) and then the whole mysql table should be displayed with the order of the selectbox value.

My current code ($order is the user input)

Sorry for this formatting, im on a mobile device :/

$order = "user_id"; //user input

$sql = $db->prepare("SELECT user_id, username, realname, auth_lvl, usercolor, activated, ban FROM users ORDER BY ?");

$sql->bind_param('s', $order);
$sql->execute();
$sql->bind_result($id, $username, $realname, $auth_lvl, $usercolor, $activated, $ban);
$sql->store_result();

while ($sql->fetch()) {
    //echo...
}

The problem

It doesn't matter what $order (ORDER BY part) is, I always get this order of ids: 3, 4, 5, 30, 29

Upvotes: 0

Views: 70

Answers (1)

Anonymous
Anonymous

Reputation: 12090

If the input parameter is 'foo', then your SQL is equivalent to

SELECT user_id, username, realname, auth_lvl, usercolor, activated, ban 
FROM users
ORDER BY 'foo'

Which of course means that the rows can be in any order. You cannot use parameters for things like order by clauses and table names. In this case you would need to actually prefix the value of the column to the string.

$q = 'SELECT user_id, username, realname, auth_lvl, usercolor, activated, ban FROM users ORDER BY ' . $order;

This is obviously prone to SQL injections, so you need to manually make sure that the column name is valid:

$valid_col = in_array($order, ['username', 'realname', ...], true);

And then only execute the query if the result is true.

Upvotes: 1

Related Questions