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.

Comments

No comments exist on this article yet. You can be the first!

Leave a Comment






Recent Articles

Getting Rid of Rogue Software
2008-09-12 Rogue software has been making is way around recently, and here is how to stop it...for you at least.
Writing Secure SQL Queries
2008-02-25 Writing SQL code that 'just works' can cause you some serious trouble.
My Experience with Ubuntu Gutsy 7.10
2007-11-01 I will talk about the desktop effects, restricted drivers, restricted software, & themes.
7 Tips to Keep WinXP Running Fast & Smooth
2007-10-06 Not maintaining your computer can negatively affect it's performance.
My 18 Favorite Firefox Extensions / Add Ons
2007-08-28 My favorite may not be yours, but that's the great thing about Firefox - customize how YOU want it!

Contact

Wells IT Solutions
Phone: 219-440-2312