Nabeel's Blog: [Security] Always parameterize/sanitize SQL queries

SQL injection is the number #1 security threat as per OWASP's top 10 threats of 2013. Even though there are tons of materials and many techniques out there on how to avoid being victims of such attacks, we still frequently see them!

Here are two funny examples of SQL injection attacks :)

(Source: http://xkcd.com/327/)

You probably may have seen the following in chain emails..

Even though the above two examples look funny, SQL injection is a serious problem and no laughing matter. Attackers have exploited SQL injection vulnerability in order to cause substantial damage to many big companies including Wall Street, LinkedIn, Yahoo, etc. (Want to see a list of victims? Check SQL injection hall of shame link :)

From the attacks we see as recently as this month, it looks like SQL injection is not preventable or is it? It is totally preventable. Before we say how, let's look at from where such attacks may most likely to originate.

1. From your application (SQL queries executed from your application)
2. From within the database itself (stored procedures, which are stored in the database, executed from within the database)

From your application, how can we mitigate SQL injection attacks?
Let me first show you a vulnerable code snippet using Java as application language:

//UNSAFE CODE

float sal = 0.0;
Statement stmt = null;
String query = "SELECT salary FROM emp WHERE username = " + request.getParameter("user");
try { 
  stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(query);
  if (rs.next()) {
    sal = rs.getFloat("salary");
  }
} catch (SQLException e) {
   throw e;
} finally {
   if (stmt != null) {
     stmt.close();
   }
}

As you can see in line 3, the user input for the form variable "user" is directly appended to the SQL query. If this input was something like "'scott'; DROP TABLE emp;", the database sever would execute two queries and the latter one would simply drop the emp table! So, how can we prevent such attacks? One option is to use parameterized queries (i.e. prepared statements) :

//SAFE CODE

PreparedStatement pstmt = null;
String query = "SELECT salary FROM emp WHERE username = ?";
String user = request.getParameter("user");
try { 
  pstmt = conn.preparedStatement(query);
  pstmt.setString(1, user);
  ResultSet rs = pstmt.executeQuery();
  ...
}

Notice that we are using prepared statements here. That's what makes you safe from SQL injection attacks. If an attacker inputs a malicious string as the variable "user", it will fail to return any value at line 7, as the sanitized input at line 6 does not match with any user name in the emp table. It should be noted that just because you use prepared statements, you are not safe! For example, you could use the same query string from the unsafe code example above as the input to the prepared statement in line 5 of the safe code example. Such an approach is still not safe due to simple string concatenation. Always use parameterized values!

If I use prepared statement, do I still need to do validation? Yes, definitely; always validate user inputs! Prepared statements help you prevent attackers from injecting code (so that you don't need to do sanitation on your own.), but it does not help with the semantics - that is, it does not check if the user entered the data in the format or domain you expect. For example, the expected values for one of the user input is 1 - 10 which is entered from a dropdown box in the browser. It is a good practice to validate the user input to make sure it falls within this range. You may think that .. why?, we have provided a dropdown; users cannot enter their own values? True in general, but you never know that an attacker could exploit a vulnerability in the client side and input a different value.

From within databases (stored procedures), how can we mitigate SQL injection attacks?
Similar rules apply to stored procedures. If you use dynamic SQLs (For example, execute immediate query in Oracle PL/SQL), you should parameterize the query and use bind variables.)

//SAFE PL/SQL CODE WITH NO DYNAMIC SQL

CREATE OR REPLACE FUNCTION get_sal_safe(emp_name IN VARCHAR2) RETURN NUMBER IS
  salary emp.sal%type;
BEGIN
  SELECT sal INTO salary FROM emp WHERE ename = emp_name;
  RETURN salary;
END;
/

//UNSAFE PL/SQL CODE WITH DYNAMIC SQL

CREATE OR REPLACE FUNCTION get_sal_dynamic_unsafe(emp_name IN VARCHAR2) RETURN NUMBER IS
  salary emp.sal%type;
  query VARCHAR2(500);
BEGIN
  query := 'SELECT sal FROM emp WHERE ename = ''' || emp_name || '''';
  EXECUTE IMMEDIATE query INTO salary;
  RETURN salary;
END;
/

String concatenation is not safe. You must use bind variables instead as shown below.

//SAFE PL/SQL CODE WITH DYNAMIC SQL

CREATE OR REPLACE FUNCTION get_sal_dynamic_safe(emp_name IN VARCHAR2) RETURN NUMBER IS
  salary emp.sal%type;
  query VARCHAR2(500);
BEGIN
  query := 'SELECT sal FROM emp WHERE ename = :1';
  EXECUTE IMMEDIATE query INTO salary USING emp_name;
  RETURN salary;
END;
/

There could be situations where you may not be able to update your code base to prepared statements or safe stored procedures. What can we do in such cases? This is where you may use sanitation. You simply escape special characters such as commas from user input before using as part of a query. All major database systems provide functions to escape strings so that you do not have to cook up your own method. One precautionary note though: unlike prepared statements or safe stored procedures, such escaping may not prevent certain sophisticated injection attacks. As a rule of thumb, when you develop new code, always use parameterized inputs (i.e. prepared statements and dynamic SQLs with binding); that will give you a peace of mind about your code!

Nabeel's Blog

Thursday, July 31, 2014

[Security] Always parameterize/sanitize SQL queries

No comments: