Safe SQL Statements

DataStream

LANGUAGES: VB .NET

TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server

Safe SQL Statements

Can't use stored procedures? Try adding parameters toyour SQL statement.

By Wayne S. Freeze

Perhaps the most dangerous thing you can do in yourdatabase application is to build SQL statements dynamically using data suppliedby the user. If you simply string together the various pieces without theproper precautions, you have made it very easy for someone to execute their owndatabase commands on your database server. Although using a stored procedureavoids this problem, there are situations where you might not want to use astored procedure. In this article, I'll explore how a hacker can exploit thissecurity loophole and a technique you can use to close it.

Executing Too Many Statements

The security problem boils down to the fact that the SQLstatement includes text supplied by the end user. This gives a hacker anopportunity to add a second SQL statement to the one you really want toexecute. Consider this simple Select statement.

Select * From Customers WhereName='Dexter Valentine'

You might build it into your application using statementssimilar to these:

s = "Select * From Customers Where Name="

s &= "'" & Key.Text & "'"

Normally a person enters their search value into a textbox and the contents of the text box are inserted into the Select statement'sWhere 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 CustomersSet Comments='deleted

The resulting string you execute would look like this:

Select * From Customers WhereName='Dexter Valentine';Update Customers Set Comments='deleted'

Note that there are now two SQL statements. The first is theSelect statement you constructed; the second is the hacker's statement. Thereal trick is that the hacker knows you're using single quotes to surround theinformation from the form. By closing the single quoted string and adding asemicolon, the hacker can insert a second SQL statement. In this case thehacker executed an Update statement, but he or she could have executed anyother SQL statement.

In this particular example, the hacker took advantage ofthe fact that you would add a second single quote at the end of the statementand left the trailing quote off the information entered into the text box. Butthe hacker could have added a third dummy statement that would use the singlequote properly, so that the statement wouldn't return an error.

Secure the SQL Statement

One approach to solving this problem is to create aroutine that replaces all single quotes with double quotes. But this couldchange the meaning of the value entered. A better way to solve this problem isto create a parameterized SQL statement.

A parameterized SQL statement is similar to a storedprocedure, in that you have a fixed block of code into which you can substitutevalues without changing the meaning of that code block. To add parameters toyour query, you simply insert the parameters into the appropriate places inyour statement:

Select * From Customers WhereName=@Name

Then, the same techniques you would use to execute astored procedure are used to execute the parameterized statement (see Figure1). Basically, you instantiate a SqlCommand object containing your SQLstatement, then define each parameter used in the statement. Then you can usethe 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 Name=@Name"

Try

   Status.Text ="Parameter: success? (" & s & ")"

   ds = New DataSet()

   conn = NewSqlConnection(ConfigurationSettings.AppSettings("ConnStr"))

   cmd = New SqlCommand(s,conn)

   cmd.Parameters.Add("@Name", SqlDbType.VarChar, 255).Value =Key.Text

   adpt = NewSqlDataAdapter(cmd)

   adpt.Fill(ds,"Customer")

   RowCount.Text =ds.Tables("Customer").Rows.Count.ToString

Catch ex As Exception

   Status.Text ="Parameter: " & ex.Message

End Try

Figure 1. You usethe same techniques to execute a parameterized SQL statement as you would toexecute a stored procedure.

A Simple Demonstration

To demonstrate this concept, I built a simple program thatshows both techniques. Pressing the Execute Direct button triggers some codethat builds the SQL statement by combining the statement with the contents ofthe text box to create a single string to be executed (see Figure 2). Noticethat the one row was returned from the database and that the value of theComments column has been changed to deleted.

Figure 2. Executing a SQL statementwith a user-supplied value inserted directly into the statement allows a hackerto execute their own SQL statement after you execute yours.

Before trying to execute the parameterized statement, youneed to press the Clear Comments button to reset the database to the initialcondition. Then pressing the Execute w/Parameters button will generate theresults shown in Figure 3. Notice that the entire value from the Search Keytext box is used as the search value, meaning no rows are returned, and theComments 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, leavingyour database intact.

The sample code in thisarticle is available for download.

Wayne S. Freeze isa full-time computer book author with more than a dozen titles to his credit,including Windows Game Programming with VisualBasic and DirectX (Que), and UnlockingOLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25years of experience using all types of computers, from small, embeddedmicroprocessor control systems to large-scale IBM mainframes. Freeze has amaster's degree in management information systems as well as degrees incomputer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mailat mailto:[email protected]. He lovesreading e-mail from his readers, whose ideas, questions, and insights oftenprovide inspiration for future books and articles.