Database Security

When using a database and accepting input to create part of a database query, it is easy to fall victim to an SQL injection attack. SQL injection occurs when a malicious user experiments on a formto gain information about a database. After gaining sufficient knowledge—usually from database error messages—the attacker is equipped to exploit the form for any possible vulnerabilities by injecting SQL into form fields. A popular example is a simple user login form:

<form method="login.php" action="POST">
Username: <input type="text" name="username" /><br />
Password: <input type="password" name="password" /><br />
<input type="submit" value="Log In" />
</form>

The vulnerable code used to process this login formmight look like the following:

$username = $_POST[’username’];
$password = md5($_POST[’password’]);
$sql = "SELECT *
FROM users
WHERE username = ’{$username}’ AND
password = ’{$password}’";
/* database connection and query code */
if (count($results) > 0)
{
// Successful login attempt
}

In this example, note how there is no code to filter the $_POST input. Instead the raw input is stored directly to the $username variable. This raw input is then used in the SQL statement—nothing is escaped. An attacker might attempt to log in using a username similar to the following:

username’ OR 1 = 1 --

With this username and a blank password, the SQL statement is now:

SELECT *
FROM users
WHERE username = ’username’ OR 1 = 1 --’ AND
password = ’d41d8cd98f00b204e9800998ecf8427e’

Since 1 = 1 is always true and - begins an SQL comment, the SQL query ignores everything after the - and successfully returns all user records. This is enough to log in the attacker. Furthermore, if the attacker knows a username, he can provide that username in this attack in an attempt to impersonate the user by gaining that user’s access credentials.
SQL injection attacks are possible due to a lack of filtering and escaping. Properly filtering input and escaping the output for SQL will eliminate the risk of attack. To escape output for an SQL query, use the driver-specific *_escape_string() function for your database. If possible, use bound parameters. For more information on bound parameters, see the Escape Output section earlier in this chapter or the Database Programming chapter.

SOURCE:ZEND PHP5 CERTIFICATION STUDY GUIDE