sql stored proc with dynamic sql

I am running a stored procedure in which i am creating the query in a variable @query by checking some input variables and then exceuting the query by execute(@query).I know these type has more chances to sql injection,but in my case i don't have any other options.I came to know that using paremeterized inputs for creating this query would reduce the risk of sql injection in these dynamic query stored proc.Can anyone show me an example of using parametrized inputs for dynamic query.
rathiaguAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
Sure...

create proc mydynamicproc
(
@tablename nvarchar(255),
@whereclause nvarchar(255)
)
as
begin
declare @sql nvarchar(2000)
set @sql = 'SELECT * FROM ' + @tablename + ' WHERE ' + @whereclause

exec sp_executesql @sql
end
0
 
Mark WillsTopic AdvisorCommented:
Just to add a couple of qualifiers to the above.... putting the "bits' on a new line and the where in brackets and extending the criteria prevents the inclusion of a terminated "where" (ie a semicolon, or commented) with a new 'command' being the injection - the @sql statement will fail. But there are still more techniques for sql injection...

Is this on the right track ?


create proc mydynamicproc (@columns varchar(2000),@tablename varchar(2000), @whereclause varchar(2000))
as
begin
declare @sql varchar(8000)

set @sql = 'SELECT
'+ @columns +'
FROM
' + @tablename + '
WHERE
(' + @whereclause +')
AND (1=1)'

exec (@sql)
end
0
 
rathiaguAuthor Commented:
Thank U chapmandew and mark wills
0
 
chapmandewCommented:
you're welcome...
0
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.

All Courses

From novice to tech pro — start learning today.