You are invited to Log in or Register a free Frihost Account!

Can you improve my sql injection detection

On a few sites I added sql error logging to find programs that were generating errors. It also logged failed sql injection attacks. I fixed the programs so it inputs are sanitized. I think the next step is to detect and block the successful injection attacks.

most of the failed attacks were of numeric key lookups from _GET or _POST
like 'SELECT * story WHERE story_id=(input)' and the (input) should be a number

I want to make a good sql injection attack detector that I can put into any site to stop the more dangerous injection attacks and I think it detects and logs enough of them so it's known which programs need to get fixed if someone is exploiting it.

How would you make my code better so it detects the serious sql injection attacks?
Don't worry about the lack of logging.

function injectionTest($str){
   if(strpos($str, ';') !== false){
      $fnds[] = '/(.*?);([ \t]*?)drop([ \t]+?)table(.*?)/i';
      $fnds[] = '/(.*?);([ \t]*?)select(.*?)from(.*?)/i';
      $fnds[] = '/(.*?);([ \t]*?)select(.*?)into(.*?)/i';
      $fnds[] = '/(.*?);([ \t]*?)select(.*?)load_file(.*?)/i';
      $fnds[] = '/(.*?);([ \t]*?)alter([ \t]*?)table(.*?)/i';
      $fnds[] = '/(.*?);([ \t]*?)update([ \t]*?)set(.*?)/i';
      $fnds[] = '/(.*?);([ \t]*?)update(.*?)load_file(.*?)/i';
      $fnds[] = '/(.*?);([ \t]*?)delete([ \t]*?)from(.*?)/i';
      $fnds[] = '/(.*?);([ \t]*?)insert([ \t]*?)value(.*?)/i';
      $fnds[] = '/(.*?);([ \t]*?)insert(.*?)load_file(.*?)/i';
      $fnds[] = '/(.*?);(.*?)\/\*(.*?)$/i';
      $fnds[] = '/(.*?);([ \t]*?)--(.*?)$/i';
      foreach($fnds as $fnd){
         if(preg_match($fnd, $str, $arr))
            return true;
   return false;

function injectionDetected(){
   echo 'Injection detected, Redirecting to ' . $_SERVER['SCRIPT_NAME'];

function injectionDetect(){
   foreach($_GET as $k=>$v)
   foreach($_POST as $k=>$v)

Fire Boar
It would probably be more robust to have a database calling method, in which parameters are coded something like this:

SELECT id FROM table WHERE intparam='%d' AND strparam='%s'

Your code would then take this and two extra arguments (the number of possible extra arguments would be unlimited, but for this query it would be two) and check to see if the first is an integer, and that the second is a safe string (using mysql_real_escape_string). It would then do a search and replace on the query.

This way, provided you always use that function rather than mysql_query, all your queries are safe.
I don't want to talk about your detection sistem. Instead of this I would like to tell something about this line.

echo 'Injection detected, Redirecting to ' . $_SERVER['SCRIPT_NAME'];

It is much better to keep out some attacker from informations like "I catch you!" because this can keep him/her to try again and again. If you just use simple redirect without any messages then:
1. Attacker will loose time with any new try
2. Attacker will lose interesting after few try
3. Attacker can't know what is happend in the background

For example, you can every attack redirect on home page. Or create two, three very heavy pages with movies or something like that. Then randomly redirect on different page. When attacker must wait 30 or more seconds to see what happend then he/she will loose any interesting for injection on your site.

The code above is the test version. What I was hoping for was some help improving the injectionTest function with more tests, better tests, better regular expressions, etc.

I think it should focus on detecting sql commands that either change the database/tables/etc or gives them data from the database. If you can't code, just state the dangerous SQL command that it fails to detect

For example I replaced the ([ \t]*?) with (\s*?)
$fnds[] = '/;(\s*?)drop(\s+)table(.*?)/i';

What I'm really using when an injection is detected minus the database code. It logs $_REQUEST, their IP number and program name.
function injectionDetected(){
        // Write a record to the log database table
        ... (database code)
        // Wrote the the log
   header('Location: ' . $_SERVER['SCRIPT_NAME']);

function injectionTest($str){
   if(strpos($str, ';') !== false){
      $fnds[] = '/;(\s*?)drop(.*?)table/i';
      $fnds[] = '/;(\s*?)select(.*?)from/i';
      $fnds[] = '/;(\s*?)select(.*?)into/i';
      $fnds[] = '/;(\s*?)select(.*?)load_file(.*?)/i';
      $fnds[] = '/;(\s*?)alter(\s+?)table/i';
      $fnds[] = '/;(\s*?)update(\s+?)set/i';
      $fnds[] = '/;(\s*?)update(.*?)load_file/i';
      $fnds[] = '/;(\s*?)delete(\*?)from/i';
      $fnds[] = '/;(\s*?)insert(.*?)value/i';
      $fnds[] = '/;(\s*?)insert(.*?)load_file/i';
      $fnds[] = '/;(\s*?)alter(.*?)table/i';
      $fnds[] = '/;(\s*?)alter(.*?)database/i';
      $fnds[] = '/;(\s*?)alter(.*?)schema/i';
      $fnds[] = '/;(\s*?)alter(.*?)server/i';
      $fnds[] = '/;(\s*?)alter(.*?)function/i';
      $fnds[] = '/;(\s*?)alter(.*?)procedure/i';
      $fnds[] = '/;(\s*?)alter(.*?)view/i';
      $fnds[] = '/;(\s*?)rename(.*?)table/i';
      $fnds[] = '/;(\s*?)rename(.*?)database/i';
      $fnds[] = '/;(\s*?)drop(\s+)database/i';
      $fnds[] = '/;(\s*?)drop(.*?)index/i';
      $fnds[] = '/;(\s*?)drop(\s+)event/i';
      $fnds[] = '/;(\s*?)drop(\s+)function/i';
      $fnds[] = '/;(\s*?)drop(\s+)procedure/i';
      $fnds[] = '/;(\s*?)drop(\s+)schema/i';
      $fnds[] = '/;(\s*?)drop(\s+)server/i';
      $fnds[] = '/;(\s*?)drop(\s+)trigger/i';
      $fnds[] = '/;(\s*?)drop(\s+)view/i';
      $fnds[] = '/;(\s*?)create(\s+)database/i';
      $fnds[] = '/;(\s*?)create(.*?)function/i';
      $fnds[] = '/;(\s*?)create(\s+)logfile/i';
      $fnds[] = '/;(\s*?)create(.*?)event/i';
      $fnds[] = '/;(\s*?)create(.*?)procedure/i';
      $fnds[] = '/;(\s*?)create(.*?)index/i';
      $fnds[] = '/;(\s*?)create(\s+)server/i';
      $fnds[] = '/;(\s*?)create(.*?)table/i';
      $fnds[] = '/;(\s*?)create(\s+)trigger/i';
      $fnds[] = '/;(.*?)\/\*(.*?)$/i';
      $fnds[] = '/;(.*?)--(.*?)$/i';
      foreach($fnds as $fnd){
         if(preg_match($fnd, $str, $arr))
            return true;
   return false;
My database error logging (not the injection detector) logged something unusual

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '><marquee><h1>hacked%20By%20AyhamR</h1></marquee>" LIMIT 0, 1' at line 1

SELECT * from page where page_name="?tag=13}</style><scr ipt>a=eval;b=alert;a(b(/XSS/.source));</scr ipt>'"><marquee><h1>hacked%20By%20AyhamR</h1></marquee>" LIMIT 0, 1

but the logged $_REQUEST is just
[tag] => 13
[PHPSESSID] => 5bf1f52a52b75afbc7dea20032dc8dee

They tried 3 times with different javascript alert values, once without the PHPSESSID. I searched every table for "Ayham" and found nothing so they must have entered it, but it's not in $_REQUEST. Is there a way they could have done the injection attack?

What I'm thinking
. The program unset() the entry from $_REQUEST or changed $_REQUEST['tag'] to an int after it was used somewhere (I can't find where)
. They used a cookie.
. They modified the program because I can't find the code that uses the 'page' table that way

Any ideas on what happened?
Fire Boar
What you should be more concerned about is why that's logged as a database query at all. Any kind of arbitrary input like that should NEVER be allowed in.

Now, if you used a wrapper method as I suggested, this wouldn't have happened. Your query in your script would look like:

SELECT * from page where page_name='%s' LIMIT 0,1

and the wrapper method would have picked up the bad input, put quotes around the strings and allowed it as a valid and safe SELECT query (which would then fail to pick up anything), provided you coded it right (mysql_real_escape_string would do the trick).
Because I'm working with several live sites that use old code (and new ones) and don't have time to find and figure out how the query might fail. In the program that was attacked it takes 90+ lines of code to generate the SQL (for filters, and permissions) you can't replace that with a one liner.

I downloaded the site and grepped the files and couldn't find the sql command anywhere. It didn't log how they attacked. About the only thing I can do is add backtracing to the sql error so I have more info with filenames and line numbers, etc
Related topics
PHP Validation Class
How To : Secure Your PHP Website
PHPBB Eklentileri
Best way to prevent SQL injection attacks
[man] phpBB 2.0.19 (Style Changer/Demo Mod) SQL Injection
protecting mysql databases from sql injection attacks
Hacked by someone sql Injection
how to use sql injection to retrive a column name ?
SQL Injection
mod_security reports WordPress as SQL injection attack!
Simple PHP Login Script
SQL Injection
Is this a sign that my website can be SQL injected?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

© 2005-2011 Frihost, forums powered by phpBB.