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:

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

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:

  // Using placeholders
  $stmt = $db->prepare("insert into user (name, email, url) values (?, ?, ?)");
  $stmt->bindParam(1, $name);
  $stmt->bindParam(2, $email);
  $stmt->bindParam(3, $url);
  $stmt->execute();
  
  // Using named parameters
  $stmt = $db->prepare("insert into user (name, email, url) values (:name, :email, :url)");
  $stmt->execute(array('url' = $url, 'name' => $name, 'email' => $email);
  

• 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!