whateverthing.com

☰

Lately I’ve been feeling a bit burnt out about technology and even the world in general. This summer has been a bit of a pounding in a few ways, at least if you follow the news. And I do follow the news - entirely too much. The world seems to be trending to a dark place.

Again.

But on the bright side, there’s one place that has always been dark and foreboding. No matter how bad the world gets, this one place can’t get any worse than it already is.

I speak, of course, of forgotten code. Not dead code, not mere legacy code, but rather, code that runs every day yet hasn’t been looked at by a developer in years. Often, the code was written in a hurry using the fastest time-to-implement solution available. Sometimes by a very inexperienced developer.

All of that means one thing: it could be vulnerable.

Here Be SQL Injections

One of the most common issues with forgotten code - forgotten PHP code, in particular - is the dreaded SQL injection. When attackers (or even just curious non-evil hackers) stumble over a vulnerable database query, this gives them a foothold that can potentially let them bend your entire server or company to their will.

No, I’m not being overdramatic.

The good news is, there’s a relatively easy way to strengthen your SQL queries - and it all has to do with PHP’s growing maturity. Since PHP 5.3, there has been a huge push to make the language safer and more professional.

One of the changes has been to deprecate the mysql_* functions that older scripts relied too heavily on.

The mysql_* functions were implemented in such a way that concatenating strings together was the easiest way to talk to the database. This often resulted in people sending raw user input straight to the database:

$result = mysql_query("SELECT * FROM users WHERE name = '"
                       . $_POST['name']
                       . "' AND password = '"
                       . md5($_POST['password'])
                       . "'"
                       , $dbi);
$user   = mysql_fetch_assoc($result, $dbi);

(MD5 used as an example; the systems I’m talking about might not even have that modicum of security. And if you’re still only using MD5, you should upgrade to bcrypt immediately - it’s easier than you think, and much safer.)

The main problem with this query is, what if you submit a form and the username is:

' OR username = 'admin' --

Answer: It will splice it straight into the query, completely altering the logic. This might not grant the attacker admin access immediately - but the resulting error will certainly give them a hint that they’re onto something. And they’ll try again. And again. And again. And one of their tries might succeed.

You might say, “Well, this is easy to fix if you just prevent users from having quotes in their name!” - but that is very much the wrong answer. Many people have apostrophes in their name. You have to fix it the right way.

You might say, “Well, this is easy to fix if you just properly escape the input!”. You’d be somewhat right, but you’re still playing with fire. What if you don’t cover all your bases? If you roll your own escaping solution, it could end up being just as easy to penetrate the system.

So now you might be thinking, “Tell me, Oh Great and Powerful Code Wizard, what is the right way?”

You Must Make A Friend Of PDO

The truth is, “the right way” always changes; however, my current preference for solving this issue is to use PDO (PHP Data Objects). PDO is PHP’s attempt to communicate in a modern way with all kinds of database engines.

Configuring PDO to work with MySQL isn't the easiest thing in the world, but you can get used to it fairly quickly. In ye olde mysql_*, you would do this:

$dbi = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME, $dbi);

I’ve grown fond of using PHP constants to hold the database settings, although that solution doesn’t work in all situations.

The PDO equivalent for this would be:

$pdo = new PDO(
    'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME,
    DB_USER,
    DB_PASS
);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

This creates the connection string ("mysql:host=localhost;dbname=my_db", for example) and passes a valid username and password combination to the database driver. Then, it sets the error mode to make PDO throw exceptions if database errors occur. Exceptions can be very useful for debugging database issues.

Finally, it disables the emulation of prepared statements. This ensures that all prepared statement processing happens inside the database itself, completely absolving PHP of having to escape parameters.

The connection string, otherwise known as a Data Source Name (DSN), is probably the single scariest thing about PDO. It boils down to a fairly straightforward format, though. The first part tells PDO to connect to MySQL (other choices include Postgresql, SQLite3, and more), then it defines the host and the name of the database to talk to.

Play It Again, MyISAM

Let's revisit our first example, this time with a refreshing taste of PDO (mmm, tangy!):

$query  = $pdo->prepare(
    "SELECT * FROM users WHERE name = :name AND password = :password"
);
$result = $query->execute(
    array(
        'name'     => $_POST['name'],
        'password' => md5($_POST['password'])
    )
);
$user   = $query->fetch(PDO::FETCH_ASSOC);

(Again, remember what I said about “bcrypt”. Use it. Don't rely on md5.)

You can see that the user input (name and password) has been completely separated from the query. Using PDO’s parameter binding feature, the name/password array gets inserted into the query in a way that should be safe and reliable. The query itself becomes a lot easier to read, as well.

Astute readers will note that I've still fetched the input straight from the $_POST superglobal. While technically safer using PDO, this is a terrible thing to do. An attacker can put anything they want inside the variable, it will just be dealt with in a slightly safer way. They could put a 2MB string in the name field, and it would merrily get sent to the database. Something like this could cause a denial of service on your site, so you will want to look into doing input validation on all incoming data.

PDO has a few other advantages over the deprecated mysql_* functions, but in terms of preventing SQL injections, this parameter binding feature alone is the bee’s knees.

That's all for now. Thanks for checking in!

---

Updates:

• 2014-08-28: Edited to add paragraph about input validation. Who has two thumbs and forgot to point out that passing user data straight to the database is still a bad idea? This guy.
• 2014-08-28: Added example for disabling emulated prepare statements.

---

---