存储过程追加?这样对吗 [英] Stored procedue appending ? is it right

问题描述

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_Category122] @id int,@mode char(1)
as
declare @query varchar(5000)
set @query = 'Select * from quest_categories where cat_id = ' + convert(varchar(max),@id)
if @mode ='a'
	begin	
		declare @query1 varchar(5000)
		set @query1=' and cat_name = ''sql'''
		set @query = @query + @query1
	end
Exec(@query)
print(@query)

http://xneuron.wordpress.com/2007/12/05/reusable-data-layer-in-c/

http://xneuron.wordpress.com/2007/12/05/reusable-data-layer-in-c/

推荐答案

为了避免sql注入，我会这样写:

In order to avoid sql injection I would write something like this:

ALTER proc [dbo].[sp_Category122] @id int,@mode char(1)
as
declare @query nvarchar(4000)
set @query = 'Select * from quest_categories where cat_id = @id'
if @node='a'
   set @query = @query + ' and cat_name=''sql'''
---
exec sp_executesql @query, N'@id int', @id

尝试过摆脱动态sql

tried to get rid of dynamic sql

SELECT
    *
FROM
    quest_categories
WHERE
    cat_id = @id
    AND ((@mode = 'a' AND cat_name = 'sql') OR @mode <> 'a'))

这篇关于存储过程追加?这样对吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！