SQL Injection


Before you start to use dynamic SQL, you need to learn about SQL injection and how you protect your application against it. SQL injection is a technique whereby an intruder enters data that causes your application to execute SQL statements you did not intend it to. SQL injection is possible as soon there is dynamic SQL which is handled carelessly, be that SQL statements sent from the client, dynamic SQL generated in T-SQL stored procedures, or SQL batches executed from CLR stored procedures. This is not a line of attack that is unique to MS SQL Server, but all RDBMS are open to it.

Here is an example. The purpose of the procedure below is to permit users to search for orders by various conditions. A real-life example of such a procedure would have many more parameters, but I’ve cut it down to two to be brief. (This is, by the way, a problem for which dynamic SQL is a very good solution.) As the procedure is written, it is open for SQL injection:

CREATE PROCEDURE search_orders @custid nchar(5) = NULL,

@shipname nvarchar(40) = NULL AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ‘ SELECT OrderID, OrderDate, CustomerID, ShipName ‘ +

‘ FROM dbo.Orders WHERE 1 = 1 ‘

IF @custid IS NOT NULL

SELECT @sql = @sql + ‘ AND CustomerID LIKE ”’ + @custid + ””

IF @shipname IS NOT NULL

SELECT @sql = @sql + ‘ AND ShipName LIKE ”’ + @shipname + ””


Before we look at a real attack, let’s just discuss this from the point of view of user-friendliness. Assume that the input for the parameters @custid and @shipname comes directly from the user and a naïve and innocent user wants to look for orders where ShipName is Let’s Stop N Shop, so he enters Let’s. Do you see what will happen? Because @shipname includes a single quote, he will get a syntax error. So even if you think that SQL injection is no issue to you, because you trust your users, you still need to read this section, so that they can search for Brian O’Brien and Samuel Eto’o.

So this is the starting point. A delimiter, usually a single quote, affects your dynamic SQL, and a malicious user can take benefit of this. For instance, consider this input for @shipname:

‘ DROP TABLE Orders —

The resulting SQL becomes:

SELECT * FROM dbo.Orders WHERE 1 = 1 AND ShipName LIKE ” DROP TABLE orders –‘

This is a perfectly legal batch of T-SQL, including the text in red. Since there is something called permissions in SQL Server, this attack may or may not succeed. A plain user who runs a Windows application and who logs into SQL Server with his own login, is not likely to have permissions to drop a table. But it is not uncommon for web applications to have a general login that runs SQL queries on behalf of the users. And if this web app logs into SQL Server with sysadmin or db_owner privileges, the attack succeeds. Mind you, with sysadmin rights, the attacker can add users and logins as he pleases. And if the service account for SQL Server has admin privileges in Windows, the attacker has access into your network far beyond SQL Server through xp_cmdshell. (Which is disabled by default on SQL 2005 and later, but if the attacker has achieved sysadmin rights on the server, he can change that.)

Typically, an attacker first tests what happens if he enters a single quote () in an input field or a URL. If this yields a syntax error, the attacker knows that there is a vulnerability. He then finds out if he needs any extra tokens to terminate the query, and then he can add his own SQL statement. Finally, he adds a comment character to kill the rest of the SQL string to avoid syntax errors. Single quote is the most common character to reveal openings for SQL injection, but if you have dynamic table and column names, there are more options an attacker could succeed with. Take this dreadful version of general_select:

CREATE PROCEDURE general_select2 @tblname nvarchar(127),

@key varchar(10) AS

EXEC(‘SELECT col1, col2, col3

FROM ‘ + @tblname + ‘

WHERE keycol = ”’ + @key + ””)

and assume that @tblname comes from a URL. There are quite some options that an attacker could use to take benefit of this hole.

And don’t overlook numeric values: they can very well be used for SQL injection. Of course, in a T-SQL procedure where the value is passed as an int parameter there is no risk, but if a supposedly numeric value is directly interpolated into an SQL string in client code, there is a huge potential for SQL injection.

Keep in mind that user input comes from more places than just input fields on a form. The most commonly used area for injection attacks on the Internet is probably parameters in URLs and cookies. Thus, be very careful how you handle anything that comes into your application from the outside.

