×

iFour Logo

OWASP Vulnerability: SQL Injection

iFour Team - August 05, 2017

Listening is fun too.

Straighten your back and cherish with coffee - PLAY !

  • play
  • pause
  • pause
Owasp Vulnerability SQL Injection

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>

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

.net software development company USA

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:

 

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.

.net software development company in USA

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.

eCommerce service provider in USA

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".

 

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.

 

custom software development company USA nopCommerce software development in USA

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

nopCommerce software development USA

 

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 .

 

OWASP Vulnerability: SQL Injection 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.1.1. Option #1: Use of Prepared Statements OR Parameterized Queries 4.1.2. Option #2: Use of Stored Procedures 4.1.3. Option #3: Escaping all User Supplied Input 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 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( );   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.   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. 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. 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. 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".   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 See here   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.   The response can be seen in the "Response" panel of the Repeater tool. Now click on "Show response in browser".   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 .  

Build Your Agile Team

Enter your e-mail address Please enter valid e-mail

Categories

Ensure your sustainable growth with our team

Talk to our experts
Sustainable
Sustainable
 

Blog Our insights

React 19 For Business: Latest Features and Updates
React 19 For Business: Latest Features and Updates

When I first started exploring React.js for our company’s project, I felt a bit lost. It was like trying to solve a big puzzle without all the pieces! But as I kept learning and trying them practically, I discovered some really cool features that blew my mind making everything seamlessly easier.

MySQL vs Azure SQL Database: Cost, Security, and Compatibility Considerations
MySQL vs Azure SQL Database: Cost, Security, and Compatibility Considerations

This blog is a continuation of MySQL vs Azure SQL Database – Part 1 , where we compared MySQL and Azure SQL databases. We learned how important it is to identify and evaluate client...

Is It Worth Using Azure With Power Platforms For Financial Business?
Is It Worth Using Azure With Power Platforms For Financial Business?

The era of traditional software development is fading; Azure Cloud and Power Platform services are taking charge to run businesses of the new age. When it comes to Financial business,...