使用变量获取 SQL xml 属性值 [英] Get SQL xml attribute value using variable

问题描述

我有一个 SQL 函数，它接受一个名为 attribute 的变量，这是我想从中获取值的 xml 属性.xmlPath 是完整的 XML 字符串.

I have a SQL function that takes a variable called attribute, which is the xml attribute I want to get the value from. xmlPath is the full XML string.

我的 xml 看起来像这样:

My xml looks like this:

<EventSpecificData>
  <Keyword>
    <Word>myWord</Word>
    <Occurences>1</Occurences>
    <Context>context</Context>
  </Keyword>
</EventSpecificData>

我想获取的值，所以我传入/Keyword/Word并设置一个变量为:

I want to get the value for <Word>, so I pass in /Keyword/Word and set a variable to:

set @value = @xmlPath.value('(/EventSpecificData/@attribute)[1]', 'varchar(max)')

但是，我不认为@attribute 实际上是在插入变量字符串.还有其他方法可以做到这一点吗?

However, I don't think @attribute is actually inserting the variables string. Is there another way to do this?

推荐答案

这里有几个解决方案供您使用.

Here are a couple of solutions for you.

示例数据:

declare @xml xml
set @xml = 
'<EventSpecificData>
  <Keyword>
    <Word>myWord</Word>
    <Occurences>1</Occurences>
    <Context>context</Context>
  </Keyword>
</EventSpecificData>'

无论父节点如何，从名为 Word 的节点获取第一个值.使用//进行深度搜索，使用local-name()匹配节点名.

Get the first value from node named Word regardless of parents. Use // to do a deep search and use local-name() to match node name.

declare @Attribute varchar(max)

set @Attribute = 'Word'
select @xml.value('(//*[local-name() = sql:variable("@Attribute")])[1]',  'varchar(max)')

使用 local-name() 在两个级别中在单独的变量中提供父节点名称和属性.

Provide parent node name and attribute in separate variables using local-name() in two levels.

declare @Node varchar(max)
declare @Attribute varchar(max)

set @Attribute = 'Word'
set @Node = 'Keyword'
select @xml.value('(/EventSpecificData
                    /*[local-name() = sql:variable("@Node")]
                    /*[local-name() = sql:variable("@Attribute")])[1]',  'varchar(max)')

由于 nodes 的参数必须是字符串文字，所以它邀请使用动态 sql 来解决这个问题.它可能看起来像这样，使其与您的原始变量内容一起使用.

Since the parameter to nodes have to be a string literal it invites to use dynamic sql to solve this. It could look something like this to make it work with your original variable content.

set @Attribute = 'Keyword/Word'
declare @SQL nvarchar(max)
set @SQL = 'select @xml.value(''(/EventSpecificData/'+@Attribute+')[1]'', ''varchar(max)'')'
exec sp_executesql @SQL, N'@xml xml', @xml

但是你应该知道，如果你使用它，你很容易受到 SQL 注入攻击.一些狡猾的最终用户可能会想出一个如下所示的属性字符串:

But you should be aware of that if you use this you are wide open to SQL Injection attacks. Some devious end-user might come up with a attribute string that looks like this:

set @Attribute = 'Keyword/Word)[1]'', ''varchar(max)'') select @@version --'

使用它执行动态 SQL 将为您提供两个结果集.select @@version 只是为了显示一些无害的代码，但它可能是更糟糕的东西.

Executing the dynamic SQL with that will give you two result sets. The select @@version is just there to show some harmless code but it might be much worse stuff in there.

您可以使用 quotename() 来防止 SQL 注入攻击.至少可以阻止我的尝试.

You can use quotename() to prevent the SQL injection attack. It will at least prevent the attempt made by me.

set @Attribute = 'Keyword/Word'
set @SQL = 'select @xml.value('+quotename('(/EventSpecificData/'+@Attribute+')[1]', '''')+', ''varchar(max)'')'
exec sp_executesql @SQL, N'@xml xml', @xml

使用 quotename() 的最后一个版本安全吗?看看 Erland Sommarskog 的这篇文章动态 SQL 的诅咒和祝福.

Is the last version using quotename()safe? Have a look at this article by Erland Sommarskog The Curse and Blessings of Dynamic SQL.

引用:

那么使用quotename()和quotestring()，我们是否有同样好的保护像我们使用参数化命令一样反对 SQL 注入?也许.一世不知道有什么方法可以通过quotename() 或引号字符串().然而，您将用户输入插入到SQL 字符串，而使用参数化命令，则不需要.

So with quotename() and quotestring(), do we have as good protection against SQL injection as we have with parameterised commands? Maybe. I don't know of any way to inject SQL that slips through quotename() or quotestring(). Nevertheless, you are interpolating user input into the SQL string, whereas with parameterised commands, you don't.

这篇关于使用变量获取 SQL xml 属性值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！