OWASP Vulnerability: SQL Injection

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

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
}

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

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 
} 

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 

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.

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