SQL injections
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:
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' 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--
"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:
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.' AND SLEEP(5) AND 'cjna'='cjna
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:
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.;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
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.
ConclusionAND 1277=DBMS_PIPE.RECEIVE_MESSAGE(CHR(89)||CHR(77)||CHR(71)||CHR(76),5)-- Mzpl
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.