1

it comes to me that sometimes parameterized query are not working in order by clause?

I have tried mysql; postgres; C#;

e.g.:

  • php+mysql

    
$stmt = $conn->prepare("SELECT id, name FROM user order by ? desc");
$stmt->bind_param("s", $orderby);
$orderby = "name";
$stmt->execute();

The order by get ignored

  • Postgres:

See this one https://github.com/brianc/node-postgres/issues/300

  • .net+mssql:

See this "Order By" using a parameter for the column name

Is there a standard reference for this situation documented somewhere? And on what platform that Parameterize does not work with ORDER BY?

404
  • 53
  • 1
  • 6
  • Slightly related to https://stackoverflow.com/q/26934412/744133 – YoYo Dec 14 '18 at 21:22
  • Unless you're limiting the result set with a `top` (SQL Server) or `limit` (MySQL) just bring the dataset back and sort in your client. WIll be worlds faster. – Adam Dec 14 '18 at 21:29
  • @Adam where did you get that idea from? An application is likely going to be terrible at sorting a large dataset, where as an RDBMS may well excel with the right indexes. – Thom A Dec 14 '18 at 21:39
  • That hasn't been my experience. When a database does a sort, it's doing a collated sort (sorting by your selected collation). When you sort in your code, you're typically doing a non-collated sort. Hit me up on the DL if you want some use-cases that prove the point. – Adam Dec 14 '18 at 21:54
  • https://www.google.com/amp/s/www.brentozar.com/archive/2013/02/7-things-developers-should-know-about-sql-server/amp/ – Adam Dec 16 '18 at 03:53

2 Answers2

1

you bind the string value 'name' to the parameter in the sql. That means that for each row processed, the SQL will see the same string, namely 'name'.

The point is that 'name' is not interpreted as the Literal name which matches the column name, but a VARCHAR value 'name' which has been set or bound to a replacement variable ?.

In this case, if you have a variable ORDER BY clause, you would have two (or more) versions of your SQL, which you can select with a regular if / then / else.

Another way is to concatenate the sorting column in your string directly rather than using bind variables. I would STRONGLY suggest against as this lays the foundation work for future SQL Injection. Either way, if you concatenate a variable input string to your SQL, then make sure it is sufficiently sanitized and checked to avoid issues.

Concatenation in PHP would be something simple like this:

$orderby = "name";
$stmt = $conn->prepare("SELECT id, name FROM user order by ".$orderby." desc");
$stmt->execute();

See also PHP - concatenate or directly insert variables in string (had to correct, used initially a syntax that only worked for the PHP echo command).

All the other Implementing Languages (C#, Java, etc) combined with any database (oracle, MySQL, TSQL, etc) would face same issues and solutions: You will have to make the distinction between bind variables and literals in your SQL.

If you still want to use a bind variable - there is a way, but you would have to modify your sql as follows:

SELECT id, name FROM user 
ORDER BY CASE ? WHEN 'name' THEN name WHEN 'id' THEN id ELSE NULL END

Actually good thinking by you (in your comment) to still use bind variables somehow. Now I do not have the issue with SQL Injection anymore either. However, now you need to account for every possible sorting that can happen, and have that coded in your SQL instead.

YoYo
  • 9,157
  • 8
  • 57
  • 74
  • 1
    Thanks for the answer; that makes sense to me; but is there a way to bind the value to match the `column name` instead of being a varchar value; if I do not want to use condition queries; Also; do you know if this just occurs on PHP+MYSQL or it is common in other SQL servers? – 404 Dec 14 '18 at 21:44
  • Sure, but you will have to account for every value that the bind value can have. - I have updated the answer. – YoYo Dec 14 '18 at 21:48
  • Very good explanation; but have to ask: `All the other Implementing Languages (C#, Java, etc) combined with any database (oracle, MySQL, TSQL, etc) would face same issues and solutions: You will have to make the distinction between bind variables and literals in your SQL.` Is that documented anywhere? I am on a small case study and would like to have a solid reference. – 404 Dec 14 '18 at 21:57
1

The placeholders in prepared statements are for values, not parameters for ORDER BY or for column names. If you wish to dynamically inject an ORDER BY condition I suggest using concatenation in whatever language you are using, PHP in your case:

$orderByCondition = "name";
$stmt = $conn->prepare("SELECT id, name FROM user order by " . $orderByCondition . " desc");
$stmt->execute();

Of course, since you are not injecting input directly from the user you don't actually need to use a prepared statement.

MAD-HAX
  • 173
  • 1
  • 7