This article is a follow up to “Prevent Tampering of Encrypted Data Using @add_authenticator Argument for ENCRYPTBYKEY” . In the last article we described a scenario where the security risk of copying encrypted data from one row to another could be blocked, but there are other scenarios that can benefit from using the @add_authenticator and @authenticator arguments of ENCRYPTBYKEY.
Generally speaking, it is highly recommended to make use of the @add_authenticator argument to add some form of integrity check, even if the value for the @authenticator parameter is a constant for the whole table. In order to understand the motivation for this recommendation, it is necessary to explain some basic concepts of block ciphers (The information I present in this article is a high-level abstraction of this subject).
In cryptography there are several modes of operation to work with multiple blocks of data. One of the most common modes of operation is cipher-block chaining (CBC) mode, which has specific error propagation characteristics. In a nutshell, one error in a given block will affect only a deterministic number of blocks. The error-correction characteristics of this chaining mode may allow an adversary to tamper with the message. A common mitigation against such data tampering is to use an integrity check mechanism.
The SQL Server ENCRYPTBYKEY built-in function uses CBC mode and therefore it is subject to this error-propagation mechanism and data tampering threat. Without using any integrity checks (i.e. if the default @add_authenticator is not set), an adversary may be able to manipulate the ciphertext in such a way that the blob can control some of the bits of the plaintext. When the @add_authenticator parameter is set, the @authenticator argument is used along with the @plaintext parameter to calculate a hash value that is encrypted and acts as the integrity check.
Below is an example describing how a crafty adversary may tamper with data. For the following sample, we assume that the attacker has no direct access to the key (i.e. access to the key may be controlled via a stored procedure), but the attacker has direct write privileges (i.e. a way to insert the tampered ciphertext), and that other than verifying for null values, the application may not have any additional checks on decrypted data.
CREATE TABLE t( data varbinary(200))
INSERT INTO t VALUES (ENCRYPTBYKEY(key_guid('key1'), N'Testingtesting1234'));
SELECT * FROM t;
go
-- 0x008CB602DBC9D145B899AC05FC14E2A30100000093384ECE68D1618EB5 E 197…
-- Application “myApp” decryps data and returns the plaintext
-- “Testingtesting1234”
SELECT CONVERT( nvarchar(100), DECRYPTBYKEY(data)) FROM t
Now, imagine that the attacker may be able to inject the following ciphertext (notice that the attacker modified a single bit of the original ciphertext):
INSERT INTO t VALUES (
0x008CB602DBC9D145B899AC05FC14E2A30100000093384ECE68D1618EB5 F 197…);
SELECT * FROM t;
Results:
Testingtesting1234
ၔestingtesting1234
Now let’s see what happens when using the @authenticator parameter. In this particular case I am using an arbitrary string to demonstrate the integrity check. The value for the @authenticator argument in this case is not important, as long as it is the same value for encryption and decryption calls.
INSERT INTO t VALUES (
ENCRYPTBYKEY(key_guid('key1'), N'Testingtesting1234', 1, 'abc'));
SELECT * FROM t;
-- 0x008CB602DBC9D145B899AC05FC14E2A3010000009925C3FB4D21B13D92869A53BB959303483575F E 0D…
-- Testingtesting1234
SELECT CONVERT( nvarchar(100), DECRYPTBYKEY(data, 1, 'abc')) FROM t
Attacker:
INSERT INTO t VALUES (0x008CB602DBC9D145B899AC05FC14E2A3010000009925C3FB4D21B13D92869A53BB959303483575F F 0D …)
SELECT CONVERT( nvarchar(100), DECRYPTBYKEY(data, 1, 'abc')) FROM t
Results:
Testingtesting1234
NULL
As the final NULL result shows, the integrity check failed, and instead of returning a corrupted plaintext, the result of the decrypt call is discarded and the DECRYPTBYKEY function returns null.
-Raul Garcia
P.S. Thanks a lot to Jack Richins & Rick Byham for their feedback.
Microsoft