« Console and GUI in on… | Home | Xojo Thanksgiving Sal… »

SQL injections

For years we told people to use prepared statements. But recently we run a test to see what happens in real world. So we created a login form on our website. The login form, does never log you in and just writes down the login credentials to a database using prepared statements. This page is not linked and no human should ever find it. But there are bots testing whether you have some common folder names like "wordpress", "admin" or "login".

Unions

After a few weeks, we checked the database and got hundreds of login attempts. At least 100 per month come to our form. Most of the things entered in our form are attempts to exploit the SQL with an SQL injection. We see common patterns like the following values for user name:

' UNION ALL SELECT NULL--
' UNION ALL SELECT NULL,NULL--
' UNION ALL SELECT NULL,NULL,NULL--
' UNION ALL SELECT NULL,NULL,NULL,NULL--
' UNION ALL SELECT NULL,NULL,NULL,NULL,NULL--
' UNION ALL SELECT NULL,NULL,NULL,NULL,NULL,NULL--
' UNION ALL SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL--
' UNION ALL SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL--
' UNION ALL SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL--
' UNION ALL SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL--

If you don't use prepared statements, this would end the string of the username comparison and combine the result with an second result with the given number of NULL values. If this is valid SQL, it may execute. e.g. if you have a query like

"SELECT UserID FROM Users WHERE Username='"+username+"' AND Password='"+Password+"'"

the attack will be successful. Instead of the proper way:

"SELECT UserID FROM Users WHERE Username=? AND Password=?"

The user names from above will end the quote from user name, do the union and declare the rest of the line to be a comment with the two dashes. If the first try works, the login may first succeed and second the attacker knows we have one field in the result.

Sleep

It's interesting to see queries like this one for sleeping the process:

' AND SLEEP(5) AND 'cjna'='cjna

If executed, this would delay processing for 5 seconds and hacker would notice the speed difference to see if the SQL syntax would process the Sleep command at all.

Same goes for WAITFOR DELAY, which is a feature in Microsoft SQL Server and tried in dozens variants, which we can compact to these cases:

;WAITFOR DELAY '0:0:5'--
' WAITFOR DELAY '0:0:5' AND 'PQLR'='PQLR
';WAITFOR DELAY '0:0:5'--
') WAITFOR DELAY '0:0:5' AND ('ucmA'='ucmA
');WAITFOR DELAY '0:0:5'--
) WAITFOR DELAY '0:0:5' AND (5513=5513
);WAITFOR DELAY '0:0:5'--
WAITFOR DELAY '0:0:5'
WAITFOR DELAY '0:0:5'-- jHan

Please note that they try various combinations with quote and bracket on front to match whatever SQL may be before it. And on the right side they try with comment or a true condition.

Pipes

A feature of some databases is to offer pipes to communicate from one instance of a database to another instance, possibly on a different computer.
We have a few calls to DBMS_PIPE.RECEIVE_MESSAGE to receive pipe messages. e.g.

AND 1277=DBMS_PIPE.RECEIVE_MESSAGE(CHR(89)||CHR(77)||CHR(71)||CHR(76),5)-- Mzpl

Conclusion

The list goes on with hundreds of more inputs. Most combinations of values with brackets, AND/OR and sub selects. Some selects use CHAR() function to obfuscate the exact strings used. Some queries refer to INFORMATION_SCHEMA.PLUGINS table to query the list of installed plugins for Oracle or MySQL. Some try to use XMLType function, which may reveal the database being Oracle.

Please always use prepared statements and never put user values into SQL strings. Even if you try to properly escape the strings, it's quite risky you miss some kind of unicode escape combination.
25 11 19 - 21:55