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

weierophinney | 2 comments | April 10th, 2007

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.