SQL injection is one of the most common code injection attack methods, in which malicious SQL statements are inserted to execute unauthorized SQL statements in the database, e.g. read data or modify data in the database. They can be inserted as input by the user through the user interface or by a program through the parameter interface from outside.

Two SQL injection variants can be found in ABAP. First is direct usage of user input to create or modify ABAP Open SQL statements to access or modify data in the database (dynamic Open SQL Injection). The other option is when using native SQL commends constructed from user input, for example when using the ABAP Database Connectivity API (ADBC) to execute database statements.

Today I will write about Open SQL injection in ABAP. Let’s start with the following example. Do you think this report is safe against SQL injection attacks?

REPORT Z_SQL_INJECTION_OPENSQL.
PARAMETERS: street  TYPE zemployees–street    LOWER CASE,
            zipcode TYPE zemployees–zipcode   LOWER CASE,
            city    TYPE zemployees–city      LOWER CASE,
            phone   TYPE zemployees–phone_ext.
DATA: set_expr TYPE string,
      user     TYPE xubname.
IF street IS NOT INITIAL.
  set_expr = set_expr && ` STREET    = ‘` && street && `’`.
ENDIF.
IF zipcode IS NOT INITIAL.
  set_expr = set_expr && ` ZIPCODE   = ‘` && zipcode && `’`.
ENDIF.
IF city IS NOT INITIAL.
  set_expr = set_expr && ` CITY      = ‘` && city && `’`.
ENDIF.
IF phone IS NOT INITIAL.
  set_expr = set_expr && ` PHONE_EXT = `  && phone.
ENDIF.
IF set_expr IS NOT INITIAL.
  user = cl_abap_syst=>get_user_name( ).
  UPDATE zemployees
     SET (set_expr)
   WHERE userid = user.
  IF sy–subrc = 0.
    MESSAGE ‘Your address was changed.’ TYPE ‘I’.
  ELSE.
    MESSAGE ‘Error when trying to update your address!’ TYPE ‘E’.
  ENDIF.ELSE.
  MESSAGE ‘No data given => No Update!’ TYPE ‘I’.
ENDIF. 

This report uses four input parameters: street, ZIP code, city and phone number, from SAP GUI to update the user’s address in the table ZEMPLOYEES. 

After entering the new address information, the user executes the report. The Open SQL statement which is transferred to the database looks like this:

UPDATE zemployees SET STREET = ‘xyz’ ZIPCODE = ‘1234’ CITY = ‘xyz’ PHONE_EXT = 123 WHERE userid = Test.

As this is a valid Open SQL statement, the report runs successfully. Now the user’s address is updated.

Where is the problem?

Assume the table ZEMPLOYEES has following fields:

As you see, there is a SALARY field. Now this is interesting. Maybe the user wants to update his address like this:

After the input is given to the report, the Open SQL statement is generated as follows:

UPDATE zemployees SET STREET = ‘xyz’ SALARY = ‘3000’ ZIPCODE = ‘1234’ CITY = ‘xyz’ PHONE_EXT = 123 WHERE userid = TEST.

As you see, this is also a valid Open SQL statement. Therefore, the report runs successfully with this input. After execution of the report, not only the user’s address is updated, but also the salary of this user. The user is happy about the increased salary he now receives every month.

The problem is that this report uses a user’s input as a dynamic SQL change expression (UPDATE zemployees SET (set_expr) WHERE userid = user) without sanitizing or encoding the input provided by the user. As a result, the user can enter his or her own code (xyz‘ salary = ‘3000) which modifies the ABAP Open SQL statement and thus enables the user to manipulate data he or she should not be able to.

This is one typical Open SQL injection attack. Yes, it could also happen in your ABAP code.

SQL Injection in ABAP

Most ABAP programs use Open SQL statements to access the central database. The Open SQL syntax allows you to specify every clause of an Open SQL statement as one data object in parentheses to set its value dynamically. As in the example shown above, (set_expr) is used in a SET clause in the UPDATE statement. If such a value is set from outside as part of Open SQL statements without sufficient escaping, it creates a potential SQL injection vulnerability in your ABAP code.

The following manipulation attacks of dynamic Open SQL are possible:

• Manipulation of the dynamic WHERE condition
• Manipulation of the SET clause in the statement UPDATE
• Manipulation of a dynamic WHERE condition using the parameter I_FILTER of the object services method CREATE_QUERY

The following attacks on unauthorized data accesses of dynamic Open SQL are possible:

• Illegal read access to a database table with a SELECT statement
• Illegal read access to table columns
• Illegal use of columns in a dynamic GROUP BY clause
• Illegal use of columns in a dynamic HAVING clause

