Injection refers to a situation where people can substitute malicious arguments into an SQL statement. Over the last several years, all of the major web programming languages have tightened down on injections. They have added features that automatically escape characters or steer developers away from this problem. Developers take this for granted when writing web applications, and tend to forget this when writing DB2 stored procedures.

To add to the problem, there are a large number of dynamic SQL examples in DB2 books and articles that also suffer from SQL injection issues. New developers starting from these examples may unknowingly be introducing security issues.

Most developers concentrate on front end UI development and accuracy of input, and they worry less about the stripping of variables at the stored procedure level. With the adoption of SOA services that use a thin layer to externalize stored procedures, this issue is becoming more prevalent. The following example is quite common and similar to an example in common DB2 sources:

CREATE PROCEDURE SET_BONUS ( IN in_empno VARCHAR(1000),
IN in_bonus INTEGER )

------------------------------------------------------------------------
-- SQL Stored Procedure
-- in_empno - The employee number to update
-- in_bonus - Set the bonus to this employee
------------------------------------------------------------------------

P1: BEGIN
DECLARE v_stmt VARCHAR(1000);
SET v_stmt = 'UPDATE employee SET BONUS= ' CHAR(in_bonus) ' WHERE empno =''' in_empno '''';
EXECUTE IMMEDIATE v_stmt;
END P1

What happens if someone sent a call to this stored procedure with the following argument (“000000' or EMPNO = '000010”,9000). Inside the stored procedure the query that is executed is:

UPDATE EMPLOYEE SET BONUS = 2000 WHERE EMPNO = '000000' or EMPNO = '000010'

This is probably not a desired behavior. It is common for the front end to automatically rewrite strings such as “000000' or EMPNO = '000010” to “000000'' or EMPNO = ''000010” so this type of injection doesn't work.

Now, say you don't have thin layers around your stored procedure and you know your web team escapes all incoming variables. So your protected, right? Not so fast - there are ways to circumvent escaped strings.

Escaped strings may be subject to character encoding exploits. How does your application stripping algorithm handle a multi-byte character such '0xbf27'? Most people don't know. When interpreted as two separate characters it can be considered a ¿ followed by a single quote or a single character that won't be escaped. If you don't have consistent character encoding this can be exploited. While the risk is small, you can't be sure of which holes exist in other sections of code. Also, what happens if there is a language exploit that allows hackers to override the escaping?

To avoid SQL injection issues, the best strategy is to always use parameter markers for dynamic sql in stored procedures or static sql. This rule also applies to all development languages. You can quickly filter for common mistakes in your SQL stored procedures by doing a db2look -d Yourdb -e and checking for concatenations in the stored procedure section.

Other types of SQL injection exists. What about adding an “' or 't'='t'” to a select statement? You can have access to all the rows. Or using the union all operator to gain access to other tables. Suddenly, all table data is publicly available.

Martin Hubel and Rob Williams
mhubel.com