• 00
  • :
  • 00
  • :
  • 00

SQL多条件查询安全高效比较

ALTER  PROCEDURE _tmp
	@ID VARCHAR(50),
	@PN VARCHAR(50),
	@Type INT
AS
BEGIN
	/**********************************
		-- 功能:多条件查询性能
		_tmp 'K3G8KG6NN94SBBS0','K7F7FF',0
	**********************************/
	PRINT '测试数据条数500W'
	
	set nocount ON
	
	DECLARE @time DATETIME
	DECLARE @Warring VARCHAR(5000)
	IF(@Type=1 OR @Type=0)
	BEGIN
		SET @Warring=CHAR(10)+'第一种方式,直接拼SQL语句,有SQL注入漏洞';PRINT @Warring;SELECT @Warring
		SET @time=GETDATE()
		
		DECLARE @SQL VARCHAR(4000)
		SELECT @SQL='SELECT * FROM dbo.tb_timetest WHERE 1=1'
		IF(ISNULL(@ID,'')<>'')	SET @SQL=@SQL+' AND id='''+@ID+''''
		IF(ISNULL(@PN,'')<>'')	SET @SQL=@SQL+' AND PN='''+@PN+''''	
		
		EXEC(@SQL)
		
		PRINT '所需时间_毫秒'
		PRINT DATEDIFF(MILLISECOND,@time,GETDATE())
	END
	IF(@Type=2 OR @Type=0)
	BEGIN
		SET @Warring=CHAR(10)+'第二种方式,没有像第一种方式那样的SQL漏洞,但是性能大大折扣,就是耗时';PRINT @Warring;SELECT @Warring
		SET @time=GETDATE()
		
		SELECT * FROM  dbo.tb_timetest WHERE (ISNULL(@ID,'')=''OR id = @ID) AND (PN = @PN OR @PN IS NULL)
		
		PRINT '所需时间_毫秒'
		PRINT DATEDIFF(MILLISECOND,@time,GETDATE())
	END
	IF(@Type=3 OR @Type=0)
	BEGIN
		SET @Warring=CHAR(10)+'第三种方式,虽然写法没有第二种简洁,但是也没有像第一种方式那样的SQL注入漏洞,是本人目前能想到最优的';PRINT @Warring;SELECT @Warring
		SET @time=GETDATE()
		
		DECLARE @S NVARCHAR(4000),@P NVARCHAR(4000)
		SET @P=N'@ID VARCHAR(50),@PN VARCHAR(50)'
		SET @S='SELECT * FROM dbo.tb_timetest WHERE 1=1'
		IF(ISNULL(@ID,'')<>'')	SET @S=@S+' AND id = @ID'
		IF(ISNULL(@ID,'')<>'')	SET @S=@S+' AND PN = @PN'
		
		EXEC sp_executesql @S,@P,@ID=@ID,@PN=@PN
		
		PRINT '所需时间_毫秒'
		PRINT DATEDIFF(MILLISECOND,@time,GETDATE())
	END
END

posted @ 2016-01-29 17:40  Garson_Zhang  阅读(860)  评论(0编辑  收藏  举报