Let's imagine a stored procedure that retreives data and do some kind of pagination. This procedure has some inputs describing which set of data we want and how we sort it. Here is a very simple query, but let's take it as an example.
create table Persons(id int, firstName varchar(50), lastName varchar(50)) go create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc' as declare @sql varchar(max) set @sql = 'select id, firstName, lastName from ( select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn from Persons ) t where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+' and rn It is supposed to be used like that:
exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id', @orderDir = 'desc' But a smart guy could launch:
exec GetPersons @pageNumber = 1, @pageSize = 20, @orderBy = 'id)a from Persons)t;delete from Persons;print''', @orderDir = '' . and drop data That's obviously not a secure situation. And how could we prevent it ? Note: this question is not about "is it a good way to do pagination ?" nor "is it a good thing to do dynamic sql?". The question is about preventing code injection when building sql queries dynamicaly in order to have some guidelines to make the code a bit cleaner if we have to do similar stored procedures again in the future. Some basic ideas: Validate inputs
create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy varchar(50) = 'id', @orderDir varchar(4) = 'desc' as if @orderDir not in ('asc', 'desc') or @orderBy not in ('id', 'firstName', 'lastName') begin raiserror('Cheater!', 16,1) return end declare @sql varchar(max) set @sql = 'select id, firstName, lastName from ( select id, firstName, LastName, row_number() over(order by '+@orderBy+' '+@orderDir+') as rn from Persons ) t where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+' and rn Pass ids instead of strings as inputs
create procedure GetPersons @pageNumber int = 1, @pageSize int = 20, @orderBy tinyint = 1, @orderDir bit = 0 as declare @orderByName varchar(50) set @orderByName = case @orderBy when 1 then 'id' when 2 then 'firstName' when 3 then 'lastName' end +' '+case @orderDir when 0 then 'desc' else 'asc' end if @orderByName is null begin raiserror('Cheater!', 16,1) return end declare @sql varchar(max) set @sql = 'select id, firstName, lastName from ( select id, firstName, LastName, row_number() over(order by '+@orderByName+') as rn from Persons ) t where rn > ('+cast(@pageNumber as varchar)+'-1) * '+cast(@pageSize as varchar)+' and rn