dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Securing Your SQL Server : Page 2

A growing consciousness of security has brought Microsoft SQL Server database security into focus as never before. Planning for database security should begin early in the development process and there are important vulnerabilities that you need to prevent in your application's SQL Server.


advertisement
Preventing SQL Injection
There are numerous ways you can prevent or deter SQL injection when working with SQL Server. Let's start with the most secure.

Avoid Dynamic SQL
You can easily eliminate the possibility of SQL injection by not dynamically constructing SQL commands in the first place. One of the easiest ways to do that with SQL Server is to call stored procedures that contain the SQL string and pass your input string to the stored procedure as a parameter:

  -- Not vulnerable to injection
   CREATE PROC usp_AuthorGetName
   @city nvarchar(2000)
   AS
   SELECT au_lname, au_fname
   FROM authors
   WHERE city = @city
   RETURN
The advantage is that whatever the user enters is considered the entire comparison value. Instead of being able to inject a SQL command the intruder simply adds characters, most likely meaningless, to the string.

You can easily eliminate the possibility of SQL injection by not dynamically constructing SQL commands in the first place.
Note that if you use dynamic SQL in a stored procedure you may end up reintroducing injection vulnerability:

  -- Vulnerable to injection
   CREATE PROC usp_AuthorGetName
   @city nvarchar(2000)
   AS
   DECLARE @cmd nvarchar(2000)
   SET @cmd = 'SELECT au_lname, au_fname 
      FROM authors WHERE city = ''' + @city + ''''
   EXEC (@cmd)
   RETURN
Use sp_executesql with Parameters
If you cannot change your code to call stored procedures you may be able to use sp_executesql. Suppose your form creates a query with a dynamically generated list of columns and search arguments. If you used a stored procedure and tried to pass in the column names as parameters, you'd still have to use dynamic SQL because you cannot use a variable in a column or table name. However, you can dynamically construct a column list and then send parameter(s) for the search argument(s) using the system stored procedure sp_executesql. The following example parameterizes the search argument and prevents injection:

  -- Not vulnerable to injection
   DECLARE @OuterVar nvarchar(2000),
   @cmd nvarchar(2000)
   SET @cmd = 'SELECT au_lname, au_fname 
      FROM authors 
      WHERE city = @Innervar'
   SET @OuterVar = ''' UNION 
      Select login, password 
      from master..syslogins --'
   EXEC sp_executesql @cmd,
   N'@Innervar nvarchar(2000)',
   @Innervar = @OuterVar
You've got to make sure to send the parameter into sp_executesql. If you send the raw command without any parameters, you reintroduce the possibility of injection:

  -- Vulnerable to injection
   DECLARE @VAR nvarchar(2000),
   @cmd nvarchar(2000)
   SET @var = ''' UNION 
      Select name, password 
      from master..syslogins --'
   SET @cmd = 'SELECT au_lname, au_fname 
      FROM authors WHERE city = ''' 
      + @var + ''''
   EXEC sp_executesql @cmd
Limit the Size of Input Strings
In the examples above the strings have deliberately been made much longer than necessary to accommodate the SQL injection example. If the city name were limited to, say, 30 characters that would dramatically reduce if not eliminate the number of SQL injection possibilities.

Replace a Single Quote with Two Single Quotes

If you must dynamically construct your string and you cannot use sp_executesql, you can replace a single quote with two single quotes in your input string. This can be a very effective measure for client-side code and can work well in stored procedures. Here's the previous stored procedure rewritten so that replacing the single quote stops the injection:

  -- Replace stops the injection
   CREATE PROC usp_AuthorGetName
   @City nvarchar(2000)
   AS
   DECLARE @cmd nvarchar(2000)
   SET @city = REPLACE(@city, '''', '''''')
   SET @cmd = 'SELECT au_lname, au_fname
   FROM authors WHERE city = ''' +
   @city + ''''
   EXEC (@cmd)
   RETURN
The generated command behaves the same as when you parameterize the search argument in a variable; the comparison value becomes the entire string, and the intruder's single quote is taken as an embedded quote mark, not the end of a string.

Scan for Injected Strings
You could scan the entered text looking for SQL keywords such as UNION in a case-insensitive manner, or scan for a single quote mark, or the two dashes that comment the ending string. This may not be possible for all input text because there are streets and cities named 'union', there are names with single quotes in them (O'Shaunessy), and so on.



Thanks for your registration, follow us on our social networks to keep up-to-date