| Subject: | SQL Injection in ORDER_BY |
During audit of a web application that uses DBIx::Class we found that it is still vulnerable to SQL injection attacks on order_by, which is frequently used for users to sort tables. We assumed that DBIx::Class and ultimately SQL::Abstract would have taken care of dealing with the user input, however, order_by parameters are just passed through literally:
use SQL::Abstract;
use Data::Dumper::Concise;
my $sql = SQL::Abstract->new;
my ($stmt, @bind) = $sql->select("users", [ '*' ], { id => 1 }, ['1)']);
print Dumper \$stmt, \@bind;
$ perl sq.pl
\"SELECT * FROM users WHERE ( id = ? ) ORDER BY 1)"
[
1
]
Note the invalid ORDER BY statement causing a syntax error.
While these injections are not as easy to exploit as others, you can still use this to fetch whatever field you want from the database, or probe out the server, e.g. in our application you could access salted password hashes:
SELECT username FROM users me WHERE id =1 ORDER BY 1,extractvalue(0x0a,concat(0x0a,(SELECT password FROM users LIMIT 1)));
ERROR 1105 (HY000): XPATH syntax error: '
{SSHA}Ggq1OB1Y3U5g/67TGSACoYBSo'
Is there a reason why bind values are not used here?
I see that there is currently another ticket open (https://rt.cpan.org/Ticket/Display.html?id=103219), which seems slightly related and that this is an area that is currently being worked on, so I will not try to come up with my own patch.
Since SQL::Abstract is likely used in many web applications, and the manual to neither DBIx::Class nor SQL::Abstract mentions that the application has to deal with order_by parameters themselves I would consider this to be an important issue.
Please let me know should you require any further input.