Basics of SQL Injection

Hey guys!
This post will be about a well known vulnerability in poorly developed data-driven applications called SQL Injection.

DISCLAIMER: This post is for educational purposes only. I assume no responsibility for any damage caused by this post. DO NOT TRY THIS ON ANY MACHINE THAT YOU DO NOT HAVE EXPRESS PERMISSION FOR. 

The Term

SQL Injection is a type of Code Injection vulnerability that allows an attacker to execute arbitrary SQL commands on the database behind a vulnerable application. This means that an attacker could steal private information or could even destroy the data altogether.

Example

Take an example of the following code that authenticates a user: 

statement = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password +"';"

If a user "foo" logged in with password "bar", the resulting SQL code will become:

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

(This scheme is very vulnerable as passwords should not be stored as plaintext)
Now, suppose a user "baz" logged in with a specially crafted SQL statement as a password:

' OR '1'='1

Then the SQL query becomes:

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

As you can see, the crafted password modifies the query by giving a statement that always returns true (OR '1'='1'). This means that all records will be returned. If the authentication code was as follows:

if(returned_rows.length != 0){

    //authenticated

}

User "baz" would be able to log in no matter what the password was since the number of returned rows would always be != 0.

Another, more destructive example would be:

statement = "SELECT * FROM product_list WHERE item LIKE '%" + itemname + "%';"

The above statement could be used for a search program. Now suppose a user entered the following query:

"RAM%'; DROP product_list; --"

That would expand to:

SELECT * FROM product_list WHERE item LIKE '%RAM%'; DROP product_list; --';

For those who don't understand SQL too much, the above statement summarizes to "Get all records that have an item field which has "RAM" in it and then erase the table". The "--" is one of the three SQL comments. As you can see, that will be disastrous if executed.

Avoiding an attack

For every computer system out there, the same principle applies - hack yourself before you get hacked. Therefore, to make sure your data is safe, keep testing your application for vulnerabilities and fix them. A few things can be done for securing data driven applications from SQL Injection:

1. Parameterized Statements: This ensures that statements only contain values that are valid for the type (ex. putting a string for an int will result in an error). A nice example would be the MySQL Java driver.

   String query = "SELECT * FROM users WHERE username='?' and password='?'";

   PreparedStatement stmt = dbconnection.prepareStatement(query);

   // Set the username and password fields statement.setString(1, "foo");

   statement.setString(2, "bar");

   statement.executeQuery();

2. Strict pattern checking: If a string has to follow a certain pattern, then you can reject any malformed strings.
3. Strict Permissions: This HAS to be the biggest of them all. Create separate users for different parts of the application and give them only the permissions they need so that if any of the user accounts were breached, the data still stays secure as the breached account has limited permissions.

Learning SQL Injection

If you want to learn more about SQL Injection, the Internet's your friend. There is a Wikipedia Article on SQL Injection that is useful. Also, if you want a training ground for learning SQL Injection (again, I'm warning you, DO NOT USE ANY MACHINE THAT YOU DO NOT HAVE EXPRESS PERMISSION FOR), SQLInjection.net has a nice simulation ground that you can host locally to practice on. The site also offers good articles about the vulnerability.

Cheers!
-Technohacker