sql server 参数化查询问题

[zhangfengyi]

有两个查询语句，要使用参数化查询，这样使用是否正确。第一个正在使用，第二个之前是拼字符串，现在也想改成参数化，这样写是否正确。这样写能否达到防止sql 注入

public bool addButton(Esa_Office.Model.Button model)
        {
            myData.AddParameter("@name", model.name);
            myData.AddParameter("@code", model.code);
            myData.AddParameter("@description", model.description);
            myData.AddParameter("@type", model.type);
            myData.AddParameter("@remarkes", model.remarkes);
            string sqlstr = "insert into button(name,code,description,type,remarkes) values(@name,@code,@description,@type,@remarkes)";
            if (myData.ExecuteNonQuery(sqlstr) > 0)
                return true;
            else return false;
        }

public DataSet getButton(string state,string name,string code,string desc,string type)
        {
            string sqlstr;
            string wherestr="";
            myData.AddParameter("@state", state);
            myData.AddParameter("@name", name);
            myData.AddParameter("@code", code);
            myData.AddParameter("@desc", desc);
            myData.AddParameter("@type", type);
            if (state != "")
            {
                wherestr += " and remarkes=@state";
            }
            if (name != "")
            {
                wherestr += " and name like %@name%";
            }

            if (code != "")
            {
                wherestr += " and code=@code";
            }

            if (desc != "")
            {
                wherestr += " and description like %@desc%";
            }

            if (type != "")
            {
                wherestr += " and type like %@type%";
            }

            sqlstr = "select * from button where 1=1 "+wherestr;
            DataSet ds = new DataSet();
            ds = myData.ExecuteDataSet(sqlstr);
            return ds;
        }

[zhangfengyi]

public DataSet getButton(string state,string name,string code,string desc,string type)
        {
            string sqlstr;
            string wherestr="";
            myData.AddParameter("@state", state);
            myData.AddParameter("@name", name);
            myData.AddParameter("@code", code);
            myData.AddParameter("@desc", desc);
            myData.AddParameter("@type", type);
            if (state != "")
            {
                wherestr += " and remarkes=@state";
            }
            if (name != "")
            {
                wherestr += " and name like %@name%";
            }

            if (code != "")
            {
                wherestr += " and code=@code";
            }

            if (desc != "")
            {
                wherestr += " and description like %@desc%";
            }

            if (type != "")
            {
                wherestr += " and type like %@type%";
            }

            sqlstr = "select * from button where 1=1 "+wherestr;
            DataSet ds = new DataSet();
            ds = myData.ExecuteDataSet(sqlstr);
            return ds;
        }

[吉普赛的歌]

应该是有些问题的， 比如： like %@type% => like '%'+@type+'%' 其实你直接运行看下结果， 是不是更快呢？

USE tempdb
GO
IF OBJECT_ID('Proc_Test') IS NOT NULL
DROP PROC Proc_Test
GO
--获取系统表中 能被 传入参数 整除的 前 3 位数
--如果传入0，则不计整除
CREATE PROC Proc_Test
@num INT
AS
BEGIN
	DECLARE @sql NVARCHAR(MAX)
	SET @sql='select top 3 sv.number from master.dbo.spt_values as sv where sv.type=''P'' '
	IF @num>0
	BEGIN
		SET @sql+=' and sv.number%@num=0 '
	END
	EXEC sp_executesql @sql,N'@num INT',@num
END
GO
EXEC Proc_Test @num=0
/*
number
0
1
2
 */
EXEC Proc_Test @num=2
/*
number
0
2
4
 */

[吉普赛的歌]

建议你用存储过程， 参考上面的写法。 因为这是绝对没有注入， 而且不用考虑参数多或者少的问题。 C#中参数多了或者少了似乎是会报错的，但我这种做法，即使条件不满足，没有用到参数， 传入参数了也是没有问题的。 再者， 出了问题修改下存储过程即可， 不必重新编译程序。

[文盲老顾]

引用 4 楼 zhangfengyi 的回复:

我这样使用参数化，查询不到

declare @roleid varchar(30)
declare @pid varchar(30)

set @roleid=15
set @pid='e1db4149-809c-4a67-8647-b573253006fe'

select distinct(id),name,url,pid,icon,Display,Ord,ordlevl,State 
from sys_menu a, RolePermissions b 
where a.id=b.treeid 
and b.roleid in(@roleid) 
and a.pid in(@pid) 
order by ord desc

必须这样才能查询到，如何解

select distinct(id),name,url,pid,icon,Display,Ord,ordlevl,State 
from sys_menu a, RolePermissions b 
where a.id=b.treeid 
and b.roleid in('15') 
and a.pid in('e1db4149-809c-4a67-8647-b573253006fe') 
order by ord desc

in (@val1,@val2) 这个可以，但是你不能 set @val = '1,2' 然后 in @val 达到 in (1,2)的效果，变量只能作为一个参数 如果是想实现 in 多个值的话，最好自己再数据库里实现出来类似split或正则这样的方式，将值进行切分，然后 in (select val from split(@val,',')) 这样

[zhangfengyi]

我这样使用参数化，查询不到

declare @roleid varchar(30)
declare @pid varchar(30)

set @roleid=15
set @pid='e1db4149-809c-4a67-8647-b573253006fe'

select distinct(id),name,url,pid,icon,Display,Ord,ordlevl,State 
from sys_menu a, RolePermissions b 
where a.id=b.treeid 
and b.roleid in(@roleid) 
and a.pid in(@pid) 
order by ord desc

必须这样才能查询到，如何解

select distinct(id),name,url,pid,icon,Display,Ord,ordlevl,State 
from sys_menu a, RolePermissions b 
where a.id=b.treeid 
and b.roleid in('15') 
and a.pid in('e1db4149-809c-4a67-8647-b573253006fe') 
order by ord desc

[二月十六]

declare @roleid varchar(30)
declare @pid varchar(30)

set @roleid=15
set @pid='e1db4149-809c-4a67-8647-b573253006fe'

EXEC('select distinct(id),name,url,pid,icon,Display,Ord,ordlevl,State 
from sys_menu a, RolePermissions b 
where a.id=b.treeid 
and b.roleid in('''+@roleid+''') 
and a.pid in('''+@pid+''') 
order by ord desc')