Re: Using sp_executesql to dynamically query xml

Tech-Archive recommends: Fix windows errors by optimizing your registry

Michael,

Thanks, I was afraid that would be the answer :(

-Andy

"Michael Rys [MSFT]" wrote:

The way to do it is to write some T-SQL or CLR code to check against
injection on the provided string before doing the concatenation.

And please go and vote on
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=168552

Thanks and best regards
Michael

"Andy Webb" <AndyWebb@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BC5F5224-CC69-4F58-BF2D-9F70D9F1FF0A@xxxxxxxxxxxxxxxx
Dennis,

Thank you for your reply; I must have made an error in the XQuery in my
hasty attempt to put together a simplified example. I was actually able
to
get the query to work fine if I did not use dynamic SQL, however my case
requires the use of dynamic SQL because I do not know how the xml will be
queried. The question that I wanted answered was: How do I do this using
dynamic SQL in such a way that I avoid the possibility of SQL injection?

Thanks,
Andy

"Denis Ruckebusch [MSFT]" wrote:

It looks like your problems stem from the use of dynamic SQL but once
your
XQuery code doesn't look correct either.

To start, you should probably build a single string for your query, and
not use
parameters. Try something like this

DECLARE @Query nvarchar(255)

DECLARE @SqlCommand nvarchar(1000)


SET @Query = N'//*:Description/@title = "Project:A"'

SET @SqlCommand = 'SELECT * FROM ACTIVITY WHERE

ACTIVITY_DETAIL.value(''' + @Query + ''',''bit'') = cast(1 as bit)'


EXEC sp_executesql @SqlCommand


This will actually fail because of the static typing of XPath expression
//*:Description/@title = "Project:A

It's hard to fix it without knowing exactly what you're trying to
accomplish but
if you want to retrieve the value of the *:Description element that
contains an
attribute title with a value equal to "Project:A" then the expression
should be

(//*:Description[@title="Project:A"])[1]


I hope this helps. Please come back if you run into any more problems.


Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm


"Andy Webb" <Andy Webb@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1E37045D-ACF0-408B-8FFF-8BE22429C510@xxxxxxxxxxxxxxxx
Hi,

I am writing a stored procedure which will allow callers to specify an
XQuery that will be passed to the value function on an Xml column. I
want to
substitute this value into the query using sp_executesql to avoid the
possibility of SQL Injection. However the following statement results
in an
error. How do I go about doing this without resorting to concatenating
input
parameters into my query.

DECLARE @Query nvarchar(255)
DECLARE @SqlCommand nvarchar(1000)

SET @Query = N'//*:Description/@title = "Project:A"'
SET @SqlCommand = 'SELECT * FROM ACTIVITY WHERE
ACTIVITY_DETAIL.value(@QUERY,''bit'') = cast(1 as bit)'

EXEC sp_executesql @SqlCommand, N'@QUERY nvarchar(255)', @QUERY =
@QUERY

This results in the the follwing error:

Msg 8172, Level 16, State 1, Line 1
The argument 1 of the xml data type method "value" must be a string
literal.

Going one step further and quoting @QUERY does not help:

DECLARE @Query nvarchar(255)
DECLARE @SqlCommand nvarchar(1000)

SET @Query = N'//*:Description/@title = "Project:A"'
SET @SqlCommand = 'SELECT * FROM ACTIVITY WHERE
ACTIVITY_DETAIL.value(''@QUERY'',''bit'') = cast(1 as bit)'

EXEC sp_executesql @SqlCommand, N'@QUERY nvarchar(255)', @QUERY =
@QUERY

This results in the error

Msg 2390, Level 16, State 1, Line 1
XQuery [ACTIVITY.ACTIVITY_DETAIL.value()]: Top-level attribute nodes
are not
supported

How can I work around these errors and provide a dynamic query without
using
concatenation?








.

Relevant Pages

  • Re: Using sp_executesql to dynamically query xml
    ... requires the use of dynamic SQL because I do not know how the xml will be ... XQuery code doesn't look correct either. ... To start, you should probably build a single string for your query, and not use ... DECLARE @SqlCommand nvarchar ...
    (microsoft.public.sqlserver.xml)
  • Re: Using sp_executesql to dynamically query xml
    ... injection on the provided string before doing the concatenation. ... get the query to work fine if I did not use dynamic SQL, ... requires the use of dynamic SQL because I do not know how the xml will be ... DECLARE @SqlCommand nvarchar ...
    (microsoft.public.sqlserver.xml)
  • Re: Need assistance with query
    ... Using that query, you can't do that. ... Declare @OKI int -- OKIData Printer ... Declare @ESI int -- ESI Printer boxes ...
    (microsoft.public.sms.admin)
  • Re: Using variables in Dynamic SQL
    ... > execute dynamic SQL I am getting the error message "Syntax error ... > DECLARE @StartDate datetime ... > So my 2nd query is can anybody advise me of the correct format here. ...
    (microsoft.public.sqlserver.programming)
  • Re: XMLColumn.query
    ... If you want to constuct the query at you can use dynamic SQL, ... DECLARE @cmd VARCHAR ... how can I use variable(e.x @Qry) when I use .query method? ...
    (microsoft.public.sqlserver.xml)