SQL injection with UNION and two queries with same input

Question

Say there are two queries, which are called after each other: one to get the amount datasets in the table, one to get the result of the actual query:

select count(*) as total from table_a where someCol='abc' and someOtherCol= 'someVariable' select * from table_a where someCol='abc' and someOtherCol= 'someVariable' 

someVariable comes directly from userInput (which should not be trusted) and is identical in both queries and is concatenated into the querystring.

This is the approach one of our developers took and he thinks it's completely safe and there is no problem with SQL injection whatsoever.

I injected the following string for someVariabledef' UNION select * from table_a where someCol='abc' and someOtherCol= 'someVariable.

The injection doesn't work for the first query, since it has only one column, but does work for the second query (table_a has more than 1 column). This isn't exactly what I call protection against sql injection.... More like two wrongs give one right...

Is there a possibility to make the injection with UNION work in this scenario? I didn't want to drop all tables, that seemed a bit harsh.

Answer 1

Your intuition is correct that this does not provide sufficient protection against SQL injection. You may need to treat this it as a blind SQL injection have a couple of options for explaining how to exploit this:

• Attack the first SQL with a union returning one column - you can get information out of if there's different behavior between failure of the first and second SQL
• Leverage another attack besides the UNION attack. There are many other techniques for getting information out via blind SQL injection - timing based or out of band being the most common. Although specific techniques will depend on the database.

There are a variety of tools (Burp Suite, Sqlmap...) which will automate the process for you if you need to actually generate an exploit for the developer. Make sure, in any case, you use this as a "teachable moment" to explain about blind SQL injection, and that any kind of untrusted data in a SQL statement is going to be exploitable.

Answer 2

def' AND 'somepass'='SELECT pass FROM admin

You could use a similar method to determine the different table names. You essentially open up a vulnerability to performing logical queries on the database, and since you have an on and off, you can do anything with the database that your credentials will allow.

There's also into outfile which would allow an attacker to upload a shell and do whatever they like.

All that aside, it's 2019 and you should be using prepared statements!