dynamic sql in a stored procedure and sql injection attacks?

Question

dynamic sql in a stored procedure and sql injection attacks?

SQL*

SSCertifiable

Points: 6638
More actions
March 3, 2010 at 2:31 am

#216206

Hi to all,
i need some guidelines about injection attacks?
i have stored procedure and is having the dynamic sql, i'm not passing the table name as dynamically will:
Ex:
declare @Loc_Qstr varchar(max)
SET @Loc_Qstr =''
set @Loc_Qstr = @Loc_Qstr +' select * from Customer where Customer_name = '''+@in_name+''''
print @Loc_Qstr
exec(@Loc_Qstr)
how it will be sqlinjection affected?
🙂

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

the problem is the @in_name.

since you are executing a string you put together, it is possible that instead of the expected customer name being 'dba-vb'

i could pass ''';DELETE FROM CUSTOMERS;DELETE FROM INVOICES;DELETE FROM PAYROLL;'

the result would be your original query not finding anything, and the other commands getting executed.

instead you should do it like this:

select * from Customer where Customer_name = @in_name

neater, cleaner, no worries about putting the single quotes before and after, etc.

this also makes it injection proof, because it looks for a Customer name named "DELETE FROM..."

INSTEAD of executing code.

dba-vb (3/3/2010)
Hi to all,
i need some guidelines about injection attacks?
i have stored procedure and is having the dynamic sql, i'm not passing the table name as dynamically will:
Ex:
declare @Loc_Qstr varchar(max)
SET @Loc_Qstr =''
set @Loc_Qstr = @Loc_Qstr +' select * from Customer where Customer_name = '''+@in_name+''''
print @Loc_Qstr
exec(@Loc_Qstr)
how it will be sqlinjection affected?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!