SQL Injection

and how to avoid it

SQL Injection seems to be one of the most common ways that hackers can steal passwords or other stuff that’s commonly saved in a database.

How it’s done: A lot of websites send some user input to a database inside of a query unfiltered. Heise has written an article on this EIGHT years ago, and yet it seems like quite a few people who should know better still don’t filter the input that gets sent to the database. This article goes into a bit of detail on how it’s done, but basically you find a way to get your own input sent to the database by the website. Let’s assume you have a Login form somewhere, that asks for username and password. Let’s then assume you have an SQL Query like this: “SELECT first_name from users WHERE username = ‘$username’ AND password = ‘$password'”. Now if you just replace $username and $password with whatever the user enters you might end up with a username like “‘; DROP TABLE users;–“. If you replace your username with that string that’s been entered into the username field of your form you will end up not having a table named users anymore.

The last article I linked goes into a bit more detail and how to use it more effectively on websites that are scripted to use index.php as the main page of the site and having it dynamically create websites with content from the database.

How to prevent SQL Injections: The obvious way would be to just str_replace(“”, “”, $username), but if it was that easy I wouldn’t bother writing this. Php even has a function that was supposed to account for all different ways people could mess with the user input. Now even the php manual says that it’s discouraged in favor of the top choices for avoiding SQL Injections. First, however, I suggest a different approach that no one really has commented on. You could just get the SHA-1 hash (hell even md5) for all the variables you’re using in a query. Obviously you would also have to save the hash of everything in the database. The where clause wouldn’t look like “WHERE id = 1” but “WHERE id = ‘356a192b7913b04c54574d18c28d46e6395428ab'”. Obviously the hash function always produces a string of the numbers 0-9 and the letters a-f, which means that it’s completely safe.

The more obvious choices, though, are either mysqli or PDO. Both of those php extensions support prepared statements and parameterized queries.

I have replaced all the SQL on my website to use PDO and the above query would look something like:

$query = $connection->prepare(“SELECT first_name FROM users WHERE username = :username AND password = :password”);

$query->bindParam(‘:username’, $username, PDO::PARAM_STR);

$query->bindParam(‘:password’, hashpassword($password), PDO::PARAM_STR);

obviously we calculate a hash of the password first. We bind both parameters and after that we can just $query->execute(); to send it to the database. Obviously inserting a row into the database like one would when putting a blog post into it gets quite a bit longer than it would ordinarily be, but as long as something that can be changed by users gets sent to the database one must take precautions.

I seriously hope people will be more security conscious in the future and at least think about what can happen when we send stuff to the database. Maybe eventually we can get rid of SQL Injections, cross site scripting, cross site request forgeries and session stealing. I have a feeling that it’ll be a while, though.