A SQL injection attack is a code injection technique where the user input is interrupted as SQL commands rather than standard data while processing the SQL query.
This vulnerability is exploited by an attacker to access, modify and delete data from the database.
By leveraging SQL Injection, an attacker will be able to bypass authentication and access unauthenticated data from databases. SQL injection vulnerability will potentially allow the attacker to escalate to more damaging attacks inside the network where the application is hosted.
Types of SQL Injection Vulnerabilities
Based on different strategies attackers use to exploit SQL injection vulnerabilities, they can be categorised as:
In-band SQL Injection
Blind SQL Injection
Out-of-band SQL injection
1. In-band SQL Injection
For in-band SQL injection, the attacker uses the same communication channel to execute attacks and retrieve information from the application.
In-band SQL injection is easy to exploit and a commonly used injection technique among the attacker community.
The In-band SQL injection vulnerability can be further classified into four:
2. Blind SQL Injection
Blind SQL injection is an injection attack where no actual data is transferred between the attacker and the application.
It is also known as Inferential SQL injection. The attacker cannot see if the inserted commands are executed or not. This is why this attack is called a blind attack.
The attacker in this attack tries to construct a separate database inside the application. The attacker determines his success by reading the response from the application.
The following types of injection attacks come under blind SQL injection:
3. Out-of-band SQL Injection
Out-of-band SQL injection is an injection attack that is rarely used by an attacker. The possibility of this attack depends on the features enabled in the database of the server.
This attack is used when executing commands and getting information can be done through a single communication channel.
Out-of-band SQL injection is used as an alternative for time-based blind SQL injection. This attack is also used if the server is not giving stable responses. If a server cannot provide a proper response, it can be easily stalled by sending requests from many different systems.
Impact
Depending on the backend database configuration, access privileges and the operating system, an attacker can mount one or more of the following type of attacks:
Reading, updating and deleting tables from the database
Executing commands on the underlying operating system
Mitigation or Precaution
Beagle Security recommends implementing the following defence measures:
Use stored procedures instead of dynamic queries
Sanitise all user-supplied input
Use of prepared statements (with parameterized queries)
Whitelist input validation
Enforce least privilege in the application configuration
Stored Procedures
Stored procedures are not always trustworthy in all types of SQL injection.
When appropriately implemented a stored procedure has the same effect of a parametrised query. A stored procedure language requires the developer to build SQL statements with parameters.
A SQL query for a stored procedure is defined and stored in the database itself, which is then invoked from the application.
Safe Java Stored Procedure Example
String custnme = request.getParameter("custName"); // This should REALLY be validated
try {
CallableStatement cs = connection.prepareCall("{call sp_getAccountBalance(?)}");
cs.setString(1, custname);
ResultSet res = cs.executeQuery();
// … result set handling
} catch (SQLException se) {
// … logging and error handling
}
Safe VB.NET Stored Procedure Example
Try
Dim command As SqlCommand = new SqlCommand("sp_getAccBal", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(new SqlParameter("@CustName", CustName.Text))
Dim reader As SqlDataReader = command.ExecuteReader()
‘ …
Catch se As SqlException
‘ error handling
End Try
Prepared Statements (With Parameterized Queries)
In a prepared statement with a parameterised query, the developer has to define all the SQL query and pass in each parameter to the query later.
A prepared statement helps the database to differentiate between code and data, regardless of the input. This statement prevents an attacker from changing the intent of a query even if SQL commands are inserted within the parameters.
Language Specific Recommendations
Java EE – PreparedStatement() using variables
PHP – PDO using bindParam()
.NET – Use SqlCommand() or OledDbCommand() using bind variables
SQLite – Use sqlite3_prepare() to create a statement object
Hibernate – Use CreateQuery() with bind variables
Safe Java Prepared Statement Example
String custname = request.getParameter("custName");
// perform input validation to detect attacks
String query = "SELECT acc_bal FROM usr_dat WHERE usr_nme = ? ";
PreparedStatement p = connection.prepareStatement( query );
p.setString( 1, custname);
ResultSet res = p.executeQuery( );
Safe C# .NET Prepared Statement Example
String query = "SELECT acc_bal FROM usr_dat WHERE usr_nme = ?";
try {
OleDbCommand comm = new OleDbCommand(query, connection);
comm.Parameters.Add(new OleDbParameter("custName", CustName Name.Text));
OleDbDataReader rdr = comm.ExecuteReader();
// …
} catch (OleDbException se) {
// error handling
}
Hibernate Query Language (HQL) Example
Query unsafHQLQry = session.createQuery("from Inventory where productID='"+userSuppliedParameter+"'");
Query safHQLQry = session.createQuery("from Inventory where productID=:productid");
safHQLQry.setParameter("productid", userSuppliedParameter);
White List Input Validation
There are many illegal locations for the use of bind variables such as the names of tables or columns and sort order indicator. Whitelisting input validation is the most appropriate defence to tackle this situation.
The name of tables or columns is usually extracted from code and not from user parameters. But, bind variables use parameter values. The parameter values are to be mapped to the legal expected table or column names to make sure invalid user input doesn’t get executed in the query.
For example:
String tabNam;
switch(PARAM):
case "Value1": tabNam = "fooTable";
break;
case "Value2": tabNam = "barTable";
break;
...
default : throw new InputValidationException("unexpected value provided for table name");
For handling sort order, the best method is to convert the user-supplied input into boolean, and then use that boolean to select the safe value to append to the query. Converting user-supplied input into boolean is an essential step in creating dynamic queries.
For example:
public String someMethod(boolean sortOrder) {
String SQLquery = "SQL code to get values from user with order by some value " + (sortOrder ? "ASC" : "DESC");
Escaping All User-Supplied Input
Escaping all user-supplied input technique is used when all other injection prevention techniques are unusable.
Implementing this mitigation is weak compared to the other defences. This fix will not guarantee prevention of a SQL injection in all situations.
Beagle Security recommends this technique to retrofit legacy code when there is ineffective input validation. This technique supports one or more character escaping schemes in each DBMS that is specific to certain kinds of queries.