SQL injection countermeasures for Open SQL

The entire problem occurs because the dynamic SQL statement generation uses input from an insecure source, for example from outside the program. Therefore all ABAP developers should be aware of this risk and try to secure their code by strict validation or encoding any input for the Open SQL statement generation.  Use the following two rules against Open SQL injection attacks in your ABAP code:

• Use static Open SQL statements where possible.

Check whether it is really necessary to use dynamic Open SQL or dynamic SQL statements in ADBC (ABAP Database Connectivity API). If not, switching to static SQL statements will eliminate the risk of an SQL injection.

• Always validate or encode input for dynamic statements.

If dynamic SQL statements must be used, then use class CL_ABAP_DYN_PRG to implement input checks and escape the input for the dynamic clauses.

Now let’s fix the vulnerability of the program above using the method QUOTE() of the class CL_ABAP_DYN_PRG. The method CL_ABAP_DYN_PRG=>QUOTE() puts single quotes around the input value and escapes single quotes to prevent unwanted Open SQL statements. The phone parameter in the report has type integer and does not need to be escaped. 

REPORT Z_SQL_INJECTION_OPENSQL.
PARAMETERS: street  TYPE zemployees–street    LOWER CASE,
            zipcode TYPE zemployees–zipcode   LOWER CASE,
            city    TYPE zemployees–city      LOWER CASE,
            phone   TYPE zemployees–phone_ext.DATA: set_expr TYPE string,
      user     TYPE xubname.
IF street IS NOT INITIAL.
  set_expr = set_expr && ` STREET    = ` && cl_abap_dyn_prg=>quote( street ).
ENDIF.
IF zipcode IS NOT INITIAL.
  set_expr = set_expr && ` ZIPCODE   = ` && cl_abap_dyn_prg=>quote( zipcode ).
ENDIF.
IF city IS NOT INITIAL.
  set_expr = set_expr && ` CITY      = ` && cl_abap_dyn_prg=>quote( city ).
ENDIF.
IF phone IS NOT INITIAL.
  set_expr = set_expr && ` PHONE_EXT = ` && phone.
ENDIF.
IF set_expr IS NOT INITIAL.
  user = cl_abap_syst=>get_user_name( ).
  UPDATE zemployees
     SET (set_expr)
   WHERE userid = user.
  IF sy–subrc = 0.
    MESSAGE ‘Your address was changed.’ TYPE ‘I’.
  ELSE.
    MESSAGE ‘Error when trying to update your address!’ TYPE ‘E’.
  ENDIF.ELSE.
  MESSAGE ‘No data given => No Update!’ TYPE ‘I’.
ENDIF.

If the user puts xyz’ salary = ‘3000 as a new address parameter, the SQL statement which is transferred to the database is as follows:

UPDATE employees SET STREET  = ‘xyz” salary = ”3000’ ZIPCODE = ‘1234’ CITY    = ‘xyz’  WHERE userid = TEST.

This is a valid SQL statement and will execute successfully. But the input xyz’ salary = ‘3000 is encoded and transferred as one complete string ‘xyz” salary = ”3000’ for the STREET parameter and put into the SET clause of the ABAP SQL statement.  The ABAP compiler recognizes salary as part of the input for STREET, not as a separate column any more. Therefore a user would be unable to manipulate the column SALARY. As a result, the code is secured against SQL injection attacks.

The class CL_ABAP_DYN_PRG offers not only a quote() method, but also many other methods to check and validate the contents of variables. Some of the methods are as follows.

• check_column_name() checks whether the input is a valid column name.
• check_table_name_str() checks whether the input is a valid database table name in a specific package.
• check_whitelist_str() checks the input against  a given whitelist.

More information about the class CL_ABAP_DYN_PRG can be found in the class documentation.

Last but not least, SAP NetWeaver Application Server, add-on for code vulnerability analysis provides static code analysis and can be used for finding such potential SQL injection positions in ABAP coding.

As a responsible ABAP programmer, you pay attention to such security problems. Tryto secure your code and create the best code you can.

Related Information

SAP NetWeaver Application Server, add-on for code vulnerability analysis

(http://scn.sap.com/community/abap/blog/2013/07/31/abap-security-at-teched-2013–scan-analyze-and-fix-your-programs)

Secure Programming – ABAP

(http://help.sap.com/saphelp_nw74/helpdata/en/58/4d767ed850443c891ad27208789f56/content.htm)

SAP note 1520356 – Avoiding SQL Injections

(http://service.sap.com/sap/support/notes/1520356)

ABAP document – SQL Injections

(http://help.sap.com/abapdocu_740/en/index.htm?url=abensql_injections_scrty.htm)