Whole-Value Substitution

In a previous post we discussed the fundamentals of encryption and made reference to why it is important to use an additional authenticator: to prevent a security breach through a technique called “whole-value substitution”. In whole-value subsitution, the attacker does not actually decrypt any sensitive information, but they exchange one encrypted value for another based on inference of the underlying data.

Let’s walk through an example to understand the concept better.

Demonstration Preparation: For demonstration purposes, let’s create a table to store personnel information such as employee name and salary. We will store the salary in encrypted form only. We will assume there already exists a valid symmetric key named “keyPersonnel” that is encrypted by a certificate named “certPersonnel” (if you are not familiar with creating a symmetric key or certificate, refer to my post on encryption fundamentals). For this example we will assume our database is named “DB” and will be working within the “dbo” schema.

-- Set context to the DB database
USE DB;
GO

-- Open the symmetric key
OPEN SYMMETRIC KEY keyPersonnel
    DECRYPTION BY CERTIFICATE certPersonnel
;

-- If the temp table we will create already exists, drop it
IF OBJECT_ID('tempdb..#TBL') IS NOT NULL
    DROP TABLE #TBL
;

-- Create a temp table
CREATE TABLE #TBL (
    ID INT IDENTITY(1,1) NOT NULL
    , Name VARCHAR(20) NOT NULL
    , Salary VARBINARY(128) NULL)
;

Demonstration: To demonstrate whole-value substitution, we will walk through two scenarios. In the first we encrypt the salary information without using an authenticator and then show how a whole-value substitution attack works. In the second scenario we will use an authenticator with encryption and then demonstrate how whole-value substitution fails.

-- =============================================================
-- Scenario 1 : no authenticator, whole-value substitution works
-- =============================================================

-- Populate the temp table with sample data
--   Note that our DBA makes less money than the CEO

INSERT INTO #TBL (Name, Salary)
VALUES
    ('DBA', ENCRYPTBYKEY(
        KEY_GUID('keyPersonnel')
        , CONVERT(VARBINARY, $50000)))
    , ('CEO', ENCRYPTBYKEY(
        KEY_GUID('keyPersonnel')
        , CONVERT(VARBINARY, $250000)))
;

-- Select and decrypt the data to confirm the values
SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(Salary))
FROM #TBL
;

As you recall, whole-value substitution involves replacing one encrypted value for another. In our example, an intruder viewing the data may not have been able to decrypt the salary and see the actual value of the DBA’s and the CEO’s salary. However, the intruder would be able to see the names. And, from the names alone, the intruder could assume that the CEO’s salary was higher. Thus the intruder could attempt to replace the DBA’s encrypted salary with the CEO’s encrypted salary, without ever knowing the value of either. Let’s see if it works….

-- Perform the whole-value substitution and replace the
--   DBA's salary with the CEO's salary

UPDATE #TBL
SET Salary = (
    SELECT Salary
    FROM #TBL
    WHERE Name = 'CEO')
WHERE Name = 'DBA'
;

-- Select and decrypt the data to validate the results
--   after the whole-value substitution

SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(Salary))
FROM #TBL
;

The whole-value substitution attack was successful: the DBA’s salary is now the same as the CEO’s, and the intruder did it without ever having decrypted the sensitive data.

Now let’s take a look at how simply adding an authenticator to the encryption function can prevent this type of attack.

-- ===============================================================
-- Scenario 2 : authenticator used, whole-value substitution fails
-- ===============================================================

-- Reset the temp table
TRUNCATE TABLE #TBL;

-- Repopulate the temp table
--   First insert the employee names

INSERT INTO #TBL (Name)
VALUES
    ('DBA')
    , ('CEO')
;

Now we add and encrypt the employee’s salary information, and we use the employee’s unique identifier to authenticate the encryption. In other words, a unique piece of information associated with the employee (their ID) is included in the encryption key used to secure their salary information. The result is that the encrypted value should only be decryptable when it is associated with the employee for whom it was encrypted; i.e. whole-value substitution fails.

-- Add the employee salary information, encrypting it
--   with the employee's unique identifier as an authenticator

UPDATE #TBL
SET Salary =
    CASE Name
        WHEN 'DBA' THEN ENCRYPTBYKEY(
            KEY_GUID('keyPersonnel')
            , CONVERT(VARBINARY, $50000)
            , 1 -- TRUE, we will use an authenticator
            , CONVERT(VARBINARY, ID)) -- the ID column is the authenticator
        WHEN 'CEO' THEN ENCRYPTBYKEY(
            KEY_GUID('keyPersonnel')
            , CONVERT(VARBINARY, $250000)
            , 1 -- TRUE, we will use an authenticator
            , CONVERT(VARBINARY, ID)) -- the ID column is the authenticator
        END
;

First, let’s confirm that the data was encrypted and that we can decrypt it.

-- Select the employees and decrypt the salary
SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(
        Salary
        , 1
        , CONVERT(VARBINARY, ID)))
FROM #TBL
;

Now let’s confirm that we actually do need to use the authenticator in order to decrypt the data. Let’s try decrypting it without the authenticator and see what happens.

-- If encrypted with authenticator, do you really need
--   the authenticator in order to decrypt? (yes!)

SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(Salary))
FROM #TBL
;

While the previous two queries basically confirm that authenticated encryption will prevent a whole-value substitution attack, let’s try it anyway and see if it works.

-- Perform the whole-value substitution and replace the
--   DBA's salary with the CEO's salary

UPDATE #TBL
SET Salary = (
    SELECT Salary
    FROM #TBL
    WHERE Name = 'CEO')
WHERE Name = 'DBA'
;

-- Query the data to see if the salary information was updated
SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(
        Salary
        , 1
        , CONVERT(VARBINARY, ID)))
FROM #TBL
;

As you see from the previous query, the DBA’s decrypted salary is now NULL. The whole-value substitution attack did not prevent the DBA salary from being updated, but it did produce a result that is far more easily identified than an actual substituted salary would have been. We have minimized the damage potential.

The next two statements are not strictly necessary: simply ending your session is sufficient. However, why not get in the habit of actively cleaning up after yourself? It shows positive control of your environment and could pay dividends in a situation when it really matters.

-- Drop the temp table
IF OBJECT_ID('tempdb..#TBL') IS NOT NULL
    DROP TABLE #TBL
;

-- Close the symmetric key
CLOSE SYMMETRIC KEY keyPersonnel;

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: