Click here to Skip to main content
15,860,859 members
Articles / Programming Languages / SQL

Database Security Testing In the Light of SQL Injection Attack

Rate me:
Please Sign up or sign in to vote.
4.40/5 (10 votes)
27 Jul 2012CPOL6 min read 46.9K   21   18
This article explains how to secure database against the SQL injection attack

Introduction

This article basically focuses on providing clear, simple, actionable guidance for preventing SQL Injection flaws in the database applications under test.

"SQL Injection" is a subset of an unverified/ unsanitized user input vulnerability, and the idea to convince the application to run SQL code that was not intended and unexpected inputs to the application that are used to frame and execute SQL statements on the database.

It provides a set of simple techniques for preventing SQL Injection vulnerabilities. These techniques can be used with practically any kind of programming language with any type of database and can be used to protect them as well. Here, we’ll attempt to shed some light on what an SQL injection attack is and how you can prevent one from occurring within your company. By the end of this article, you’ll be able to identify situations where an SQL injection attack may allow unauthorized persons to penetrate your system, and you’ll learn ways to fix existing code to prevent an SQL injection attack.

The database is the heart of most Web applications, it stores the data needed for the websites and applications to "survive". It is through the combination of a database and Web scripting language that we as developers can produce sites that keep clients happy, pay the bills, and — most importantly — run our businesses.

But what happens when you realize that your critical data may not be safe? What happens when you realize that a new security bug has just been found. Security flaws and patches are found all the time in both databases and programming languages which make the databases breachable and any unauthorised user can attack the database with SQL Injection which results in the following things that might result from SQL Injection.

The unauthorised user could log in to the application as another user, even as an administrator and would be able to view private information belonging to other users, e.g., details of other users profiles, their transaction details, etc. The malicious user could change application configuration information and data of the other users, modify the structure of the database, even delete tables in the application database and finally take control of the database server and execute commands on it at will.

Since the consequences of allowing the SQL injection technique could be severe, it follows that SQL injection should be tested during the security testing of an application. Now with an overview of the SQL injection technique, let us understand a few practical examples of SQL injection.

The SQL injection problem should be tested only in the test environment.

SQL injection is a technique used to take advantage of non-validated input vulnerabilities to pass SQL commands through a Web application for execution by a backend database. Attackers take advantage of the fact that programmers often chain together SQL commands with user-provided parameters, and can therefore embed SQL commands inside these parameters. The result is that the attacker can execute arbitrary SQL queries and/or commands on the backend database server through the Web application. Severity of SQL injection is really very high as in 2008 database containing the information about the thousands of users of Sony were hacked a million of passwords from the Sony Pictures site, 77 million accounts from the PlayStation Network, and nearly 25 million user accounts from Online Entertainment. Sony now holds spots #4 and #10 for largest breaches of all time due to these SQL injection attack in the following countries:

SONY BMG-GREECE
SONY MUSIC-JAPAN
SONY-CANADA
SONY PICTURES- FRANCE
SONY PICTURES- RUSSIA
SONY MUSIC- PORTUGAL

If the application has a login page, it is possible that the application uses a dynamic SQL such as statement below. This statement is expected to return at least a single row with the user details from the Users table as the result set when there is a row with the user name and password entered in the SQL statement.

SQL
SELECT * FROM Users WHERE User_Name = ‘" & strUserName & _
	"AND  Password = ‘" & strPassword & "’;"

If the tester would enter John as the strUserName (in the textbox for user name) and Smith as strPassword (in the textbox for password), the above SQL statement would become:

SQL
SELECT * FROM Users WHERE User_Name = ‘John’ AND Password = ‘Smith’;

If the tester would enter John’– as strUserName and no strPassword, the SQL statement would become:

SQL
SELECT * FROM Users WHERE User_Name = ‘John’– AND Password = ‘Smith’;

Note that the part of the SQL statement after John is turned into a comment. If there were any user with the user name of John in the Users table, the application could allow the tester to log in as the user John. The tester could now view the private information of the user John.

What if the tester does not know the name of any existing user of the application? In such a case, the tester could try common user names like admin, administrator and sysadmin. If none of these users exist in the database, the tester could enter John’ or ‘x’=’x as strUserName and Smith’ or ‘x’=’x as strPassword. This would cause the SQL statement to become like the one below:

SQL
SELECT * FROM Users WHERE User_Name = ‘John’ or _
	‘x’='x’ AND Password = ‘Smith’ or ‘x’=’x’;

Since ‘x’=’x’ condition is always true, the result set would consist of all the rows in the Users table. The application could allow the tester to log in as the first user in the Users table.

SQL injection might be possible in applications that use SSL. Even a firewall might not be able to protect the application against the SQL injection technique.

There are two primary methods to protect your database from SQL injection. First, make sure that applications validate user input by blocking invalid characters. In many cases, only alphanumeric characters should be accepted. At the minimum, single quotes should be blocked. Second, use protected queries that bind variables rather than combining SQL statements together as strings such as stored procedures.

