"Jeff Wang" <JeffW***@discussions.microsoft.com> wrote in message
news:451F19DB-2D81-4BAD-967D-854822D57D5C@microsoft.com...
> Hi all, i am experimenting to see what vulernabilities there may be with
> user
> provided inputs of Xquery. One minimum safety measure that should be
> enforced
> is that any single quote input from the user should be escaped with
> another
> quote. But even with that, could we get into nasty situations with
> accidently
> escaping escapes?Is it true that the scope of my Xquery will only be
> inside
> the column as statically defined in my dynamic statement? Perhaps we can
> have
> everyone using this approach have only "select" priveleges during
> execution?
>
> Well, i hope we can all learn something here with respect to security and
> XQuery and dynamic SQL queries in general.
>
>
> Here is how i am setting up my table and XML content, with my dynamic
> XQuery.
>
>
> -- Setup the environment
> CREATE TABLE [dbo].[Workbooks2]
> (
> [XmlContent] [xml] NULL
> )
>
> DECLARE @XmlContentInstance XML;
> SET @XmlContentInstance = '<?xml version="1.0"?>
> <!-- A fragment of a book store inventory database -->
> <bookstore xmlns:bk="urn:samples">
>  <bk:book genre="novel" publicationdate="1997" bk:ISBN="1-861001-57-8">
>    <title>Pride And Prejudice</title>
>    <author>
>      <first-name>Jane</first-name>
>      <last-name>Austen</last-name>
>    </author>
>    <price>24.95</price>
>  </bk:book>
>  <bk:book genre="novel" publicationdate="1992" bk:ISBN="1-861002-30-1">
>    <title>The Handmaids Tale</title>
>    <author>
>      <first-name>Margaret</first-name>
>      <last-name>Atwood</last-name>
>    </author>
>    <price>29.95</price>
>  </bk:book>
>  <bk:book genre="novel" publicationdate="1991" bk:ISBN="1-861001-57-6">
>    <title>Emma</title>
>    <author>
>      <first-name>Jane</first-name>
>      <last-name>Austen</last-name>
>    </author>
>    <price>19.95</price>
>  </bk:book>
>  <bk:book genre="novel" publicationdate="1982" bk:ISBN="1-861001-45-3">
>    <title>Sense and Sensibility</title>
>    <author>
>      <first-name>Jane</first-name>
>      <last-name>Austen</last-name>
>    </author>
>    <price>19.95</price>
>  </bk:book>
> </bookstore>';
> insert into [dbo].[Workbooks2] (XmlContent) values (@XmlContentInstance)
>
> declare @namespace nvarchar(250);
> declare @xquery nvarchar(250);
> declare @query nvarchar(500);
>
> -- both @namespace and @xquery is supplied by
> -- user, what do we need to do on these inputs
> -- to ensure that there is no risk of security
> -- vulernability. Is this a flawed approach?
>
> -- CAN this be hijacked in the context?
> -- What can the user supply here that will ruin security?
> set @namespace = 'declare namespace p1 = "urn:samples";';
> -- CAN this be hijacked in the context?
> -- What can the user supply here that will ruin security?
> set @xquery = '//p1:book[title="Sense and Sensibility"]';
>
>
> -- this is the static dynamic query construct.
> set @query ='
> SELECT
> [XmlContent]
> FROM [Workbooks2]
> WHERE
> [XmlContent].value(''' + @namespace + ' count (' + @xquery + ')'',
> ''int'')
> != 0'
>
> exec(@query)
>
> select @query
>
>
>
> -- Cheers!

> -- both @namespace and @xquery is supplied by
> -- user, what do we need to do on these inputs
> -- to ensure that there is no risk of security
> -- vulernability.