Stored Procedure to prevent sql injection with optional parameters and server side Paging and Sorting
To prevent sql injection on dynamic sql queries we must use parametrized sql queries. You can generate stored procedure from tables using this tool CodeGenerator from https://github.com/mijaved/CodeGenerator The following example illustrates optional parametrized sql statement with user supplied sorting and paging parameters: DECLARE @Mode INT = 2, @UserId INT = 0, @LoginId VARCHAR(50)='', @FirstName VARCHAR(50) ='', @LastName VARCHAR(50)='', @Password VARCHAR(32)='', @LastLoginDateFrom VARCHAR(32)='2012-09-19', @LastLoginDateTo VARCHAR(32)='2012-09-20', @SortBy VARCHAR(32)='UserId', @SortOrder VARCHAR(32)='DESC', @StartIndex INT = 1, @MaxIndex INT = 100; IF @Mode = 1 --Without Paging BEGIN SELECT UserId,FirstName,LastName,LoginId,LastLoginDate FROM SEC_TblUsers WHERE 1=1 AND (UserId = @UserId OR @UserId IS NULL OR @UserId <= 0) AND (LoginId = @LoginId OR @LoginId IS NU...