or How To Protect User Data From SQL Injection
xkcd
Fun Facts:
SQL injection has been around pretty much ever since websites have stored data in databases.
The Open Web Application Security Project rated injection attacks as one of the top 10 threats facing web applications in 2017.
SQL injection is still responsible for many large data leaks. In 2016, the Illinois and Arizona State Board of Elections voter registration databases were breached. While no information was stolen in Arizona, in Illinois attackers had access to voter data including name, address, date of birth, gender and partial Social Security numbers, for about 80,000 people.?
That?s Terrifying, What Is It?
SQL injection is a type of attack that targets an application?s database through the insertion of unintended code into user input fields. By taking advantage of SQL syntax the attacker can use entry fields to capture information from those databases ? including things like passwords and credit card numbers. Someone could even take control of your database or delete all of the information it holds.
How Does It Work?
SQL Injection works by using information inserted into a field to manipulate the corresponding SQL statement into performing an unintended action.
Here is a basic example:
SELECT * FROM customers WHERE name = ” + user_name + “;
In this example let?s say the variable user_name is created directly from the user?s input on a website. That string is inserted into the above SQL statement and all fields relating to that user name are returned. No big deal.
But what happen?s if someone types Steve? OR 1=1; ? as their user name? then the SQL generated would be this:
SELECT * FROM customers WHERE name = “Steve” OR 1=1;–“;
Since 1=1 is always true, this would return all the data in the table. Not Good!
Let?s take one more look at that comic.
xkcd
The son?s name is Robert?); DROP TABLE students; ? What would that do exactly?
Well, we can assume that if Little Bobby Tables was being added to the school?s database of students, the SQL statement would look something like:
INSERT INTO students (name) VALUES (‘<Student Name>’);
? and when we insert Bobby?
INSERT INTO students (name) VALUES (‘Robert’); DROP TABLE students;–‘);
Since Bobby?s name contains the ); the VALUES argument is closed in the middle of his name and the text that follows DROP TABLE students is a new SQL statement that deletes the whole table. Finally the ? at the end comments out the remaining SQL, essentially ignoring the rest of the original code and making sure no error occurs.
So, What Your Saying Is My Data Will Never Be Safe Again?
Nope! There are actually a lot of steps you can take to protect yourself from this sort of attack. Let?s look at few:
- Sanitize Data. The first step is controlling what the user is allowed to input. The best way to do this is to limit the types of input allowed for a certain field. For example in a phone number field you could only allow numeric input, or in an email field only allow characters that can be found in a valid email address. Obviously, some fields will require characters that could be used in an attack, so this method isn?t unbeatable.
- Configure Error Reporting. Often the default error reporting on Database Management Systems has developer debugging information in it. This can return helpful information to the attacker, like table names or column names. Make sure that you aren?t showing this type of information to outside users as it could make a potential attacker?s life way easier.
- Use Bound Parameters. Bound parameters allow you to store user data in a variable and then insert it into an SQL statement that has been created with placeholders. Since the SQL statement and the variable are sent to the server separately, bound parameters are good at protecting against injection. Take that Little Bobby T! An example of bound parameters in an update method in Ruby would look like this:
def update sql = <<-SQL UPDATE students SET name = ?, grade = ? WHERE id = ? SQL DB[:conn].execute(sql, [self.name, self.grade, self.id])end
It?s crazy to think that something so simple and well understood is still responsible for so many big data breaches. Even in high profile instances like the voter registration hacks mentioned above and the recent Equifax breach (which wasn?t due to SQL injection, but to a similar vulnerability that the company failed to address), the root of the vulnerability comes down to human error and lack of attention to detail. Taking the time to carefully think through the problem can significantly help us protect user data from common breaches, especially SQL injections.
