April 30, 2014 at 10:53 am
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?
April 30, 2014 at 11:32 am
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.
April 30, 2014 at 11:43 am
Right, like I said, I am doing that and it is not working reliably.
April 30, 2014 at 11:48 am
Quick thought, how about extracting the node to an encrypted table, delete it from the XML and when needed, join the two together?
😎
April 30, 2014 at 12:20 pm
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.
May 1, 2014 at 5:32 am
I think this is what i may have to do. but how do I join them back together?
May 1, 2014 at 6:21 am
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
May 1, 2014 at 9:33 pm
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)
😎
May 1, 2014 at 10:53 pm
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;
May 2, 2014 at 4:55 am
Eirikur Eiriksson (5/1/2014)
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
May 2, 2014 at 5:22 am
Lowell (5/2/2014)
... looked for lower hanging forum posts 😎
Felt I could not leave this one hanging......:cool:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply