SQL-Injection Attacks

SQL stands for Structured Query Language and is a specialized language for processing data contained in a relational database. SQL is a language just as Visual Basic .NET is a language—with its own unique syntax and capabilities. You reap the unique benefits of both languages by invoking SQL commands from your Visual Basic .NET application. A common way you might use SQL in your Visual Basic .NET application is to embed SQL commands in a string and then call through a database object such as an ADO.NET command object to execute the command. Your application becomes vulnerable to attack when you use unchecked user input as part of the SQL string you are constructing. Take for instance the following SQL statement:

Dim sql As String
‘Assume strLastName is a passed in string parameter
sql = "SELECT * FROM Authors WHERE LastName = ’" & _
 strLastName & "‘"

The intent of the preceding SQL statement is to return all authors having the last name specified by the strLastName parameter. However, the preceding SQL statement makes a bold assumption about the contents of strLastName— namely that the parameter contains a valid last name such as Theroux or Hemmingway. What if an attacker passed in the following string for the strLastName parameter instead?

Hemmingway’ DELETE FROM Authors WHERE LastName = ’Theroux

In this example, the attacker is taking advantage of the rich nature of SQL by terminating your SELECT query with “Hemmingway” and injecting an additional SQL command to delete all authors with the last name of Theroux. This is a classic example of a SQL-injection attack.

Note

The example shown is effective when launched against database systems such as Microsoft SQL Server 2000 that support execution of multiple SQL expression as part of a single SQL string. The attack will not work if launched against a Microsoft Access database because Access does not support executing multiple SQL expressions contained within the same SQL string. This is not to say that Access is resistant to all forms of SQL injection attacks. The next example demonstrates a SQL injection attack that works effectively against SQL Server, Access, and many other database systems.

The logon screen often presents itself to an attacker as a good opportunity to launch a SQL-injection attack. The reason is that to perform validation of the user name, you’ll be tempted to write code such as the following. Note this code was taken from the Create function contained within clsEmployee.vb in the Employee Management System (EMS) practice application located in the practice files directory on the companion CD under CH06_AvoidingAttacks\ EMS\Start\EMS.sln.

'G_CONNECTION string as defined in MainModule.vb
Const G_CONNECTIONSTRING As String = _
  "Provider=Microsoft.Jet.OLEDB.4.0; " & _
  "User ID=Admin;Data Source=..\..\..\..\..\EmployeeDatabase.mdb; " & _
  "Mode=Share Deny None;"
 
Dim strSQL As String = _
  "Select * from Employee where Username =‘" & _
 strUsername & "‘"

The code is troubling because it takes direct, unchecked user input for the user name and combines it into a SQL statement in the following line of code:

Dim strSQL As String = _
  "Select * from Employee where Username =‘" & _
 strUsername & "‘"

What if an attacker enters the following user name?

Bogus’ OR Username like ’%%

Some user name, eh? The attacker supplies any name—Bogus will do— followed by an apostrophe to complete the user name clause in SQL, followed by a conditional OR statement and an additional expression such as Username like ‘%%’ that returns all employees listed in the Employee table. The EMS application assumes if the database query returns one or more rows that the first user returned by the query represents the logged-on user. In this case, the attacker by supplying input to modify the SQL query to return all rows can successfully trick the EMS application into logging him on as the first user returned by the query—provided that the attacker can guess a name of a column in the Employee table (such as UserName) and the password of the first user returned by the query. In the case of the EMS sample database, the first employee listed in the Employee table is Anne Dodsworth; the first employee returned in the query based on the malicious SQL input supplied by the attacker. If the attacker can guess (or brute force the password by means of an automated tool) Anne’s password, which is ADodsworth, he can successfully log on to the system as Anne (without knowing her user name). You can see this for yourself by running the EMS application, supplying the user name shown previously, and the password of ADodsworth in the EMS logon dialog box. This is an example of where SQL’s rich support for embedded expressions containing conditional statements such as AND or OR can work against you.

Note that the trailing apostrophe provided in the strSQL assignment shown previously completes the expression making the following SQL statement:

Select * from Employee where Username = ’Bogus’ OR Username like ’%%’

This attack will work on most databases and doesn’t require any special database account permissions such as administrative permissions. In the next section, we’ll show you how to prevent it.

In addition to being able to avoid having to provide a valid user name and password, an attacker can execute arbitrary applications on your application server by providing embedded SQL commands. For example, suppose the attacker enters the following user name:

Bogus’; exec master..xp_cmdshell ’IISRESET /STOP’; --

As shown previously, the name Bogus followed by an apostrophe is used to complete the WHERE clause for the Username field. The attacker can provide any number of commands thereafter separated by semi-colons. In this case, the attacker injects the exec command to execute a SQL Server stored procedure named xp_cmdshell—a powerful command that lets you execute any application on the system. The attacker also executes IISRESET.EXE (located in the Windows directory) with the /STOP command. This will force the IIS server where the code is run to shut down—a form of denial of service (DoS) attack. The final two dashes at the end of the user name entered by the attacker is a SQL comment. This is needed to comment out the trailing apostrophe used at the end of the strSQL string shown in the code example earlier. The strSQL string contains the following text when executed:

