A3:2021 | SQL Injection Mitigation (11) | Cycubix Docs
Order by clause
Question: Does a prepared statement always prevent against an SQL injection? Answer: No it does not
Let us take a look at the following statement:
"SELECT * FROM users ORDER BY " + sortColumName + ";"
If we look at the specification of the SQL grammar the definition is as follows:
SELECT ...
FROM tableList
[WHERE Expression]
[ORDER BY orderExpression [, ...]]
orderExpression:
{ columnNr | columnAlias | selectExpression }
[ASC | DESC]
selectExpression:
{ Expression | COUNT(*) | {
COUNT | MIN | MAX | SUM | AVG | SOME | EVERY |
VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP
} ([ALL | DISTINCT][2]] Expression) } [[AS] label]
Based on HSQLDB
This means an orderExpression
can be a selectExpression
which can be a function as well, so for example with a case
statement we might be able to ask the database some questions, like:
SELECT * FROM users ORDER BY (CASE WHEN (TRUE) THEN lastname ELSE firstname)
So we can substitute any kind of boolean operation in the when(….)
part. The statement will just work because it is a valid query whether you use a prepared statement or not. An order by clause can by definition contain an expression.
Mitigation
If you need to provide a sorting column in your web application you should implement a whitelist to validate the value of the order by
statement. It should always be limited to something like 'first name' or 'last name'.
Last updated
Was this helpful?