2

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 someVariable def' 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.

  • 1
    Don't play his game! The only way to be completely safe is to use prepared queries. Considering that the difference in development time between prepared queries and the unsafe alternative is virtually non-existent, there is simply no reason NOT to use prepared queries. Don't get stuck arguing about whether or not this is secure. Insisting on prepared queries no matter what, is one of the few "absolute" statements that I would make without hesitation. – Conor Mancone Sep 23 at 22:49
  • @ConorMancone I completely agree with you, but you underestimate the human factor in this case. If people, like the dev in question, did this for for their entire career you cannot be like "Do this, this is best practice", especially when you're a junior and just entered the team some months ago. Their answer usually is "Well trends and best practices come and go, I've witnessed enough in twenty years being a dev". Thats why I needed a proof. – acincognito Sep 24 at 7:49
  • @ConorMancone - I've found that demonstrating (at least in theory) why something is vulnerable helps change behavior much more effectively than dictating rules. For valid reasons, developers don't like to touch things that "ain't broke". – Egret Sep 24 at 16:49
  • @Egret In general I agree 100%. This is one of the few times I would make a "rule". SQLi is both one of the most dangerous vulnerabilities, and also one of the easiest to fix. Once someone has been shown the general dangers of SQLi, they should be more than happy to accept the rule of "and prepared queries should always be used to safe guard against this". This is literally the only topic I would make an actual rule about. – Conor Mancone Sep 24 at 17:05
  • @ConcorMancone - And I completely agree with you - I do make it a "rule" for all our developers. But, there are people that don't just accept rules, and there are times you can't use prepared queries (select and order by clauses, DML...). By the way, I would make other rules as well - (e.g. encoding is required when writing out to HTML) but I'm getting off topic :-) – Egret Sep 26 at 1:23
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.

  • Thanks for the second bulletpoint! I just injected another condition and the injection worked as I expected. – acincognito Sep 24 at 7:52
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.