Select * from Employee where Username =‘Bogus’; 
exec master..xp_cmdshell ’IISRESET /STOP’; --’

Execution of an arbitrary command using xp_cmdshell will work only if the SQL command is executed on behalf of a SQL Server administrator account. This can easily happen if you use the sa account (and associated password) as part of the ADO.NET connection string. For example, the following connection string uses the SQL Server sa account to connect to the database:

Provider=SQLOLEDB.1;Password=I’m4Strong$pWds;Persist Security Info=True;User ID=sa;Initial Catalog=Emplo yeeDatabase;Data Source=(local)

Warning

You should avoid connecting to a database using an administrator account—especially if your application is going to perform database actions on behalf of a normal user (as opposed to an administrative application that performs database actions on behalf of an administrator). You should instead connect using an account that can perform only those actions that need to be performed and no more—which is the principle of least privilege. Also the connection string just shown contains the administrative password in plain sight within the code (as well as in the compiled application). You should avoid storing secrets such as database passwords in plain sight. Chapter 1 shows how you can safely encrypt and store database passwords that are used as part of a connection string.

As luck would have it, the EMS application is protected against this type of xp_cmdshell attack if you use a Microsoft Access database, which is the default configuration of the EMS sample application as provided on the companion Web site. Microsoft Access does not support stored procedures, and execution of multiple SQL statements is not supported by the version of SQL used by Access. However, this is no reason to celebrate.

If the system is later upgraded to use a Microsoft SQL Server database (see Appendix A for steps on how to convert the Microsoft Access database to Microsoft SQL Server), which does support stored procedures (namely the xp_cmdshell stored procedure) and execution of multiple SQL statements, and the code is left unchanged, this is a welcome invitation to any intruder. There is no excuse to leave the code in its current state. Doing so is sloppy and is only one SQL Server upgrade away from being compromised.

Defensive Techniques for SQL-Injection Attacks

There are a few procedures you can follow to prevent a SQL-injection attack. You should, at a minimum, try to use the first two techniques.

Validate Input Parameters

One approach to prevent SQL-injection attacks is to check the input string to ensure that it doesn’t contain unnecessary characters—especially punctuation characters such as apostrophes, quotes, commas, semicolons, and parentheses that can be used to construct or modify SQL expressions. Consider the logon dialog box for the EMS sample application. There is no need for the user name (entered in the logon dialog box) to contain punctuation characters that would allow an attacker to construct damaging embedded SQL statements. The EMS application requires that user names be composed of alphabetic characters only. You can enforce a rule of allowing only alphabetic characters by using a regular expression check such as the following.

Dim fValidUserName As Boolean = False
'TODO: Add Imports System.Text.RegularExpressions to the top
' of the code module
'Note: strUserName contains the user name entered in the log on 
' dialog
fValidUserName = Regex.IsMatch(strUsername, "^[A-Za-z]+$")

In addition, there is no reason to allow the user to enter an infinitely long user name. In this case, a 15-character limit on the user-name is sufficient. You can modify the previous example by adding a check to ensure the length is 15 characters or less as follows:

Dim fValidUserName As Boolean = False
Const MAX_USERNAME_LENGTH = 15
'TODO: Add Imports System.Text.RegularExpressions to the top of 
' the code module
'Note: strUserName contains the user name entered in the log on 
' dialog
fValidUserName = strUsername.Length <= MAX_USERNAME_LENGTH _
 AndAlso Regex.IsMatch(strUsername, "^[A-Za-z]+$")

For a more in-depth discussion on how to use regular expressions and string length checks to validate input, see Chapter 7.

Use Parameterized Queries

A technique that works well to defend against SQL injection attacks is to use parameterized queries. A parameterized query allows you to define the structure of a SQL query and leave placeholders for parameters that will be passed into the query. This removes the opportunity for the attacker to modify the SQL string by passing in SQL statements as part of the input. Even if the attacker passes in SQL statements, the attacker’s input is treated as a parameter to the query as opposed to being allowed to modify the underlying query string itself (as shown previously).

The following line of code is an example of a parameterized query string:

“Select * from Employee where Username = @ParameterUserName”

Parameterized queries work for both Microsoft SQL Server and Microsoft Access databases. In the exercise presented later, you’ll see how you can use the preceding parameterized query string in conjunction with the OleDbCommand.Parameters collection to protect the EMS application from a SQL injection attack.

Add a Stored Procedure to Validate the User

If your application utilizes a Microsoft SQL Server database (or any database system that supports stored procedures), as an alternative to using parameterized queries, you should consider using a stored procedure to let your database run the query. You can create a stored procedure defined in SQL such as the following:

CREATE PROCEDURE IsValidUser 
 @username VarChar(50)
AS
 SELECT *
 FROM employee
 WHERE @username = UserName
GO

Note

Refer to Appendix A for an example of how to add the preceding stored procedure to the EMS sample EmployeeDatabase SQL Server database.

