Wednesday, 11 April 2007

Prevent SQL Injection Attacks By Using Abstraction Database Layer

In a post over the Zend developer Zone, Weirophinney gives you a security tip to prevent SQL injection attacks by using Abstraction database layer. He says, SQL injections are a common vulnerability in web-based applications that use databases. As an example, he considers a potential SQL injection, a login form asking only for a username, where the backend has code reading as follows:

mysql_query('SELECT * FROM user WHERE username = "' . $_GET['username'] . '");

He warns you that 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.

He points out the following methods to prevent this type of attack:

• Use your database extension's quoting mechanism to quote values prior to executing a query in MySQL like mysql_real_escape_string(), PostgreSQL: pg_escape_string() and 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.
• 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.