SQL Injection

SQL stands for Structured Query Language. It is used to query data stored in a database. The structure of an SQL query is fairly easy to understand. For example:

SELECT * FROM users WHERE name = ‘fred’

Will select anyone in the database that has the name ‘fred’. SQL commands are usually formatted in capital letters.

Most sites with any extended functionality will use a database and PHP pages can have SQL queries embedded into them. However, if user input is unsanitized then they can become vulnerable to SQL injections.

Examples

Lets start with a very simple SQL injection. Say there is a table called "users" that has a field in it called UserID. Now there is a script on the site that lets you enter the UserID and the SQL will fetch the information about the person who owns the UserID from the database. The SQL for it is as follows:

SELECT * FROM `users` WHERE UserID = $ID

($ID being a variable passed through from PHP)

The * means select all that match that ID. Now say instead of entering a number, you enter the word UserID. This will make the SQL perform the following query:

SELECT * FROM `users` WHERE UserID = UserID

This is similar to a 1=1 SQL injection. The UserID is always equal to itself. So the result of the SQL query would be the page showing you the user details of every single person who has registered.

In simple terms, what you enter becomes part of the SQL query – meaning that you can type SQL commands and these will be added to the actual SQL query.

Now lets try another SQL query. This is one of the most commonly used SQL injections and is often successful. If there is a login box asking for a username and password on the site the username and password will be compared to usernames and passwords stored in the database. Say the SQL is like so:

SELECT * FROM users WHERE username = '$username' AND password = '$password';

$username and $password again being the usernames and passwords entered into the PHP form. Now if the following details were to be entered into the username and password form fields:

foo' OR 1=1 OR 'bar

The resulting SQL query is:

SELECT * FROM users WHERE username = 'foo' OR 1=1 OR 'bar' AND password = 'foo' OR 1=1 OR 'bar';

This tricks the query using the 1=1 statement. There is no column called ‘1’ in the database so its basically saying if 1=1 which always ewuated to trye. So the result of this SQL injection is the attacker being logged in as the first username on the list, which in a lot of cases is the admin.

String terminator

In SQL, a double dash ( -- ) or comment syntax /* signifies the end of the query. Adding a double dash to the end of your SQL injection basically makes anything after it a comment, thus ignored.

This is useful for making the server ignore the final quotation mark at the end of an SQL command. E.g. if the SQL looked like this:

SELECT * FROM `users` WHERE username = '$_POST['uname']';

(POST is the PHP syntax to get information from a form.) Entering the command with a double dash will solve this problem. The SQL statement could now look like this:

SELECT * FROM `users` WHERE username='admin' OR 1=1 --';

Because of the double dash at the end, the rest of the query is ignored.

Drop / Create

The DROP command can be used to remove records from a database. This is very dangerous and can be used to delete a whole database if the SQL isn't properly sanitized. for example entering the command:

foo'; DROP TABLE `users`; --

Into a username/password box will search the database for the username/password a then delete the whole database afterwards. This is malicious and is not normally recommended.

The CREATE command as expected is used to create a new table. For example:

a'; CREATE TABLE `hello` (ColumnName VARCHAR(100)); --

Would create a new table called 'hello' with a column 'ColumnName'.

Wild Cards

To make the chance of guessing a username or password even higher, there are also wildcards. The most popular being % This with a LIKE statement makes things a lot easier.

For example, does the admin's password have an 'm' in it?

SELECT * FROM users WHERE name='Admin' AND password LIKE '%m%'

Does it start with an m?

SELECT * FROM users WHERE name='Admin' AND password LIKE 'm%'

Is the third letter an e?

SELECT * FROM users WHERE name='Admin' AND password LIKE '__e%'

This can be used with the "EXISTS" command.

Finding out more info

If you don't know anything at all about the structure of the database, querying the information schema will help. TABLE_SCHEMA will reveal the database name and TABLE_NAME the table name. For example:

' OR EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='users') AND ''='

Will show if there is a table called 'users' in a database called 'test'

Magic Quotes

Because of the problems SQL injections can cause, A lot of servers have magic quotes enabled. These simply add a backslash (\) to all quotation marks (‘ ") entered into input therefore 'escaping' them and preventing injection. It can sometimes be hard to tell if a server is using magic quotes but if entering a single quote (‘) does not cause an SQL error they may be.

This is just the start of basic SQL injections. The combination of possible SQL injections to try is endless For more, check out the Wikipedia article and possibly look into other syntax such as ALTER, UNION and JOIN.