sql 通用高效分页代码(存储过程是带有sql注入)

sql 通用高效分页代码(存储过程是带有sql注入)

大家可以看到上面的存储过程中是通过一些步骤最终拼接成一个sql字符串，然后通过exec执行这个串得到分页的结果。

我们假定要做一个这样的查询，通过用户名username模糊查询用户，为了叙述方便，便于理解我们只考虑取第一页的情况，取出存储过程中取第一页的拼串行如下：

为了便于说明问题，我们可以假定@pagesize为20，@strgetfields为 ‘*'，@tblname为useraccount,@strorder为' order by id desc' 那么上面一行可以写成如下形式：

set @strsql = 'select top 20 * from [useraccount] where ' + @strwhere + ' order by id desc'

我们可以假定用户输入的模糊用户名是: jim's dog
我们用sqlparameter传递参数给分页存储过程@strwhere 的值是：'username like ‘'%jim'' dog%'''(注意like后边的字符串中的单引号已经全部变成两个单引号了)，我们将这个值代入上面的@strsql赋值语句中,如下：

set @strsql = 'select top 20 * from [useraccount] where username like ''%jim'' dog%'' order by id desc'

让我们写上声明变量的部分执行在查询分析器中测试一下，代码如下：

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) 

create procedure pagination
@tblname varchar(255), -- 表名
@strgetfields varchar(1000) = '*', -- 需要返回的列
@fldname varchar(255)='', -- 排序的字段名
@pagesize int = 10, -- 页尺寸
@pageindex int = 1, -- 页码
@docount bit = 0, -- 返回记录总数, 非 0 值则返回
@ordertype bit = 0, -- 设置排序类型, 非 0 值则降序
@strwhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
as
declare @strsql varchar(5000) -- 主语句
declare @strtmp varchar(110) -- 临时变量
declare @strorder varchar(400) -- 排序类型
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
--以上代码的意思是如果@docount传递过来的不是0，就执行总数统计。以下的所有代码都是@docount为0的情况
else
begin
if @ordertype != 0
begin
set @strtmp = '<(select min'
set @strorder = ' order by [' + @fldname +'] desc'
--如果@ordertype不是0，就执行降序，这句很重要！
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
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strsql以真正执行的sql代码
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

set @strsql = 'select top ' + str(@pagesize) + ' ' + @strgetfields + ' from [' + @tblname + '] where ' + @strwhere + ' ' + @strorder