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

June 18, 2010
by Teo

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'];

This entry was posted in Best Practice, PHP Development and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>