In association with heise online

Better code

To prevent an SQL injection, you need carefully to check and filter the strings entered before the application expands them as an argument for an SQL command and sends it to the database. The strategy of the filter should be restrictive. Two approaches are productive depending on the application. If the number of possible user inputs can be predicted, only those included in a defined list should be allowed. For instance, the content of the field where users can enter car brands can be compared to a list of all of the known brands. The application will not accept inputs that do not correspond to any entry in the list. This would be a "deny all, allow some" strategy like the filtering strategies that firewalls use.

Things get a bit harder when you have to filter unpredictable input, such as street names, family names, and telephone numbers. Here, the only satisfactory approach is "allow all, deny some." The application should only be allowed to pass on input that does not contain certain elements to the database: quotation marks and semicolons are banned. In addition, the program will refuse input if the string contains two consecutive hyphens ("--").

Furthermore, the filter should look for SQL commands in the string. If it finds any, it must reject the input. Unfortunately, this procedure has some exceptions. Some content management systems store text in the database. If these tips were minded, this text would never find its way into such a system. Here, programmers have to combine various methods. The use of prepared SQL statements or stored procedures is highly recommended. Here, you define templates for the desired SQL commands in the program and send them to the database:

SELECT * FROM users WHERE card = ?

The database stores these commands and later only adds the parameters transmitted during runtime. Additional SQL commands now cannot be smuggled in at a later date.

Programmers can resort to a large number of tools to help them create and operate database applications. These tools help them detect and improve critical functions and variables even in the development phase [5] . Special scanners check for weak points while applications and various database products are running to detect SQL injection, cross-site scripting, and even buffer overflows; they also generate a report about the security holes detected.

Network security

Normal firewalls are not able to prevent SQL injection because attackers can only access the database via a web front end with forms. Firewalls filter at the level of ports, and access to the front end via HTTP (port 80) is allowed.

Application-level firewalls (proxies), firewalls with intrusion prevention (IPS) and network-based intrusion detection systems (IDS) may be able to detect SQL fragments in packets, but it is very difficult to create a meaningful set of rules because the system that handles protection does not know anything about the current context. For instance, one user may be allowed to delete certain data records, while another is not. But IDS cannot make that distinction.

Special host-based database intrusion detection systems (DB-IPS) [4] attempt to detect and prevent attacks on the database. They are able to tell which application and which users can send which commands to the database. Some DB IPSs can even detect anomalies in communication, such as when an application suddenly transfers commands that may be valid, but have never occurred before.

While previous registration with the server or the application does not remedy the SQL injection problem, it does limit the number of possible users. A web server can offer protection if the connection is SSL-secured and there is authentication in both directions – in other words, both the server and client send a certificate. However, this encryption prevents the network-based intrusion prevention system from looking into the packets to detect an attack. Host-based IPS does not have this limitation.

While registration via a web front end is possible, the error then only occurs elsewhere because user authentication also requires a database and is therefore vulnerable to SQL injection.


SQL injection is becoming a serious threat to web solutions that use databases. In particular, statistics suggest that applications developed with PHP and ASP are especially vulnerable. Although reports of buffer overflows in open-source solutions and commercial products are much more common, SQL injection attacks are much easier to perform because attackers only need a web browser. Programmers need to work much more carefully to create database applications that prevent important data from being manipulated. (dab)


[1] Advanced SQL-Injection

[2] More Advanced SQL-Injection

[3] Manipulating SQL-Server using SQL-Injection

[4] AppRadar for Microsoft SQL Server

[5] WebInspect

[6] Extended Stored Procedures Programmer's Reference

Print Version | Permalink:
  • Twitter
  • Facebook
  • submit to slashdot
  • StumbleUpon
  • submit to reddit

  • July's Community Calendar

The H Open

The H Security

The H Developer

The H Internet Toolkit