Skip to Content

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 zemployeesstreet    LOWER CASE,
            zipcode
TYPE zemployeeszipcode   LOWER CASE,
            city   
TYPE zemployeescity      LOWER CASE,
            phone  
TYPE zemployeesphone_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 sysubrc = 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. 

/wp-content/uploads/2013/11/sql_injection_01_327828.png

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:

/wp-content/uploads/2013/11/sql_injection_02_327875.png

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

/wp-content/uploads/2013/11/sql_injection_03_327876.png

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 (xyzsalary = 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 zemployeesstreet    LOWER CASE,
            zipcode
TYPE zemployeeszipcode   LOWER CASE,
            city   
TYPE zemployeescity      LOWER CASE,
            phone  
TYPE zemployeesphone_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 sysubrc = 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)

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. nabheet madan

    Thanks Nan for sharing. I have read about it but could not understand completely. But after reading your blog i have a fair bit idea now.

    Thanks for sharing 🙂

    (0) 
  2. Andreas Wiegenstein

    Hello Nan,

    It’s good to see that SAP now starts to write Blogs on ABAP vulnerabilities.

    However, these vulnerabilities are known for years (see “Sichere ABAP Programmierung” [Secure ABAP Programming], SAP Press 2009). And I have given a talk at Hack in The Box Amsterdam back in 2011 about SQL Injections in ABAP, too.

    In your blog you cover Open SQL Injections. I would also like to point the interested reader to my blog about Native SQL Injection risks.

    SQL Injections in ABAP are very dangerous (see BIZEC APP-03) and quite common (see BIZEC APP-08).

    It’s very important that SAP and SAP customers deal with this problem, since a single Native SQL Injection will give an attacker SAP_ALL rights in the system…

    Cheers,

    Andreas

    (0) 
    1. Nan Zhang Post author

      Hello Andreas,

      Thanks for adding further information about native SQL injection.

      Security improvements should be part of our daily work and not only once in one project. Therefore, our goals are to continuously improve the security awareness and help every ABAP developer simply find the vulnerability and provide the best code.

      Best regards,

      Nan

      (0) 
  3. sampath kumar

    Hi,

    nice info… any document available to know more about open sql statement. how database interface convert nativesql into opensql?

    regards,

    sampath

    (0) 
      1. sampath kumar

        Hi Andreas,

                    If we give select * from table into table itab…., and in CRUD syntax how database interface converting this query?…how it works? whether query is in encrypt form r else???

        (0) 
        1. Andreas Wiegenstein

          Hi Sampath,

          you can see this transformation in the Performance Analysis, ST05. On a test system, activate SQL Trace, run your program, deactivate SQL Trace and display the Trace.

          Double-click on the lines in the result list for details. You can also look at the DB Explain (F9) to get further details.

          No encryption at the SQL command layer, however.

          Cheers,

          Andreas

          (0) 

Leave a Reply