Security Tip: Use a Database Abstraction Layer to help prevent SQL Injection

SQL injections are a common vulnerability in web-based applications that use
databases. As an example of a potential SQL injection, consider a login form
asking only for a username, where the backend has code reading:

A malicious hacker could attempt to enter the value ""; DELETE FROM
user WHERE 1"
, which would have the effect of removing all users in
the table. (Granted, this won’t happen with PHP’s mysql extension as it will
not execute multiple queries by default; this is just an illustration.)

There are several methods to prevent this type of attack:

  • Use your database extension’s quoting mechanism to quote values prior to
    executing a query:

    • MySQL: mysql_real_escape_string()
    • PostgreSQL: pg_escape_string()
    • SQLite: sqlite_escape_string()
  • Use PDO’s prepared statements support. PDO uses the native prepared
    statement support for your database, or, if your database does not
    support prepared statements, emulates it using the quoting mechanisms
    available; either way, you protect against SQL injections. As examples:

  • Use a database abstraction layer (DAL), such as
    AdoDB,
    PEAR::MDB2, or
    Zend_Db.
    Most DALs provide support for prepared statements and quoting, often
    delegating to PDO.

Protect your database and adopt one of these practices today!

About Matthew Weier O'Phinney

Matthew is a Principal Engineer at Zend Technologies. He is currently project lead for both Zend Framework and Apigility; a Zend Certified Engineer; and a member of the Zend Education Advisory Board, the group responsible for authoring the Zend Certification Exam. He contributes to a number of open source projects, blogs on PHP-related topics, and presents talks and tutorials related to PHP development and the projects to which he contributes. You can read more of his thoughts on his blog, mwop.net/blog.