AppSec Blog

AppSec Blog

Top 25 Series - Rank 2 - SQL Injection

Item #2 in this year's Top 25 is CWE-89 [1]. It is officially called Improper Sanitization of Special Elements used in an SQL Command ('SQL Injection').

There are many public examples that show the devastating impact that SQL Injection can have including the Mass SQL Injection attacks that began in 2008 [2,3,4] as well as the recent SQL Injection vulnerability in RockYou that led to the disclosure of 32 million plaintext passwords [5,6].

SQL Injection occurs when your SQL statements are created dynamically without appropriate encoding or escaping. Despite its prevalence, SQL Injection is very easy to address at a code level. One of the best ways to prevent SQL Injection is to make sure that you use parameterized queries correctly. This post uses Java for illustration purposes. The PreparedStatement class is Java's implementation of a parameterized query. You can use it in the following manner to prevent SQL Injection:

// GOOD code that prevents SQL Injection
String query = "SELECT id FROM users WHERE userid = ?";
PreparedStatement stmt = con.prepareStatement(query);
stmt.setString(1, userid);
ResultSet rs = stmt.executeQuery();

However, it is important to keep in mind that a PreparedStatement can still be used incorrectly. The following code creates a SQL statement using string concatenation and does not use bind variables.

// BAD code that is still vulnerable to SQL Injection
String query = "SELECT id FROM users WHERE userid = '" + userid + "'";
PreparedStatement stmt = con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();

Additionally, to ensure that the PreparedStatement class prevents SQL Injection, you must be using a correctly implemented JDBC driver. A buggy driver can negate the benefits of correctly using a PreparedStatement. Fortunately, I don't know of any such bugs in the major JDBC drivers that are being used but would love to hear about it if you have.

In some special cases you may not want to use a PreparedStatement at all. In those cases you can manually encode the input for your specific database. Fortunately, ESAPI [7] provides an encodeForSQL method in the Encoder class that can be used for just this purpose. However, PreparedStatements are the preferred approach since manually doing this encoding may open you up to potential attacks. Chris Shiflett had a great post about multi-byte characters, character encoding, and escaping for MySQL that still allowed SQL Injection [8].

If you happen to be using an object/relational mapping tool like Hibernate you must still take care not to create your queries dynamically. Instead of creating your Hibernate queries using string concatenation you must create them using Hibernate's binding syntax (i.e. using a colon like :value).

Stored procedures are also vulnerable to SQL Injection if they are not coded correctly. In Java, stored procedures are called using the CallableStatement class. It may appear that you're not creating dynamic queries in your Java code, but if the backend stored procedure code is via an "execute" call, then you're still vulnerable to SQL Injection.

In addition to writing secure code as we've described you should also limit the privileges that are assigned to the account which is being used to connect to the database. You can even do all your database access from stored procedures and only grant your application database account access only to these stored procedures. That way, an attacker will only ever have access to call the stored procedures themselves. However, in many large organizations DBAs control stored procedure code and it may not be convenient for developers who are in a separate organization to depend on an external party to maintain their database queries.



Posted March 1, 2010 at 4:46 PM | Permalink | Reply

Andre Gironda

Great suggestions ''" all around. There are a few more things to remember, such as certain SQL clauses e.g. LIKE.
A more exhaustive look at SQL injection can be found in Justin Clarke's book, SQL Injection Attacks and Defenses

Posted March 3, 2010 at 3:01 AM | Permalink | Reply


// GOOD code that prevents SQL Injection
String query = "SELECT id FROM users WHERE userid = ?";
PreparedStatement stmt = con.prepareStatement(query);
query.setString(1, userid);
Shouldn't it be: stmt.setString(1, userid); instead?

Posted March 3, 2010 at 5:28 PM | Permalink | Reply

Frank Kim

Good catch. Fixed in the post. Thanks!

Posted March 4, 2010 at 2:19 AM | Permalink | Reply


The process of detect and revert SQL Injection Attacks with Sax2
Some IDS software will execute effective detection for SQL Injection Attacks, though, firewall can not. Now, we go to the process of detect and revert SQL Injection Attacks with IDS software Sax2.
The steps of SQL Injection Attacks are:
a) Determine environment to find the injection point.
b) Determine the type of database.
c) Guess datasheet.
d) Guess the field.
e) Guess the content.
The steps "Guess datasheet", "Guess the field" and "Guess the content" are very important fro SQL Injection Attacks during the full process. Let's analyze these there steps.
Sax2 will detect and alarm the attacks in network real-time. It will show the in the table Event when there is SQL Injection Attacks, see the figure 1.
Sax2 alarm the MS_SQL Injection Attacks real-time
Figure 1 Sax2 alarm the MS_SQL Injection Attacks real-time
The selected event in the Figure 1 shows the attacker's IP, the victim's IP And the original message is "select * from [dirs]", means enquire whether there is a datasheet named "dirs" in current database, in the Original Communication view.
The attacker will repeat the operation to gain the expected datasheet. He will try to guess the filed in the datasheet if found the corresponding datasheet in the database.
Sax2 analysis the attacker is guessing the filed in the admin database
Figure 2 Sax2 analysis the attacker is guessing the filed in the admin database
The code in the red circle in the Figure 2 show the attacker is guessing the "paths" filed in the admin database. Also, the attacker will repeat the operation till find the corresponding filed.
The attacker will determine the length of the filed and guess the content after found the corresponding filed. It will be a SQL Injection Attacks after the attacker guess the content in the filed successfully. Sometimes, the attacker has to decryption the content if it in MD5 encryption.
Above is the whole process of SQL Injection Attacks and we detect it with Sax2. As we know, Sax2 can effectively detect and alarm the SQL Injection Attacks when it occurs. IDS software Sax2 is a useful tool for SQL Injection Attacks and make your network security combine with firewall software.

Posted July 6, 2010 at 10:15 AM | Permalink | Reply

security services

Justin Clarke book on SQL injection is more variant and vast to learn SQL based clauses in detail. Anyway, i am thoroughly impressed with the topic, it would probably help to learn thing better

Post a Comment


* Indicates a required field.