These suggestions may also help:

  1. Use original names for tables and columns to make the names harder to guess.
  2. Use aliases to provide more layers of separation between the data and the intruder. (For example, an intruder might find the alias "b" after some digging. But "b" is an alias for "book," and the actual term is necessary to perform the correct query.)
  3. Set length limits on form fields and validate data for content length and format.
  4. Keep up-to-date on patches.
  5. Make your schema unique.
  6. Use stored procedures, which use parameters, at all times.
  7. Avoid using query strings for Web page building.
  8. Use Post and Get for HTML commands.
  9. Audit your code to expose vulnerabilities.
  10. Lock down your server.
  11. Use of Prepared Statements (Parameterized Queries)
  12. Escaping all User Supplied Input- Each DBMS supports one or more character escaping schemes specific to certain kinds of queries. If you then escape all user supplied input using the proper escaping scheme for the database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities.

Make sure the application is running with the minimal rights necessary to complete its task. Remove any unnecessary accounts and any unnecessary information, such as example databases and unused features. Also remove or disable unnecessary stored procedures.

We have tried to explain the SQL injection technique in a simple form. We would like to re-iterate that SQL injection should be tested only in a test environment and not in the development environment, production environment or any other environment. Instead of manually testing whether the application is vulnerable to SQL injection or not, one could use a web vulnerability scanner that checks for SQL injection.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Tester / Quality Assurance Aricent Technologies
India India
HARISH CHAUDHARY FREE LANCE TECHNICAL WRITER is a young, motivated and dynamic Software Quality Engineer,worked for QAInfoTech Pvt.Ltd.
He is passionate for Security and Penetration Testing to safeguard Data from hackers. He is a keen orator and has won himself a position by participating in testing conference organized internally at QA InfoTech as well as presented paper on "Assuring Data Security by penetration Testing" at 11th International Software Testing Conference 2011.
He is Master of Computer Applications from Guru Gobind Singh IndraPrastha University. Harish holds Diploma in Software Engineering and is also Microsoft certified SQL 2005 professional.Now working as a Information Security Researcher.

Comments and Discussions

 
GeneralThank you! Pin
stu6830-Jul-12 13:19
stu6830-Jul-12 13:19 
QuestionWow, why all the harsh feedback? Pin
ednrg27-Jul-12 6:35
ednrg27-Jul-12 6:35 
QuestionA little bit outdated... Pin
SledgeHammer0129-Jul-11 12:09
SledgeHammer0129-Jul-11 12:09 
AnswerRe: A little bit outdated... Pin
HARISHCHOWDHARY2-Sep-11 22:11
HARISHCHOWDHARY2-Sep-11 22:11 
GeneralRe: A little bit outdated... Pin
ii_noname_ii30-Jul-12 2:51
ii_noname_ii30-Jul-12 2:51 
QuestionNice Info Pin
DanHodgson8827-Jul-11 23:26
DanHodgson8827-Jul-11 23:26 
AnswerRe: Nice Info Pin
KP Lee1-Aug-11 15:01
KP Lee1-Aug-11 15:01 
AnswerRe: Nice Info Pin
KP Lee1-Aug-11 15:24
KP Lee1-Aug-11 15:24 
AnswerRe: Nice Info Pin
HARISHCHOWDHARY2-Sep-11 22:14
HARISHCHOWDHARY2-Sep-11 22:14 
I am writing another article of this series.
Thanks for your appreciation "DanHodgson88". Smile | :) Rose | [Rose]
QuestionUse stored procedures, which use parameters, at all times. - Why? Pin
Stephen Brannan27-Jul-11 12:27
Stephen Brannan27-Jul-11 12:27 
AnswerRe: Use stored procedures, which use parameters, at all times. - Why? Pin
KP Lee1-Aug-11 14:26
KP Lee1-Aug-11 14:26 
GeneralRe: Use stored procedures, which use parameters, at all times. - Why? Pin
Stephen Brannan2-Aug-11 12:30
Stephen Brannan2-Aug-11 12:30 
GeneralRe: Use stored procedures, which use parameters, at all times. - Why? Pin
KP Lee2-Aug-11 12:54
KP Lee2-Aug-11 12:54 
AnswerRe: Use stored procedures, which use parameters, at all times. - Why? Pin
HARISHCHOWDHARY2-Sep-11 22:21
HARISHCHOWDHARY2-Sep-11 22:21 
GeneralRe: Use stored procedures, which use parameters, at all times. - Why? Pin
Stephen Brannan2-Sep-11 22:43
Stephen Brannan2-Sep-11 22:43 
GeneralRe: Use stored procedures, which use parameters, at all times. - Why? Pin
Jasmine250130-Jul-12 8:31
Jasmine250130-Jul-12 8:31 
GeneralRe: Use stored procedures, which use parameters, at all times. - Why? Pin
Stephen Brannan30-Jul-12 8:59
Stephen Brannan30-Jul-12 8:59 
GeneralRe: Use stored procedures, which use parameters, at all times. - Why? Pin
Jasmine250130-Jul-12 10:21
Jasmine250130-Jul-12 10:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.