Protection against SQL Injection using PDO and Zend Framework

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. Usually, user input is not filtered by the script and is passed into a SQL statement.

PDO – PHP Data Objects – is a database access layer providing a standardized method of access to multiple databases. 
PDO provides a data-access abstraction layer, meaning that depending on what database you’re using, you will have apply the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn’t rewrite SQL or emulate missing features. Among  PDO benefits there are:
–    Access methods allow complete control over how attributes are read and written
–    Validation on a per-record and per-attribute level
–    Easier fetching of objects from related table
–    Reusable logic – means that the same codebase is much easier to maintain
–    Cleaner code by using object oriented code
–    Less errors from SQL query generation
–    Last but not least : Protection against SQL injection

In Zend Framework for database access, methods usually support prepared statements. Dynamic SQL queries are allowed, but you must escape all the parameter, otherwise you have SQL injection.  Because of this, prepared statements are encouraged to be used. They can handle escaping parameters for you.
Most people believe that using prepared statements they are 100% protected from SQL injection. But this is by far true. Input data should always be validated and sanitized, and PDO should be seen as another line of defense. PDO is not protecting you from other security vulnerabilities like XSS(cross-site scripting), but helps protect your application against SQL injection.

It may also occur a problem in Zend Framework when you have SQL injection in your application while you are using PDO_MySQL. PDO_MySQL is a more dangerous application than any other traditional MySQL applications. Traditional MySQL allows only a single SQL query. In PDO_MySQL there is no such limitation, but you risk to be injected with multiple queries. To avoid this you should try to use the correct prepared statements from Zend Framework. You should also pay attention when you have in your SQL query WHERE IN and ORDER BY; they cannot be handled by prepare statements normally. In this case you should escape your data.

Zend_Db has two escaping methods which can be used: quote() and quoteIdentifier(). Note that these two methods are handling strings by putting them between single quotes.

For more details see:

http://ezinearticles.com/?SQL-Injection-Protection-in-PHP-With-PDO&id=1815110

http://www.zend.com/webinar/Framework/70170000000bEs9-webinar-secure-application-development-with-the-ZF-20100505.flv