Home page logo
/

webappsec logo WebApp Sec mailing list archives

RE: good database testing tools to guard against SQL injection for Microsoft, Oracle?
From: "Mark Curphey" <mark () curphey com>
Date: Mon, 10 May 2004 20:11:04 -0400

Not sure I fully understand the question as it seems you are not looking for
database testing tools, but tools to test the application that connects to
the database.

Obviously the best, most effective and efficient way to test for SQL
injection is to look at the code itself. There are various texts to explain
what to look for including the OWASP Guide draft version 2

SQL Injection Mitigation Techniques
If your input validation strategy is to only accept expected input then the
problem is significantly reduced. However this approach is unlikely to stop
all SQL injection attacks and can be difficult to implement if the input
filtering algorithm has to decide whether the data is destined to become
part of a query or not, and if it has to know which database such a query
might be run against. For example, a user who enters the last name "O'Neil"
into a form includes the special meta-character ('). This input must be
allowed, since it is a legitimate part of a name, but it may need to be
escaped if it becomes part of a database query. Different databases may
require that the character be escaped differently, however, so it would also
be important to know for which database the data must be sanitized.
Fortunately, there is usually a very good solution to this problem. 

The best way to protect a system against SQL injection attacks is to
construct all queries with prepared statements and/or parameterized stored
procedures. A prepared statement, or parameterized stored procedure,
encapsulates variables and should escape special characters within them
automatically and in a manner suited to the target database. 

Common database API's offer developers two different means of writing a SQL
query. For example, in JDBC, the standard Java API for relational database
queries, one can write a query either using a PreparedStatement or as a
simple String. The preferred method from both a performance and a security
standpoint should be to use PreparedStatements. 

With a PreparedStatement, the general query is written using a ? as a
placeholder for a parameter value. Parameter values are substituted as a
second step. The substitution should be done by the JDBC driver such that
the value can only be interpreted as the value for the parameter intended
and any special characters within it should be automatically escaped by the
driver for the database it targets. Different databases escape characters in
different ways, so allowing the JDBC driver to handle this function also
makes the system more portable. 

Common database interface layers in other languages offer similar
protections. The Perl DBI module, for example, allows for prepared
statements to be made in a way very similar to the JDBC PreparedStatement.
Developers should test the behavior of prepared statements in their system
early in the development cycle. 

Parameterized stored procedures are a related technique that can also
mitigate SQL Injection attacks and also have the benefit of executing faster
in most cases. Most RDBMS systems offer a means of writing an embedded
procedure that will execute a SQL statement using parameters provided during
the procedure call. Typically these procedures are written in a proprietary
Fourth Generation Language (4GL) such as PL/SQL for Oracle. 

When stored procedures are used, the application calls the procedure passing
parameters, rather than constructing the SQL query itself. Like
PreparedStatements in JDBC, the stored procedure does the substitution in a
manner that is safe for that database. 

Use of prepared statements or stored procedures is not a panacea. The JDBC
specification does NOT require a JDBC driver to properly escape special
characters. Many commercial JDBC drivers will do this correctly, but some
definitely do not. Developers should test their JDBC drivers with their
target database. Fortunately it is often easy to switch from a bad driver to
a good one. Writing stored procedures for all database access is often not
practical and can greatly reduce application portability across different
databases. 

Because of these limitations and the lack of available analogues to these
techniques in some application development platforms, proper input data
validation is still strongly recommended. This includes proper
canonicalization of data since a driver may only recognize the characters to
be escaped in one of many encodings. Defense in depth implies that all
available techniques should be used if possible. Careful consideration of a
data validation technique for prevention of SQL Injection attacks is a
critical security issue. 

Wherever possible use the "only accept known good data" strategy and fall
back to sanitizing the data for situations such as "O'Neil". In those cases,
the application should filter special characters used in SQL statements.
These characters can vary depending on the database used but often include
"+", "-", "," "'" (single quote), '"' (double quote), "_", "*", ";", "|",
"?", "&" and "=". 

Further Reading 

Appendix C in this document contains source code samples for SQL Injection
Mitigation. 

http://www.nextgenss.com/papers/advanced_sql_injection.pdf 

http://www.sqlsecurity.com/faq-inj.asp 

http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf 

http://www.nextgenss.com/papers/advanced_sql_injection.pdf 

http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf 

Failing that there are some black-box approaches with varying efficiency,
quality and cost.

Free / Open Source

1. Paros (www.proofsecure.com)
2. Wpoison - (http://wpoison.sourceforge.net)

Commercial 
Usual Suspects of Sanctum, SPI and Kavado.

-----Original Message-----
From: Earl.Perkins () metagroup com [mailto:Earl.Perkins () metagroup com] 
Sent: Monday, May 10, 2004 12:55 PM
To: webappsec () securityfocus com
Subject: good database testing tools to guard against SQL injection for
Microsoft, Oracle?

does anyone have recommendations for good database testing tools to spot and
correct potential exploitation opportunities for SQL injection attacks in
Microsoft and Oracle database environments?
thanks.

Earl L. Perkins
Vice President, Security & Risk Strategies Technology Research Services
META Group, Inc.     http://www.metagroup.com
earl.perkins () metagroup com
Voice: 504-362-0291   Fax: 925-889-2523

META Group --- Return On Intelligence*
=========================
*A service mark of META Group, Inc.





-----------------------------------------------------------------
                       METAmorphosis 2004 META Group's 15th Annual Forum for
Meeting Business and IT Change

 "The Adaptive Organization: Building Value by Remodeling for IT
                          Flexibility"
                 http://www.metagroup.com/mm2004

                         March-May 2004
     San Diego - Chicago - Barcelona - Sydney - Johannesburg
-----------------------------------------------------------------




  By Date           By Thread  

Current thread:
[ Nmap | Sec Tools | Mailing Lists | Site News | About/Contact | Advertising | Privacy ]