Nov 09 2008

Responsible SQL: How to Authenticate Users

By James

Most SQL-injection articles set a horrible example for young programmers.

Here is a very typical “bad example” of why you need to escape user data before it goes into SQL queries:

(ed. The symbol « is a line break that’s not in the real code.)

$username = $_POST[‘username’]; // username=admin
$password = $_POST[‘password’]; // password=’ OR 1=1; — ‘

$user = $db->query("SELECT * FROM users WHERE «
           username=’$username’ AND «
           password=’$password’ LIMIT 1;");

The point, of course, is that you must sanitize your user input, or else this person would run this query:

$user = $db->query("SELECT * FROM users WHERE «
           username=’admin’ AND «
           password = ” OR 1=1; — ‘ LIMIT 1;");

Which grants the sneaky user all your admin privileges. Other versions have nefarious users dropping your users or articles tables.

The problem is: this is the wrong way to authenticate users. These examples are written for beginners to understand the importance of sanitizing input, but they also provide a model to those beginners for how user authentication works. And it’s a very bad model.

This is a long one, more after the break. (more…)

Categories: Database, MySQL, PHP
tags: , , , , , , , ,