Pr0gr4mm3r

Writing Secure SQL Queries

Writing secure SQL queries can be one of the most important factors in your site’s security, yet I see so many people that don’t do it. So many programmers write queries that “just work” taking little consideration of the malicious potential of unsecured code – SQL queries included. This post is going to show some examples of bad queries in MySQL and how to correct them.

I will start out with the most common mistake that I see:

SELECT * FROM Users WHERE user = $_POST['user'] && password = $_POST['password']

The query above in itself is correct. Assuming the HTML form posts the correct data, the query will correctly process the information and find whether the website user provided the correct credentials to sign in to their account.

…but what if I, being the malicious user that I am (not really), inserted the following data?

User: admin
Pass: something’ or ‘x’='x

That would make the query equal to this:

SELECT * FROM Users WHERE user = ‘admin‘ && password = ‘something’ or ‘x’='x‘

Since ‘x’ always equals ‘x’, the following query would grant me access as the user, admin! It sound like a simple attack, and surely web database programmers know to protect themselves from attacks like this. The sad truth is that this happens all the time.

If you are a programmer that writes SQL queries, I hope you know about this method of exploitation. If you don’t, please, please, please read over the following articles, because it’s critical to write queries that go beyond “just working”. You must make them secure.

Further Reading

Typing “sql injection” into Google returns several good articles, and here are a few of them that are worth reading over.

SQL Injection Attacks by Example – The people from unixwiz.net were asked to review an intranet site for a customer. The site had some exploitable areas, and they show what steps they took to compromise an admin-level account. It’s very interesting to know what malicious users go through to gain unauthorized access to your site. By knowing this, you can help keep them out.

SQL Injection – A definition and couple examples from Wikipedia.

Exploits of a Mom – Ok, so it’s not an informative article, but it’s funny.

Posted in: MySQL

This entry was posted on Monday, February 25th, 2008 at 12:50 pm and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.