SQL Server stored procedures are an effective defense against SQL-injection attacks because the SQL statement definition is frozen within the stored procedure and cannot be modified by the input you supply. For example, any user name input you supply is considered to be part of the UserName parameter even if the UserName you supply happens to contain SQL statements.

When you call the IsValidUser stored procedure, it will return a row containing the user name if the user name is found in the database. The following code demonstrates how you can declare a SQL string to execute a SQL Server stored procedure named IsValidUser passing strUserName as an argument:

Dim strSQL As String = “IsValidUser “ & “‘“ & strUsername & “‘“

If you have converted the EMS database to SQL Server as shown in Appendix A, you can use the preceding declaration for strSQL in place of the declaration shown in step 5 of the following exercise.

Protect the EMS application against SQL-injection attacks

A few places in the EMS sample application are at risk for a SQL-injection attack. The following steps demonstrate how to fortify the application against this type of attack:

1. Run Visual Basic .NET, and open the practice file solution CH06_ValidatingInput\EMS\Start\EMS.sln provided on the companion CD.

2. Open SecurityLibrary.Vb, and add the following Imports statement to the top of the file:

   Imports System.Text.RegularExpressions

3. Add the following code to the end of the SecurityLibrary.vb code file. This is an example of a public function that can be used to validate an input parameter; you can call this function from any point in the code where the parameter is used.

   Namespace ValidateInput
    Module ValidateInput
    Public Function IsValidUserName(ByVal UserName As String)_
    As Boolean
    Const MAX_USERNAME_LENGTH As Integer = 15
    ’The EMS application requires that all user names be 
    ’15 characters or less and composed strictly of 
    ’alphabetic characters A-Z,
    ’no spaces or symbols
    If UserName.Length <= MAX_USERNAME_LENGTH AndAlso _
    Regex.IsMatch(UserName, "^[a-zA-Z]+$") Then
    Return True
    End If
    Return False
    End Function

4. Open clsEmployee.vb, and add the following code to the beginning of the Create function after the declaration of employee as follows:

    If Not ValidateInput.IsValidUserName(strUsername) Then
    employee.m_IsValidUser = False 
     Return employee
    End If

   Note

   The remaining code in the Create function is not shown. See the following step 6 for a full listing.

5. Change the SQL string to a parameterized SQL query by changing the string from

   Dim strSQL As String = _
     "Select * from Employee where Username =‘" & _
    strUsername & "‘"

   to

   Dim strSQL As String = "Select * from Employee " & _
     "where Username = @ParameterUserName"

6. Pass the user name to the parameterized SQL query by adding the user name value to the OleDbCommand object Parameters collection as follows:

   cmd.Parameters.Add("@ParameterUserName", strUsername)

   Add this statement immediately after the declaration of the cmd (OleDbCommand) variable.

When these steps have been completed, the Create function should appear as follows:

Public Shared Function Create(ByVal strUserName As String) _
 As clsEmployee
 
 Dim employee As New clsEmployee()
 
 'Avoid a SQL injection attack: Insure username contains no dangerous 
 'symbols such as apostrophes or dashes (SQL comment)
 If Not ValidateInput.IsValidUserName(strUserName) Then
 employee.m_IsValidUser = False
 Return employee
 End If
 
 'Avoid a SQL injection attack: Use a parameterized query to load 
 'information from the employee table
 Dim strSQL As String = _
 "Select * from Employee where Username = " & _
 "@ParameterUserName"
 
 Dim cn As OleDbConnection
 Dim cmd As OleDbCommand
 Dim dr As OleDbDataReader
 
 cn = New OleDbConnection(G_CONNECTIONSTRING)
 cmd = New OleDbCommand(strSQL, cn)
 
 cmd.CommandType = CommandType.Text
 cmd.Parameters.Add("@ParameterUserName", strUserName)
 
 cn.Open()
 dr = cmd.ExecuteReader()
 
 If dr.Read() Then
 employee = New clsEmployee()
 employee.FirstName = CStr(dr("FirstName"))
 employee.LastName = CStr(dr("LastName"))
 employee.FullName = CStr(dr("Fullname"))
 employee.m_PasswordHash = CStr(dr("PasswordHash"))
 employee.m_BankAccountEncrypted = _
 CStr(dr("BankAccountEncrypted"))
 employee.m_Username = strUserName
 employee.m_IsValidUser = True
 End If
 
 'Obtain the list of roles associated with the user and assigns
 'the principal--containing the user and roles--to the 
 'current thread.
 SetPrincipalPolicy(employee.m_Username)
 Return employee
 
End Function

Note

The call to ValidateInput.IsValidUserName has been carefully placed at a point where the input value could do the most damage. The check is a final check of the parameter, a last-line-of-defense check so to speak, before it is passed as a SQL parameter to a SQL parameterized query to do the user name lookup. This emphasizes the point that no matter how much error checking you add to the rest of the application, if a call that bypasses all other error checking can be made into this function, all of those checks are for nothing. This is the check on input that counts.