You may think that it takes not only skill, but also luck for someone to find and exploit a hole for SQL injection. But remember that there are too many hackers out there with too much time on their hands. SQL injection is a serious security issue, and you must take precautions to protect your applications against it.

One approach I seen mentioned from time to time, is to validate input data in some way, but in my opinion that is not the right way to go. Here are the three steadfast principles you need to follow:

  • Never run with more privileges than necessary. Users that log into an application with their own login should normally only have EXEC permissions on stored procedures. If you use dynamic SQL, it should be confined to reading operations so that users only need SELECT permissions. A web site that logs into a database should not have any elevated privileges, preferably only EXEC and (maybe) SELECT permissions. Never let the web site log in as sa!
  • For web applications: never expose error messages from SQL Server to the end user.
  • Always used parameterised statements. That is, in a T-SQL procedure use sp_executesql, not EXEC().

The first point is mainly a safeguard, so that if there is a injection hole, the intruder will not be able to do that much harm. The second point makes the task for the attacker more difficult as he cannot get feedback from his attempts.

But it is the third point that is the actual protection, and that we will look a little closer at. The procedure search_orders above should be coded as:

CREATE PROCEDURE search_orders @custid nchar(5) = NULL,

@shipname nvarchar(40) = NULL AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ‘ SELECT OrderID, OrderDate, CustomerID, ShipName ‘ +

‘ FROM dbo.Orders WHERE 1 = 1 ‘

IF @custid IS NOT NULL

SELECT @sql = @sql + ‘ AND CustomerID LIKE @custid ‘

IF @shipname IS NOT NULL

SELECT @sql = @sql + ‘ AND ShipName LIKE @shipname ‘

EXEC sp_executesql @sql, N’@custid nchar(5), @shipname nvarchar(40)’,

@custid, @shipname

Since the SQL string does not include any user input, there is no opening for SQL injection. It’s as simple as that. By the way, note that since we can include parameters in the parameter list, even if they don’t actually appear in the SQL string, we don’t need any complicated logic to build the parameter list, but can keep it static. In the same way, we can always pass all input parameters to the SQL string.

As you may recall, you cannot pass everything as parameters to dynamic SQL, for instance table and column names. In this case you must enclose all such object names in quotename(), that I will return to in the section “Good Coding Practices and Tips for Dynamic SQL article link”

The example above was for dynamic SQL in a T-SQL stored procedure. The same advice applies to SQL generated in client code or in a CLR stored procedure. Since this is so important, here is an example of coding the above in VB6 and ADO:

Set cmd = CreateObject(“ADODB.Command”)

Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdText

cmd.CommandText = ” SELECT OrderID, OrderDate, CustomerID, ShipName ” & _

” FROM dbo.Orders WHERE 1 = 1 “

If custid <> “” Then

cmd.CommandText = cmd.CommandText & ” AND CustomerID LIKE ? “


cmd.CreateParameter(“@custid”, adWChar, adParamInput, 5, custid)

End If

If shipname <> “” Then

cmd.CommandText = cmd.CommandText & ” AND ShipName LIKE ? “

cmd.Parameters.Append cmd.CreateParameter(“@shipname”, _

adVarWChar, adParamInput, 40, shipname)

End If

Set rs = cmd.Execute

Since the main focus of this text is dynamic SQL in T-SQL procedures, I will explain this example only briefly. In ADO you use ? as a parameter marker, and you can only pass parameters that actually appear in the SQL string. (If you specify too many parameters, you will get a completely incomprehensible error message.) If you use the SQL Profiler to see what ADO sends to SQL Server, you will find that it invokes – sp_executesql.

Protection against SQL injection is not the only advantage of using parameterised queries. In the section “Caching Query Plans article link”, we will look more in detail on parameterised queries and at a second very important reason to use them.

You may think that an even better protection against SQL injection is to use stored procedures with static SQL only. Yes, this is true, but! It depends on how you call your stored procedures from the client. If you compose an EXEC command into which you interpolate the input values, you are back on square one and you are as open to SQL injection as ever. In ADO, you need to call your procedure with the command type adCmdStoredProc and use. CreateParameter to specify the parameters. By specifying adCmdStoredProc, you call the stored procedure through RPCRemote Procedure Call, which not only protects you against SQL injection, but it is also more efficient. Similar measures apply to other client APIs; all APIs I know of supply a way to call a stored procedure through RPC