Responsible SQL: How to Authenticate Users
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.)
$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:
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. Continue reading