A "SQL Guard" utility to help with SQL injection - available "AS IS"

I was asked the following question via a Twitter DM today:

I stumbled upon a mention of "SQL Guard" and I was wondering what has become of it. Google confused me. Has it been published under some other name, or is it part of a bigger product?

Great question! I sort of vaguely remembered creating something like that. So I searched around in my folders and found....SQL Guard!

NOTE: SQL Guard is not a product I offer anywhere on the Internet. There are things out there called SQL Guard. I am not challenging their copyright or trademark or anything else.

What you find will find below is the document I wrote in 2007 explaining my ideas and code.

I am making it available AS IS through this site and a set of LiveSQL scripts, links below. I make no warranty about usability or usefulness or even basic validity of this code. If it helps you great. If it destroys your database I WARNED YOU.

LiveSQL: create tables
LiveSQL: create package specification
LiveSQL: create package body
LiveSQL: install metadata
LiveSQL: demonstration script

Overview

SQL injection occurs when a program executing dynamic SQL or dynamic PL/SQL allows a malicious user to "inject" code into the dynamic statement that was not originally intended by the author of the program. SQL injection can cause major security problems in one's code. There are several things programmers can do to minimize the chance of SQL injection, such as:

• Avoid concatenation inside your dynamic SQL statements. Instead use bind variables wherever possible.
• If text will be concatenated, then check the contents of that text to see if you can find any characters indicating possible trouble.
• In particular, do not allow the execution of supplied packages that interact (or can be used to interact) with the operating system, such as UTL_SMTP, UTL_FILE, UTL_TCP (and related packages) and DBMS_PIPE.
• "Lock down" user accounts so that they have the minimal privileges needed to run the application; avoid granting privileges that malicious users can exploit.
• When defining programs that use dynamic SQL, be sure to define the subprogram as AUTHID CURRENT_USER. That way, all SQL statements will be executed using the privileges of the currently-connected schema.

The problem with these recommendations is that they rely on the proactive diligence of an individual developer or DBA to minimize the risk. That should be done, but perhaps something more could be offered to developers.

The sql_guard package described in this document takes another approach: analyze the string provided by the user to see if it contains a risk of SQL injection. The programmer can then decide whether or not to execute that statement and perhaps to log the problematic text (note: a logging mechanism is not yet availabled in SQL Guard). 

The tests used to determine if there is a risk of SQL injection can be configured by the user. In other words, SQL Guard comes with a set of pre-defined tests. You can remove from or add to that list of tests, to check for SQL injection patterns that may be specific to their own application environment.

I don't think it is possible to ever come up with a mechanism that will with 100% certainty trap all possible SQL injection (in fact, it is quite possible that my SQL Guard idea is deemed by those with more experience in security to be worthless).  - Steven Feuerstein 

Having said all that, if you decide to use SQL Guard, you should (it seems to me) be able to achieve the following:

1. Increase awareness of SQL injection amongst your developers. 
2. Thwart the most common SQL injection attacks.
3. More easily analyze one's code base to identify possible injection pathways.

There are three main areas of functionality to SQL Guard:

• Define the tests used to identify possible SQL injection
• Analyze text for possible SQL injection
• Use the SQL Guard-processed text in your dynamic SQL statements 

They are explained in the following sections.

Installing SQL Guard

SQL Guard consists of the following elements:

• sql_guard package: all of the functionality of SQL Guard, in one package
• sql_guard_tests table: each row in this table defines a test that will be run to identify possible SQL injection
• sql_guard_drivers table: each row in this table defines a test case that can be used to exercise sql_guard's injection detection capabilities
• sql_guard_seq sequence: used to generate primary keys for the drivers table

To install SQL Guard, run the sql_guard_install.sql script in your Oracle schema.

The sql_guard_demo.sql file contains some demonstrations of using SQL Guard.

A quick guide to SQL Guard functionality

Define tests used to identify possible SQL injection

SQL Guard comes with a set of pre-defined tests for SQL injection. You can add your own tests as needed.

To add your own tests, use one of the following programs. Here is a description of the arguments used in all three of the variations:

Argument Name	Usage
test_string_in	the dynamic code or string that will be applied to the SQL text to test for injection
name_in	the name of the test, and it must be unique. If you do not provide a name, SQL Guard will create one from the test string.
operator_in	the operator to be used when testing the test string against the SQL text. The default is LIKE; you can also specify that the test string is a PL/SQL expression which must evaluate to a Boolean or a regular expression that will be used in a call to the REGEXP_LIKE function of PL/SQL. Default is 'LIKE'. The other values that you can pass are: sql_guard.c_expression_test sql_guard.c_regexp_like_test
surround_with_pct_in	Pass TRUE if you want the test string automatically surrounded by % to do LIKE wildcarding. Default is TRUE.
apply_once_in	Pass TRUE if you want this test to be used only in the next evaluation of your SQL text, and after that removed. Default is FALSE. This feature might be useful if you have specific injection tests to run for a single context, but in general should not be considered an issue.

