Avoid SQL Injection Attacks When Using Dynamic SQL
The EXEC () (or EXECUTE ())
command in SQL Server enables you to execute queries built dynamically
into a character string. This is a great feature for building queries on
the fly in your T-SQL code when it may not be possible to account for
all possible search criteria in a stored procedure or when static
queries may not optimize effectively.
However, when coding dynamic
SQL, it’s important to make sure your code is protected from possible
SQL injection attacks. A SQL injection attack is, as its name suggests,
an attempt by a hacker to inject T-SQL code into the database without
permission. Typically, the hacker’s goal is to retrieve confidential
data such as Social Security or credit card numbers or to possibly
vandalize or destroy data in the database.
SQL injection is
usually the result of faulty application design—usually an unvalidated
entry field in the application user interface. For example, this could
be a text box where the user would enter a search value. A hacker may
attempt to inject SQL statements into this entry field to try to gain
access to information in the database.
Although SQL injection is
essentially an application flaw, you can minimize the possibility of SQL
injection attacks by following some coding practices in your stored
procedures that make use of the EXEC() statement to dynamically build and execute a query. For example, consider the stored procedure shown in Listing 1, which might support a search page in a web application where the user is able to enter one or more optional search parameters.
Listing 1. Sample Procedure to Demonstrate SQL Injection
use bigpubs2008
go
create proc dbo.get_titles @type varchar(12) = null,
@pubdate varchar(10) = null,
@price varchar(6) = null,
@title varchar(80) = null
as
declare @where varchar(4000)
set @where = ' WHERE 1 = 1 '
if @type is not null
set @where = @where + ' AND type = ''' + @type + ''''
if @pubdate is not null
set @where = @where + ' AND pubdate = ''' + @pubdate + ''''
if @price is not null
set @where = @where + ' AND price = ' + @price
if @title is not null
set @where = @where + ' AND title like ''' + @title + ''''
exec ('select left(title, 60) as title,
convert (char(10), pubdate, 101) as pubdate from dbo.titles ' + @where)
return
|
Following is an example of a typical execution of this procedure if the end user enters business in the book type search field:
exec dbo.get_titles @type = 'business'
go
title pubdate
------------------------------------------------------------ ----------
The Busy Executive's Database Guide 06/12/2004
Cooking with Computers: Surreptitious Balance Sheets 06/09/2004
You Can Combat Computer Stress! 06/30/2004
Straight Talk About Computers 06/22/2004
To understand how to prevent SQL
injection attacks, let’s look at a way that a hacker might attempt a SQL
injection attack. What a hacker attempting a SQL injection attack might
typically do with a web application is to seek out a search field and
try to inject some SQL code into it to see if the application is
susceptible to an attack. This would typically be done by inserting a
single quotation mark into the search field to close out the string and
appending a SQL statement after it. For example, the hacker may enter
the string ' OR 1 = 1 -- into the search field. This search condition contains a single quotation mark and an end comment marker (--).
The quotation mark would have the effect of closing the search string,
and the comment marker would comment out the rest of the query to
prevent syntax errors caused by the SQL injection. The additional search
condition appended to the query would cause the query to return all
rows from the table, and the hacker would know he’s found a susceptible
text box:
exec dbo.get_titles @title = ''' OR 1 = 1 --'
go
title pubdate
------------------------------------------------------------ ----------
Samuel Johnson 09/19/2008
Freud, Dora, and Vienna 1900 02/25/2008
Freud: A Life for Our Time 06/21/2008
For Love of the World 01/06/2006
Freud: A Life for Our Time 10/16/2007
The Riddle of Anna Anderson 02/21/2007
Final Analysis 05/31/2006
...
When the hacker sees the
results of this query, he knows he’s found a susceptible text field and
may try to query the system catalogs. Typically, this is done with a UNION ALL statement because the application would likely generate an error if multiple result sets were returned. With UNION,
the hacker’s SQL injection statement would have to return the same
number of columns as the original query, and the columns would need to
be of compatible data types. It might take the hacker some trial and
error, but eventually, he might come up with a query string such as
UNION ALL select TABLE_NAME, NULL from INFORMATION_SCHEMA.TABLES --
to append to the following search value:
exec dbo.get_titles @title = ''' UNION ALL select TABLE_NAME, NULL
from INFORMATION_SCHEMA.TABLES --'
go
title pubdate
-------------------------------- -----------
sales_big NULL
roysched NULL
titleauthor NULL
PARTS NULL
titleview NULL
publishers NULL
top_sales NULL
sales_archive NULL
authors NULL
jobs NULL
pub_info NULL
sales NULL
sales_qty_rollup NULL
stores NULL
titles NULL
discounts NULL
employee NULL
sales_noclust NULL
With this information, the
hacker has a list of all the tables defined in the database. At this
point, the hacker might try to probe deeper into the database to look
for sensitive data. For example, the hacker might decide to try to query
the authors table to see if there is
any sensitive data, such as Social Security numbers, in it. To do so,
the hacker would use a trial-and-error approach of entering various SQL
injection queries against the system catalogs to get column information,
possibly eventually coming up with the SQL injection statements used in
the following queries:
exec dbo.get_titles @title = ''' UNION ALL select name, str(id) from sysobjects
where name = ''authors''--'
go
title pubdate
-------------------------------- ----------
authors 1253579504
exec dbo.get_titles @title = ''' UNION ALL select name, null from syscolumns
where id = 1253579504--'
go
title pubdate
-------------------------------- ----------
au_id NULL
au_lname NULL
au_fname NULL
phone NULL
address NULL
city NULL
state NULL
zip NULL
contract NULL
At this point, the hacker has an idea what fields are in the authors
table and could attempt to view data in those columns to try to uncover
personal information such as Social Security numbers, credit card
numbers, addresses, and so on:
exec dbo.get_titles @title = ''' UNION ALL select au_id + au_lname,
au_fname from authors--'
go
title pubdate
------------------------------------------------------------ --------------------
681-61-9588Ahlberg Allan
739-35-5165Ahlberg Janet
499-84-5672Alexander Lloyd
969-00-7063Amis Martin
263-46-4600Arendt Hannah
626-03-3836Arnosky Jim
432-31-3829Bate W. Jackson
437-99-3329Bauer Caroline Feller
378-33-9373Benchley Nathaniel
409-56-7008Bennet Abraham
648-92-1872Blotchet-Halls Reginald
...
The hacker could
continue the SQL injection attack by attempting to view data in other
tables. Or, worse, he could attempt to destroy data by attempting
updates, deletes, or inserts of his own data, or, even worse, truncate
tables or drop tables entirely. Depending on the account that the
application runs under and the rights assigned to that account, the
hacker might even be able to run system stored procedures or extended
stored procedures such as xp_cmdshell.
So what can be done to help
avoid SQL injection attacks? The best solution is to make sure the
application itself performs validation on the user input fields to
prevent the injection of SQL commands. Also, the application should keep
any input fields as small as possible to reduce the likelihood of a
hacker being able to squeeze SQL code into the field without it being
truncated (which usually leads to a T-SQL syntax error). In addition,
there are precautions you can take in your databases and T-SQL code as a
secondary mechanism to prevent SQL injection.
On the database side, one
thing you can do is to make sure the application runs under a user
account with the minimal permissions necessary. You should avoid giving
the application user account either dbo or, worse, sa permissions. (It’s frightening how many applications are out there running under the dbo or sa account!) Restricting the permissions restricts the amount of damage a hacker might possibly inflict on a database.
Another way you can help avoid
SQL injection is to check for common SQL injection methods in your
stored procedure code. For example, you could recode the procedure shown
in Listing 1 to look for the UNION
statement (one common method used in SQL injection attacks) and to
strip out any text from an input string from the point where the UNION statement is found. Listing 2 shows an example of this.
Listing 2. Stripping Out the UNION Clause to Help Avoid SQL Injection
alter proc get_titles @type varchar(12) = null,
@pubdate varchar(10) = null,
@price varchar(6) = null,
@title varchar(80) = null
as
declare @where varchar(4000),
@query varchar(8000)
set @query = 'select left(title, 60) as title,
convert (char(10), pubdate, 101) as pubdate from dbo.titles '
set @where = ' WHERE 1 = 1 '
if @type is not null
set @where = @where + ' AND type = ''' + @type + ''''
if @pubdate is not null
set @where = @where + ' AND pubdate = ''' + @pubdate + ''''
if @price is not null
set @where = @where + ' AND price = ' + @price
if @title is not null
set @where = @where + ' AND title like ''' + @title + ''''
-- The following statement truncates the @where clause at the point where
-- it finds the UNION statement
select @query = @query + left(@where, charindex('UNION', @where) - 1)
exec (@query)
return
|
When this query is executed, the UNION statement is stripped out. The WHERE clause ends up looking like the following:
WHERE 1 = 1 AND title like ''
With the UNION stripped out, leaving only an empty string, the query now returns an empty result set:
exec dbo.get_titles @title = ''' UNION ALL select name,null from syscolumnswhere id = 1253579504--'
title pubdate
------------------------------------------------------------ ----------
In addition to having the stored procedure code look for the UNION
clause, you could further expand on this to look for other common SQL
injection methods. One other, possibly more reliable, way to help avoid
SQL injection attacks is to parameterize your dynamic queries by using sp_executesql.
When you embed parameters in the dynamic SQL string rather than build
it on the fly, it is much harder for a hacker to insert SQL statements
into the dynamic query. Listing 3 shows an example of this approach.
Listing 3. Using sp_executsql to Help Avoid SQL Injection
alter proc get_titles @type varchar(12) = null,
@pubdate varchar(10) = null,
@price varchar(6) = null,
@title varchar(80) = null
as
declare @where nvarchar(2000),
@query nvarchar(2000)
set @query = 'select left(title, 60) as title,
convert (char(10), pubdate, 101) as pubdate from dbo.titles '
set @where = ' WHERE 1 = 1 '
if @type is not null
set @where = @where + ' AND type = @type '
if @pubdate is not null
set @where = @where + ' AND pubdate = @pubdate '
if @price is not null
set @where = @where + ' AND price = convert(money, @price) '
if @title is not null
set @where = @where + ' AND title like @title '
-- Build the final query
select @query = @query + @where
exec sp_executesql @query,
N'@type varchar(12), @pubdate varchar(10),
@price varchar(6), @title varchar(80)',
@type, @pubdate, @price, @title
return
|
With this version of the procedure, the query that gets built looks like the following:
select left(title, 60) as title,
convert (char(10), pubdate, 101) as pubdate from dbo.titles
WHERE 1 = 1 AND title like @title
With the query written this way, the value passed in for @title when sp_executesql is invoked is the following:
'' UNION ALL select name, null from syscolumns where id = 1253579504--
With this value passed in as the argument to the @title variable in the where clause, the search condition eventually becomes this:
AND title like
"'' UNION ALL select name, null from syscolumns where id = 1253579504--"
As you might surmise, this string matches no actual titles in the titles tables, so an empty result set is returned.
There is another
alternative approach to preventing SQL injection attacks. Instead of
searching for possible injection methods within the parameters used to
build the query, you can make sure the dynamic SQL code fragments
contain only allowable keywords and values. For example, consider a
variation of the stored procedure in Listing 3 that allows for the specification of a dynamic sort on the query:
alter proc get_titles @type varchar(12) = null,
@pubdate varchar(10) = null,
@price varchar(6) = null,
@title varchar(80) = null,
@sort varchar(1000) = ''
as
declare @where nvarchar(2000),
@query nvarchar(2000)
set @query = 'select left(title, 60) as title,
convert (char(10), pubdate, 101) as pubdate from dbo.titles '
set @where = ' WHERE 1 = 1 '
if @type is not null
set @where = @where + ' AND type = @type '
if @pubdate is not null
set @where = @where + ' AND pubdate = @pubdate '
if @price is not null
set @where = @where + ' AND price = convert(money, @price) '
if @title is not null
set @where = @where + ' AND title like @title '
-- Build the final query
select @query = @query + @where
+ case when @sort <> '' then 'ORDER BY ' + @sort
else '' end
exec sp_executesql @query,
N'@type varchar(12), @pubdate varchar(10),
@price varchar(6), @title varchar(80)',
@type, @pubdate, @price, @title
Return
Although the use of parameterized SQL with sp_executesql avoids issues with SQL injection via the @type, @pubdate, @price, and @title parameters, the code opens itself up again to SQL injection with the @sort
variable being tacked onto the end of the query. Instead of trying to
find all the possible commands that could be embedded in the @sort variable, and alternative approach is to make sure it contains only allowable keywords and columns. The ORDER BY clause is reasonably simple; the only allowable values are the keywords DESC and ASC, column position numbers, and column names. In this example, you set up the code in the procedure to allow only the DESC and ASC
keywords and a limited set of column names. The code strips out all the
allowable values, and if anything else is left, you know that the @sort parameter contains something that it shouldn’t and can disallow it. An example using this approach is shown in Listing 4.
Listing 4. Checking for Only Allowable Values to Prevent SQL Injection
alter proc get_titles @type varchar(12) = null,
@pubdate varchar(10) = null,
@price varchar(6) = null,
@title varchar(80) = null,
@sort varchar(1000) = ''
as
declare @where nvarchar(2000),
@query nvarchar(2000) ,
@sort_check varchar(1000)
set @query = 'select left(title, 60) as title,
convert (char(10), pubdate, 101) as pubdate from dbo.titles '
-- Replace all allowable terms/syntax in @sort with empty strings
-- Allowable terms are ASC, DESC, title, pubdate, price, type and comma (,)
Set @sort_check = replace(@sort, 'ASCENDING', '')
Set @sort_check = replace(@sort_check, 'DESCENDING', '')
Set @sort_check = replace(@sort, 'ASC', '')
Set @sort_check = replace(@sort_check, 'DESC', '')
Set @sort_check = replace(@sort_check, ',', '')
Set @sort_check = replace(@sort_check, 'title', '')
Set @sort_check = replace(@sort_check, 'pubdate', '')
Set @sort_check = replace(@sort_check, 'price', '')
Set @sort_check = replace(@sort_check, 'type', '')
If @sort_check <> '' -- the @sort column contains a disallowed term
Begin
Raiserror ('Invalid sort specified', 16, 1)
Return -101
end
set @where = ' WHERE 1 = 1 '
if @type is not null
set @where = @where + ' AND type = @type '
if @pubdate is not null
set @where = @where + ' AND pubdate = @pubdate '
if @price is not null
set @where = @where + ' AND price = convert(money, @price) '
if @title is not null
set @where = @where + ' AND title like @title '
-- Build the final query
select @query = @query + @where
+ case when @sort <> '' then 'ORDER BY ' + @sort
Else '' end
exec sp_executesql @query,
N'@type varchar(12), @pubdate varchar(10),
@price varchar(6), @title varchar(80)',
@type, @pubdate, @price, @title
return
go
-- execute with a valid @sort value
exec get_titles @pubdate = '2/21/2007',
@sort = 'type ASC, price DESC'
-- execute with an attempted SQL injection in @sort parameter
exec get_titles @pubdate = '2/21/2007',
@sort = 'type ASC, price DESC; select * from sysobjects'
go
title pubdate
------------------------------------------------------------ ----------
The Riddle of Anna Anderson 02/21/2007
Zuckerman Unbound 02/21/2007
Later the Same Day 02/21/2007
(3 row(s) affected)
Msg 50000, Level 16, State 1, Procedure get_titles, Line 26
Invalid sort specified
|
Dynamic
SQL is a great feature in SQL Server that provides considerable
flexibility in building queries in stored procedures. However, it also
presents a risk if you are not careful to prevent possible SQL injection
attacks.
Comment Your T-SQL Code
Anyone who has ever had to
review or change some code recognizes the importance of comments. Even
if it seems obvious what the code does when you’re writing it, the
meaning will most certainly not be as obvious later, especially if
someone other than the original author is looking at it.
When you are working with
large code blocks, a good technique to follow is to add a comment at the
beginning and end of the code block:
while (...) /* Begin loop1 */
begin
...
end /* End loop1 */
...
if (@price > 1) /* if (@price > 1) */
begin
...
end /* if (@price > 1) */
Another recommendation is to
include a comment header at the beginning of compiled objects such as
stored procedures, functions, and triggers. Following is a sample
comment header you might want to use as a template:
/*********************************************************/
-- Procedure Name: p_proc_name
--
-- Purpose: Describe the purpose of the procedure here. Include any
-- special notes on usage or code logic
--
-- Input Parameters:
-- @param1 numeric(12,0) Not Required
-- @param2 tinyint Not required
--
-- Output Parameters:
-- None
--
-- Return Values: 0 Success
-- Other Error
--
-- Written By: Joe Developer, 7/1/08
-- Modified By: Joe Developer, 7/5/08
-- Describe changes made to the proc here
-- Reviewed By:
--
/*************************************************************/
