Remedy 2: Building SQL Statements Securely

Remedy #2: Building SQL Statements Securely

Building SQL strings in code is problematic, as I've demonstrated earlier in this chapter. A simple way to remedy this is to leave the completion of the SQL string to the database and to not attempt the SQL string construction in your code. Instead, you should use placeholders, which are often referred to as parameterized commands. When you define the query, you determine which parts of the SQL statement are the parameters. For example, the following is a parameterized version of a query:

SELECT count(*) FROM client WHERE name=? AND pwd=?

Next, we need to define what the parameters are; these are passed along with the skeletal SQL query to the SQL database for processing. The following Visual Basic Scripting Edition (VBScript) function outlines how to use SQL placeholders:

Function IsValidUserAndPwd(strName, strPwd) ' Note I am using a trusted connection to SQL Server. ' Never use uid=sa;pwd= strConn = "Provider=sqloledb;" + _  "Server=server-sql;" + _  "database=client;" + _  "trusted_connection=yes" Set cn = CreateObject("ADODB.Connection") cn.Open strConn Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = cn cmd.CommandText = _  "select count(*) from client where name=? and pwd=?" cmd.CommandType = 1 ' 1 means adCmdText cmd.Prepared = true ' Explanation of numeric parameters: ' data type is 200, varchar string; ' direction is 1, input parameter only; ' size of data is 32 chars max. Set parm1 = cmd.CreateParameter("name", 200, 1, 32, "") cmd.Parameters.Append parm1 parm1.Value = strName Set parm2 = cmd.CreateParameter("pwd", 200, 1, 32, "") cmd.Parameters.Append parm2 parm2.Value = strPwd Set rs = cmd.Execute IsValidUserAndPwd = false If rs(0).value = 1 Then IsValidUserAndPwd = true rs.Close cn.Close End Function

Additionally, parameterized queries are faster than hand-constructing the SQL query in code. It's not often you find an approach that's both more secure and faster!

One prime benefit of using parameters is that you can define the parameter data type. For example, if you define a numeric parameter, the strong type checking will thwart most attacks because a SQL-based attack cannot be made purely from numbers. If your application uses open database connectivity (ODBC) and you want to use parameters, you need to use the SQLNumParams and SQLBindParam functions. If you use OLE DB, you can use the ICommandWithParameters interface. If your code is managed code, you can use the SqlCommand class.

Building SQL Stored Procedures Securely

The parameterized queries demonstrated are useful when the database is accessed from an external application, such as a Web service. However, you might need to perform similar actions within SQL stored procedures. You should be aware of the following two simple mechanisms that help build secure statements.

First, use the quotename function for object names. For example, select top 3 name from mytable would become select top 3 [name] from [mytable] if you quote name and mytable. The function quotename is a built-in Transact-SQL function see SQL Server Books Online for more information that works well. It adds delimiters to object names to help nullify invalid characters. You can see the effect if you run the code below in SQL Query Analyzer. The example also shows that the query also handles ASCII codes, discussed earlier in this chapter.

declare @a varchar(20) set @a=0x74735D27 select @a set @a=quotename(@a) select @a set @a='ts]''' select @a set @a=quotename(@a) select @a

Note the data in @a in the second code block ('ts] '''). It becomes a safe string delimited by [ and ].

Second, use sp_executesql to execute SQL statements built dynamically, instead of just concatenating a string. This makes sure no malformed parameters are passed along to the database server. Here's an example:

-- Test the code with these variables declare @name varchar(64) set @name = N'White' -- Do the work exec sp_executesql     N'select au_id from pubs.dbo.authors where au_lname=@lname',     N'@lname varchar(64)',     @lname = @name

These two mechanisms are present in Microsoft SQL Server, and developers creating stored procedures should use them, as they provide an extra level of defense. You never know how your stored procedures might be called in future! On the subject of defense in depth, let's look at how defense in depth database-manipulation code should be written.