SQL injections are a real treat. Even thou it is an old type of attack, a lot of people are still unaware how to protect their website. More important, what is PHP already doing to protect you?
For the purpose of this article I am talking about PHP (some part might apply to other languages). For those who are not familiar with sql injection, I would first recommend taking a look on wikipedia. For those who are well aware of those attack vectors and even know to defend them self, this blog post is not for you!
There is three attack vectors; data extraction, data manipulation and remote code execution. In most case, the hacker first gain access to the data and then can start modifying and eventually execute code on the server. In other word, when the attacker get access to your database, he can do whatever he wants with your data and can even compromise the whole server.
Until PHP 5.3, magic quotes were on by default. That directive automatically escape input variables to specifically avoid sql injection. It means that if I would send a form, the server would get “ted\’ website” instead of “ted’s website”. It was an effective way to prevent injection (exept for numeric value, which is discussed later on). On top of that, programmers can manually escape input using mysql_real_escape_string.
Why is magic quotes a decent protection? In most case, when an input is used in a sql query, it is quoted:
SELECT content FROM _pages WHERE title = “$title”
Meaning that if I would want to exploit it, I could use as input:
-1″ union select db_name() –
Now, by using magic quote, it would be percieved as -1\” union select db_name() — and the query would look like:
SELECT content FROM _pages WHERE title = “-1\” union select db_name() –”
The only reason the attack failed is because the input inside the query is quoted. There is a quote before the input and one after. And that is exactly where programmers create security flaws, usually it is with numeric value because the quote are not mandatory.
$id = mysql_real_escape_string($_POST['id']);
SELECT content FROM _pages WHERE id = $id
In this case, the query is totally unsafe and neither magic_quotes or mysql_real_escape_string can protect this query from sql injections.
The truth is, it is really easy to protect yourself, PHP is making most of the work, just remember:
- Put the magic_quote directive on (not always the best option) OR use mysql_real_escape_string on every input (recommended).
- Quote every value in every sql query.
What is difficult to understand is why enormous projects like Sony’s website are vulnerable to such attack. Security should always be one of the top concern in any web based project. I would even say that any project should be created around a security system.
A last word on the subject, I did not mention “SQL statements” or “stored procedure” even tho they are a really good protection to SQL injections, if you never heard of that, you should take a look here. I recommend using one of them over magic_quotes!