title: Preventing SQL injections with PHP and Zend Framework - how?
body: <p>I'm trying to defend the sign-in form on my page from SQL injections. On the serverside, I use Zend Framework (<code>Zend_Db</code>,<code>Zend_Db_Table_Abstract</code>), but its build-in injection prevention functions: <code>quote</code>, <code>quoteInto</code>, <code>quoteIdentifier</code> don't make their work well (as far as I know how to use them). Other ways like <code>mysql_real_escape_string</code>, <code>addslashes</code> do not seem to work at all...</p>
  
  <p>This is what I'm trying to implement for the defense:</p>
  
  <pre><code>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('/(=|&amp;|\|)/', $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('/(&amp;#x31;|&amp;#x27;|&amp;#x20;|&amp;#x4F;|&amp;#x52;|&amp;#x3D;|&amp;#49&amp;#39&amp;#32&amp;#79&amp;#82&amp;#32&amp;#39&amp;#49&amp;#39&amp;#61&amp;#39&amp;#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)|(&amp;lt;)|(&amp;gt;)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE)|(GRANT) |(REVOKE)|(UNION)|(&amp;amp;lt;)|(&amp;amp;gt;)/', $str))
       { die('attack12'); exit; } 
  elseif(!preg_match('/^["a-zA-Z0-9\040]+$/', $str))
       { die('attack13'); exit; } 
  else return $str;
  
  }
  </code></pre>
  
  <p>As to test my results, I use Firefox extension <a href="https://addons.mozilla.org/en-US/firefox/addon/7597/" rel="noreferrer">SQL Inject Me</a> and it shows 14 more errors (sometimes 21 or 17 and I don't know why the results are different):</p>
  
  <pre><code>Server Status Code: 302 Found
  Tested value: &amp;#x31;&amp;#x27;&amp;#x20;&amp;#x4F;&amp;#x52;&amp;#x20;&amp;#x27;&amp;#x31;&amp;#x27;&amp;#x3D;&amp;#x27;&amp;#x31;
  Server Status Code: 302 Found
  Tested value: 1 UNI/**/ON SELECT ALL FROM WHERE
  Server Status Code: 302 Found
  Tested value: &amp;#49&amp;#39&amp;#32&amp;#79&amp;#82&amp;#32&amp;#39&amp;#49&amp;#39&amp;#61&amp;#39&amp;#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))) &gt; 116
  </code></pre>
  
  <p>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?</p>