Such an efficient and common paging stored procedure is a SQL injection vulnerability

Stored Procedures | Paging in GoogleSearch for "Paging stored procedures" will come out many results, is commonly used in the paging stored procedures, today I would like to say that it is flawed, and the vulnerability can not modify the stored procedures to remedy, if you think I was wrong, please read down maybe you will change your view. It's common for everyone to think that stored procedures can avoid SQLInjected vulnerability, which applies to general stored procedures and is not appropriate for common paging stored procedures, see the following code and analysis! The general common paging stored procedure Code is as follows: Common Paging stored procedures
CREATE PROCEDURE Pagination
@tblName varchar (255),--table name
@strGetFields varchar (1000) = ' * ',--columns to be returned
@fldName varchar (255) = ',--sorted field name
@PageSize int = 10,--page size
@PageIndex int = 1,--page number
@doCount bit = 0--Returns the total number of records, not 0 values.
@OrderType bit = 0,--set sort type, not 0 value descending
@strWhere varchar (1500) = '--Query criteria (note: Do not add where)
As

DECLARE @strSQL varchar (5000)--subject sentence
DECLARE @strTmp varchar (110)--Temporary variable
DECLARE @strOrder varchar (400)--Sort type
If @doCount!= 0
Begin
If @strWhere!= '
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] where ' + @strWhere
Else
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] '
End
--The above code means that if @docount passes over 0, the total count is executed. All of the following code is @docount 0

Else
Begin
If @OrderType!= 0
Begin
Set @strTmp = ' < (select Min '
Set @strOrder = ' ORDER by [' + @fldName + '] desc '
If @ordertype is not 0, it is important to perform descending order.
End

Else
Begin
Set @strTmp = ' > select Max '
Set @strOrder = ' ORDER by [' + @fldName + '] ASC '
End
If @PageIndex = 1
Begin
If @strWhere!= '
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] where ' + @strWhere + ' + @st Rorder
Else
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] ' + @strOrder
--If the first page executes the above code, this will speed up execution
End

Else
Begin
--The following code gives @strsql the SQL code to actually execute
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from ['
+ @tblName + '] where [' + @fldName + '] ' + @strTmp + ' ([' + @fldName + ']) from (select Top + str ((@PageIndex-1) * @PageSi Ze) + ' [' + @fldName + '] from [' + @tblName + '] ' + @strOrder + ') as Tbltmp ' + @strOrder
If @strWhere!= '
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from ['
+ @tblName + '] where [' + @fldName + '] ' + @strTmp + ' (['
+ @fldName + ']) from (select Top + str (@PageIndex-1) * @PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + '
+ @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder
End
End
EXEC (@strSQL)
Go

You can see the above stored procedure is through a number of steps eventually splicing into a SQLString, and then through the execExecute this string to get the results of pagination. We assume that a query like this will be done by using the username UserNameFuzzy query users, in order to describe the convenience, easy to understand we only consider taking the first page of the case, remove the stored procedure to take the first page of the spelling serial is as follows: Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] where ' + @strWhere + "+ @strOrderTo illustrate the problem, we can assume that @pageSizeFor -， @strGetFieldsFor ' * '， @tblNameFor UserAccount, @strOrderFor ' ORDER by ID DESC 'Then the above line can be written in the following form: Set @strSQL = ' SELECT top ~ ' [UserAccount] where ' + @strWhere + ' ORDER by ID DESC 'We can assume that the user entered the ambiguous user name is : Jim ' s dogWe use SqlParameterPassing parameters to a paging stored procedure @strWhereThe values are: ' UserName like '%jim ' dog% ' (Attention likeThe single quotes in the string below have all become two single quotes. ), we're substituting this value for the @strSQLIn an assignment statement ,As follows: Set @strSQL = ' SELECT top ~ ' [UserAccount] where UserName like '%jim ' ' dog% ' ORDER by ID DESC 'Let's write the partial execution of the declaration variable to test in Query Analyzer, the code is as follows: DECLARE @strSQL varchar (8000)
DECLARE @strWhere varchar (1000)
SET @strWhere = ' UserName like '%jim ' dog% '
Set @strSQL = ' SELECT top ~ ' [UserAccount] where ' + @strWhere + ' ORDER by ID DESC '
Print @strSQL
EXEC (@strSQL)

You can paste the above lines of code into the Query Analyzer to perform, you can see the following screen:


in the first line of the message, the SQL statement to be executed is printed , and it is clear that the part after the like '%jim ' of the statement is truncated, that is, if the user does not enter the Jim ' s dog instead, Jim ' delete from UserAccount will perform the deletion correctly, and the legendary SQL injection will appear. The problem arises, how should we solve the problem? 1. It is obvious that we have replaced single quotes with SqlParameter pass parameters, but it is not possible to solve the problem because we have created a replacement in the database. 2. According to my experiment, if using stored procedures is not possible to solve this problem, we can only avoid this problem if we put the operation of the stored procedure to the data access layer. If you have the way to solve this problem in the stored procedure, please do not hesitate to enlighten us.
Note: This article is about a database of MS SQL Server2000, rather than a new feature paging using SQL 2005.