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 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.
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
strPassword (in the
textbox for password), the above SQL statement would become:
SELECT * FROM Users WHERE User_Name = ‘John’ AND Password = ‘Smith’;
If the tester would enter
strUserName and no
strPassword, the SQL statement would become:
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
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
strPassword. This would cause the SQL statement to become like the one below:
SELECT * FROM Users WHERE User_Name = ‘John’ or _
‘x’='x’ AND Password = ‘Smith’ or ‘x’=’x’;
‘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
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:
- Use original names for tables and columns to make the names harder to guess.
- 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.)
- Set length limits on form fields and validate data for content length and format.
- Keep up-to-date on patches.
- Make your schema unique.
- Use stored procedures, which use parameters, at all times.
- Avoid using query strings for Web page building.
Get for HTML commands.
- Audit your code to expose vulnerabilities.
- Lock down your server.
- Use of Prepared Statements (Parameterized Queries)
- 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.
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.