SQL Injection attacks can be divided into the following three classes:

• Inband: data is extracted using the same channel that is used to inject the SQL code. This is the most straightforward kind of attack, in which the retrieved data is presented directly in the application web page.
• Out-of-band: data is retrieved using a different channel (e.g., an email with the results of the query is generated and sent to the tester).
• Inferential or Blind: there is no actual transfer of data, but the tester is able to reconstruct the information by sending particular requests and observing the resulting behavior of the DB Server.

Most of the situation and techniques below here can be performed in a automated way using some tools. In this article the tester can find information how to perform an automated auditing using SQLMap:

https://www.owasp.org/index.php/Automated_Audit_using_SQLMap

Equally Static Code Analysis Data flow rules can detect of unsanitized user controlled input can change the SQL query.

Stored Procedure Injection

When using dynamic SQL within a stored procedure, the application must properly sanitize the user input to eliminate the risk of code injection. If not sanitized, the user could enter malicious SQL that will be executed within the stored procedure.

Time delay Exploitation technique

The time delay exploitation technique is very useful when the tester find a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. This technique consists in sending an injected query and in case the conditional is true, the tester can monitor the time taken to for the server to respond. If there is a delay, the tester can assume the result of the conditional query is true. This exploitation technique can be different from DBMS to DBMS (check DBMS specific section).

http://www.example.com/product.php?id=10 AND IF(version() like ‘5%’, sleep(10), ‘false’))--

In this example the tester if checking whether the MySql version is 5.x or not, making the server to delay the answer by 10 seconds. The tester can increase the delay time and monitor the responses. The tester also doesn’t need to wait for the response. Sometimes he can set a very high value (e.g. 100) and cancel the request after some seconds.

Out of band Exploitation technique

This technique is very useful when the tester find a Blind SQL Injection situation, in which nothing is known on the outcome of an operation. The technique consists of the use of DBMS functions to perform an out of band connection and deliver the results of the injected query as part of the request to the tester’s server. Like the error based techniques, each DBMS has its own functions. Check for specific DBMS section.

If dynamic statements are being made please check if the data is sanitized before used as par of the statement.

Auditors should always look for uses of sp_execute, execute or exec within SQL Server stored procedures. Similar audit guidelines are necessary for similar functions for other vendors.

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

Defense Option 2: Stored Procedures

The difference between prepared statements and stored procedures is that the SQL code for a stored procedure is defined and stored in the database itself, and then called from the application. Both of these techniques have the same effectiveness in preventing SQL injection so your organization should choose which approach makes the most sense for you. Stored procedures are not always safe from SQL injection. However, certain standard stored procedure programming constructs have the same effect as the use of parameterized queries when implemented safely* which is the norm for most stored procedure languages.

*Note: 'Implemented safely' means the stored procedure does not include any unsafe dynamic SQL generation.

Defense Option 3: White List Input Validation

Various parts of SQL queries aren't legal locations for the use of bind variables, such as the names of tables or columns, and the sort order indicator (ASC or DESC). In such situations, input validation or query redesign is the most appropriate defense. For the names of tables or columns, ideally those values come from the code, and not from user parameters. But if user parameter values are used to make different for table names and column names, then the parameter values should be mapped to the legal/expected table or column names to make sure unvalidated user input doesn't end up in the query. Please note, this is a symptom of poor design and a full re-write should be considered if time allows.

Defense Option 4: Escaping All User-Supplied Input

This technique should only be used as a last resort, when none of the above are feasible. Input validation is probably a better choice as this methodology is frail compared to other defenses and we cannot guarantee it will prevent all SQL Injection in all situations.

This technique is to escape user input before putting it in a query. It's usually only recommended to retrofit legacy code when implementing input validation isn't cost effective.

Safe Java Prepared Statement Example

The following code example uses a PreparedStatement, Java's implementation of a parameterized query, to execute the same database query.

 String custname = request.getParameter("customerName"); // This should REALLY be validated too // perform input validation to detect attacks String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";  PreparedStatement pstmt = connection.prepareStatement( query ); pstmt.setString( 1, custname);  ResultSet results = pstmt.executeQuery( ); 

We have shown examples in Java, but practically all other languages, including Cold Fusion, and Classic ASP, support parameterized query interfaces.

Safe 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 would have to be predefined in the database and implement the same functionality as the query defined above.

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

Example: http://sensitive/something.php?dir=%3Bcat%20/etc/passwd

If the applucation responds with the output of the /etc/passwd file then you know the attack has been successful.

Many web application scanners can be used to test for this attack as they inject variations of command injections and test the response.

Equally Static Code Analysis tools check the data flow of untrusted user input into a web application and check if the data is then entered into a dangerous method which executes the user input as a command.

Example: http://sensitive/something.php?dir=%3Bcat%20/etc/passwd

If the applucation responds with the output of the /etc/passwd file then you know the attack has been successful.

Many web application scanners can be used to test for this attack as they inject variations of command injections and test the response.

Equally Static Code Analysis tools check the data flow of untrusted user input into a web application and check if the data is then entered into a dangerous method which executes the user input as a command.

1. Parametrization - If available, use structured mechanisms that automatically enforce the separation between data and command. These mechanisms can help to provide the relevant quoting, encoding.
2. Input validation - the values for commands and the relevant arguments should be both validated. There are different degrees of validation for the actual command and its arguments:
   • When it comes to the commands used, these must be validated against a whitelist of allowed commands.
   • In regards to the arguments used for these commands, they should be validated using the following options:
     • Positive or “whitelist” input validation - where are the arguments allowed explicitly defined
     • White list Regular Expression - where is explicitly defined a whitelist of good characters allowed and the maximum length of the string. Ensure that metacharacters like & |  ; $ > < ` \ ! and white-spaces are not part of the Regular Expression. For example, the following regular expression only allows lowercase letters and numbers, and does not contain metacharacters. The length is also being limited to 3-10 characters:

 ^[a-z0-9]{3,10}$ 

ProcessBuilder b = new ProcessBuilder("C:\DoStuff.exe -arg1 -arg2"); 

In this example, the command together with the arguments are passed as a one string, making easy to manipulate that expression and inject malicious strings.

Correct Usage

Here is an example that starts a process with a modified working directory. The command and each of the arguments are passed separately. This make it easy to validated each term and reduces the risk to insert malicious strings.

ProcessBuilder pb = new ProcessBuilder("TrustedCmd", "TrustedArg1", "TrustedArg2"); Map<String, String> env = pb.environment(); pb.directory(new File("TrustedDir")); Process p = pb.start();