OWASP Vulnerability: SQL Injection

iFour Team - August 05, 2017

Listening is fun too.

Straighten your back and cherish with coffee - PLAY !

play
pause
pause

Vulnerable software is threatening to our major sectors of development such as finance, defence, service industry, IT, healthcare, energy generation, manufacturing etc and many more critical infrastructures. As the digital industry is developing and becoming more and more complex, the difficulty of security increases manifolds. And therefore it is a huge risk on our parts to tolerate vulnerabilities that are exposed to risk or which are simple security problems mentioned in the OWASP Top 10 list and software development companies should consider these vulnerabilities while developing software and products.

Table of Content

OWASP is an open community which facilitates to enable organizations to develop, maintain and purchase applications that can be trusted. The objective of the OWASP Top 10 list is to have awareness about the application security and identifying some of the most important risks faced by organizations in today’s world. It gives IT companies the freedom to provide unbiased, cost effective information about the application security that is transparent enough to make valid decisions.

The OWASP Top 10 Vulnerabilities (2013) are as follows:

A1–Injection
A2–Cross-Site Scripting (XSS)
A3–Broken Authentication and Session Management
A4–Insecure Direct Object References
A5–Security Misconfiguration
A6 – Sensitive Data Exposure
A7 – Missing Function Level Access Control
A8 – Cross-Site Request Forgery (CSRF
A9 – Using Known Vulnerable Components
A10 –Integration with Dot Net Desktop Applications/p>

1. FINDINGS / HOW CAN THE VULNERABILITY BE COMPROMISED?
2. AFFECTED ITEMS AND SEVERITY
3. DESCRIPTION
4. RECOMMENDATIONS TO MITIGATE THE RISKS (AVOID/ REDUCE/ TRANSFER THE RISKS)
- 4.1. Primary Defences
- 4.2. Additional Defences
5. TESTING FOR SQL INJECTION
5.1. Method: 1
5.2. Method: 2

FINDINGS / HOW CAN THE VULNERABILITY BE COMPROMISED?

The SQL injection has received more attention as this vulnerability can breach the confidentiality of the data in the compromised databases. The loss of confidentiality may result in financial loss, downtime, legal and regulatory penalties, negative publicity of the data or confidential information, databases integrity etc.

This vulnerability may also allow the attacker to gain an advantage to include malicious code in the compromised site/ application. Therefore the visitors can be tricked to install the malicious code or can be redirected to the malicious sites that can exploit many more vulnerabilities into the system.
This can also be compromised to attack the third party sites.
The attacker can log into the system as another fake user or even as an administrator
The attacker can view the private details of the users in the application eg profiles, transaction details etc
The attacker can change the configuration and data of the users/ application
The attacker can modify the structure of the database and modify details

AFFECTED ITEMS AND SEVERITY

Affected items: No alerts in this category.

Severity: High

SQL Injection may result in corruption of data and data loss, denial of service, lack of accountability or sometimes it could even lead to complete host takeover.

DESCRIPTION

A SQL query is one of the ways in which an application can talk with the database. And a SQL injection can occur when an application fails to validate and sanitize the un-trusted data. An attacker can specially craft an SQL command to trick such vulnerable applications asking the database to execute unexpected commands.

SQL injection can be defined as an application’s security weakness that will allow the attacker to control the database of the application and it will let them make changes in the database such as edit, delete data and exploit data to do undesirable things. Such SQL vulnerabilities are preventable but still SQL remains one of the leading web application risks.

RECOMMENDATIONS TO MITIGATE THE RISKS (AVOID/ REDUCE/ TRANSFER THE RISKS)

SQL Injection flaws are introduced when developers create dynamic database queries which include user supplied input. Developers need to either:

To stop writing dynamic queries; and/or

To prevent user supplied input that contains malicious SQL from affecting the logic of the executed query in the command.

Some of the instructions that the developers need to take are as follows:

1.) Primary Defences:

Option #1: Use of Prepared Statements OR Parameterized Queries

Prepared statements OR Parameterized queries ensure that an attacker is not able to change the intent of a sql query, even if SQL commands are inserted by an attacker or hacker. In the example below, if an attacker were to enter the uID of tommy' or '1'='1, the parameterized query would not be vulnerable and would instead look for a username which matched the entire string tommy' or '1'='1.

Language specific recommendations:

