Protection against SQL Injection using PDO and Zend Framework – part 2

Following the preview article about SQL Injection, here is more – a strong argument why you should use Zend Framework for handling database access. Zend_Db is the primary class used for access the database, but there is more: Zend_Db_Statement, Zend_Db_Select and Zend_Db_Tables.
What you should know about their methods is:

• query (mixed $sql, [mixed $bind = array()])
  • use prepare statements internally
  • but SQL Injection is still possible if $sql is dynamically created
• fetchAll (string|Zend_Db_Select $sql, [mixed $bind = array()], [mixed $fetchMode = null])
  • all the fetch methods are using prepared statements internally
  • but SQL Injection is still possible if $sql is dynamically created
• insert (mixed $table,  $bind)
  • use prepare statements internally
  • so, SQL Injection is not possible
• update (mixed $table,  $bind, [mixed $where = ”])
  • use prepare statements internally
  • but SQL Injection may be possible if $where is created dynamically
• delete (mixed $table, [mixed $where = ”])
  • SQL Injection may be possible if $where is created dynamically

Note*: even if you use prepared statements using Zend_Db methods, SQL Injection is still possible if WHERE and ORDER BY clause are wrongly written, so pay attention to them.

For more details see Stefan Esser slides.

PS. A short tip, you can use cast type to avoid SQL Injection in WHERE clause where is possible.

$sql= 'SELECT * FROM table WHERE id = ' . (int)$_POST['id'];