Prevent SQL Injection attacks by Samsoftpk - Part 1

SQL Injection is the most common and most popular website attacking technique used by Hackers to Hack websites and own website's databases. SQL Injection's attacks are popular because of its 4 rules i.e. easy to exploit, hard to secure, coders negligence and most important lack of knowledge on secured coding. 100's of websites are there on internet which teaches you how to perform SQL injection to hack websites but only quite a few who teaches you how to prevent SQL injection. The only reason behind that People know how to exploit because its damn easy but they don't know how to secure it. According to survey held on Injection Attacks in March 2013 by IT Security Companies, survey results were really shocking.  Note : This survey was only for web developers and approximately 60 thousand web developers participated in it.

• 60% developers never listened the word "SQL Injection". 
• Out of 40%, 14% Web developers don't know "What is SQL Injection?".
• Out of 26% Web Developers who knows SQL Injection, 17%does not know how to prevent SQL injection, 3% said they have security teams to look into vulnerabilities.
• Only 6% Web developers know What is SQL Injection and How to prevent their websites from SQL Injection.

That was survey data based on very basic objective questionnaire, Imagine what will the actual scenario. Frankly speaking, at max 3-4% web developers know how to protect or prevent SQL Injection i.e. secure coding.

Prevent SQL Injection

But friends, there is no need to worry about SQL Injections. After reading this articles you can proudly say that you are among those 3-4% coders who know secure coding standards. But before everything you must know what is SQL injectionand what's its scope i.e. how much severe damage it can do to your website and database.

SQL Injection : Basic Introduction

First of all let's understand the words separately i.e. break the word SQL Injectioninto SQL + Injection. What is SQL? SQL stands for Structured Query Language, its used to query and manipulate the relational database. By querying, i meant selecting data from database based on some conditions. By manipulating, i meant updating, deleting, inserting etc on database. 
Injection as the word implies injecting something extra into something. In case ofSQL Injection, it means injecting an extra piece of code into SQL query to manipulate its behavior from existing. So this gives us basic idea that SQL injectionwill going to put something extra in our existing SQL query and what we have to do is to handle this extra code from altering the actual SQL. But you guys were still be thinking that how an injection impact our query. Here are two God Principle's whySQL injection occurs:

"SQL Injection can attack those SQL queries which are dynamically created by using some inputs from either program or user or some functionality."

"SQL Injection can also occur if escape sequences and types are not handled properly in the SQL query."

Let us learn two God Principles in detail: 

Dynamic SQL Queries

I am sure most of you have heard this term but still for newbies i will explain them what dynamic SQL is.

Dynamic SQL is an SQL code which generated within an web application or from the system tables and then executed or run against the database to manipulate the data. The SQL code is not stored in the source program, but rather it is generated based on user input. This can include determining not only what objects are involved, but also the filtration criteria and qualifiers that define the set of data being acted on.

Using Dynamic SQL, we can create powerful web applications that allow us to create database objects and manipulate them based on user input.
Wow what an feature it is? Is that really going in you mind. If yes then calm down. Every dynamic query increases the SQL injection attack surface and make your website prone to SQL Injection attack. But How? 

Consider an example: Consider the below dynamic SQL

String query = "SELECT * FROM items WHERE owner = "'" + userName + "' AND itemname = '" + ItemName.Text + "'";

Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery(query);

 

When the above query executes, this will result into below SQL query: 

SELECT * FROM items WHERE owner = AND itemName = ;

means above query will extract all those results from "Items" table where owner name and itemname are empty or spaces. The above SQL statement is correct but is it secured?? Think about it.

Off course its not secured. If you look above statement closely, the above statement only behaves correctly if itemName does not contain a single-quote character. But why?? Everything looks good. Its because above dynamic query is made by concatenating a constant base query string and a user input string.

Since itemName variable is not correctly validated that means if Hacker enters something that results in always true, the query will result into yielding all the itemNames in the table. Don't understand always true concept? Consider an example say itemName is an character field then what about 'a' ='a' . Since a will always equal to a, this condition will always return true. Now what if we concatenate this to itemName for some user say "Lokesh". So, the query becomes something like below:

SELECT * FROM items
 WHERE owner = 'Lokesh'
 AND itemName = 'a'='a';

