Creating Dynamic SQL in a Stored Procedure with SQL Injection protection

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
LVL 1
Xavior2K3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
using sp_executesql can help:
http://doc.ddart.net/mssql/sql70/sp_ea-ez_4.htm
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
HuyBDCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Xavior2K3Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
imran_fastCommented:
>but it is still prone to SQL Injection attacks.

Can you clearify this a bit.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Xavior2K3Author Commented:
Yes i was talking about HuyBD's suggestion.  Thanks for that sample, i will try it out and get back to you :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.