How to create dynamic parameters in SQL Server stored procedure
Below script is an example for how to create a procedure to pass dynamic parameters. This is not a new trick and many developers are using using the method. But probably not every one knows that it might leads to SQL Injection attacks.But still, one good thing about using .NET is that if you execute any sql statement from the front end then it will automatically calls the API function (sp_executesql)that rewrites the SQL statements to a parameterized query which is safe. so make sure the front-end technology which you are using has this capability to call the APIS while passing sql statements to backend.
-- execution script: Execute dbo.pr_customers 'te','tx',1
CREATE PROCEDURE dbo.Pr_customers(@firstname VARCHAR(90)=NULL,
@lastname VARCHAR(90)=NULL,
@age INT=NULL,
@phone INT=NULL)
AS
BEGIN
-- declare the variables to build the string dynamically
DECLARE @Where VARCHAR(MAX),
@SQL VARCHAR(MAX)
SET @Where = ' WHERE 1=1 '
IF ( @firstname IS NOT NULL )
SET @Where = @Where + ' AND firstname = ''' + @firstname + ''''
-- make sure that you append four quotes(') for charecter parameters
IF ( @lastname IS NOT NULL )
SET @Where = @Where + ' AND lastname = ''' + @lastname + ''''
IF ( @age IS NOT NULL )
SET @Where = @Where + ' AND city = ' + CONVERT(VARCHAR, @age)
-- for integer parameters you no need to append quotes(')
IF ( @phone IS NOT NULL )
SET @Where = @Where + ' AND country = ' + CONVERT(VARCHAR, @phone)
SET @SQL = 'select * from customers ' + @Where
EXEC (@SQL)
END
Thanks a lot :)
ReplyDeleteDO NOT USE THIS CODE.
ReplyDeleteCalling this procedure with sp_executesql is NOT going to help with the injection vulnerability, because you're explicitly defeating it. Yes, you have a nice parameterized stored procedure... but then you're building SQL on the fly inside your procedure and executing it. The front end has no way of knowing that you're doing this.
So someone comes along and says their first name is:
Bobby'; drop table customers--
The front end dutifully sticks Bobby'; drop table customers-- into the @firstname parameter, where it can't be executed, and passes it to your procedure. But then the procedure takes it OUT of the parameter and sticks it directly into what it's executing! Which results in this statement being run:
select * from customers WHERE 1=1 AND firstname = 'Bobby'; drop table customers--'
And now your customer table is deleted. They can pretty much do anything they like to your database. Or, you know, someone just tries to enter their actual name of O'Connor and the procedure blows up.
There's also performance issues with the query optimizer not being able to match statements and reuse query plans, but that's minor compared to the gaping security hole.
Agreed and mentioned at the very first place on this article. if you are using .net then there wont be any
Delete