CODEWITHSUNDEEP
sql-serverxmlstored-proceduressql-server-2012
abdulkhadar7
abdulkhadar7

Reputation: 125

XML node encryption in Stored procedure

I need to insert couple of rows into a table where the values are inserted using XML.The problem I'm facing is the table contains a field 'password' which is varbinary in table definition and the value passed from front-end should be encrypted using a function in the database before insertion

@encryptUsername=dbo.Encrypt(CONVERT(VARCHAR(MAX),@userName))

I dont know how to do this when values are inserted using XML.

Following is the sample sp where values are inserted from XML

 INSERT INTO CLIENT(username,password)
   SELECT 
        Col.valueCol.value('(userName/text())[1]','VARCHAR'),
        Col.value('(password/text())[1]','VARCHAR')
 FROM @XmlData.nodes('/usernames/username')   Tab(Col)

here i need to get the xml node value 'password' and encrypt before it is add to table. Is there any way to achieve this?

Upvotes: 1

Views: 195

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

It is dangerous to use varchar or nvarchar without a length (Bad habits to kick: Varchar without a length (Aaron Bertrand)).

As the .value() method returns a typed value, this is a varchar(max), which you can pass over to your function directly. Try this: 

INSERT INTO CLIENT(username,password)
   SELECT 
        dbo.Encrypt(Col.value('(userName/text())[1]','VARCHAR(MAX)')),
        dbo.Encrypt(Col.value('(password/text())[1]','VARCHAR(MAX)'))
 FROM @XmlData.nodes('/usernames/username')   Tab(Col)

Attention I took the above from your code, you use Col.valueCol.value() as well as Col.value(). I took the one I assume to be correct.

Attention 2 You should change the INSERT statement to a SELECT-statement in order to check the result before you change your table's data...

Upvotes: 1

Related Questions