I’m using SQL Server 2005, and this concerns making the xpath part of the
XQUERY exist() function dynamic.

I'm using the stored procedure below to make a Selection.  In the Where
clause i use an XQUERY exist() function on my, xml_data column, which is of
type XML.

I'd like the xpath part of the XQUERY exist() function,
/ProcessData/ZipCode, to be dynamic instead of hard-coding it.  For example
using the variabl @ParamMyXPATH below.

I'll try out your answers with this procedure.

CREATE PROCEDURE [dbo].[GetProcessDataList]
@ParamMyXPATH [nvarchar](500),
@ParamStatusList [nvarchar] (500),
@TheValueToCheck[nvarchar] (30)
AS
BEGIN
   SET NOCOUNT ON ;

SELECT      ede_processes. proc_id , ede_processes.proc_state,
FROM         
ede_processes
INNER JOIN
      dbo.SplitStringIntoListOfIntegers(@ParamCreatorGroupList ) AS grp
ON    ede_creator_group.creatorgroup_type = grp.IntegerID
WHERE
       (ede_processes.xml_data.exist ('
        declare default element namespace "
http://www.bs-card-service.com/ede"; 
        /ProcessData/ZipCode [(text()[1] cast as xs:string ?) =
xs:string(sql:variable("@ TheValueToCheck ")) ] ') = 1)

END

Regards,
Paul  schwartzenberg[§@]gmail.com

Hello Schwartzenberg,

SQL Server only does simple substitution at runtime, not this type of dynamic
query.  If you really want to do this, you'll have to use dynamic SQL as
well.

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Schwartzenberg wrote:

I think the arguments to the XQuery methods like exist need to be string
literals, there is no way to dynamically construct such strings at runtime.
The only way I can think of is e.g.
   /*[local-name() = sql:variable("@name1")/*[local-name() =
sql:variable("@name2")]
where the string literal passed to e.g. exist can access SQL variables.

Thanks Ken and Martin.

I used the dynamic SQL idea that Ken mentioned.

A low scale template of my solution to make dynamic both the xpath and value
parts in XQUERY is in the stored procedure below.

Because exec(@query ) (see below) presents a level of indirection, the SQL
Server error messages (like when @query is too short) are not helpful.  Also,
the nesting (and which) appostrophes to nest is not obvious.

Here's what i came up with.  If somebody has an alternative or optimisations
- please add them.

CREATE PROCEDURE [dbo].[GetDynamicList]
@ParamStatusList [nvarchar] (500),
@ParamStatusList [nvarchar](500),
@ParamVersion [nvarchar](10),
@ParamStatusList [nvarchar](500),
@ ParamAXPATH [nvarchar](200),
@ParamAValue [nvarchar](150)
as
begin
    DECLARE @query nvarchar(2200)
      SET @query='
WITH XMLNAMESPACES( ''http://www.myNamspace.com/ede'' AS  "edeNS")
SELECT     proc_id,.proc_state,
                 proc_data.query(''' + @ParamAXPATH + ''') as Result,
INNER JOIN
--@ParamStatusList is a delimited string of integers & input to the T-SQL
function
-- dbo.SplitStringIntoListOfIntegers, which parses it into a table of
integers.
                      dbo.SplitStringIntoListOfIntegers(''' +
@ParamStatusList + ''') AS sta
ONproc_state = sta.IntegerID

WHERE  (proc_version IN (''' + @ParamVersion + '''))
  AND  (proc_data.exist(''' +
  @ParamAXPATH + '[(text()[1] cast as xs:string ?) = "' + @ParamAValue  + '"
]'') = 1)'

exec(@query )

end
Show quoteHide quote

Personally, I'd be fine concatenating the query (assuming this is from
a trusted development source), but I wouldn't concatenate the user
input (@ParamAValue) due to injection risk; I'd try using
sp_executesql, passing @ParamAValue in - kinda like below.

Marc

SET NOCOUNT ON
CREATE TABLE XML_TEST(id int IDENTITY(1,1) NOT NULL, data xml NOT
NULL)
INSERT XML_TEST (data) VALUES ('<xml><test id="a"/></xml>')
INSERT XML_TEST (data) VALUES ('<xml><test id="a"/></xml>')
INSERT XML_TEST (data) VALUES ('<xml><test id="b"/></xml>')

DECLARE @Query nvarchar(max), @XQuery varchar(250), @Value varchar(10)
SELECT @Value = 'a', @XQuery = '/xml/test[@id=sql:variable("@tmp")]'

SET @Query = '
SELECT *
FROM XML_TEST
WHERE data.exist(''' + @XQuery + ''') = 1'
EXEC sp_executesql @Query, N'@tmp varchar(10)', @Value
DROP TABLE XML_TEST

Hello Marc and others,

Yes, (needless to say) protection against injection risk is important. 
The question is how does binding the user input value in the XQuery
sql:variable() Function offer more protection agains injection in an XQuery
expression?   Would anyone give an example?

MSDN writes that the sql:variable() XQuery Function causes the SQL value it
binds to l be mapped "to a corresponding XQuery value and its type will be an
XQuery base type that is equivalent to the corresponding SQL type."
(http://msdn2.microsoft.com/en-us/library/ms188254.aspx)

In our example below the user input is used in an xpath comparison
expression in the XML type Exist() method.  It would be interesting to see
examples of injection (both SQL injection or XQuery injection) in this XQuery
expression in the sample below.  Both when binding user input in a comparison
expresson to the sql:variable() function, and (especially) when not doing so.

I modified Marcs SQL script below for both methods (with/without
sql:variable()).

An exampleof injection (in the Xquery expression) would demonstrate (an
additional) use of sql:variable() in this context.    Any suggestions? 

Hope the length and delay of this response will not discourage further
community input.

Last note for further commentary and response:
It could also be that it is the combination of using sql:variable() with the
sp_executesql stored procedure.  Would anybody know how to find the location
(and see the code of sp_executesql)??

Here's Marcs modified SQL script example:

SET NOCOUNT ON

CREATE TABLE XML_TEST(id int IDENTITY(1,1) NOT NULL, data xml NOT NULL)
INSERT XML_TEST (data) VALUES ('<xml><test id="a">firstrow</test></xml>')
INSERT XML_TEST (data) VALUES ('<xml><test id="a">secondrow</test></xml>')
INSERT XML_TEST (data) VALUES ('<xml><test id="b">thirdrow</test></xml>')

--Delete from XML_TEST
--where id = 1

DECLARE
    @Query nvarchar(max),
    @XQuery varchar(250),
    @UserInputValue varchar(10)


SELECT @UserInputValue = 'firstrow',
--       @XQuery = '(/xml/test)[(text()[1] cast as xs:string ?) =
sql:variable("@tmp")]'
         @XQuery = '(/xml/test)[(text()[1] cast as xs:string ?) = "' +
@UserInputValue  + '" ]'

SET @Query = '
SELECT *
FROM XML_TEST
WHERE data.exist(''' + @XQuery + ''') = 1'

--EXEC sp_executesql @Query, N'@tmp nvarchar(10)', @UserInputValue
exec(@Query)

DROP TABLE XML_TEST

Show quoteHide quote

First; parameterisation isn't just about injection; it is also
about performance. Using a fixed query where the parameter values
change allows the database to cache (and re-use) the query-plan
for consecutive calls. If you include the user's literal values,
it will have to recompile for every different input value.
Also - to build a literal into a query you have to get the value
as a string. That is OK for varchar, int, etc - but what about
datetime, varbinary, float, guid, etc... it quickly gets hard to do
this
reliably. If you use typed parameters (as here), then it can
simply pass the existing typed values around - no need to format
it and parse it, with all the errors that this might introduce.

The XQuery expression can refer to either a literal or an
sql:variable.
As soon as you mention literal, you are talking about concatenating
user
input, which is always going to be vulnerable to injection.

This is a server-internal proc, so you can't really see the source
AFAIK.
It is just part of SQL Server.

Note that this is just SQL injection; I'm not sure how I would define
xquery injection (if at all).

I'll need a little-bit more space in @UserInputValue,
but; how about the user entering their name (or whatever) as:
"]')=1;DELETE FROM CUSTOMERS;--
(don't forget that the ' is equivalent to '' if you copy/paste into
query analyzer; other variants involving GO, RETURN, etc will have
similar effects)

(See also: http://www.xkcd.com/327/)

This evaluates the query:
SELECT *
FROM XML_TEST
WHERE data.exist('(/xml/test)[(text()[1] cast as xs:string ?) =
""]')=1;DELETE FROM CUSTOMERS --" ]') = 1

and bam, no more customers ;-(
You *could* start worrying about escaping character combinations
like single-or-double quote, but
a: this is prone to bugs
b: everything I said in my first paragraph ;-p


Now test it using the sql:variable() approach - no injection; it
treats @tmp/@UserInputValue as variables throughout, and simply
finds no matches to my freaky value.

Basically, never, ever, *ever* conncatenate user-input. It is,
however, OK to dynamically append pre-selected developer-checked
strings *based* on user input, i.e. to select @XQuery based on a
mapping from some other user-input. But I wouldn't trust user-entered
@XQuery.

Finally; yes, constructing the above example query is unlikely -
however, depending on the error-handling in your app (i.e. how
much of the original error-message it shows the user) a clever
[ab]user will be able to figure it out. Besides, it amounts to
"security through obscurity".

First; parameterisation isn't just about injection; it is also
about performance. Using a fixed query where the parameter values
change allows the database to cache (and re-use) the query-plan
for consecutive calls. If you include the user's literal values,
it will have to recompile for every different input value.
Also - to build a literal into a query you have to get the value
as a string. That is OK for varchar, int, etc - but what about
DateTime, varbinary, float, etc... it quickly gets hard to do this
reliably. If you use typed parameters (as here), then it can
simply pass the existing typed values around - no need to format
it and parse it, with all the errors that this might introduce.

The XQuery expression can refer to either a literal or an
sql:variable.
As soon as you mention literal, you are talking about concatenating
user
input, which is always going to be vulnerable to injection.

This is a server-internal proc, so you can't really see the source
AFAIK.
It is just part of SQL Server.

Note that this is just SQL injection; I'm not sure how I would define
xquery injection (if at all).

I'll need a little-bit more space in @UserInputValue,
but; how about the user entering their name (or whatever) as:
"]')=1;DELETE FROM CUSTOMERS;--
(don't forget that the ' is equivalent to '' if you copy/paste into
query analyzer; other variants involving GO, RETURN, etc will have
similar effects)

(See also: http://www.xkcd.com/327/)

This evaluates the query:
SELECT *
FROM XML_TEST
WHERE data.exist('(/xml/test)[(text()[1] cast as xs:string ?) =
""]')=1;DELETE FROM CUSTOMERS --" ]') = 1

and bam, no more customers ;-(
You *could* start worrying about escaping character combinations
like single-or-double quote, but
a: this is prone to bugs
b: everything I said in my first paragraph ;-p


Now test it using the sql:variable() approach - no injection; it
treats @tmp/@UserInputValue as variables throughout, and simply
finds no matches to my freaky value.

Basically, never, ever, *ever* conncatenate user-input. It is,
however, OK to dynamically append pre-selected developer-checked
strings *based* on user input, i.e. to select @XQuery based on a
mapping from some other user-input. But I wouldn't trust user-entered
@XQuery.

Finally; yes, constructing the above example query is unlikely -
however, depending on the error-handling in your app (i.e. how
much of the original error-message it shows the user) a clever
[ab]user will be able to figure it out. Besides, it amounts to
"security through obscurity".

XML limits in SQL/Server?
Extracting xml values using xquery
Need help with XML Bulk Load
Newbie - generate XML tree from parent-child hierarchy
Getting hashbytes from XML