An SQL injection attack
En Español  

Disclaimer: Of course I don't do this in the systems of others (without consent) and neither should you. Even if you don't do any damage, in pretty much every country is against the law to access a system in this way.

Since I wrote two different guides about the use of prepared MySQL statements in PHP, and one of the reasons for use this is to prevent SQL injection attacks, I am going to write a little about these attacks that many websites has suffer and continue to suffer even today. More than define what this attack is, the objective is to show how it works.

Lets start by using an example table

users

id	name	user	pass	group
1	admin	admin	whatever	admin
2	Juan	juan	something	normal
3	Valencia	valencia	other	normal
4	Cindy	cindy	nothing	normal

Note: Do NOT store the passwords in plain text, I do it here as an example, but you should not do this, because if your database becomes compromised, the attacker gains access to every account without any effort. Hash the passwords and use a salt, unfortunately my guide on this in yet to be finished, but Google is your friend. Also, do NOT use passwords this simple.

A simple way to log in as a user, is to search in the database the username and the password that the person provide us, and allow the connection as said user if the both values are found in a record, or show an error message if they are not found. For example, given this two input fields in a form:

We would receive this in two post variables (you should not send passwords in the URL of the website by using the get method, because then it is stored in the browser history and someone else with access to the computer could easily access the account):

Now, THIS IS WHAT YOU MUST NEVER DO, neither this nor something similar that results in sending the user input directly and unfiltered to the database:

Of course this is going to work, but in a moment you will see why this is such a terrible idea. And, the sad and disturbing part is that I have seen many systems that do this. I don't know what the experience of others is, but in my own personal experience, most systems developed in-house do this.

Lets say we fill the two input fields using this two values:

If we substitute the values in the respective variables, the query that we end up sending to the database would be as follows:

This would give us back a database entry, we could then store the values in session variables to handle the connection, for example, we could end up with the following values:

All seems nice and it does work, but, what if instead of fill the input fields with those values, someone fill them with these values instead?:

After we do the substitutions, we would end with the following query!

When MySQL does this operations, the following would occur, as neither "whatever" nor the empty password field exists, and one always equals one:

The AND operator takes precedence over the OR operator, and the AND operation is only TRUE if both values are TRUE. In this case both values are FALSE, therefore we end up with this operation:

The OR operator is TRUE if either of the operands are TRUE, so in this case, the result of all the conditional operations is a simple TRUE for every record, thus making this act as a select query without a condition. And a select query without a condition would give us the entire table (every record is a match).

Now, since the PHP script receives the table from the database but only cares about the first record that it reads, because it is only expecting one record as a result, then we would start a session with that first user found.

The first user in most systems is almost always the administrative user. So we are done for, our system is under the control of some random person which most likely doesn't have the best intentions.

This is how a SQL injection attacks works, it "injects" code that our database executes. And this is how it can be exploited to gain administrative privileges. You should never trust the input of the users of your system, you must always sanitize the input.

Of course the query may be different, in this example I only modify the select query. What can be done may vary with the system. I will only use this example to illustrate this, but upon analyzing a system you can almost always get the information from other users, from other tables in the database, you can get the entire database, change information in the database, even delete tables or delete the entire database. And of course, if you log in as an administrator you may already have enough privileges to cause real mayhem and serious damage.

In the next posts I cover the use of prepared MySQL statements which offer protection against this, even if that was not why they were originally implemented (it was for speed). In a near future I am hoping to also finish a guide about how to use hashes and salt to protect the passwords in the database, and how to protect the sessions. I also hope to post a simple user system implementing all the information from this guides. I just need more rl time =/

Related Articles

• Prepared MySQL statements in PHP (structured examples)
• Prepared MySQL statements in PHP (object oriented examples)