Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Symmetrically encrypt a single node in XML field Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 10:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 5:31 AM
Points: 4, 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?
Post #1566506
Posted Wednesday, April 30, 2014 11:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:42 PM
Points: 31,036, Visits: 15,466
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
Post #1566523
Posted Wednesday, April 30, 2014 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 5:31 AM
Points: 4, Visits: 9
Right, like I said, I am doing that and it is not working reliably.
Post #1566527
Posted Wednesday, April 30, 2014 11:48 AM This worked for the OP Answer marked as solution
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:49 AM
Points: 1,914, Visits: 5,275
Quick thought, how about extracting the node to an encrypted table, delete it from the XML and when needed, join the two together?
Post #1566531
Posted Wednesday, April 30, 2014 12:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:42 PM
Points: 31,036, Visits: 15,466
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
Post #1566542
Posted Thursday, May 1, 2014 5:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 5:31 AM
Points: 4, Visits: 9
I think this is what i may have to do. but how do I join them back together?
Post #1566676
Posted Thursday, May 1, 2014 6:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1566687
Posted Thursday, May 1, 2014 9:33 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:49 AM
Points: 1,914, Visits: 5,275
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)


Post #1566910
Posted Thursday, May 1, 2014 10:53 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:49 AM
Points: 1,914, Visits: 5,275
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.:


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;

Post #1566917
Posted Friday, May 2, 2014 4:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
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.:



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


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1566985
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse