SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Symmetrically encrypt a single node in XML field


Symmetrically encrypt a single node in XML field

Author
Message
msdevtech
msdevtech
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 9
How do I reliably symmetrically encrypt a single node in an XML field in my SQL Server 2012 database? I know I cannot use SQL Server encryption to encrypt an XML field and I have been getting unpredictable results with a home grown solution and now I am stumped. Any ideas?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148432 Visits: 19444
You'd have to be able to extract the value of the node reliably, with something like XQUERY, take that and encrypt it. However, the result would be binary, and I'm not sure to what extent the binary result would cause problems in the XML schema and need escapes.

Of course, the encrypted binary string would need to then update the node inside the XML document.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
msdevtech
msdevtech
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 9
Right, like I said, I am doing that and it is not working reliably.
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41212 Visits: 19491
Quick thought, how about extracting the node to an encrypted table, delete it from the XML and when needed, join the two together?
Cool
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148432 Visits: 19444
msdevtech (4/30/2014)
Right, like I said, I am doing that and it is not working reliably.


you haven't said what you're doing, and what's unpredictable. Don't forget, we can't see what you're doing or what the results are.

Eirikur's idea of joining to another table could work, but you might have perf issues and you'd need reliable key matching from the document to the table row.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
msdevtech
msdevtech
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 9
I think this is what i may have to do. but how do I join them back together?
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73245 Visits: 40960
i found this interesting, and am about 90% there with an example solution.
my problem is if there is a text representation of a binary string in the xml, how do i convert it to varbinary, so i can feed it to the decrypt function?


i took a string and used the encryption to get the binary value, and modified the xml accordingly and put it in the <Secret> xml tag.

i've tried sys.fn_cdc_hexstrtobin to convert the string to varbinary, but it truncates a large portion of the value.
maybe someone else can take the ball and run with it form here?


IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'AES128SecureSymmetricKey')
BEGIN
CREATE SYMMETRIC KEY AES128SecureSymmetricKey
WITH ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
END
-- must open the key if it is not already
OPEN SYMMETRIC KEY AES128SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
--sample data
IF Object_id('[dbo].[XMLExample]') IS NOT NULL
DROP TABLE [dbo].[XMLExample]

GO

CREATE TABLE [dbo].[XMLExample]
(
[XID] INT IDENTITY(1, 1) NOT NULL,
[XData] XML NULL,
CONSTRAINT [PK__XMLExamp__DB7757004E739D3B] PRIMARY KEY CLUSTERED ([XID] ASC)
)

DECLARE @XML XML;

SELECT @XML = '<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<Secret>0x00DC079B85ED3B4299B9276648A092B201000000ABA3095D0DD8D6DCC34DF92A7EB24E067024A4653F1203B9BA00A946CD97D02493AD7DE0312F2675A978AFA8ABE31A029198F5BBE93AFA7D2C1AA9635D458BA6</Secret>
<City>NY</City>
</Customers>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<Secret>0x00DC079B85ED3B4299B9276648A092B2010000001ABF8DC5B30323745A5D9F6314478607C0795A0E6009F26C65058F9A0FDE768BA5E9360266E5FF6F7B58F91758E59CEA97D23BC8425289F09194A1D504FDA34E</Secret>
<City>LA</City>
</Customers>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
<Secret>0x00DC079B85ED3B4299B9276648A092B2010000001A0A49247F58956CF3E5A197551AE38FA0541526B317E351682774738795577FC2A91648B4A9FE7F7938EF0EC56E4EE4AE389F5AF5E40B3859960A3E69AEC3E78060139C2228598A73F66DB1EC4D5B17</Secret>
<City>MIA</City>
</Customers>
</ROOT>';

INSERT INTO XMLExample
(XData)
SELECT @XML


SELECT --R.Node.query('.'),
R.Node.query('.').value('(/Customers/CustomerId/.)[1]', 'varchar(100)') AS CustomerID,
R.Node.query('.').value('(/Customers/CompanyName/.)[1]', 'varchar(100)') AS CompanyName,
R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varchar(8000)') AS CompanySecret,
R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varbinary(8000)') AS CompanySecretBin, --does not work:
sys.fn_cdc_hexstrtobin( R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varchar(8000)')) As BinVal,
DECRYPTBYKEY(sys.fn_cdc_hexstrtobin( R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varbinary(8000)'))) As [Decrypted?],
--EncryptByKey(Key_GUID('AES128SecureSymmetricKey'), R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varchar(100)'))As EncryptedName,
R.Node.query('.').value('(/Customers/City/.)[1]', 'varchar(100)') AS CityName
--into #t
FROM XMLExample
CROSS APPLY xData.nodes('/ROOT/Customers') R(Node);

-- close and drop the key
CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;
--DROP SYMMETRIC KEY AES128SecureSymmetricKey;



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41212 Visits: 19491
Lowell (5/1/2014)
i've tried sys.fn_cdc_hexstrtobin to convert the string to varbinary, but it truncates a large portion of the value. maybe someone else can take the ball and run with it form here?


Use the convert function for string to varbinary:
DECLARE @STRXHEX VARCHAR(50) = '0x1234567890abcdef';
SELECT CONVERT(VARBINARY(50),@STRXHEX,1)


Cool
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41212 Visits: 19491
Lowell (5/1/2014)

i've tried sys.fn_cdc_hexstrtobin to convert the string to varbinary, but it truncates a large portion of the value.
maybe someone else can take the ball and run with it form here?


Lowell, you where almost there! Changed the code to modify and store the encrypted secret, then decrypt in the select. This code has everything needed for either storing the encrypted secret within the xml or for ripping it out and storing it elsewhere.:
Cool

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'AES128SecureSymmetricKey')
BEGIN
CREATE SYMMETRIC KEY AES128SecureSymmetricKey
WITH ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
END
-- must open the key if it is not already
OPEN SYMMETRIC KEY AES128SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
--sample data
IF Object_id('[dbo].[XMLExample]') IS NOT NULL
DROP TABLE [dbo].[XMLExample]

GO

CREATE TABLE [dbo].[XMLExample]
(
[XID] INT IDENTITY(1, 1) NOT NULL,
[XData] XML NULL,
CONSTRAINT [PK__XMLExamp__DB7757004E739D3B] PRIMARY KEY CLUSTERED ([XID] ASC)
)

DECLARE @XML XML;
INSERT INTO dbo.XMLExample(XData)
VALUES
('<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<Secret>##PASSWORD001</Secret>
<City>NY</City>
</Customers>
</ROOT>'),
('<ROOT>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<Secret>##PASSWORD002</Secret>
<City>LA</City>
</Customers>
</ROOT>'),
('<ROOT>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
<Secret>##PASSWORD003</Secret>
<City>MIA</City>
</Customers>
</ROOT>');


;WITH XML_SRC AS
(
SELECT
XE.XID
,CUS.TID.value('.[1]','INT') AS CustomerId
,SEC.RET.value('.[1]','NVARCHAR(MAX)') AS Secret
,CONVERT(NVARCHAR(MAX),EncryptByKey(Key_GUID('AES128SecureSymmetricKey'),SEC.RET.value('.[1]','NVARCHAR(MAX)')),2) As EncryptedSecret
FROM XMLExample XE
OUTER APPLY XE.XData.nodes('ROOT/Customers') AS CUST(OMER)
OUTER APPLY CUST.OMER.nodes('CustomerId') AS CUS(TID)
OUTER APPLY CUST.OMER.nodes('Secret') AS SEC(RET)
)

UPDATE XT
SET XData.modify('
replace value of (/ROOT/Customers/Secret[1]/text())[1]
with sql:column("XS.EncryptedSecret") ')
FROM XML_SRC XS
INNER JOIN dbo.XMLExample XT
ON XS.XID = XT.XID;


SELECT
XE.XID
,CUS.TID.value('.[1]','INT') AS CustomerId
,SEC.RET.value('.[1]','NVARCHAR(MAX)') AS Secret
FROM XMLExample XE
OUTER APPLY XE.XData.nodes('ROOT/Customers') AS CUST(OMER)
OUTER APPLY CUST.OMER.nodes('CustomerId') AS CUS(TID)
OUTER APPLY CUST.OMER.nodes('Secret') AS SEC(RET);


SELECT
XE.XID
,CUS.TID.value('.[1]','INT') AS CustomerId
,SEC.RET.value('.[1]','NVARCHAR(MAX)') AS Secret
,CONVERT(NVARCHAR(MAX),DECRYPTBYKEY(CONVERT(VARBINARY(MAX),SEC.RET.value('.[1]','NVARCHAR(MAX)'),2))) AS Secret2
FROM XMLExample XE
OUTER APPLY XE.XData.nodes('ROOT/Customers') AS CUST(OMER)
OUTER APPLY CUST.OMER.nodes('CustomerId') AS CUS(TID)
OUTER APPLY CUST.OMER.nodes('Secret') AS SEC(RET);

-- close and drop the key
CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;
--DROP SYMMETRIC KEY AES128SecureSymmetricKey;


Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73245 Visits: 40960
Eirikur Eiriksson (5/1/2014)
[quote][b]
Lowell, you where almost there! Changed the code to modify and store the encrypted secret, then decrypt in the select. This code has everything needed for either storing the encrypted secret within the xml or for ripping it out and storing it elsewhere.:
Cool


wow awesome job cleaning up and expanding my modest example, Eirikur! I've saved your modifications in my snippets.

I got so stuck on extracting out the varbinary to feed the decrypt function.
When that wouldn't work for me, i gave up and looked for lower hanging forum posts Cool

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search