Then what about if Lokesh is admin of the website and he's the person who added all these items. Then query will become :

SELECT * FROM items;

which is absolutely a generalized query that will result into sharing of everything inside Items table.

Isn't it dangerous? Off course it is! But How to prevent this? There are several ways of preventing it below is one example using prepared statements. How will the dynamic query look like :

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM items WHERE owner = ? AND itemName=?");
stmt.setString(1, userName);
stmt.setString(2, itemName);
ResultSet rs = stmt.executeQuery();

This code is not vulnerable to SQL Injection because it correctly uses parameterized queries. This just an example, we will discuss all preventive measures in detail in coming articles.

Incorrectly Filtered Escape Sequences or Types

First of all we must understand what are escape sequences?  Escape sequences are those characters which alters the normal behavior of the characters.
Escape sequences use an escape character to change the meaning of the characters which follow it, meaning that the characters can be interpreted as a command to be executed rather than as data.

Escape characters are different for different types of databases like oracle, mySQL, SQL server etc. We will discuss here for mySQL as its the most popular one and its free.

MySQL supports two types of Escaping modes:
1. ANSI_QUOTES SQL Mode
2. MySQL mode

ANSI_QUOTES Mode : It encodes all single quote in the SQL with double quotes. But its rarely used, we will discuss it later why its rarely used because this type of escape sequence filtering is not considered completely fail safe.

MySQL Mode : In MySQL, the MySQL mode is turned on by default for handling escape sequences. It uses below encoding pattern, usually its by default but sometimes you have to manually encode these:

 NUL (0x00) --> \0  
 BS   (0x08) --> \b
 TAB (0x09) --> \t
 LF    (0x0a) --> \n
 CR   (0x0d) --> \r
 SUB (0x1a) --> \Z
 "      (0x22) --> \"
 %    (0x25) --> \%
 '      (0x27) --> \'
 \      (0x5c) --> \\
 _     (0x5f) --> \_ 
 all other non-alphanumeric characters with ASCII values less than 256  --> \c
 where 'c' is the original non-alphanumeric character.

Escaping wildcard characters like LIKE keyword which collaborates '_' and '%' characters.

That was about escape sequences but let's see practically with an example how escape sequence allows an SQL injection attack.

This type of SQL injection vulnerability occurs when user input is not correctly validated for escape sequences mentioned above. 
Consider an below example :

String.query = "SELECT * FROM users WHERE name = '" + userName +"';"

Statement stmt = connection.createStatement();

The above mentioned SQL query is designed to pull up the records of the specified username from its table of users. Its a correct query but is it secured?  No its not. The field userName is vulnerable to SQL injection because userName field's supplied user input is not properly handled for single quote escape character.

The above SQL can be manipulated to result in always true condition by just passing always true condition in userName field.

For example if we replace the '(single quote) by always true condition i.e. ' or '1'='1 then this will yield all the users in the database. The query will become something like :

SELECT * FROM users WHERE name = ' ' OR '1'='1';

which is actually equivalent to 

SELECT * FROM users

Similar to Dynamic SQL queries. This can be prevented too using above concept or using standard functions available in PHP like mysql_query() function etc. This prevents attackers from injecting entirely separate queries, but doesn't stop them from modifying queries.

Similarly incorrectly handled type causes the SQL injection. Incorrect Type handling SQL injection occurs when a user-supplied field is not strongly typed or is not checked for type constraints. This could take place when a numeric field is to be used in a SQL statement, but the programmer makes no checks to validate that the user supplied input is numeric. For example :

 "SELECT * FROM userinfo WHERE id = " + a_variable + ";"

If you take a close look at the statement, you will find that author intended a_variable to be a number correlating to the "id" field. However, if it is in fact a string then the end-user may manipulate the statement as they choose, thereby bypassing the need for escape characters. And it will result into severe damage to database and even the whole web application.

So its always recommended that we must encode all escape sequences before using them in SQL code else it will result into SQL injection.

Note: These two God principles are not the ways for SQL injection, there are other things too but these are responsible for atleast 95% SQL injection attacks.

This was the First tutorial of the SQL Injection Prevention Tutorial, there are atleast 5 more to come in series so keep visiting for latest ones.

Have Fun! Keep Reading! If you have any queries ask in form of comments.