Problem solve Get help with specific problems with your technologies, process and projects.

Defense tactics for SQL injection attacks

The rate of application intrusions continues to rise, and many result from SQL injection attacks. Fortunately, SQL injection holes are relatively simple to defend against.

This tip reprinted courtesy of

The rate of application intrusions continues to rise, and many result from SQL injection attacks. These attacks are extremely dangerous in comparison to other types of Web-based attacks, because the end result is data manipulation. However, while SQL injection holes can be easy to exploit, they can also be simple to defend against.

SQL injection attacks are a small subset of the overarching user-input validation attack class, which ranges from buffer overflows to character encoding to script insertion. SQL injection attacks are nothing more than maliciously crafted, valid SQL statements. One example of this is an authentication attack, which aims to bypass username and password logon:

SELECT Username FROM Users WHERE Username = '' OR ''='' AND Password = '' OR ''=''

The authentication attack allows an attacker to poke through a site with strong authentication but weak input validation and database column controls. Enterprises that lack these types of protections are common victims of SQL injection attacks, among myriad other Web-based threats that spawn from poor development practices.

A more efficient attack that produces a similar end result and lists objects within a protected sysobjects table resembles the following:

SELECT name FROM sysobjects WHERE xtype = 'U'

Organizations can defend against SQL injection attacks with vendor products and secure development. Host-based solutions, such as SANA Security's Primary Response or McAfee Entercept can monitor and block SQL injection attacks. These types of prevention systems have the ability to parse inbound kernel and application requests to verify that the requests are valid and not malicious. However, host-based prevention systems provide little value considering the amount of configuration that is required to protect against each server and application. The price tag is about $1,000 per server, and one server may take as much as three days to configure and appropriately test.

Another approach for remedying SQL-based vulnerabilities is to deploy a perimeter or proxy-based security product. This is a more effective approach because it can prevent all inbound attack patterns at the perimeter, versus waiting until the attack reaches the host system. Check Point's Software Technologies Application Intelligence, NFR's Security Sentivist and McAfee's IntruShield are three of the industry's leading perimeter security products that can be implemented to protect against SQL attacks. These solutions typically employ deep packet analysis with signature matching to determine if an inbound TCP/IP packet is an attack (think traditional Snort signatures for attack identification utilized in conjunction with proprietary blocking technology).

While perimeter-based solutions can provide fast relief, they can be costly and don't fix the underlying problem. A more efficient and effective solution is to eliminate SQL injection holes in-house. Your developers and administrators can fix the front-end Web code and appropriately configure the backend database for connected applications.

On the backend, Oracle tables in the default SYS database such as these should top the list for hardening: ALL_TABLES, TAB, USER_OBJECTS, USER_TABLES, USER_VIEWS, USER_TAB_COLUMNS, USER_CONSTRAINTS, USER_TRIGGERS, and USER_CATALOG.

In theory, however, the front-end Web code could be insecure even if the backend database is hardened.

The best practice is to strip special characters in front-end Web code or in custom rules within the database column- and row-level security controls. The parsing of characters from user input fields removes the required characters to successfully exploit a SQL injection hole. The usual suspects for potentially dangerous SQL injection characters include:

("*^';&><</) One of the easiest mechanisms for removing these characters from user-supplied strings is to create a removal or substitution regular expression, which will search, find and remove the desired dangerous characters. For instance, this Perl-compatible regular expression can remove the previously stated characters:

next if (/^"*^';&<>()/);

Another option to eliminate SQL injection attack strings is to remove the commonly exploited SQL commands from inputted strings to include "INSERT, DELETE, SELECT, UNION, WHERE, FROM, LIKE." The risk of doing this is disallowing a harmless string that has an embedded SQL command. For instance a username "LIKEMIKE" would not be permitted if you were stripping strings with the word LIKE embedded in them. This simple regular expression could identify a potentially dangerous string:


The easiest method for ensuring that malicious characters are removed from strings is to conduct a global search and replace via a regular expression to disallow all non-alphanumeric characters, as displayed in this expression:


Niche database security products can add additional security layers to Web infrastructure, and consulting firms can also assist organizations with the ongoing application security war. But the highest return on investment comes from Web developer and database administrator training, which can help eliminate SQL injection vulnerabilities.

About the author

James C. Foster is the Deputy Director for Global Security Solution Development for Computer Sciences Corporation, and a contributing editor for Information Security magazine.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.