You log into the staging site you spun up without overthinking it a few days ago, only to find the homepage replaced by a black background and scrolling green marquee text saying "Hacked by ShadowR00t". Very original, kid. The worst part is that the site was hosted on a shared server running PHP 4, and the admin panel was completely full of holes. They didn't use any sophisticated exploit, nor did they overwhelm the server with some obscure attack. They simply dropped a single quote into the login form.

If you've been writing web code for a while, you've definitely heard of SQL Injection. But it still amazes me how much production code I encounter daily where security is completely missing. People just don't learn.

The single quote sieve

To understand the problem, let's take a look. Imagine you have your typical login form, and in your PHP code, you process the request by validating it against your MySQL database (probably the 4.0 version that comes with cPanel).

The code written by "the client's cousin" looks something like this:

<?php
$usuario = $_POST['username'];
$clave = $_POST['password'];

// Nos conectamos y lanzamos la query a pelo
$sql = "SELECT id FROM usuarios WHERE username = '$usuario' AND password = '$clave'";
$resultado = mysql_query($sql, $conexion);

if (mysql_num_rows($resultado) > 0) {
    echo "¡Bienvenido!";
} else {
    echo "Acceso denegado.";
}
?>

At first glance, it seems logical. The problem is that we are blindly trusting whatever the user types into the form. What happens if, in the username field, someone feeling adventurous types exactly this?

admin' --

If we substitute that input into our $sql variable, the query that actually reaches the MySQL engine ends up looking like this:

SELECT id FROM usuarios WHERE username = 'admin' -- ' AND password = 'lo-que-sea'

In SQL, the double dashes (--) denote the start of a comment. Everything that follows is completely ignored. Congratulations, the attacker just logged in as an administrator without needing to know the password, simply because we concatenated text strings without sanitizing them.

Another classic is dropping ' OR '1'='1 into the username field. The query will evaluate that one equals one, which is always true, returning the first record in the table (which is usually the damn administrator).

How to patch this once and for all?

The quick patch everyone uses is crossing their fingers and hoping the server has magic_quotes_gpc enabled in php.ini, but relying on that is like playing Russian roulette. If you migrate hostings, you might be in for a very nasty surprise.

The proper way to prevent your database from getting wrecked is to always escape data before feeding it into the query. If you're expecting a number, force the type with intval(). If you're working with text strings, you absolutely must pass them through mysql_real_escape_string():

<?php
// Limpieza básica antes de tocar la base de datos
$usuario = mysql_real_escape_string($_POST['username']);
$clave = mysql_real_escape_string($_POST['password']);

$sql = "SELECT id FROM usuarios WHERE username = '$usuario' AND password = '$clave'";
?>

This function is responsible for prepending backslashes to dangerous characters like single or double quotes, neutralizing the injection so MySQL interprets it as plain text and not as instructions.

The future of validation

It fascinates me that in the middle of 2005, with the web supposedly maturing into more serious applications, we still have to explain this. Sometimes I think the blame lies with the tutorials floating around forums, which teach you how to do things quickly to get them working, but forget all about security.

Honestly, I doubt SQL injections will disappear anytime soon. As long as it's easier to brute-force string concatenation than to build a secure system, laziness will win. I guess until programming languages force us to separate SQL code from data by design—perhaps with those weird abstractions they talk about now in PHP 5 with OOP—we'll keep seeing defaced forums and dumped databases all over the internet. For now, we're stuck cleaning variables by hand, one by one, and crossing our fingers that we don't miss any. I'm going to make another coffee.