Writing Secure SQL Queries

Published: February 25th, 2008 by: Andrew

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.

2 Responses to “Writing Secure SQL Queries”

Andrew Wells
Mar 3rd, 2008
12:21 am

Yeah, WordPress likes to mess with my quotes. I have been looking into how to disable that fancy formatting.

That’s a good idea with putting in that magic quotes test. I was escaping strings on this one server, and everything was being outputted with extra backslashes. Turned out it was because “magic_quotes_gpc” was enabled. I always disable that function whenever possible because I secure it myself.
Matt
Mar 3rd, 2008
12:00 am
I knew about SQL injection, but I actually never tried it. I copied your sample into a login form on my site (after removing the escape logic), and it does work. Neat-O! (I had to replace all those funky quotes in your sample code though).

I escape the user input in my PHP code with this function:
```
function escapeData($data)
{
	if(ini_get('magic_quotes_gpc'))
		$data = stripslashes($data);
	return mysql_real_escape_string($data);
}
```
It’s more portable than just using mysql_real_escape_string() everywhere.

About the Author

Andrew has been coding PHP applications since 2006, and has plenty of experience with PHP, MySQL, and Apache. He prefers Ubuntu Linux on his desktop and has plenty of experience at managing CentOS web servers. He is the owner of Wells IT Solutions LLC, and develops PHP applications full time for anyone that needs it as well as does desktop computer support locally in the local area. He spends most of his free time exploring new programming concepts and posting on The Webmaster Forums.