Symmetrically encrypt a single node in XML field

  • 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?

  • 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.

  • Right, like I said, I am doing that and it is not working reliably.

  • Quick thought, how about extracting the node to an encrypted table, delete it from the XML and when needed, join the two together?

    😎

  • 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.

  • I think this is what i may have to do. but how do I join them back together?

  • 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!

  • 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)

    😎

  • 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;

  • 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


    --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!

  • 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