SQL Injection

SQL Injection is quite a common vulnerabilities, well known by most experienced developpers but still not managed correctly in lots of applications/websites.

To see the number of vulnerabilities and the systems impacted, you can have a look at: cvedetails.com/sql-injection

The principles

Most systems relies on a database, to propose a dynamic experience the system must show data related to user query

If you are in a user search engine, you enter a username, your application will generate a SQL query and execute it on the database to return the result.

but what happens if you enter something that is not expected?

Here, what has been done is a typical SQL Injection, you will have as a result something that was not planned by the systems.

In this case, you access more information, but you understand easily what could occurs if you truncate/delete data.

What are the usual countermeasures?

Most systems provides embedded security to protect the system from SQL Injection.

It is always based on user input validation: escape all simple and double quotes to consider them as a string content and not a command. For example: magic_quotes in PHP.

 

Another possibility is to delegate the Data Access Layer to an ORM, they usually prevent you from SQL Injection by using parameterized SQL.

But as always, you need to ensure, you are not opening new ORM Injection vector 🙂

 

How to be sure to prevent SQL Injection flaw?

First, the design and coding rules:

• Never trust any data:
  • Data validation at every external input (not only users, other apps can be attack vector as weel)
  • Never do unique data validation on client side (javascript can be deactivated)
• Include SQL Injection tests in your test cases
• Never generate dynamically SQL query, even in a batch where there is no direct user input (two band sql injection can occurs; user creation where everything is secured but afterward user update batch executing dynamically generated SQL is a possible flaw)

One of the solution to replace them is Parameterized SQL, it exists in most language and is the easiest way to avoid SQL Injection (and as well it make applications more maintainable).
Here are a few examples (yeah basic ones but it is easy to find more detailled info):

.NET/C# with Entity Framework

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    string FirstName = "Jean";
    string LastName = "Aymard";
    var userQuery = from user in context.Users
                       where user.LastName == LastName && user.FirstName == FirstName
                       select user;
    foreach (var result in userQuery)
    {
        Console.WriteLine("{0} {1} ", result.FirstName, result.LastName);
    }
}

Java – Standard

String username = request.getParameter("userName"); 
String query = "SELECT user_id FROM users WHERE user_name = ? ";  
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, username); 
ResultSet results = pstmt.executeQuery( );

Java – Hibernate / HQL (not real SQL Injection but ORM injection)

String hql = "from Users where u.FirstName = ? and u.LastName = ?";
List result = session.createQuery(hql)
.setString(0, "Jean")
.setParameter(1, "Aymard")
.list();

PHP – Standard

prepare("SELECT * FROM REGISTRY where name = ?");
   if ($stmt->execute(array($_GET['name']))) {
     while ($row = $stmt->fetch()) {
       print_r($row);
     }
   }
?>