Parameterizing the XPath for a modify()

Hi all,

I'm trying to write a generic stored procedure which will parameterize the XPath in my XML file so that I can update a value by giving just its path and the new value I wish to store:

Here is the code:
CREATE PROCEDURE ModifyLoanXML
@.LoanNumber char(60),
@.XPathQuery varchar(300),
@.Value varchar(300)
AS
UPDATE Loans SET LoanXML.modify('replace value of (sql:variable("@.XPathQuery"))[1] with xs:string(sql:variable("@.Value"))')
WHERE Loans.LoanNumber = @.LoanNumber
GO

Unfortunately I am getting the following error:
Msg 2337, Level 16, State 1, Procedure ModifyLoanXML, Line 6
XQuery [Loans.LoanXML.modify()]: The target of 'replace' must be at most one node, found 'xs:string ?'

I've tried a number of things to try to get this to go through but I'm having no luck. Is it possible to completely parameterize the XPath you wish to change when calling XQuery.modify() ?

Is there any way to specify that my path will always point to an attribute (not a node) so that this proc can be created? Thanks!

-Karthik Hariharan

The path specified in modify() must be a string literal, so the approach you are taking will not work out. The sql:variable("@.XPathQuery") is not interpreted as a path, but as a string value.

You can use dynamic sql to achieve what you are looking for. SInce you will be generating dynamic SQL, you will need to take steps to ensure that you dont end up with SQL injection from accepting untrusted XPaths.

|||Thanks Todd. I was trying to avoid the dynamic SQL method precisely to avoid any SQL injection vulnerabilities. Is there no way to achieve this dynamix XQuery using a stored procedure? If anyone else has a suggestion please let me know. Thanks.
|||I resolved the issue by using an Exec() T-SQL command. Here is my code below:

CREATE PROCEDURE [dbo].[ModifyLoanXML]
@.LoanID UNIQUEIDENTIFIER,
@.XPathQuery varchar(max),
@.Value varchar(max)
AS
DECLARE @.query varchar(max)
DECLARE @.LoanIDstr varchar(max)
SET @.LoanIDStr = CONVERT (varchar(max),@.LoanID)

SET @.query='UPDATE Loans SET LoanXML.modify(''declare namespace MISMO="http://mrgdev.local/mismo/";replace value of ' + @.XPathQuery + ' with "' +@.Value +'"'') WHERE Loans.InternalID = ''' + @.LoanIDstr + ''''
exec(@.query)

To avoid a possible SQL injection, I parameterized the LoanID and convert it to a varchar within the stored procedure. Just wanted to share this with you all.

Regards,
Karthik Hariharan
|||

Hi Karthik,

Your code still appears to be subject to SQL injection attacks with respect to the XPathQuery variable and the Value variable, if either is untrusted. Since you are concatenating them with the SQL string, if an untrusted user was able to specify the XPathQuery or Value variable, then they may be able to embed quotes and comment characters to change the behavior of your query. You can change your query to parameterize both Value (using sql:variable) and LoadIDstr (using a parameter) but accepting untrusted XPathQuery will be difficult without fully validating that it is safe.