Safe SQLi

It may sound like an oxymoron, but it’s a legitimate concern among the good guys in the security industry. Under the circumstance that a company has authorised you to try and hack into their live system, you really don’t want to be pissing around with ‘dangerous’ SQL injections. Dangerous is defined here as an SQL injection which results in undesirable data modification in the database, which could have otherwise have been avoided if the SQLi was considered more carefully (or wasn’t present in the first place). Let’s have a quick demonstration.

Mr Developer decides that passing unsanitised user input is a good idea, and that prepared statements are not. He wants to use a SELECT query to determine if the username and password are correct, and he also wants to update a table containing login information using an UPDATE statement. The company Mr Developer works for have received a tip that the developer might be a bit of a tit, and decide to hire Mr Security Man to try and break into Mr Developer’s web application. Unfortunately, Mr Security Man is also a bit of a tit, and thinks that still using some variant of ‘ or 1=1 — is a good idea. Let’s see how it all unravels:

SELECT * FROM tbl_test WHERE username = 'test' AND password = 'pass';

Above we have an innocent enough looking SQL statement. The attacker finds the login form associated with this statement and injects the password field with ‘ or 1=1 —

SELECT * FROM tbl_test WHERE username = 'test' AND password = 'pass' or 1=1 -- '

Mr Security Man is excited, he’s bypassed the login form and is now sitting on the site with a message reaffirming his suspicion that his non-skiddy SQLi has landed him the test user. “Hello test!”. Excellent.

Unfortunately, Mr Security Man didn’t realise that Mr Developer runs an UPDATE statement on an audit table using his username and password. This is where brown hits blade.

mysql> select * from audit;                                                                                                                        
| id   | username | password | last_login |
|    1 | test     | pass     | yesterday  |
|    2 | admin    | password | yesterday  |
2 rows in set (0.00 sec)

mysql> UPDATE audit SET last_login = 'today' WHERE username = 'test' AND password ='pass' or 1=1 -- '
    -> ;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from audit;                                                                                                                        
| id   | username | password | last_login |
|    1 | test     | pass     | today      |
|    2 | admin    | password | today      |
2 rows in set (0.00 sec)


Whoops? It’s okay though right because it’s only an audit table? Well, not really. It was only an audit table in this example, and a few things had to happen for this injection to be ‘dangerous’, but are they that inconceivable in real life?

Okay, so if ‘ or 1=1 — is bad, what should be used instead? Well, that’s a tough question, because any form of SQLi transforms the structure of the statement, which could potentially manipulate data in ways unintended or by second-order. I’m currently of the opinion that the safest way to test for SQLi is by forcing a syntax error. Doing this shouldn’t get you passed a login page, but it will at least indicate the presence of an SQL injection.

A slightly riskier stratedgy would be the utilisation of the ORDER BY or LIMIT clauses, or the BENCHMARK function. While the web app probably has to be of particularly bad quality for the aforementioned clauses or function to result in undesirable data modification, this is still a possibility you have to assume if you don’t want to be Mr Security Man.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s