Such an efficient and common paging stored procedure is the ZT with the SQL injection vulnerability.

Searching for "Paging Stored Procedures" in Google will produce many results, which are common paging stored procedures. Today I want to say that they have vulnerabilities, in addition, the vulnerability cannot be remedied by modifying the stored procedure. If you think I am wrong, read it and you may change your mind.
Generally, you will think that stored procedures can avoid SQL injection vulnerabilities, which is suitable for general stored procedures, but not for general paging stored procedures. Please refer to the following Code And analysis!
The general code of the paging stored procedure is as follows: Copy code The Code is as follows: Create procedure pagination
@ Tblname varchar (255), -- table name
@ Strgetfields varchar (1000) = '*', -- the column to be returned
@ Fldname varchar (255) = '', -- Name of the sorted Field
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Docount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ Ordertype bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ Strwhere varchar (1500) = ''-- Query condition (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 indicates that if @ docount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ docount
Else
Begin
If @ ordertype! = 0
Begin
Set @ strtmp = '<(select min'
Set @ strorder = 'order by ['+ @ fldname +'] desc'
-- If @ ordertype is not 0, execute the descending order. This sentence is very important!
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 +'' + @ strorder
Else
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ strgetfields +' from ['+ @ tblname +'] '+ @ strorder
-- Execute the above Code on the first page, which will speed up the execution.
End
Else
Begin
-- The following code gives @ strsql the SQL code to be actually executed
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ strgetfields + 'from ['
+ @ Tblname + '] Where [' + @ fldname + ']' + @ strtmp + '([' + @ fldname + ']) from (select top '+ STR (@ PageIndex-1) * @ pagesize) + '[' + @ 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 that in the above stored procedure, an SQL string is finally spliced through some steps, and then the results are displayed by executing the string through exec.

Let's assume that we want to perform such a query and use username to fuzzy query users. For the sake of convenience and convenience, we only consider the first page, the serial number of the first page retrieved from the stored procedure is as follows:
Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ strgetfields + 'from ['+ @ tblname +'] where' + @ strwhere +'' + @ strorder

For ease of Problem description, we can assume that @ pagesize is 20, @ strgetfields is '*', @ tblname is useraccount, @ strorder: 'order by id desc '. The preceding line can be written as follows:
Set @ strsql = 'select top 20 * from [useraccount] where' + @ strwhere + 'order by ID desc'

We can assume that the fuzzy user name entered by the user is Jim's dog.
We use sqlparameter to pass parameters to the paging Stored Procedure @ strwhere. The value is: 'username like ''' % Jim ''dog % ''' (note that the single quotation marks in the string behind like have all been changed to two single quotation marks ), we will place this value into the above @ strsql value assignment statement, as follows:

Set @ strsql = 'select top 20 * from [useraccount] Where username like ''% Jim'' dog % ''order by id desc'

Let's write the Declaration variable and test it in the query analyzer. The Code is as follows:

Copy codeThe Code is as follows: declare @ strsql varchar (8000)
Declare @ strwhere varchar (1000)
Set @ strwhere = 'username like ''' % Jim ''dog % '''
Set @ strsql = 'select top 20 * from [useraccount] where' + @ strwhere + 'order by ID desc'
Print @ strsql
Exec (@ strsql)

You can paste the above lines of code into the query analyzer and execute it. The following figure is displayed:

In the first line of the message, the SQL statement to be executed is printed. Obviously, all parts of the like '% Jim' statement are truncated, that is to say, if the user does not input Jim's dog but Jim 'delete from useraccount, the deletion operation will be performed correctly, and the legendary SQL injection will appear.

What should we do if the problem arises?

1. It is clear that we have replaced single quotes with single quotes by passing the sqlparameter parameter. However, replacing them with strings in the database cannot solve the problem.

2. according to my experiment, it is proved that this problem cannot be solved by using a stored procedure. We can avoid this problem only by placing the concatenation operation to the data access layer.

If you have any solutions to this problem in the stored procedure, please do not hesitate to inform us.

Note: This article describes the ms SQL Server2000 Database, rather than the new SQL 2005 features.