DEV Community

Cover image for OWASP Vulnerability: SQL Injection | iFour Technolab
Harshal Suthar
Harshal Suthar

Posted on • Originally published at ifourtechnolab.com

OWASP Vulnerability: SQL Injection | iFour Technolab

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.

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.

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

Read More: Owasp Vulnerability: Security Misconfiguration

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( ); 

Enter fullscreen mode Exit fullscreen mode

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
 }

Enter fullscreen mode Exit fullscreen mode

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);

Enter fullscreen mode Exit fullscreen mode

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 
  } 

Enter fullscreen mode Exit fullscreen mode

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 sa
me 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 

Enter fullscreen mode Exit fullscreen mode

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.

Image description

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

Image description

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

Image description

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

Method: 2

Using Burp Suite to Investigate SQL Injection Flaws

In the following example we will demonstrate how to investigate SQL injection flaws using Burp Suite.

Image description

Ensure that the Proxy "Intercept" is on in the burp suite. Now we will send a request to the server, in this example by clicking the "Go" button.

Image description

This request will be captured in the Proxy "Intercept" tab in the burp suite.

Now, right click anywhere on the request to bring the context menu and click on"Send to Repeater".

Image description

Image description

Now go to the "Repeater" tab.

In this part we can input various payloads in the input field of a web application.

We can test various kinds of inputs by editing the values of parameters in the "Raw" or "Params" tabs.

Looking to Hire Dedicated .NET Core Developer? Contact Now

Here we are trying to reveal the user details held by the application.

User' OR '1' = '1 is an attempt (query) to alter the query logic and reveal all the user information held in the table.

Image description

Image description

The response can be seen in the "Response" panel of the Repeater tool. Now click on "Show response in browser".

Image description

We will paste the URL in to the browser to view the response there.

Here the URL does not give any detail of the user. Therefore it implies that SQL injection is not present in the hose management system.

Top comments (0)