Re: FW: Introducing a new generic approach to detecting SQL injection

["Paul J. Morris" <mole () morris net>]

On Tue, 19 Apr 2005 14:32:38 -0400
<Glenn.Everhart () chase com> wrote:
> As you know, blocking SQL injection with filters on characters is 
> painful and not always successful. I got thinking about it and thought
> of an approach that might detect such activity, and which is pretty
> generic.

Once the allowed character set gets beyond $sanitized =
preg_replace("/[^a-zA-Z0-9]/", "", $untrusted) especially into the realm
of unicode character strings where valid input from a user may include
the characters ; % ' " and #, sanitizing by filtering can indeed get
difficult.  

Let me suggest an elaboration on Glenn's idea that poisons a query with
a known error, fires the poisoned query including the untrusted input
against a test database, and looks to see if the expected error from the
poisoning occurs.  Untrusted input that contains a sql injection attack
should raise a different error than expected. 

<?php
// example of query poisoning test for sql injection

$untrusted = $_GET['untrusted'];
// e.g. $untrusted = "'; drop database;"

// create and lock down a database to test untrusted input against
// CREATE DATABASE poisontest;
// USE poisontest;
// CREATE TABLE poisontest (testfield char(1));
// GRANT SELECT ON poisontest TO poisontestuser@localhost;
// poisontestuser should have no rights other than select, 
// those rights should preferably be only on poisontest table

// create connections to the real production database 
// and to the locked down test database
$connection = mysql_connect("localhost","webuser","webuserpassword");
mysql_select_db("productiondatabase",$connection);
$poison_connection = 
mysql_connect("localhost","poisontestuser","poisontestuserpassword");
mysql_select_db("poisontest",$poison_connection);

// generate a marker that attacker should not be able to guess
$poison_marker =
preg_replace("/[^a-zA-Z0-9]/","x",md5(time().rand(10000,90000)));

// clause with which to poison sql query
$poison_clause = " hell freezes over and $poison_marker ";

// expected failure message from poisoned query
$expected_message = "near 'freezes over and $poison_marker' at line 1";
// expected failure number from poisoned query
$expected_error_code = '1064';

// query poisontest database
$poisoned_sql = 
  "SELECT COUNT(*) 
     FROM poisontest 
     WHERE testfield = '$untrusted' and $poison_clause";

$result = mysql_query($poisoned_sql,$poison_connection);
$errornumber = mysql_errno($poison_connection);
if ($errornumber==$expected_error_code) {
   $errormsg = mysql_error($poison_connection);
   if (substr_count($errormsg,$expected_message)==1) { 
      // query was poisoned as expected
      // $untrusted probably does not contain a sql injection attack    
      $real_sql = "SELECT stuff FROM production_table 
                      WHERE target = '$untrusted'"; 
      $real_result = mysql_query($real_sql,$connection);  
      // and so on....      
      echo "Input passed test and query ran\n";
   }
} 

?>

Opinions? 

Note, I wouldn't use this in cases where $sanitized =
preg_replace("/[^a-zA-Z0-9]/", "", $untrusted) would work perfectly
well, but only in cases where the scope of valid characters was quite
large.  

-Paul
--------------
Paul J. Morris
Biodiversity Information Manager, The Academy of Natural Sciences
1900 Ben Franklin Parkway, Philadelphia PA, 19103, USA
mole () morris net  AA3SD  PGP public key available

Attachment: _bin
Description:

_______________________________________________
Full-Disclosure - We believe in it.
Charter: http://lists.grok.org.uk/full-disclosure-charter.html
Hosted and sponsored by Secunia - http://secunia.com/