sql - 在子句参数化一个sql语句

  显示原文与译文双语对照的内容

如何参数化包含变量数量的IN 子句的查询,如下所示?


select * from Tags 
where Name in ('ruby','rails','scruffy','rubyonrails')
order by Count desc

在这里查询中,参数的数目可以是从 1到 5的任意位置。

我不希望为此( 或者 XML ) 使用专用存储过程,但是如果有一些特定于 SQL Server 2008的优雅方法,我将打开。

时间:

下面是我使用的quick-and-dirty技术:


select * from Tags
where '|ruby|rails|scruffy|rubyonrails|'
like '%|' + Name + '|%'

下面是 C# 代码:


string[] tags = new string[] {"ruby","rails","scruffy","rubyonrails" };
const string cmdText ="select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
 cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

两个警告:

  • 性能很差。like"%...%" 查询未被索引。
  • 确保没有任何 |,空白或者空标记,否则将不起作用

还有其他方法可以实现,有些人可能认为干净,所以请继续阅读。

可以参数化的,因此类似于:每个值


string[] tags = new string[] {"ruby","rails","scruffy","rubyonrails" };
string cmdText ="SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
 (s, i) =>"@tag" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
 for(int i = 0; i <paramNames.Length; i++) {
 cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
 }
}

这将给你:


cmd.CommandText ="SELECT * FROM Tags WHERE Name IN (@tag0,@tag1,@tag2,@tag3)"
cmd.Parameters["@tag0"] ="ruby"
cmd.Parameters["@tag1"] ="rails"
cmd.Parameters["@tag2"] ="scruffy"
cmd.Parameters["@tag3"] ="rubyonrails"

不,这不是对 SQL注入的打开。 只将插入的文本插入CommandText不是基于用户输入。 它只基于硬编码的"@tag"前缀和数组的索引。 索引将总是 是一个整数,用户生成的并不是安全的。

用户输入的值仍然被填充到参数中,所以没有漏洞。

编辑:

注入关注点,注意构造命令文本以适应可变的参数( 如上所述) SQL阻碍服务器利用缓存查询的能力。 最终结果是,你几乎肯定会在第一次使用参数时丢失参数( 与只将谓词字符串插入到SQL本身相反) 。

在it,没那么复杂,足以看出上述benefit.已经缓存的查询计划并不是有价值的,但我的这个查询比较麻烦( 虽然编译成本可能接近( 甚至超过)的执行成本,但你仍然在谈论毫秒。

如果你有足够的内存,我想 SQL Server 可能会缓存一个参数的公共计数的计划。 我想你总是可以添加五个参数,并让未指定的标记为空- 查询计划应该是相同的,但这对我来说很难看,我也不确定它是否值得 micro-optimization ( 虽然在堆栈溢出上,它可能是值得的) 。

此外,SQL Server 7和以后将 auto-parameterize查询,所以使用的参数不是真的有必要从性能角度来看 — —然而,它是从安全的角度 — —尤其是与用户输入数据的关键喜欢这个。

对于 SQL Server 2008,可以使用表值参数 。 它有点工作,但它比的其他方法

首先,你必须创建一个类型


CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

然后,你的ADO.NET 代码如下所示:


string[] tags = new string[] {"ruby","rails","scruffy","rubyonrails" };
cmd.CommandText ="SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

//value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName ="dbo.TagNamesTableType";

//Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
 if (values == null ||!values.Any()) return null;//Annoying, but SqlClient wants null instead of 0 rows
 var firstRecord = values.First();
 var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
 return values.Select(v => 
 {
 var r = new SqlDataRecord(metadata);
 r.SetValues(v);
 return r;
 });
}

最初的问题是 "如何参数化查询。。"

让我在这里声明,这是对原始问题的 已经有一些关于其他好答案的演示。

这样的话,继续并标记这个答案,downvote,把它标记为不是一个答案- - 。 做你认为正确的事情。

请从标记Brackett中查看我( 还有 231个) upvoted的首选答案。 在他的回答中给出的方法允许 1的有效使用绑定变量,以及 2的谓词。

选定答案

这里我想介绍的是Spolsky回答的方法,答案"选定"作为正确答案。

Spolsky的Joel方法很聪明。 它工作正常,它将显示可以预测的行为和可以预测的性能,给定的"普通"值,以及标准的边缘情况,比如NULL和空字符串。 对于一个特定的应用程序来说可能已经足够了。