Java EE – PreparedStatement() with bind variables
.NET – parameterized queries like OleDbCommand() or SqlCommand() with bind variables
PHP – PDO with parameterized queries (using bindParam())
Hibernate - createQuery() using bind variables (named parameters in Hibernate)
SQLite - sqlite3_prepare() to create a statement object

Java Prepared Statement Example

The below code example uses a PreparedStatement, Java's implementation of a parameterized query, for execute the same DB query.

String customername = request.getParameter("customerName");
// This should be validated too // perform input validation to detect attacks
String queryvalidate = "SELECT acc_balance FROM u_data WHERE u_name = ? ";
PreparedStatement prestmt = connection.prepareStatement( queryvalidate );
prestmt.setString( 1, customername); 
ResultSet results = prestmt.executeQuery( );

C# .NET Prepared Statement Example

In .NET, the creation and execution of the query doesn't change. All we have to do is simply pass the parameters to the query using the Parameters.Add() call as shown below.

String queryselect = "SELECT acc_balance FROM u_data WHERE u_name = ?";
try {
OleDbCommand command = new OleDbCommand(queryselect, connection);
command.Parameters.Add(new OleDbParameter("customerName", CustomerName Name.Text)); 
OleDbDataReader reader = command.ExecuteReader();
// …write catch handling
} catch (OleDbException se)
{
// error handling
}

Here, we have shown examples in Java and .NET but practically all other languages, including PHP, Cold Fusion, and Classic ASP, support parameterized query interfaces. Even SQL abstraction layers, like the Hibernate Query Language (HQL) have the same type of injection problems (which we call HQL Injection). HQL supports parameterized queries as well, so we can avoid these attacks:

HQL - Hibernate Query Language Prepared Statement OR Named Parameters Examples

Following is an unsafe HQL Statement 
Query unsafeHQLQueryexample = session.createQuery("from Inventory where pID='"+userSuppliedParam+"'"); 
And following is a safe version of the above query using named parameters Query 
safeHQLQueryexample = session.createQuery("from Inventory where pID=:pid");
safeHQLQuery.setParameter("pid", userSuppliedParam);

Option #2: Use of Stored Procedures

Java Stored Procedure Example

The following code example uses a CallableStatement, Java's implementation of the stored procedure interface, to execute the same database query. The "sp_getAccountBalance" stored procedure has to be predefined in the database and should implement the same functionality as the query defined above.

String customername = request.getParameter("customerName"); 
// This should REALLY be validated 
try { 
CallableStatement cs = connection.prepareCall("{call sp_getAccBalance(?)}"); 
cs.setString(1, customername); ResultSet results = cs.executeQuery(); 
// … result set handling here 
} catch (SQLException se) { 
// … logging and error handling here 
}

VB .NET Stored Procedure Example

The following code example uses a SqlCommand, .NET’s implementation of the stored procedure interface, to execute the same database query. The "sp_getAccBalance" stored procedure would have to be predefined in the database and implement the same functionality as the query defined above.

Try 
Dim command As SqlCommand = new SqlCommand("sp_getAccBalance", connection) 
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(new SqlParameter("@CustomerName", CustomerName.Text)) 
Dim reader As SqlDataReader = command.ExecuteReader()
‘ write here 
Catch se As SqlException 
‘ error handling here End Try

Option #3: Escaping all User Supplied Input

2) Additional Defences:

Enforce: Least Privilege
Perform: White List Input Validation

TESTING FOR SQL INJECTION

Method: 1

Manual testing for SQL injection flaws in the OWASP Vulnerability List.

.net software development company in USA

Using burp suite tool for manual testing the application for the vulnerability named SQL Injection. Turn off the intercept in the “Proxy tab” and then visit the application you want to test in your browser

Now input certain characters in the application to detect SQL Injection. Here, inputting single quote ‘produces an error message.

asp.net software development company USA

And if you input double quotes it does not give any custom error message. The reason behind the difference is that the SQL Strings are contained in the single quote delimiters. Therefore when we submit one quote, it breaks the representation of the SQL string and wider the SQL statement. Double quotes are considered as an escape sequence and it represents a literal single quote. Therefore when we submit double quotes in the string it modifies the value of the string and does not break the SQL statement.

Here we do not get any query in the error message. Therefore it implies that SQL injection vulnerabilty does not exist in the application.

We will also do one more SQLi TEST on the application as follows:

https://support.portswigger.net/customer/portal/articles/2791037-Methodology_SQL_Injection_Investigation_.html