How can I prevent SQL injection?
Problem
I have heard of several techniques for SQL injection, or artificially altering the SQL:
For example, assume I have a web-based application with a user interface
(UI) that prompts for age. In that data field, the user could enter 55. The result is dynamic SQL that is generated by the application. The generated SQL is something like ... WHERE AGE = 55. The SQL injection occurs if the user enters 55 OR 1=1. The generated SQL is now ... WHERE AGE=55 OR 1=1.
The OR 1=1 predicate qualifies every row.
Another technique is to add a terminator character within the prompt. The users can then type in their own SQL. For example, suppose an application prompts a user for a name, and the user enters 'SMITH' ; DROP TABLE xyz; at the name prompt. If the application recognized the semicolon as a terminator, the DBMS might execute the next SQL statement, which in this case is a DROP TABLE statement.
>From my testing with Java™, the IBM® JDBC drivers seems to truncate SQL
>at
the first semicolon if a semicolon is embedded in the SQL text. If someone does manage to corrupt the SQL, at least the JDBC drivers do not pass it on.
The use of host variables also removes this exposure, because the data is separated from the SQL.
Do any other SQL injection techniques exist? How does DB2® prevent them?
Solution
The essence of SQL injection is a situation where dynamic SQL is constructed from user input, and the input is inadequately checked.
When you use static SQL, the basis for this exposure does not exist. If you implement security with systems constructs like views, this attack can not get past them. Your primary exposure is to an application that uses dynamic SQL, is responsible for the security checking, does not check on the data entered, and moves the data into a dynamic SQL statement.
The primary rules are
1. Avoid dynamic SQL when possible. Use SQLJ, rather than JDBC for Java.
You can improve performance, ease of writing code and security.
2. Use system security techniques when possible, for example access controls and views.
3. Put input data into host variables with just the value or use a parameter marker in dynamic SQL.
Regardless of the other choices, check all input. Be sure that input is the intended data type and format. This is required for all programs to be sure they work properly, but is crucial for data intended as part of an SQL statement. Only accept numbers for a numeric comparison. Do not allow special characters if they do not apply in this situation. If the SQL provides security within the application, then additional care is needed.
Understand the limitations of security within an application. System security can use security and integrity mechanisms that are not available to application programs. The level of assurance that can be provided in system security can be much higher. If the applications are run on the client or have fewer protection layers and firewalls than the database, note those limitations as well.
Many papers are available on the web. Many products exist to help with this application security exposure. Google on the phrase SQL injection. Here are some examples:
http://www.webappsec.org/projects/threat/classes/sql_injection.shtml
http://www.appsecinc.com/presentations/Manipulating_SQL_Server_Using_SQL_Injection.pdf
http://www.sitepoint.com/article/sql-injection-attacks-safe
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
http://www.securiteam.com/securityreviews/5DP0N1P76E.html
http://www.ngssoftware.com/papers/advanced_sql_injection.pdf
P.S. Please pass my address to anyone interested in DB2 HOTLINE - thank you.
With kind regards
Michael Dewert, Software Group
DB2 Development
DB2 Information Management Software
