programming4us
programming4us
DATABASE

SQL Injection : Code-Level Defenses - Encoding Output

10/1/2012 2:03:20 AM
In addition to validating input received by the application, it is often necessary to also encode what is passed between different modules or parts of the application. In the context of SQL injection, this is applied as requirements to encode, or “quote,” content that is sent to the database to ensure that it is not treated inappropriately. However, this is not the only situation in which encoding may be necessary.

An often-unconsidered situation is encoding information that comes from the database, especially in cases where the data being consumed may not have been strictly validated or sanitized, or may come from a third-party source. In these cases, although not strictly related to SQL injection, it is advisable that you consider implementing a similar encoding approach to prevent other security issues from being presented, such as XSS.

Encoding to the Database

Even in situations where whitelist input validation is used, sometimes content may not be safe to send to the database, especially if it is to be used in dynamic SQL. For example, a last name such as O'Boyle is valid, and should be allowed through whitelist input validation. This name, however, could cause significant problems in situations where this input is used to dynamically generate an SQL query, such as the following:

String sql = “INSERT INTO names VALUES ('“ + fname + ”','“ + lname + ”');”

Additionally, malicious input into the first name field, such as:

',''); DROP TABLE names--

could be used to alter the SQL executed to the following:

INSERT INTO names VALUES ('',''); DROP TABLE names--','');

You can prevent this situation through the use of parameterized statements. However, where it is not possible or desirable to use these, it will be necessary to encode (or quote) the data sent to the database. This approach has a limitation, in that it is necessary to encode values every time they are used in a database query; if one encode is missed, the application may well be vulnerable to SQL injection.

Encoding for Oracle

As Oracle uses the single-quote character as the terminator for a string literal, it is necessary to encode the single quote when it is included in strings that will be included within dynamic SQL. In Oracle, you can do this by replacing the single quote with two single quotes. This will cause the single quote to be treated as a part of the string literal, and not as a string terminator, effectively preventing a malicious user from being able to exploit SQL injection on that particular query. You can do this in Java via code that is similar to the following:

