Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Creating Dynamic SQL in a Stored Procedure with SQL Injection protection

Posted on 2006-04-18
10
Medium Priority
?
246 Views
Last Modified: 2012-08-13
Hi,

I am currently dynamically creating my SQL statements in some of my stored procedures, as quite alot of information is coming through in my parameters.  Currently I declare a nvarchar and build my statement in that, inserting my parameter variables where needed, then execute it.  But this, i have only recently realised, does not give me the protection that non-dynamic stored procedures give me.

For example, i have several tables that have 2 fields, code and description.  If i want to remove, update or insert into one of these tables, i pass the code, description and the table name as parameters.  How can i create a statement that can allow me to use a variable in the statement to allow me to specify a specific table to use, and that will offer me protection for SQL Injection attacks.

I have more complex SP's so im hoping that when i find out how to do this i will be able to apply it on a more complex query.

Kind Regards,
Michael
0
Comment
Question by:Xavior2K3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16476165
using sp_executesql can help:
http://doc.ddart.net/mssql/sql70/sp_ea-ez_4.htm
0
 
LVL 17

Accepted Solution

by:
HuyBD earned 1000 total points
ID: 16476217
Try this with insert sp


create procedure "yoursp"
@code int, @desc nvarchar(1000),@tbname char(100)
AS
declare @sql nvarchar(5000)
select @sql='select * from '+@tbname+' where code='+cast(@code as char (10))+' and description like ''%'''+rtrim(@desc)+'%'''
exec @sql
GO
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:Xavior2K3
ID: 16476268
That is currently how i build my statements, but it is still prone to SQL Injection attacks.

About using sp_executesql, in the examples from those links, i still cant see how i can use a parameter to specify a table name.  
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16476283
>i still cant see how i can use a parameter to specify a table name.  
indeed, you cannot use it for the table names, but for all the others.
then, to protect against a bad tablename, check if the table name exists:

if exists ( select * from sysobjects where name = @tablename )
begin
  ... do your sp_executesql here

end

with this sql, you can safely check the value of the  @tablename variable, which cannot be injected.
then, in the proc you can build the sql with the variable, but all the field values should be passed as variables in the sp_executesql
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16476620
>but it is still prone to SQL Injection attacks.

Can you clearify this a bit.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 16477018
I guess he speaks about HuyBD's suggestion.

Here is a full sample that is safe against injection, testing the table name explicitely, and using sp_executesql to prevent injection on the values.

create procedure "yoursp"
@code int, @desc nvarchar(1000),@tbname char(100)
AS
declare @sql nvarchar(5000)
set @desc = @desc + '%'

if exists(select 1 from sysobjects where name = @tblName)
begin
select @sql='select * from '+@tbname+' where code=@code and description like @desc '
exec sp_execute_sql @sql, N'@code int, @desc nvarchar(1000) ' ,@code, @desc
end

GO
0
 
LVL 1

Author Comment

by:Xavior2K3
ID: 16477328
Yes i was talking about HuyBD's suggestion.  Thanks for that sample, i will try it out and get back to you :)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question