When working with PHP and MySQL, it is super easy to discount the possibility of SQL injection. It can happen to anyone, whether through laziness or a general obliviousness. It’s an easy case to overlook, if you’re not used to thinking about it.

This sort of form is common.

Any time you ask for user input is a time to be careful. In programming and in life, always remember that the user is not trustworthy. It is an important concept.

Whether that user is a boat captain on the run from giant snakes in search of a rare and mysterious flower, or another programmer who will be using your code — never assume that their input is valid or safe.

In this situation, the user inputs data via the form’s text fields. This provides a way for them to inject SQL.

The query PHP runs when the form is submitted probably looks something like this:

SELECT [fields] 
FROM [table] 
WHERE username="[username]" AND password="[password]"

In this instance, knowing which fields are being retrieved from which table isn’t important.

Submitting the above form would create a query something like this:

SELECT [fields] 
FROM [table] 
WHERE username="admin" AND password="friendship"

But say we were to do:

The SQL might then be:

SELECT [fields] 
FROM [table] 
WHERE username="admin" OR "1"="1"  AND password="whatever"

The actual SQL isn’t important, here; what is, is that the user has the ability to modify your query.

A quick explanation of the SQL above:

“1”=”1″ always evaluates as TRUE, but that doesn’t matter in this case. AND takes priority over OR (I can’t remember what this is called — shaun?). Unless admin‘s password actually happens to be whatever, the latter portion of the query will evaluate as FALSE.

1. WHERE username=”admin” OR “1”=”1″ AND password=”whatever“
2. WHERE username=”admin” OR TRUE AND FALSE
3. WHERE username=”admin” OR FALSE

The only condition that matters becomes the username.

Life would be so much easier if we didn’t ever have to deal with user input. Sadly, people want to feel necessary. They want you to listen to them, to take their words to heart, and they always want their programs to server a purpose, or at least be mildly entertaining.

Once we do away with misplaced and ill-advised trust, programming becomes much more complicated, and tiresome — but users hate security flaws, and programmers will seize any opportunity to blame your library when they use it wrong. 😛

Since we don’t trust the input from the login form, we need to sanitize the strings before using them to create the query. We can do that using the mysql_real_escape_string function:

$username = $_POST['username']; //the submitted username
$password = $_POST['password']; //the submitted password

$query = "SELECT * FROM users "
           .  "WHERE username=\"".mysql_real_escape_string($username)."\" "
           .  "AND password=\"".mysql_real_escape_string($password)."\"";

$result = sql_query($q); //run the query
... // do other stuff

Calling mysql_real_escape_string will escape certain troublesome characters, including both single and double quotes.

Resubmitting the form will result in the perfectly safe:

SELECT * 
FROM users 
WHERE username="admin\" OR \"1\"=\"1"  AND password="whatever"

Unless you have a very strange naming convention for users, that will result in no match and the login will fail.