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

Краткие примеры использования

Пример 1: Базовое шифрование текстовых данных.

-- Создание главного ключа и симметричного ключа
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPassword!'; 
CREATE SYMMETRIC KEY TestKey WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'MyKeyPassword';

-- Получение GUID ключа для использования в функции
DECLARE @KeyGUID uniqueidentifier;
SELECT @KeyGUID = key_guid FROM sys.symmetric_keys WHERE name = 'TestKey';

-- Открытие ключа и шифрование
OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = 'MyKeyPassword';
SELECT ENCRYPTBYKEY(@KeyGUID, 'Конфиденциальный текст');
CLOSE SYMMETRIC KEY TestKey;

0x00E78E1A5B5B4C4AA5F0... (двоичные данные)

Пример 2: Шифрование с использованием аутентификатора.

OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = 'MyKeyPassword';
SELECT ENCRYPTBYKEY(@KeyGUID, 'Секретные данные', 1, CONVERT(varbinary, 12345));
CLOSE SYMMETRIC KEY TestKey;

0x00E78E1A5B5B4C4AA5F0... (двоичные данные, отличные от первого примера)

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

ENCRYPTBYPASSPHRASE - шифрует данные с использованием парольной фразы. Не требует создания и управления ключами, что упрощает использование, но может быть менее безопасным при слабой парольной фразе. Подходит для простых сценариев.

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

ENCRYPTBYCERT - выполняет шифрование с помощью сертификата. По сути, является оберткой для асимметричного шифрования. Используется в схожих с ENCRYPTBYASYMKEY сценариях, но предоставляет дополнительные возможности управления через инфраструктуру открытых ключей.

Выбор функции зависит от требований безопасности, производительности и архитектуры приложения. ENCRYPTBYKEY с правильно управляемыми симметричными ключами часто является оптимальным решением для шифрования больших объемов данных на уровне столбцов.

Типичные ошибки

Ошибка 1: Попытка шифрования при закрытом ключе.

-- Ключ не открыт
SELECT ENCRYPTBYKEY(@KeyGUID, 'Текст');

NULL

Ошибка 2: Использование несуществующего GUID ключа.

DECLARE @WrongGUID uniqueidentifier = NEWID();
OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = 'MyKeyPassword';
SELECT ENCRYPTBYKEY(@WrongGUID, 'Текст');
CLOSE SYMMETRIC KEY TestKey;

NULL

Ошибка 3: Превышение максимального размера данных (8000 байт для результата).

OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = 'MyKeyPassword';
-- Попытка шифрования очень большого текста
DECLARE @BigText nvarchar(max) = REPLICATE('Очень длинный текст...', 1000);
SELECT ENCRYPTBYKEY(@KeyGUID, @BigText);
CLOSE SYMMETRIC KEY TestKey;

NULL (или усеченный результат в зависимости от версии)

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

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

В SQL Server 2017 добавлены улучшения производительности для криптографических операций. Важно отметить, что максимальный размер возвращаемых данных для ENCRYPTBYKEY остался 8000 байт, что может быть ограничивающим фактором для больших данных.

Рекомендуется использовать последние доступные обновления для получения исправлений уязвимостей в криптографических компонентах.

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

Пример 1: Шифрование данных в таблице с помощью курсора.

OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = 'MyKeyPassword';

UPDATE Users 
SET EncryptedPhone = ENCRYPTBYKEY(@KeyGUID, PhoneNumber),
    EncryptedEmail = ENCRYPTBYKEY(@KeyGUID, Email, 1, CONVERT(varbinary, UserID))
WHERE PhoneNumber IS NOT NULL;

CLOSE SYMMETRIC KEY TestKey;

(число обработанных строк)

Пример 2: Шифрование с использованием временного ключа сессии.

-- Создание временного ключа, существующего только в текущей сессии
CREATE SYMMETRIC KEY #TempSessionKey WITH ALGORITHM = AES_128 ENCRYPTION BY PASSWORD = 'TempPass';
DECLARE @TempKeyGUID uniqueidentifier;
SELECT @TempKeyGUID = key_guid FROM sys.symmetric_keys WHERE name LIKE '#TempSessionKey%';

OPEN SYMMETRIC KEY #TempSessionKey DECRYPTION BY PASSWORD = 'TempPass';
SELECT ENCRYPTBYKEY(@TempKeyGUID, 'Временные секретные данные');
CLOSE SYMMETRIC KEY #TempSessionKey;
DROP SYMMETRIC KEY #TempSessionKey;

0x00A45B2C... (двоичные данные)

Пример 3: Комбинированное шифрование с проверкой целостности через аутентификатор.

OPEN SYMMETRIC KEY TestKey DECRYPTION BY PASSWORD = 'MyKeyPassword';

DECLARE @UserID int = 42;
DECLARE @CreditCard nvarchar(50) = '1234-5678-9012-3456';
DECLARE @EncryptedData varbinary(8000);

-- Шифрование с аутентификатором
SET @EncryptedData = ENCRYPTBYKEY(@KeyGUID, @CreditCard, 1, CONVERT(varbinary, @UserID));

-- Попытка расшифровки с неправильным аутентификатором (имитация повреждения или атаки)
SELECT DECRYPTBYKEY(@EncryptedData, 1, CONVERT(varbinary, 999)) AS DecryptedWithWrongAuth;

-- Расшифровка с правильным аутентификатором
SELECT CONVERT(nvarchar(50), DECRYPTBYKEY(@EncryptedData, 1, CONVERT(varbinary, @UserID))) AS CorrectlyDecrypted;

CLOSE SYMMETRIC KEY TestKey;

DecryptedWithWrongAuth: NULL
CorrectlyDecrypted: 1234-5678-9012-3456

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

MySQL: Использует функции AES_ENCRYPT() и AES_DECRYPT(). Работают напрямую с ключом, без отдельного создания объектов ключей в БД.

SELECT AES_ENCRYPT('Confidential Data', 'encryption_key');

(двоичные данные)

PostgreSQL: Модуль pgcrypto предоставляет функции pgp_sym_encrypt() и pgp_sym_decrypt(). Поддерживает алгоритмы PGP.

SELECT pgp_sym_encrypt('Secret text', 'my_password');

\xc30d04070302b42b1062d... (bytea данные)

Oracle: Пакет DBMS_CRYPTO содержит процедуры для шифрования. Требует более сложной настройки.

SELECT DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW('Data'), DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5, UTL_I18N.STRING_TO_RAW('key')) FROM dual;

(RAW данные)

Основное отличие MS SQL - интеграция с внутренней системой управления ключами (иерархией ключей), что обеспечивает централизованное управление безопасностью.