但应用这个方法,让我们也考虑到更多阴暗的角落来讲情况( 例如当 Name 列包含一个通配符( 如谓词所识别的那样。) 通配符我看到最常用的是 % ( 百分号。) 。 现在我们来处理这个问题,以后再继续。

以 % 字符, 一些问题

考虑 'pe%ter'的名称值。 ( 对于这里的例子,我使用了一个字面字符串值代替列名。) 使用该表单的查询返回名为`'pe%ter'name的行:


select.. .
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

但是,如果搜索条件的顺序颠倒,则将不会返回相同的


select.. .
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

我们观察到的行为有点奇怪。 更改列表中搜索项的顺序更改结果集。

毫无疑问,我们可能不想让 pe%ter 匹配花生黄油,不管他多么喜欢它。

阴暗的角落案例

( 是的,我同意这是一个模糊的例子) 。 可能是一个不太可能被测试的。 在列值中不应有通配符。 我们可以假设应用程序防止存储这样一个值。 但在我的经验中,我很少见到一个数据库约束,它特别不允许在 LIKE 比较运算符右边考虑通配符或者模式。

Patching

修补这个漏洞的一种方法是转义 % 通配符。 ( 对于不熟悉操作符的转义子句的人,这里有一个指向 SQL Server 文档的链接) 。


select.. .
 where '|peanut|butter|'
 like '%|' + 'pe%ter' + '|%' escape ''

现在我们可以匹配字面 %. 当然,当我们有一个列名时,我们需要动态地转义通配符。 我们可以使用 REPLACE 函数查找 % 字符的出现,并在每个字符前面插入一个反斜杠,如下所示:


select.. .
 where '|pe%ter|'
 like '%|' + REPLACE( 'pe%ter', '%','%') + '|%' escape ''

这样就解决了 % 通配符的问题。 几乎几乎。

转义转义

我们认识到我们的解决方案引入了另一个问题。 转义符。我们看到我们还需要转义任何转义符本身。 这次,我们使用 ! 作为转义符:


select.. .
 where '|pe%t!r|'
 like '%|' + REPLACE(REPLACE( 'pe%t!r', '!','!!'),'%','!%') + '|%' escape '!'

下划线太

现在我们已经在滚动了,我们可以添加另一个 REPLACE 处理下划线通配符。 为了好玩,这次我们将使用 $ 作为转义符。


select.. .
 where '|p_%t!r|'
 like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r', '$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

我喜欢这种方法来转义,因为它在Oracle和MySQL以及 SQL Server 中都有效。 ( 我通常使用 反斜杠作为转义符,因为这是我们在 正规表达式 中使用的字符) 。 但是为什么受到约定约束 !

那些讨厌的括号

SQL Server 还允许将通配符作为文本处理,方法是将它们括在括号 [] 中。 所以我们还没有完成修复,至少对于 SQL Server 。 因为对括号有特殊含义,所以我们需要转义它们。 如果我们设法正确地转义括号,那么至少我们不用在括号内使用连字符 - 和 carat ^ 。 我们可以在括号内留下任何 %_ 字符,因为我们基本上已经禁用括号的特殊含义。

查找匹配的括号不应该是硬的。 它比处理单个 % 和_的出现要困难一点。 ( 请注意,只转义所有括号是不够的,因为一个单独的括号被认为是字面的,并且不需要转义) 。 如果不运行更多的测试用例,逻辑就会变得比我能够处理的更模糊。)

内联表达式会混乱

SQL中的内联表达式越来越长。 我们可能会使它工作,但天堂帮助了后面的可怜的灵魂,并且必须对它的进行解读。 尽可能多的烟雾我是,我是倾向不使用框架是,这主要是因为我不想为内联表达式必须为它的混乱,和apologizing发表评论解释这样做的原因。

一个函数在哪

好,如果我们不把它作为一个内联表达式处理在SQL中,我们拥有的最接近的替代函数就是用户定义函数。 而且我们知道,如果我们必须创建一个函数,这不会加速任何( 除非我们可以在它上面定义索引,就像我们可以使用 oracle 。),我们最好在调用SQL语句的代码中进行。

函数在行为上可能有一些差异,取决于DBMS和版本。 ( 对所有的Java开发人员大声喊出,希望能够互换使用任何数据库引擎。)

领域知识

我们可以对列的域有专门的知识( 即对列实施的允许值集合) 。 我们可能会知道列中存储的值不会包含百分号,下划线或者括号对。 在这种情况下,我们只包含一个快速评论,这些案例被覆盖。

列中存储的值可能允许 % 或者_ 字符,但约束可能需要转义这些值,可能使用定义的字符,这样值就像比较"安全"。 同样,对允许的值集合进行快速注释,特别是使用哪个字符作为转义符,并使用Joel的Spolsky方法。

但没有为我们这些专门的知识和一个保证,这一点很重要,但这样至少会考虑处理这些阴暗的角落情况下,并考虑该行为是否符合逻辑和"按照规范"。


其他问题摘要

我相信其他人已经充分指出了一些其他常见的考虑领域:

  • SQL注入 ( 获取用户提供的信息,包括在SQL文本中,而不是通过绑定变量提供它们) 。 不需要使用绑定变量,它只是一种方便的方法来阻止 SQL注入 。 还有其他方法可以处理它:

  • 使用索引扫描而不是索引扫描的优化器计划,可能需要表达式或者函数来转义通配符( 表达式或者函数可能的索引)

  • 使用文本值替代绑定变量会影响可伸缩性


结论

我喜欢Spolsky的Joel方法。 它很聪明而且它也能。

但当我看到它的时候,我立即看到了一个潜在的问题,它不是我的天性让它滑动。 我并不想对别人的工作非常关键。 我知道很多开发人员都非常个人地接受他们的工作,因为他们投入了大量的精力,并且他们非常关心它。 所以请理解,这不是个人攻击。 我在这里识别的是生产中的问题,而不是测试。

是的,我远离原来的问题。 但是,还有什么地方留给我关于"选定"回答问题的重要问题?

我希望有人会发现这篇文章有一些用处。


致歉

再次,很抱歉我未能遵守规则和约定的堆栈溢出,在这里发帖什么显然不回答 op的问题。

你可以将参数作为字符串传递

所以你有这个字符串


DECLARE @tags

SET @tags = 'ruby|rails|scruffy|rubyonrails'

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, '|'))
order by Count desc

然后你要做的就是把字符串作为 1参数传递。

下面是我使用的split函数。


CREATE FUNCTION [dbo].[fnSplit](
 @sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
 @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
 @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem)> 0
 INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList)> 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

我听到杰夫/joel今天上的讨论这个播客( 剧集 34,,2008-12-16 ( MP3,31 MB ) ),1 h 03最小 38秒工时- 1 h 06最小 45秒),而我觉得自己是在利用堆栈溢出撤回 linqtosql,但可能它片浩大声势。 下面是LINQ到SQL中的相同内容。


var inValues = new [] {"ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
 where inValues.Contains(tag.Name)
 select tag;

就这样,并反向写是的,很棒的看起来已经足够,但在 Contains 子句似乎额外向后给我。 当我在工作中对一个项目做类似的查询时,我自然会尝试通过在本地数组和 SQL Server 表之间做一个连接来错误地完成这一点。 使用包含,它没有,但它确实提供了一条错误消息,它是描述性和举了一个 towards.

无论如何,如果在强烈推荐的分隔符 。运行这里查询,你可以查看 SQL LINQ提供程序生成的实际 SQL 。 它将向你展示将参数化为 IN 子句的每个值。

如果你是从. NET 调用的,你可以使用短小精悍的dot网络:


string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

这里的思想是简洁的,所以你不必。 使用 LINQ到 SQL,可以使用类似的方法,当然:


string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
 where names.Contains(tag.Name)
 orderby tag.Count descending
 select tag;

我们有创建可以加入的表变量的函数:


ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list AS VARCHAR(8000),
 @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
 Position INT,
 Value VARCHAR(8000))
AS
 BEGIN
 DECLARE @myPos INT

 SET @myPos = 1

 WHILE Charindex(@delim, @list)> 0
 BEGIN
 INSERT INTO @listTable
 (Position,Value)
 VALUES (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))

 SET @myPos = @myPos + 1

 IF Charindex(@delim, @list) = Len(@list)
 INSERT INTO @listTable
 (Position,Value)
 VALUES (@myPos,'')

 SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
 END

 IF Len(@list)> 0
 INSERT INTO @listTable
 (Position,Value)
 VALUES (@myPos,@list)

 RETURN
 END 

所以:


@Name varchar(8000) = null//parameter for search values 

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

这可能是一种半讨厌的方式,我曾经用过它,非常有效。

根据你的目标,它可能是使用的。

  1. 用一列创建临时表。
  2. 将每个查找值插入该列。
  3. 你可以只使用标准的连接规则,而不用使用。 ( Flexibilty++ )

这一直一点添加的弹性,你可以做,但它的更适合于在以下情况你有一个大的表,查询,具有良好的索引,并且希望使用程序参数化列表中多次。 节省执行两次并手动完成所有卫生设备的操作。

在我的情况那是needed,我从未合上,分析如何正确 那是快,but.

这是粗略的,但如果保证至少有一个,你可以:


SELECT.. .
. . .
 WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )

拥有 IN('tag1','tag2','tag1','tag1','tag1') 将被 SQL Server 轻易地优化。 另外,你得到直接索引查找

...