DECRYPTBYKEY: примеры (SQL)

Примеры использования DECRYPTBYKEY

Базовый пример расшифровки данных.

-- Предполагаем, что симметричный ключ уже создан и открыт
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

SELECT 
    DECRYPTBYKEY(EncryptedData) AS DecryptedData
FROM 
    Customers;

CLOSE SYMMETRIC KEY MySymmetricKey;

DecryptedData
0x4A6F686E20446F65
(преобразуется в текст 'John Doe')

Пример с использованием authenticator.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

SELECT 
    DECRYPTBYKEY(EncryptedData, 1, CONVERT(varbinary, CustomerID)) AS DecryptedData
FROM 
    Customers;

CLOSE SYMMETRIC KEY MySymmetricKey;

DecryptedData
0x4A6F686E20446F65
(расшифрованный текст)

Пример с проверкой результата.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

SELECT 
    CASE 
        WHEN DECRYPTBYKEY(EncryptedData) IS NULL THEN 'Расшифровка не удалась'
        ELSE CONVERT(varchar, DECRYPTBYKEY(EncryptedData))
    END AS DecryptionResult
FROM 
    Customers;

CLOSE SYMMETRIC KEY MySymmetricKey;

DecryptionResult
John Doe

Похожие функции в MS SQL Server

В MS SQL Server существуют другие функции для работы с шифрованием.

• DECRYPTBYCERT - расшифровка с использованием сертификата. Применяется, когда данные шифруются асимметрично с помощью открытого ключа сертификата. Медленнее симметричного шифрования, но безопаснее для передачи ключей.
• DECRYPTBYASYMKEY - расшифровка асимметричным ключом. Аналогична DECRYPTBYCERT, но использует асимметричный ключ, созданный без сертификата.
• DECRYPTBYPASSPHRASE - расшифровка с использованием парольной фразы. Не требует управления ключами, но безопасность зависит от сложности парольной фразы. Удобна для простых сценариев.

DECRYPTBYKEY предпочтительнее для больших объемов данных из-за скорости симметричного шифрования, особенно когда требуется шифрование на уровне столбцов.

Аналоги в других СУБД и языках

В других системах управления базами данных существуют свои механизмы шифрования.

MySQL

Функция AES_DECRYPT используется для расшифровки данных, зашифрованных AES_ENCRYPT.

SELECT AES_DECRYPT(encrypted_column, 'encryption_key') AS decrypted_data
FROM table_name;

decrypted_data
'John Doe'

Oracle

Пакет DBMS_CRYPTO предоставляет функции для шифрования и расшифровки.

DECLARE
  l_decrypted RAW(2000);
BEGIN
  l_decrypted := DBMS_CRYPTO.DECRYPT(
      src => encrypted_data,
      typ => DBMS_CRYPTO.DES_CBC_PKCS5,
      key => utl_raw.cast_to_raw('encryption_key')
  );
END;

PostgreSQL

Расширение pgcrypto предлагает функцию pgp_sym_decrypt.

SELECT pgp_sym_decrypt(encrypted_column, 'encryption_key')
FROM table_name;

SQLite

Встроенных криптографических функций нет, обычно шифрование реализуется на уровне приложения.

Отличия от MS SQL

В MS SQL управление ключами интегрировано в систему безопасности базы данных (сертификаты, асимметричные ключи), тогда как в других СУБД часто используется прямое указание ключа в запросе или управление через внешние модули.

Типичные ошибки при использовании

Ошибка из-за неоткрытого ключа.

-- Ключ не открыт
SELECT DECRYPTBYKEY(EncryptedData) FROM Customers;

NULL
(все строки вернут NULL)

Несоответствие параметра authenticator.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

-- При шифровании использовался authenticator, но здесь он не указан
SELECT DECRYPTBYKEY(EncryptedData) FROM Customers;

CLOSE SYMMETRIC KEY MySymmetricKey;

NULL
(расшифровка не удается)

Попытка расшифровки данных, не зашифрованных с помощью ENCRYPTBYKEY.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

SELECT DECRYPTBYKEY(0x123456) AS Result; -- Случайные бинарные данные

NULL

Использование неверного типа данных для authenticator.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

-- Authenticator должен быть того же типа, что и при шифровании
SELECT DECRYPTBYKEY(EncryptedData, 1, 'wrong_type') FROM Customers;

NULL

Изменения в последних версиях

В SQL Server 2016 и более поздних версиях появилась поддержка Always Encrypted, которая предлагает новый подход к шифрованию на уровне столбцов. Однако функция DECRYPTBYKEY остается рабочей для данных, зашифрованных традиционным способом.

В SQL Server 2017 и 2019 улучшена производительность шифрования и расшифровки за счет оптимизации внутренних алгоритмов.

Важных изменений в синтаксисе или поведении функции DECRYPTBYKEY в последних основных версиях не было. Функция поддерживается для обеспечения обратной совместимости.

Расширенные примеры использования

Расшифровка данных с автоматическим открытием и закрытием ключа в процедуре.

CREATE PROCEDURE DecryptCustomerData
AS
BEGIN
    OPEN SYMMETRIC KEY MySymmetricKey
        DECRYPTION BY CERTIFICATE MyCertificate;
    
    SELECT 
        CustomerID,
        CONVERT(varchar, DECRYPTBYKEY(EncryptedName)) AS DecryptedName,
        CONVERT(varchar, DECRYPTBYKEY(EncryptedEmail, 1, CONVERT(varbinary, CustomerID))) AS DecryptedEmail
    FROM 
        Customers;
    
    CLOSE SYMMETRIC KEY MySymmetricKey;
END;

Использование в выражении CASE для выборочной расшифровки.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

SELECT 
    CustomerID,
    CASE 
        WHEN @UserHasPermission = 1 THEN CONVERT(varchar, DECRYPTBYKEY(EncryptedData))
        ELSE 'Access Denied'
    END AS SensitiveData
FROM 
    Customers;

CLOSE SYMMETRIC KEY MySymmetricKey;

Обновление таблицы с расшифрованными данными.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

UPDATE Customers
SET DecryptedName = CONVERT(varchar, DECRYPTBYKEY(EncryptedName))
WHERE CustomerID > 100;

CLOSE SYMMETRIC KEY MySymmetricKey;

Расшифровка данных, зашифрованных разными ключами, с динамическим выбором ключа.

-- Предположим, есть столбец KeyID, указывающий, каким ключом зашифрованы данные
DECLARE @KeyName varchar(100);
SET @KeyName = (SELECT KeyName FROM EncryptionKeys WHERE KeyID = Customers.KeyID);

-- Динамическое открытие ключа (в реальном сценарии требуется сложная логика)
IF @KeyName = 'Key1'
    OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE Cert1;
ELSE
    OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE Cert2;

SELECT DECRYPTBYKEY(EncryptedData) FROM Customers;

CLOSE ALL SYMMETRIC KEYS;

Пример с обработкой NULL и поврежденных данных.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

SELECT 
    CustomerID,
    ISNULL(CONVERT(varchar, DECRYPTBYKEY(EncryptedData)), 'Invalid or Missing Data') AS SafeDecryption
FROM 
    Customers;

CLOSE SYMMETRIC KEY MySymmetricKey;