Adam Innes · Blog

Lessons from 2011's Breach Parade

· 4 min · security, php, mysql, sql injection

It’s been a rough few months to run a website, with a string of well known companies admitting that someone got into their systems. A lot of the details floating around come from attackers’ own claims or from press reports, so I’m going to stick to what the companies involved have actually said, and then get practical about a plain PHP and MySQL app.

What we actually know

Sony posted on the PlayStation Blog on April 26 that between April 17 and April 19, PlayStation Network and Qriocity account information was compromised in what it called an illegal and unauthorized intrusion, and that it had turned both services off. Among the information Sony said it believed was obtained were names, addresses, email addresses, birthdates and passwords. It doesn’t say how the attacker got in, so neither will I.

Barracuda Networks was more specific. On April 12, its product management blog said an automated script had crawled the company’s website looking for unvalidated parameters and, after about two hours, found a SQL injection hole in a simple PHP script that served customer case studies. Barracuda said its own web application firewall had been left in passive monitoring mode during a maintenance window. The detail that should make every PHP developer wince is that the little case study script shared a database with marketing data, which held names and email addresses of leads, partners and some employees.

And today, MITRE and SANS posted the 2011 CWE/SANS Top 25 Most Dangerous Software Errors. Number one is SQL injection. Its entry recommends prepared statements and running database accounts with the least privilege they need, which lines up nicely with the fixes below.

Use prepared statements, everywhere

The PHP manual’s page on prepared statements says that if an application uses only prepared statements, you can be sure no SQL injection will occur, with the caveat that other parts of a query built from unescaped input can still be a problem. With PDO it looks like this:

$db = new PDO('mysql:host=localhost;dbname=shop;charset=utf8', $user, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $db->prepare('SELECT id, title FROM case_studies WHERE vertical = :vertical');
$stmt->execute(array(':vertical' => $_GET['vertical']));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

The PHP manual’s caveat about other parts of the query matters in practice. The MySQL manual notes that parameter markers can only stand in for data values, not SQL keywords or identifiers, which means they can’t cover table names, column names or ORDER BY directions. If a user can pick a sort column, check it against a fixed list of allowed names in PHP before it goes near the query. And note that the charset in the DSN only works as of PHP 5.3.6, so if you’re on an older 5.3, upgrade.

Give the app a MySQL user that can’t do much

If a query does slip through, the damage is limited to what the database user is allowed to do. A typical web app needs to read and write rows in its own database, and that’s it:

GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_web'@'localhost' IDENTIFIED BY 'long-random-password';

Use a separate account with CREATE, ALTER and friends for schema changes. The MySQL 5.5 manual says to take particular care with FILE and the administrative privileges. FILE can be abused to read any file the server can read into a table, PROCESS shows the text of running statements (including ones that set passwords), and SUPER can terminate other sessions. A web app needs none of them. And think about Barracuda’s case study script. Giving it its own user on its own database would likely have kept the marketing list out of reach.

Turn off display_errors in production

A database error printed to the page tells an attacker your table names and sometimes your query structure, which is exactly what they need to refine an injection. PHP 5.3 comes with a php.ini-production file that already sets the first two lines here. The third, error_log, is commented out in that file, so point it somewhere sensible yourself:

display_errors = Off
log_errors = On
error_log = /var/log/php/error.log

The comments in that file warn that displaying errors in production could leak sensitive information like database usernames and passwords, and recommend logging instead. The built in default for display_errors is on, so if your server’s php.ini came from somewhere unknown, check it rather than assuming.

Don’t let a stack trace spill your password

This one catches people using PDO. The PHP manual’s PDO connection page warns that if you don’t catch the exception from the PDO constructor, the fatal error comes with a back trace that can leak your connection details, and those include the username and password you passed in. So catch it, log the real message, and show the visitor something boring:

try {
    $db = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
    error_log($e->getMessage());
    header('HTTP/1.1 503 Service Unavailable');
    exit('Sorry, something went wrong. Please try again later.');
}

A set_exception_handler() doing the same for everything else is a good backstop.

The takeaway

None of this is exciting, and that’s the point. Prepared statements close the door, a least privilege MySQL user limits what’s behind it, and quiet error pages stop you from handing out a map. My advice is to pick one app this week and check all four. It’s a lot cheaper than writing the blog post Barracuda had to write.

← all posts