Understanding Whole Value Substitution Attacks

Salary Manipulation Example

In order to understand a whole value substitution attack, imagine that WideWorldImporters had unknowingly employed a dishonest worker. Grant Culberston is an HR administrator and therefore has the SELECT and UPDATE permissions granted against the Application.Salary table. Consider the data in the new Application.Salary table. The plain text salary column has been left in for reference, but in a production scenario, this column would of course be dropped, so that only the encrypted salary value remains.

If Grant was to return data from this table using the query in Listing 14-2, then he would see the results in Figure 14-1.

SELECT   FirstName

              , LastName

              , Posistion

              , SalaryEncrypted

FROM Application.Salary ;

Listing 14-2

Viewing the Application.Salary Table

While Grant is unable to determine the salary that Michael Ramsdon receives, he knows that as the CEO, his salary is a lot more than his own. Therefore, if Grant were to update his SalaryEncrypted value, using the query in Listing 14-3 he would increase his salary to be equal to the CEO’s.

UPDATE Application.Salary

SET SalaryEncrypted =

(

        SELECT SalaryEncrypted

        FROM Application.Salary

        WHERE FirstName = 'Michael'

                   AND LastName = 'Ramsdon'

)

WHERE FirstName = 'Grant'

AND LastName = 'Culberston' ;

Listing 14-3

Perform a Whole Value Substitution Attack

Let’s now look at the effect of the whole value substitution attack by returning the decrypted salary values for both Grant and Michael using the query in Listing 14-4.

SELECT FirstName

     , LastName

     , Posistion

     , CAST(DECRYPTBYPASSPHRASE('Pa$$w0rd',SalaryEncrypted) AS NVARCHAR) AS Salary

FROM Application.Salary

WHERE (FirstName = 'Michael' AND LastName = 'Ramsdon')

      OR (FirstName = 'Grant' AND LastName = 'Culberston') ;

Listing 14-4

Assessing the Impact of the Attack

The results in Figure 14-2 show that both employees now have the same salary. Assuming that the Application.Salary table drives the application that performs the salary payment run, Grant has increased his wage.

Credit Card Fraud Example

Whole value substitution attacks can also be carried out by external attackers as well as internal attackers. Imagine that a WideWorldImporters customer, Valter Viiding, has gained access to the WideWorldImporters database by using an SQL injection attack.

With some exploration, Valter has managed to compile the query in Listing 14-5 in an attempt to steal the credit card details of other customers. The unencrypted credit card number has been left in for reference, but it would not exist in a production scenario.

Tip

The script to create the Application.CreditCards table can be found in Chapter 4. The script to add the encrypted card number column can be found in Chapter 5.

SELECT

       CardID

      , Cust.CustomerName

      , CardType

      , ExpMonth

      , ExpYear

      , cc.CustomerID

      , CardNumberEncrypted

  FROM Application.CreditCards cc

  INNER JOIN Sales.Customers Cust

      ON Cust.CustomerID = cc.CustomerID ;

Listing 14-5

Returning Credit Card Details

As you can see from the results of the query in Figure 14-5, which are partially displayed in Figure 14-3, Valter’s first attempt to steal credit card details has been thwarted, as the credit card number is encrypted.

Although the credit card numbers cannot be seen, it is still possible for Valter to perform a whole value substitution attack. The query in Listing 14-6 will replace Valter’s credit card number with Agrita Kanepa’s.

UPDATE Application.CreditCards

SET CardNumberEncrypted =

(

        SELECT CardNumberEncrypted

        FROM Application.CreditCards

        WHERE CardID = 15

),

ExpMonth =

(

        SELECT ExpMonth

        FROM Application.CreditCards

        WHERE CardID = 15

),

ExpYear =

(

        SELECT ExpYear

        FROM Application.CreditCards

        WHERE CardID = 15

)

WHERE CardID = 1 ;

Listing 14-6

Credit Card Whole Value Substitution Attack

We can now use the query in Listing 14-7 to evaluate the results of the attack.

SELECT

        CardID

      , Cust.CustomerName

      , CardType

      , ExpMonth

      , ExpYear

      , cc.CustomerID

     ,CONVERT(NVARCHAR(25),DECRYPTBYPASSPHRASE('Pa$$w0rd',cc.CardNumberEncrypted, 0)) AS CardNumber

FROM Application.CreditCards cc

INNER JOIN Sales.Customers Cust

      ON Cust.CustomerID = cc.CustomerID

WHERE CardID IN (15,1) ;

Listing 14-7

Assessing the Results of the Attack

The results of this assessment are shown in Figure 14-4.

The results of this attack are bad enough when you consider the obvious option of Valter now using Agrita’s card details to buy goods on his account. The situation becomes much worse, however, when you consider the bigger picture.

Assuming that the WideWorldImporters web app has the ability to view and edit your own credit card details, Valter now has a mechanism by which he can systematically reveal each customer’s credit card details in turn. These details can then be collated and sold on the dark web.

Protecting Against Whole Value Substitution Attacks

The key parameters to note are the add_authenticator and the authenticator parameters. These parameters provide you with the option to add contextual information, usually the primary key of the table, to the value being encrypted. This primary key value is encrypted, along with the sensitive value. Therefore, when decrypting the value, the same authenticator value must also be passed, ensuring that the sensitive value has not been moved between rows.

The important parameters to note are the add_authenticator and authenticator parameters. If data has been encrypted using an authenticator, then the same authenticator must be passed when decrypting the data.

Now let’s see what happens if we try to repeat the whole value substitution attack against the credit card column by re-running the script in Listing 14-6. The attack will appear to succeed, but let’s now assess the impact of the attack by using the script in Listing 14-9.

The results of the attack are displayed in Figure 14-5.

As you can see, even though Valter has replaced his credit card number with Agrita’s, he can no longer decrypt the value, and his credit card number is replaced with a NULL value. This is because the authenticator, which in this instance was the CardID column, did not match.

Performance Considerations

The time statistics for the first benchmark are shown in Figure 14-6.

The time statistics for the second benchmark are shown in Figure 14-7.

As you can see, the second benchmark took 33% longer to execute. While we are still talking about ms, if you imagine this percentage being scaled up to a table with millions of rows, or if the action is performed millions of times a day, you can see the impact that may occur. As expected, there is a trade-off between security and performance.

Of course, the performance will depend on many factors, such as the specification of the server that the query is being run on and the amount of concurrent activity on the server.

If you must encrypt data with an authenticator, you should pay specific attention to processor utilization during your capacity planning, as additional CPU time will be required to compare the authenticator.

It is important to also note, however, that the bloat (amount of space used to store the data) will increase, due to the encryption of the authenticator. Therefore, for large datasets, more data pages will need to be read in order to fulfill the query. This will, in turn, put additional pressure on memory, forcing pages out of the buffer cache quicker, meaning that more data will need to be retrieved from disc.

Summary

Whole value substitution attacks can be used by attackers to manipulate data without needing to decrypt it first, by replacing an encrypted value, with a different encrypted value, that they know to be preferential.

Whole value substitution attacks can be deferred by using an authenticator. An authenticator is contextual information from a unique, static key, which ensures that the encrypted value has not been moved at the point when it is decrypted. If it has been, then the decryption will fail, and a NULL value will be returned.

An authenticator will not stop the encrypted data being moved, but it will prevent an attacker from revealing or utilizing that data. This comes at the expense of performance degradation, however.