How to detect and prevent

The only way to detect flaws in your system is through deep testing.

Fortunately, there’s a set of tools recommended by OWASP, that may help you on this task. W3AF (w3af is a Web Application Attack and Audit Framework), Web Scarab (WebScarab is a framework for analysing applications that communicate using the HTTP and HTTPS protocols. It is written in Java, and is thus portable to many platforms. WebScarab has several modes of operation, implemented by a number of plugins. In its most common usage, WebScarab operates as an intercepting proxy, allowing the operator to review and modify requests created by the browser before they are sent to the server) and BURP (Burp Suite is an integrated platform for performing security testing of web applications. Its various tools work seamlessly together to support the entire testing process), just to mention some examples from the top of my head.

The general rule is to clean up all inbound and outbound.

This means you should ensure, in the first step, that you’re rejecting or deleting invalid characters. To do that you can use any sanitise library, PHPSanitizer as example mentioned on a previous post (Security in Web-apps: Overview).

Following with the process of harden our system, lets continue with SQL Injection, that can be tackled with code best practices. (the others might need some server configuration as part of the solution)

We can follow the next steps as a cheat sheet that will help to prevent most of the attacks.

• Use of Prepared Statements (Parameterized Queries)

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 rare circumstances, prepared statements can harm performance. When confronted with this situation, it is best to either a) strongly validate all data or b) escape all user supplied input using an escaping routine specific to your database vendor as described below, rather than using a prepared statement.

• Use of Stored Procedures

Stored procedures have the same effect as the use of prepared statements when implemented safely (this means without dynamic query generation) which is the norm for most stored procedure languages. 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.

• Escaping all User Supplied Input

This technique is based on escape the inputs before put them in the query – I strongly recommend that use this technique in addition with other – for instance, using a library suggested at the top of this section.

For this technique, you should also take care on specific DBMS escaping character information, in the following link is an example of Oracle Escaping information

http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_escape_special_characters_when_writing_SQL_queries.3F

In addition to

• Principle of Least Privilege

In order to minimize  the potential damage of a success injection, you shouldn’t use a DBA privileged user, but instead, connect with a user that has the minimum privileges required by the system. You also shouldn’t run your DBMS as root, sometimes DBMS allow to execute OS instructions. This is not matter of data you store in your database only.

Examples

Based on the next statement I’m going to show the most common SQL vectors:

Based on 1=1 is Always True

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
SELECT * FROM Users WHERE UserId = 105 or 1=1

Result, this will list all records in Users table, no matter of the UserId.

Based on “”=“” is Always True

uName = getRequestString("UserName");
uPass = getRequestString("UserPass");
sql = "SELECT * FROM Users WHERE Name ='" + uName + "' AND Pass ='" + uPass + “'"

Result, this will list all records in Users table, no matter of the UserName nor UserPass.

Based on Batched SQL Statements

txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers

Result, you will lost Suppliers table.

This is just a quick start on Injection prevention, security should be implemented in layers.

In our next post, we will talk about Cross Site Scripting (XSS), definition and tips to dealing with it.