1. Add Test: this most generic of the add test programs adds a row of data to the sql_guard_tests table. You must specify all values for arguments as described above.

2. Add PL/SQL Expression Test: use this variation to add a test which is made up of a PL/SQL expression.

If you include the string '[SQLTEST]' in your expression, then SQL Guard will automatically replace that tag with the text being evaluated for SQL injection.

3. Add Regular Expression Test: use this variation to add a test which uses a regular expression in a LIKE operation to detect possible injection.

If you include the string '[SQLTEST]' in your expression, then SQL Guard will automatically replace that tag with the text being evaluated for SQL injection.

To remove a test, call the sql_guard.remove_test program. You must identify your test by its unique name.

Analyze text for possible SQL injection

Someone "out there" has provided some text which is supposed to be concatenated into a dynamic SQL statement. Is it safe? Here are the programs you can call in SQL Guard to try to answer that question:

1. analyzed_sql: you pass the SQL text to this function and whether or not you want to apply the current tests just for this occurrence (default FALSE) and it returns the SQL Guard handle. This handle is an opaque integer which points back to the full set of information produced by the analysis (see the next section for details on how to access the information referenced by the handle).

The important point to note here is that after calling the analysis function, the programmer (user of SQL Guard) will no longer refer directly or pass as an argument the SQL text. Instead, she works with the handle.

2. sql_safety_level: call this function if you simply want to find out if SQL Guard thinks the text is safe or not. 

Generally, you will use the analysis function and not the second "quick check" function. I added sql_safety_level to make it easier to automatically test SQL Guard.

Use SQL Guard-processed text

I assume we all agree that the most fundamental rule regarding SQL injection is that any SQL text that can originate from outside the application itself (typed in by some sort of user; let's call it "external SQL") cannot be trusted, and must be checked for validation.

So the first thing you do is identify all those programs in your application that accept external SQL. Then for each of these programs, make the following changes:

• Before calling that program, call the sql_guard.analyzed_sql to analyze the SQL text and determine the safety of that text. This function returns a "handle" (a pointer) to a bundle of information maintained within the SQL Guard package about that SQL text. 
• Then, instead of passing the SQL statement as an argument to your program, pass the SQL Guard handle. 
• Inside your program, you call the sql_guard.sql_is_safefunction to determine if SQL statement is safe. If so, extract the SQL text from the SQL Guard via the handle, and then execute your statement. 

Of course, you can also put all of these steps into your single program, but I think that there might be an advantage to separating out these different steps and setting a very simple rule:

Programs that execute dynamic SQL statements can only be passed a SQL Guard handle, never the SQL text itself.

If this rule is the standard in your organization, you can then see if all programs that execute dynamic SQL also use SQL Guard. Note: the sql_guard.show_no_sql_guard_useprocedure offers a very crude example of such an automated review mechanism. It needs to be improved with more sophisticated scanning of source code.

Here are the programs in the sql_guard package that retrieve information for a given handle:

FUNCTION sql_safety_level(sql_handle_inINanalyzed_sql_handle_t)

  RETURNPLS_INTEGER;

An overloading that accepts the SQL Guard handle and returns the safety level associated with the SQL text for that handle.

FUNCTION sql_is_safe(sql_handle_inINanalyzed_sql_handle_t)

  RETURNBOOLEAN;

Returns TRUE if the SQL text was determined to be free of SQL injection dangers.

FUNCTION injection_detected(sql_handle_inINanalyzed_sql_handle_t)

  RETURNBOOLEAN;

Returns TRUE if the SQL text was determined to be contain possible SQL injection.

FUNCTION sql_text(sql_handle_inINanalyzed_sql_handle_t)

  RETURNVARCHAR2;

Returns the SQL text analyzed by SQL Guard. 

FUNCTION injection_indicators_found(sql_handle_inINanalyzed_sql_handle_t)

  RETURNDBMS_SQL.varchar2s;

Returns a list of the tests that SQL Guard used to find that the SQL text contained possible SQL injection. 

Ideas for Enhancements

here are the things I can think of that still could  be done with SQL Guard. 

• Integrate logging: offer an option to log all possible SQL injection text for later analysis and fine tuning of rules.
• Allow for specifying of different levels of danger in the tests. For example, text with a call to DBMS_SQL might be considered "code red" and always rejected, while text with the word "DROP" in it might only be something you want to track, log, and anlyze.
• Application-specific sets of tests. For example, with the Oracle Applications or SAP, you can probably come up with a set of typical injection attempts against those tables and other database objects.