I write code.
Yes, most of you know this, but what most of you don’t know is:
I didn’t write secure code, until now.
Let me explain: It is no good building a website for a client and going “Hey! Here’s a new site I’ve built you. It’s super secure!”, when you haven’t given one thought the people on the dark side (of the planet and of the force), and allowed for SQL injection attacks.
SQL injection attacks are where people ‘inject’ code into your site. Imagine this scenario:
A good person goes onto the website and enters ‘Josh’ into a box called ‘username’.
The PHP for this database query looks as so:
$db->query("SELECT name FROM people WHERE username='".$username."'");
This code inserts
$username into the mysql query. The query that is sent to the database looks as follows:
SELECT name FROM people WHERE username='Josh';
So what happens if someone injects SQL into this query?
Say Mr Baddie comes along and enters
Josh'; DELETE FROM people into the ‘username’ box, the query sent to the database will be:
SELECT name FROM people WHERE age='Josh'; DELETE FROM people;' Note the apostrophe at the end!
SELECT name FROM people WHERE age='Josh';
DELETE FROM people;'
A lovely list of people with the username Josh will be returned. Before promptly deleting everything from the table ‘people’.
Oh Dear. I hope someone remembered to take a backup of that database.
Once people have been observed throwing insult-ridden shouts across the office at each other, blaming each other for the incident; and the boss has been to throw his own insults; and the site has been down for a week; and the head office has held a conference call to throw their own insults, it is finally time to sit down and work out what went wrong and how it can be prevented in the future.
Option 1: Delete the site and pretend none of this ever happened.
Option 2: Use different users for fetching and writing data to the database.
Option 3: ‘Sanitise’ the code to prevent SQL injection attacks.
I suggest Option 1 if you want a 100% guarantee that this will never happen again, but a compromise is required for those who don’t want to/can’t take it that far.
I suggest Option 2 & 3 for the near best you can get. I will explain Option 3 only.
The process of sanitisation not only protects against code injection, it also protects against the good guys potentially accidentally breaking your server.
There are different ways of sanitising user input for each language. I will demonstrate how to sanitise input for a mysql database through php.
It’s a function called
So, instead of putting the given input straight into the sensitive belly of the database, I need to sanitise it.
Here’s the code instead.
$age = $db->real_escape_string($_POST['username']);
$db->query('SELECT name FROM people WHERE age=".$username');
So when Mr Baddie inputs
Josh'; DELETE FROM people into the ‘username’ box, the content is sanitised and the day is saved.