LANGUAGES: VB .NET
TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server
Safe SQL Statements
Can't use stored procedures? Try adding parameters to your SQL statement.
By Wayne S. Freeze
Perhaps the most dangerous thing you can do in your database application is to build SQL statements dynamically using data supplied by the user. If you simply string together the various pieces without the proper precautions, you have made it very easy for someone to execute their own database commands on your database server. Although using a stored procedure avoids this problem, there are situations where you might not want to use a stored procedure. In this article, I'll explore how a hacker can exploit this security loophole and a technique you can use to close it.
Executing Too Many Statements
The security problem boils down to the fact that the SQL statement includes text supplied by the end user. This gives a hacker an opportunity to add a second SQL statement to the one you really want to execute. Consider this simple Select statement.
Select * From Customers Where Name='Dexter Valentine'
You might build it into your application using statements similar to these:
s = "Select * From Customers Where Name="
s &= "'" & Key.Text & "'"
Normally a person enters their search value into a text box and the contents of the text box are inserted into the Select statement's Where clause. If someone were to enter a value like this into the text box, however, the integrity of the statement is radically changed:
Dexter Valentine';Update Customers Set Comments='deleted
The resulting string you execute would look like this:
Select * From Customers Where Name='Dexter Valentine';Update Customers Set Comments='deleted'
Note that there are now two SQL statements. The first is the Select statement you constructed; the second is the hacker's statement. The real trick is that the hacker knows you're using single quotes to surround the information from the form. By closing the single quoted string and adding a semicolon, the hacker can insert a second SQL statement. In this case the hacker executed an Update statement, but he or she could have executed any other SQL statement.
In this particular example, the hacker took advantage of the fact that you would add a second single quote at the end of the statement and left the trailing quote off the information entered into the text box. But the hacker could have added a third dummy statement that would use the single quote properly, so that the statement wouldn't return an error.
Secure the SQL Statement
One approach to solving this problem is to create a routine that replaces all single quotes with double quotes. But this could change the meaning of the value entered. A better way to solve this problem is to create a parameterized SQL statement.
A parameterized SQL statement is similar to a stored procedure, in that you have a fixed block of code into which you can substitute values without changing the meaning of that code block. To add parameters to your query, you simply insert the parameters into the appropriate places in your statement:
Select * From Customers Where [email protected]
Then, the same techniques you would use to execute a stored procedure are used to execute the parameterized statement (see Figure 1). Basically, you instantiate a SqlCommand object containing your SQL statement, then define each parameter used in the statement. Then you can use the statement anywhere you can use a SqlCommand object.
Dim s as String
Dim ds As DataSet
Dim adpt As SqlDataAdapter
Dim conn As SqlConnection
Dim cmd As SqlCommand
s = "Select * From Customers Where [email protected]"
Status.Text = "Parameter: success? (" & s & ")"
ds = New DataSet()
conn = New SqlConnection(ConfigurationSettings.AppSettings("ConnStr"))
cmd = New SqlCommand(s, conn)
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 255).Value = Key.Text
adpt = New SqlDataAdapter(cmd)
RowCount.Text = ds.Tables("Customer").Rows.Count.ToString
Catch ex As Exception
Status.Text = "Parameter: " & ex.Message
Figure 1. You use the same techniques to execute a parameterized SQL statement as you would to execute a stored procedure.
A Simple Demonstration
To demonstrate this concept, I built a simple program that shows both techniques. Pressing the Execute Direct button triggers some code that builds the SQL statement by combining the statement with the contents of the text box to create a single string to be executed (see Figure 2). Notice that the one row was returned from the database and that the value of the Comments column has been changed to deleted.
Figure 2. Executing a SQL statement with a user-supplied value inserted directly into the statement allows a hacker to execute their own SQL statement after you execute yours.
Before trying to execute the parameterized statement, you need to press the Clear Comments button to reset the database to the initial condition. Then pressing the Execute w/Parameters button will generate the results shown in Figure 3. Notice that the entire value from the Search Key text box is used as the search value, meaning no rows are returned, and the Comments column in the database remains unchanged.
Figure 3. By your using parameters, a hacker's attempt to execute his or her SQL statement with yours fail, leaving your database intact.
The sample code in this article is available for download.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que), and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail at mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.