PHP Security Guide
Table of Contents

PHP Security Guide: Databases and SQL


< Previous Next >
Form Processing Sessions

Exposed Access Credentials

Most PHP applications interact with a database. This usually involves connecting to a database server and using access credentials to authenticate:

<?php

$host = 'example.org';
$username = 'myuser';
$password = 'mypass';

$db = mysql_connect($host, $username, $password);

?>

This could be an example of a file called db.inc that is included whenever a connection to the database is needed. This approach is convenient, and it keeps the access credentials in a single file.

Potential problems arise when this file is somewhere within document root. This is a common approach, because it makes include and require statements much simpler, but it can lead to situations that expose your access credentials.

Remember that everything within document root has a URL associated with it. For example, if document root is /usr/local/apache/htdocs, then a file located at /usr/local/apache/htdocs/inc/db.inc has a URL such as http://example.org/inc/db.inc.

Combine this with the fact that most web servers will serve .inc files as plaintext, and the risk of exposing your access credentials should be clear. A bigger problem is that any source code in these modules can be exposed, but access credentials are particularly sensitive.

Of course, one simple solution is to place all modules outside of document root, and this is a good practice. Both include and require can accept a filesystem path, so there's no need to make modules accessible via URL. It is an unnecessary risk.

If you have no choice in the placement of your modules, and they must be within document root, you can put something like the following in your httpd.conf file (assuming Apache):

<Files ~ "\.inc$">
    Order allow,deny
    Deny from all
</Files>

It is not a good idea to have your modules processed by the PHP engine. This includes renaming your modules with a .php extension as well as using AddType to have .inc files treated as PHP files. Executing code out of context can be very dangerous, because it's unexpected and can lead to unknown results. However, if your modules consist of only variable assignments (as an example), this particular risk is mitigated.

My favorite method for protecting your database access credentials is described in the PHP Cookbook (O'Reilly) by David Sklar and Adam Trachtenberg. Create a file, /path/to/secret-stuff, that only root can read (not nobody):

SetEnv DB_USER "myuser"
SetEnv DB_PASS "mypass"

Include this file within httpd.conf as follows:

Include "/path/to/secret-stuff"

Now you can use $_SERVER['DB_USER'] and $_SERVER['DB_PASS'] in your code. Not only do you never have to write your username and password in any of your scripts, the web server can't read the secret-stuff file, so no other users can write scripts to read your access credentials (regardless of language). Just be careful not to expose these variables with something like phpinfo() or print_r($_SERVER).

SQL Injection

SQL injection attacks are extremely simple to defend against, but many applications are still vulnerable. Consider the following SQL statement:

<?php

$sql = "INSERT
        INTO   users (reg_username,
                      reg_password,
                      reg_email)
        VALUES ('{$_POST['reg_username']}',
                '$reg_password',
                '{$_POST['reg_email']}')";

?>

This query is constructed with $_POST, which should immediately look suspicious.

Assume that this query is creating a new account. The user provides a desired username and an email address. The registration application generates a temporary password and emails it to the user to verify the email address. Imagine that the user enters the following as a username:

bad_guy', 'mypass', ''), ('good_guy

This certainly doesn't look like a valid username, but with no data filtering in place, the application can't tell. If a valid email address is given (shiflett@php.net, for example), and 1234 is what the application generates for the password, the SQL statement becomes the following:

<?php

$sql = "INSERT
        INTO   users (reg_username,
                      reg_password,
                      reg_email)
        VALUES ('bad_guy', 'mypass', ''), ('good_guy',
                '1234',
                'shiflett@php.net')"; ?>

Rather than the intended action of creating a single account (good_guy) with a valid email address, the application has been tricked into creating two accounts, and the user supplied every detail of the bad_guy account.

While this particular example might not seem so harmful, it should be clear that worse things could happen once an attacker can make modifications to your SQL statements.

For example, depending on the database you are using, it might be possible to send multiple queries to the database server in a single call. Thus, a user can potentially terminate the existing query with a semicolon and follow this with a query of the user's choosing.

MySQL, until recently, does not allow multiple queries, so this particular risk is mitigated. Newer versions of MySQL allow multiple queries, but the corresponding PHP extension (ext/mysqli) requires that you use a separate function if you want to send multiple queries (mysqli_multi_query() instead of mysqli_query()). Only allowing a single query is safer, because it limits what an attacker can potentially do.

Protecting against SQL injection is easy:

  • Filter your data.

    This cannot be overstressed. With good data filtering in place, most security concerns are mitigated, and some are practically eliminated.

  • Quote your data.

    If your database allows it (MySQL does), put single quotes around all values in your SQL statements, regardless of the data type.

  • Escape your data.

    Sometimes valid data can unintentionally interfere with the format of the SQL statement itself. Use mysql_escape_string() or an escaping function native to your particular database. If there isn't a specific one, addslashes() is a good last resort.


< Previous Next >
Form Processing Sessions