Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

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