sql = sql.replace(“'”, “''”);

For example, the preceding code would cause the string O'Boyle to be quoted to the string O’’Boyle. If stored to the database, it will be stored as O'Boyle but will not cause string termination issues while being manipulated while quoted. You should be careful when doing a string replacement in PL/SQL code, however. Because the single quote needs to be quoted in PL/SQL since it is a string terminator, you need to replace a single quote with two single quotes in PL/SQL via the slightly less straightforward replacement of one quote (presented by two single quotes) with two quotes (represented by four quotes) as follows:

sql = replace(sql, '''', '''''');

which may be more logical and clearer to represent as character codes:

sql = replace(sql, CHR(39), CHR(39) || CHR(39));

For other types of SQL functionality, it may also be necessary to quote information that is submitted in dynamic SQL, namely where using wildcards in a LIKE clause. Depending on the application logic in place, it may be possible for an attacker to modify how the application logic works by utilizing wildcards in user input that is later used in a LIKE clause. In Oracle, the wildcards in Table 1 are valid in a LIKE clause.

Table 1. Oracle LIKE Wildcards
CharacterMeaning
%Match zero or more of any characters
_Match exactly one of any character

In instances where user input includes one of the characters in Table 8.2, you can ensure that they are treated correctly by defining an escape character for the query, preceding the wildcard character with the escape character, and specifying the escape character in the query using an ESCAPE clause. Here is an example:

SELECT * from users WHERE name LIKE 'a%'
-- Vulnerable. Returns all users starting with 'a'
SELECT * from users WHERE name LIKE 'a\%' ESCAPE '\'
-- Not vulnerable. Returns user 'a%', if one exists

Note that when using the ESCAPE clause, you can specify any single character to be used as the escape character. I used the backslash in the preceding example because this is a common convention when escaping content.

Additionally, on Oracle 10g Release 1 and later, there is one more method of quoting a string—the “q quote, which takes the form q'[QUOTE CHAR]string[QUOTE CHAR]’. The quote character can be any single character that doesn't occur in the string, with the exception that Oracle expects matching brackets (i.e., if you're using “[” as the opening quote character, it expects the matching “]” as the closing quote character). The following are some examples of quoting strings in this way:

q'(5%)'
q'AO'BoyleA'

Oracle dbms_assert

With Oracle 10g Release 2, Oracle introduced a new package called dbms_assert. This package was then back-ported to older database versions (until Oracle 8i). You should use dbms_assert to perform input validation if parameterized queries (e.g., in FROM clauses) are not possible. dbms_assert offers seven different functions (ENQUOTE_LITERAL, ENQUOTE_NAME, NOOP, QUALIFIED_SQL_NAME, SCHEMA_NAME, SIMPLE_SQL_NAME, and SQL_OBJECT_NAME) to validate different types of input.

Warning

You should not use the NOOP function, because the function does nothing and does not protect you from SQL injection. Oracle uses this function internally to avoid false positives during automatic source code scanning.


You can use the preceding functions as shown in the following examples. The first code snippet is an insecure query without dbms_assert (SQL injection in FIELD, OWNER, and TABLE):

execute immediate 'select '|| FIELD ||'
  from'|| OWNER ||'.'|| TABLE;

Here is the same query, with input validation using dbms_assert:

execute immediate 'select '||sys.dbms_assert.SIMPLE_SQL_NAME(FIELD) ||'
  from'||sys.dbms_assert.ENQUOTE_NAME
  (sys.dbms_assert.SCHEMA_NAME(OWNER),FALSE)
  ||'.'||sys.dbms_assert.QUALIFIED_SQL_NAME(TABLE);

Table 2 lists the various functions supported by dbms_assert.

Table 2. dbms_assert Functions
FunctionDescription
DBMS_ASSERT.SCHEMA_NAMEThis function checks to see whether the passed string is an existing object in the database
DBMS_ASSERT.SIMPLE_SQL_NAMEThis function checks that characters in an SQL element consist only of A–Z, a–z, 0–9, $, #, and _. If the parameter is quoted with double quotes, everything with the exception of double quotes is allowed
DBMS_ASSERT.SQL_OBJECT_NAMEThis function checks to see whether the passed string is an existing object in the database
DBMS_ASSERT.SIMPLE_SQL_NAMEThis function checks that characters in an SQL element consist only of A–Z, a–z, 0–9, $, #, and _. If the parameter is quoted with double quotes, everything with the exception of double quotes is allowed
DBMS_ASSERT.QUALIFIED_SQL_NAMEThis function is very similar to the SIMPLE_SQL_NAME function but also allows database links
DBMS_ASSERT.ENQUOTE_LITERALThis function quotes the passed argument in double quotes. If the argument was already quoted, nothing will be done
DBMS_ASSERT.ENQUOTE_NAMEThis function encloses the user-supplied string in single quotes if it has not already been done

Encoding for Microsoft SQL Server

As SQL Server also uses the single quote as the terminator for a string literal, it is necessary to encode the single quote when it is included in strings that will be included within dynamic SQL. In SQL Server, you can achieve this by replacing the single quote with two single quotes. This will cause the single quote to be treated as a part of the string literal, and not as a string terminator, effectively preventing a malicious user from being able to exploit SQL injection on that particular query. You can do this in C# via code that is similar to the following:

sql = sql.Replace(“'”, “''”);

For example, the preceding code would cause the string O'Boyle to be quoted to the string O’’Boyle. If stored to the database, it will be stored as O'Boyle but will not cause string termination issues while being manipulated while quoted. You should be careful when doing a string replacement in stored procedure Transact-SQL code, however. Because the single quote needs to be quoted in Transact-SQL since it is a string terminator, you need to replace a single quote with two single quotes in Transact-SQL via the slightly less straightforward replacement of one quote (presented by two single quotes) with two quotes (represented by four quotes) as follows:

SET @enc = replace(@input, '''', '''''')

which may be more logical and clearer to represent as character codes:

SET @enc = replace(@input, CHAR(39), CHAR(39) + CHAR(39));

For other types of SQL functionality, it may also be necessary to quote information that is submitted in dynamic SQL, namely where using wildcards in a LIKE clause. Depending on the application logic in place, it may be possible for an attacker to subvert logic by supplying wildcards in the input that is later used in the LIKE clause. In SQL Server, the wildcards that are shown in Table 3 are valid in a LIKE clause.

Table 3. Microsoft SQL Server LIKE Wildcards
CharacterMeaning
%Match zero or more of any character
_Match exactly one of any character
[ ]Any single character within the specified range [a–d] or set [abcd]
[^]Any single character not within the specified range [^a–d] or set [^abcd]

In instances where you need to use one of these characters in a LIKE clause within dynamic SQL, you can quote the character with square brackets, []. Note that only the percentage (%), underscore (_) and opening square bracket ([) characters will need to be quoted; the closing square bracket (]), carat (^), and dash (-) characters have special meaning only when they are preceded by an opening square bracket. You can do this as follows:

sql = sql.Replace(“[”, “[[]”);
sql = sql.Replace(“%”, “[%]”);
sql = sql.Replace(“_”, “[_]”);

Additionally, to prevent a match on one of the preceding characters, you can also define an escape character for the query, precede the wildcard character with the escape character, and specify the escape character in the query using an ESCAPE clause. Here is an example:

SELECT * from users WHERE name LIKE 'a%'
-- Vulnerable. Returns all users starting with 'a'
SELECT * from users WHERE name LIKE 'a\%' ESCAPE '\'
-- Not vulnerable. Returns user 'a%', if one exists

Note that when using the ESCAPE clause, you can specify any single character to be used as the escape character. I used the backslash in this example because this is a common convention when escaping content.

Tip

When encoding single quotes as two single quotes in Transact-SQL (e.g., in a stored procedure), be careful to allocate enough storage to the destination string; generally twice the expected maximum size of the input plus one should be sufficient. This is because Microsoft SQL Server will truncate the value that is stored if it is too long, and this can lead to problems in dynamic SQL at the database level. Depending on the query logic in place, this can lead to an SQL injection vulnerability that is caused by the filtering you have in place to prevent it.

For the same reason, it is recommended that you use replace( ) rather than quotename( ) to perform encoding, as quotename() does not correctly handle strings longer than 128 characters.


Encoding for MySQL

MySQL Server also uses the single quote as a terminator for a string literal, so it is necessary to encode the single quote when it is included in strings that will be included within dynamic SQL. In MySQL, you can do this either by replacing the single quote with two single quotes as with other database systems, or by quoting the single quote with a backslash (\).

Either of these will cause the single quote to be treated as a part of the string literal, and not as a string terminator, effectively preventing a malicious user from being able to exploit SQL injection on that particular query. You can do this in Java via code that is similar to the following:

sql = sql.replace(“'”, “\'”);

Additionally, PHP provides the mysql_real_escape( ) function, which will automatically quote the single quote with a backslash, as well as quoting other potentially harmful characters such as 0x00 (NULL), newline (\n), carriage return (\r), double quotes (“), backslash (\), and 0x1A (Ctrl+Z).

mysql_real_escape_string($user);

For example, the preceding code would cause the string O'Boyle to be quoted to the string O\'Boyle. If stored to the database, it will be stored as O'Boyle but will not cause string termination issues while being manipulated while quoted. You should be careful when doing a string replacement in stored procedure code, however. Because the single quote needs to be quoted since it is a string terminator, you need to replace a single quote with two single quotes in stored procedure code via the slightly less straightforward replacement of one quote (presented by a quoted single quote) with a quoted single quote (represented by a quoted backslash and a quoted single quote) as follows:

SET @sql = REPLACE(@sql, '\'', '\\\'')

which may be more logical and clearer to represent as character codes:

SET @enc = REPLACE(@input, CHAR(39), CHAR(92, 39));

For other types of SQL functionality, it may also be necessary to quote information that is submitted in dynamic SQL, namely where using wildcards in a LIKE clause. Depending on the application logic in place, it may be possible for an attacker to subvert logic by supplying wildcards in the input that is later used in the LIKE clause. In MySQL, the wildcards in Table 4 are valid in a LIKE clause.

Table 4. MySQL LIKE Wildcards
CharacterMeaning
%Match zero or more of any characters
_Match exactly one of any character

To prevent a match on one of the characters shown in Table 8.5, you can escape the wildcard character with the backslash character (\). Here's how to do this in Java:

sql = sql.replace(“%”, “\%”);
sql = sql.replace(“_”, “\_”);

Damage & Defense…

Encoding from the Database

A common issue when using databases is the inherent trust of the data that is contained in the database. Data contained within the database commonly is not subjected to rigorous input validation or sanitization before being stored in the database; or, it may have come from an external source—either from another application within the organization or from a third-party source. An example behavior that can cause this is the use of parameterized statements. Although parameterized statements are secure in that they prevent exploitation of SQL injection by avoiding dynamic SQL, they are often used instead of validating the input; as a result, the data stored within the database can contain malicious input from the user. In these cases, you must be careful when accessing the data in the database to avoid SQL injection and other types of application security issues when the data is ultimately used or presented to the user.

One example of an issue that commonly occurs when unsafe data is present in the database is XSS. However, SQL injection is also possible in this instance. 

Therefore, you should always consider performing context-specific encoding on the data you fetch from the database. Examples would include encoding for XSS issues before presenting content to the user's browser, as well as encoding for SQL injection characters, as discussed in the previous section, before using database content in dynamic SQL.

Other  
  •  Upgrading and Converting to Access 2010 : INSTALLING MULTIPLE VERSIONS OF ACCESS ON ONE PC, CHANGING FILE FORMATS
  •  Upgrading and Converting to Access 2010 : TO CONVERT OR TO ENABLE
  •  Oracle Database 11g : Database Fundamentals - Learn the Basic Oracle Database 11g Data Types, Work with Tables, Work with Stored Programmed Objects
  •  Oracle Database 11g : Database Fundamentals - Define a Database, Learn the Oracle Database 11g Architecture
  •  SQL Server 2005 : Advanced OLAP - Calculations (part 2) - Named Sets, More on Script View
  •  SQL Server 2005 : Advanced OLAP - Calculations (part 1) - Calculated Members
  •   Exploiting SQL Injection : Automating SQL Injection Exploitation
  •   Exploiting SQL Injection : Out-of-Band Communication
  •  SQL Server 2008 R2 : Dropping Indexes, Online Indexing Operations, Indexes on Views
  •  SQL Server 2008 R2 : Managing Indexes - Managing Indexes with T-SQL, Managing Indexes with SSMS
  •  
    video
     
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us
    programming4us
     
     
    programming4us