Preventing SQL Injection(Information leakage, data corruption, data destruction )

SQL injection attacks are among the most dangerous and commonly used Web-based attacks today. The basis for an SQL injection attack involves a malicious user causing a database to run destructive and compromising SQL commands. We will give specific examples of how attackers use SQL injection attacks, the information the attacker can gather, and the damage an attacker can perform. We will then cover several methods for preventing these attacks.

SQL Injection Examples

Virtually every Web site reads information entered by users, from login information to search criteria. When a user purposely inserts SQL code into data that the database is going to process, a SQL injection occurs. For example, a Web site may read in the username and password of a user during login and check the database to see if this is valid login information. A common and insecure SQL query string may look like the strings shown in Figure 6.11 (C#) or Figure 6.12 (VB.NET).

string queryString = "select * from Accounts where username = '" +      Request.QueryString["username"] + "' and password='" +      Request.QueryString["password"] + "'";

Figure 6.11: Common Query String [C#]

Dim queryString = New String("select * from Accounts " + _     "where username = '" + Request.QueryString["username"] + _     "' and password='" + Request.QueryString["password"] + "'") 

Figure 6.12: Common Query String [VB.NET]

The SQL query strings in Figures 6.9 and 6.10 have various critical problems. If you have any strings like these in your code, you are susceptible to attack on many levels. Please read on to learn how to secure your queries.

• • Figure 6.15: Username Value Exposed in Error Message

An attacker could use the same SQL injection and substitute password for username to learn the admin account’s password, and so forth for every table or record in the database.

• • Compromise database integrity This attack aborts a query by closing the original statement appropriately, often by a closing quotation mark and/or a semi-colon, and then appends a destructive or corruptive SQL statement. Here are some examples of destructive input that could be entered as a password:

    • • '; delete from Accounts

      • or

      • '; insert into Accounts (username, password) values   ('hahaha', '0wn3d')

• • • These queries result in the intended query running with a blank password and an unintended, destructive query running second.

• • Compromise a query An attacker may shorten a query and circumvent the authentication process. In this example, the user name entered was:

• • • admin'--

    • Using the example query shown previously in Figure 6.1, this query causes the SQL query to prematurely end after specifying the using “admin.” Assuming an account named “admin” exists, the attacker circumvented knowing the admin password, and the code permits the attack to log in as “admin” with the appropriate password.

    • Logical statements can also corrupt a query. If for a username, the attacker entered:

    • ' or a=a--

    • the attacker will be logged in as the first user in the database table. This works because the SQL Server will match the logically always true statement of a=a with the first account in the table.

These are some of the more common methods used for SQL injection. Most attacks use one or more of these methods. This is not, however, an exhaustive list of all the different permutations. Hackers are developing new SQL injection techniques all the time. Thankfully, code and operating policies can protect you from most, if not all, attacks.

Many Web sites will tell you that all you need to do to prevent SQL injections is to filter out or escape certain characters used in SQL injection attacks, such as ‘, --, and ;. Filtering and escaping is not enough. The following are some solutions you can use to prevent SQL injections. Ideally, you should use more than one of these techniques.

Filtering or Escaping Dangerous Characters

Filtering or escaping dangerous characters is the most common and easiest to break method of preventing SQL injections. The idea behind the technique is to either remove (filter) dangerous characters from user input or cause the database to treat a dangerous character as a literal (escape).

Filtering can be a bad idea because the “dangerous” character might be a valid part of the user’s input. For example, removing a single quote (‘) from the company’s name or a user’s password could cause problems. You can, however, raise an error in the presence of “known bad” data. Known-bad data is characters that generally have no place outside an SQL statement, such as — or ; characters. If these characters are inappropriate for the specific field—for example, a username field or a password field that doesn’t allow punctuation characters—instead of trying to filter or escape the characters, present an error to the user that says these characters are not allowed.

Escaping characters generally involves duplicating the dangerous character so that the code treats the character as a literal instead of the close of a string, in the case of the ‘ character. Figure 6.16 (C#) and Figure 6.17 (VB.NET) show example code to escape the ‘ character.

private string escapeQuoteCharacter(string stringToEscape)  {     return stringToEscape.Replace("'", "''"); }

Figure 6.16: Escaping the ‘ Character [C#]

Private Function escapeQuoteCharacter(ByVal stringToEscape As String) _   As String     Return stringToEscape.Replace("'", "''") End Function

Figure 6.17: Escaping the ‘ Character [VB.NET]

Merely escaping dangerous characters is not sufficient protection, because an attacker could still insert malicious data into your database that your database accidentally activates later. For example, consider an attacker entering the following as a username:

Timebomb'; drop table account--

The escapeQuoteCharacter method escapes the string, The new string reads:

Timebomb''; drop table Accounts--

Since the double ’’ marks means the literal ‘, the code safely inserts into the database:

Timebomb'; drop table Accounts-- 

No damage has been caused at this point, since the ‘ character was treated as a literal; the user just has a strange username. For this example, assume that the Accounts table contains an e-mail column. Consider what happens when the Web site tries to send all users in the system an e-mail. Code would typically create a dataset containing the username of all the users to whom the application will send an e-mail. Here is the code that will run when the application uses the username data to retrieve the e-mail of the user with the malicious username:

select email from emailAddress where username='Timebomb'; drop table _    Accounts--'

The ‘ character in the username closes the select statement, the drop clause is appended, and the final quotation mark is commented out. The database interprets the username and drops the Accounts table. To prevent attacks such as this, escape the data contained in the results of all database queries. If the application had run the escapeQuoteCharacter method on each username in the result set before querying for the e-mail, the attack would have failed.

Another reason escaping characters is not sufficient protection is that an attacker could use ASCII hexadecimal characters and other character sets to bypass the checks. The database and code can interpret these hexadecimal characters correctly as the ‘ character, but if your escape code sees the value 0x2C instead of the ‘ character, it won’t escape it.

Using SqlParameters

The .NET framework has a collection type called SqlParameter that can provide type and length checking as well as automatically escaping user input. Figure 6.18 (C#) and Figure 6.19 (VB.NET) show examples of how to use the SqlParameter collection to assign variables when you’re building an SQL statement.

SqlDataAdapter command =      new SqlDataAdapter("select password from Accounts " +     "where password=@password", conn); SqlParameter sqlParameter =      command.SelectCommand.Parameters.Add("@password",      SqlDbType.VarChar, 8); sqlParameter.Value = Request.Form["username"]; 

Figure 6.18: Using SqlParameters in Building SQL Statements (C#)

Dim command = New SqlDataAdapter("select password " + _     "from Accounts where password=@password", conn) Dim sqlParameter = command.SelectCommand.Parameters.Add( _     "@password", SqlDbType.VarChar, 8) sqlParameter.Value = Request.Form["username"]

Figure 6.19: Using SqlParameters in Building SQL Statements (VB.NET)

Use this same technique when calling stored procedures. See Figure 6.20 (C#) and Figure 6.21 (VB.NET) for an example of how to use the SqlParameter when calling a stored procedure.

SqlDataAdapter command = new SqlDataAdapter("AccountInsert", conn); command.SelectCommand.CommandType = CommandType.StoredProcedure; SqlParameter sqlParameter =      command.SelectCommand.Parameters.Add("@username",      SqlDbType.DateTime, 8); sqlParameter.Value = Request.Form["username"];

Figure 6.20: Using SqlParameters in Calling Stored Procedures (C#)

Dim command = New SqlDataAdapter("AccountInsert", conn) command.SelectCommand.CommandType = CommandType.StoredProcedure Dim sqlParameter = _     command.SelectCommand.Parameters.Add("@username", _     SqlDbType.DateTime, 8) sqlParameter.Value = Request.Form["username"];

Figure 6.21: Using SqlParameters in Calling Stored Procedures (VB.NET)

The database regards input assigned to the parm.Value as a literal, so there is no need to escape the user input. Notice SqlParameter also enforces types and type length. If the user input values don’t conform to the described type and size, the code throws an exception. Whenever possible, constrain user data by the type and length it must be to benefit from type and length checking.

Constraining Data Types and Length

If you are collecting a date from a user, store it as a date in the database. If you are collecting an ID number, store it as a number in the database. If you are collecting an eight-character password, store it as a varchar of 8 characters maximum. If you combine the use of SqlParameter and data constraints, your code can reject data that doesn’t belong. For example, if an attacker to tries to inject a new user account on the end of the password field:

'; insert into Accounts (username, password) values   ('hahaha', '0wn3d')

our SqlParameter code will detect that the password is more than 8 characters long, and it will throw an exception. Alternatively, if an attacker tries to perform the same attack on a numeric field, the SqlParameter code will refuse it because the attack includes nonnumeric characters.

Using Least Privileges

Restrict the database user to the bare minimum of actions. If your application only needs to read data from the database, there is no reason to allow the database user to drop tables, insert records, or anything other than reading data. If hostile code does make it to the database, lack of permission will minimize the damage.

Rejecting Known Attack Signatures

Depending on what your application does, you might be able to reject a query based on bad data that could be dangerous. Another way to look at this idea is to apply the principle of least privilege through code. Consider filtering user input keywords for dangerous SQL commands, such as drop or delete. Figure 6.22 (C#) and Figure 6.23 (VB.NET) show an example of filtering potentially dangerous SQL commands.

private bool containsBadData(string stringToCheck)  {     string[] badData = new string[]        { "drop", "delete", "insert", "update" };     for (int x=0; x < badData.Length; x++)     {         if (stringToCheck.IndexOf(badData[x]) > -1)             return true;     }     return false;    }

Figure 6.22: Filtering Dangerous SQL Commands (C#)

Private Function containsBadData(_   ByVal stringToCheck As String) As Boolean     Dim badData = _       New String() {"drop", "delete", "insert", "update"}      For x As Integer = 10 To badData.Length         If (stringToCheck.IndexOf(badData(x)) > -1) Then             Return True         End If     Next      Return False End Function

Figure 6.23: Filtering Dangerous SQL Commands (VB.NET)

If the method returns true, the user input contained bad data. You can take this idea further by creating regular expressions that check for an attacker trying to enter SQL syntax into a field. Take care to consider which user input fields you check with this kind of method. If you are validating a field that contains a user’s comments, there may be legitimate reasons for the user to type some of the dangerous command words.

Handling Errors on the Server

As explained in the SQL injection examples, error messages can give an attacker many details about your database. Wrap database actions in Try and Catch statements and properly process errors on the server side. In your Catch statement, log details about the error that occurred. This will help you know that an attack was attempted and what the attack was trying to do. By processing errors on the server, you will prevent the server from passing error messages, and the sensitive details they contain, to the client. Keep in mind that a successful SQL injection attack won’t necessarily cause errors. SQL injections that cause errors are often an attacker gathering information about your database as a precursor to an attack

Properly implemented, these solutions will greatly reduce your susceptibility to SQL injection attacks. Keep in mind, however, that preventing SQL injections is an ongoing battle. Hackers regularly find new exploits across all the different databases. Here are a few sites you can use to keep up to date with the latest SQL injection developments:

• • www.governmentsecurity.org/articles/_SQLInjectionModesofAttackDefenceandWhyItMatters.php

  • www.owasp.org

Security Policy

• • Code a variety of protections against SQL injection, not just one method.

  • Escape user input upon insertion into and retrieval from the database.

  • Use SqlParameters.

  • Process all errors on the server side.

  • Enforce the rule of least privilege in the code and in the database account.

--Proper exception handling we will see it on tomorrow.