Preventing SQL injections with PHP and Zend Framework - how?

Question

I'm trying to defend the sign-in form on my page from SQL injections. On the serverside, I use Zend Framework (Zend_Db,Zend_Db_Table_Abstract), but its build-in injection prevention functions: quote, quoteInto, quoteIdentifier don't make their work well (as far as I know how to use them). Other ways like mysql_real_escape_string, addslashes do not seem to work at all...

This is what I'm trying to implement for the defense:

function prevent_from_sql_injection($str) {
    if(preg_match('/[\'"]/', $str))
     {die('attack1'); exit;  }// no quotes
elseif(preg_match('/[\/\\\\]/', $str))
     {die('attack2'); exit;  }// no slashes
elseif(preg_match('/(and|or|null|not)/i', $str))
     {die('attack3'); exit;  }// no sqli boolean keywords
elseif(preg_match('/(union|select|from|where)/i', $str))
     {die('attack4'); exit;  }// no sqli select keywords
elseif(preg_match('/(group|order|having|limit)/i', $str))
     {die('attack5'); exit;  }//  no sqli select keywords
elseif(preg_match('/(into|file|case|LOAD_FILE|DUMPFILE|char|schema|AES_DECRYPT|AES_ENCRYPT)/i', $str))
     {die('attack6'); exit;  }// no sqli operators
elseif(preg_match('/(--|#|\/\*)/', $str))
     {die('attack7'); exit; }// no sqli comments
elseif(preg_match('/(=|&|\|)/', $str))
     {die('attack8'); exit;  }// no boolean operators
elseif(preg_match('/(UNI\*\*ON|1 OR 1=1|1 AND 1=1|1 EXEC XP_)/', $str))
     {die('attack9'); exit; }
elseif(preg_match('/(1|'| |O|R|=|&#49&#39&#32&#79&#82&#32&#39&#49&#39&#61&#39&#49|%31%27%20%4F%52%20%27%31%27%3D%27%31)/', $str))
     { die('attack10'); exit; }
elseif(preg_match('/(SELECT\s[\w\*\)\(\,\s]+\sFROM\s[\w]+)| (UPDATE\s[\w]+\sSET\s[\w\,\'\=]+)| (INSERT\sINTO\s[\d\w]+[\s\w\d\)\(\,]*\sVALUES\s\([\d\w\'\,\)]+)| (DELETE\sFROM\s[\d\w\'\=]+)/', $str))
     { die('attack11'); exit; } 
elseif(preg_match('/(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)/', $str))
     { die('attack12'); exit; } 
elseif(!preg_match('/^["a-zA-Z0-9\040]+$/', $str))
     { die('attack13'); exit; } 
else return $str;

}

As to test my results, I use Firefox extension SQL Inject Me and it shows 14 more errors (sometimes 21 or 17 and I don't know why the results are different):

Server Status Code: 302 Found
Tested value: 1' OR '1'='1
Server Status Code: 302 Found
Tested value: 1 UNI/**/ON SELECT ALL FROM WHERE
Server Status Code: 302 Found
Tested value: &#49&#39&#32&#79&#82&#32&#39&#49&#39&#61&#39&#49
Server Status Code: 302 Found
Tested value: 1 OR 1=1
Server Status Code: 302 Found
Tested value: 1' OR '1'='1
Server Status Code: 302 Found
Tested value: 1 EXEC XP_
Server Status Code: 302 Found
Tested value: 1 UNION ALL SELECT 1,2,3,4,5,6,name FROM sysObjects WHERE xtype = 'U' --
Server Status Code: 302 Found
Tested value: %31%27%20%4F%52%20%27%31%27%3D%27%31
Server Status Code: 302 Found
Tested value: 1 AND 1=1
Server Status Code: 302 Found
Tested value: 1' OR '1'='1
Server Status Code: 302 Found
Tested value: 1 AND ASCII(LOWER(SUBSTRING((SELECT TOP 1 name FROM sysobjects WHERE xtype='U'), 1, 1))) > 116

So what is the best way to prevent all this SQL injection attacks? Using placeholders is good but it doesn't ok in some cases. Maybe this extension is wrong and I have a paranoia?

Answer 1

Use prepared SQL statements instead of value escaping.

 $st = $pdo->prepare("SELECT * FROM tbl WHERE x = ?");
 $st->execute($x);

Blacklisting a couple of SQL statements isn't a sensible approach. That Firefox extension is misleading and gave you the wrong impression.

Obviously you shouldn't start to accept random SQL commands as input. At most you use some request variables as query data. And prepared statements are sufficient to provide security for that case. Concatenating SQL commands and variables is an outdated practice (what that questionable Firefox extension seems to suggest.).

Answer 2

I strongly recommend the use of Zend_DB. It uses prepared statements.
The parameters to prepared statements don't need to be quoted; the driver automatically handles this.

If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible

$db = Zend_Db::factory('Pdo_Mysql', array(
    'host'     => '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test'
));

$stmt = $db->query('SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?',
    array('goofy', 'FIXED')
);

$rows = $stmt->fetchAll();

echo $rows[0]['bug_description'];

Answer 3

Put this code into your header page.

function clean_header($string)
{
$string = trim($string);

// From RFC 822: “The field-body may be composed of any ASCII
// characters, except CR or LF.”
if (strpos($string, “\n“) !== false) {
$string = substr($string, 0, strpos($string, “\n“));
}
if (strpos($string, “\r“) !== false) {
$string = substr($string, 0, strpos($string, “\r“));
}

return $string;
}