Практически в любой отрасли существуют свои критические данные, которые требуют шифрования. Начиная с SQL сервера версии 6.x, для шифрования данных вы можете применять функцию ENCRYPT (в последних версия эта функция уже недоступна), которая использует тот же метод (шифрования), что и опция WITH ENCRYPTION.
Начиная с SQL Server 2000 появилась недокументированная функция PWDENCRYPT, которая возвращает Хэш пароля (PWDENCRYPT — это устаревшая функция, которая может не поддерживаться в будущих версиях SQL Server. Вместо этого используйте программу HASHBYTES. HASHBYTES предоставляет больше алгоритмов хэширования), а так же PWDCOMPARE - Хэширует пароль и сравнивает хэш с хэшем существующего пароля.
Для более сложного шифрования данных до версии SQL Server 2005 приходилось "изобретать велосипед", но начиная с 9ой версии шифрование и дешифровка стали встроенным функционалом. А в SQL Server 2008 появилось прозрачное шифрование.
Но в очередной раз попробуем изобрести велосипед и организовать своё шифрование данных в виде CLR-сборки.
В качестве алгоритма шифрования я использую RSA. Алгоритм используется в большом числе криптографических приложений. На 2009 год система шифрования на основе RSA считается надёжной, начиная с размера в 1024 бита.
За основу взят класс RSACryptoServiceProvider.
Код сборки на C#:
using System;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Security.Cryptography;
using System.Text;
public class RSACLR
{
[SqlFunction(FillRowMethodName = "FillRow",
TableDefinition = "publicAndPrivateKeys nvarchar(max), justPublicKey nvarchar(max)")
]
//Генерируем 2 ключа: закрытый с публичным и только публичный
public static IEnumerable GenerateKeys(int KeySize)
{
ArrayList row = new ArrayList();
RSACryptoServiceProvider RSAProvider = new RSACryptoServiceProvider(KeySize);
row.Add(new object[] { RSAProvider.ToXmlString(true), RSAProvider.ToXmlString(false) });
return row;
}
public static void FillRow(Object row, out string publicAndPrivateKeys, out string justPublicKey)
{
object[] xrow = (object[])row;
publicAndPrivateKeys = (string)xrow[0];
justPublicKey = (string)xrow[1];
}
//Шифрование, на входе строка, размер ключа и открытый(публичный) ключ
public static string EncryptString(string inputString, int dwKeySize,
string xmlString)
{
RSACryptoServiceProvider rsaCryptoServiceProvider =
new RSACryptoServiceProvider(dwKeySize);
rsaCryptoServiceProvider.FromXmlString(xmlString);
int keySize = dwKeySize / 8;
byte[] bytes = Encoding.UTF32.GetBytes(inputString);
int maxLength = keySize - 42;
int dataLength = bytes.Length;
int iterations = dataLength / maxLength;
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i <= iterations; i++)
{
byte[] tempBytes = new byte[
(dataLength - maxLength * i > maxLength) ? maxLength :
dataLength - maxLength * i];
Buffer.BlockCopy(bytes, maxLength * i, tempBytes, 0,
tempBytes.Length);
byte[] encryptedBytes = rsaCryptoServiceProvider.Encrypt(tempBytes,
true);
Array.Reverse(encryptedBytes);
stringBuilder.Append(Convert.ToBase64String(encryptedBytes));
}
return stringBuilder.ToString();
}
//Расшифровка, на входе шифрованная строка, размер ключа и закрытый ключ
public static string DecryptString(string inputString, int dwKeySize,
string xmlString)
{
RSACryptoServiceProvider rsaCryptoServiceProvider
= new RSACryptoServiceProvider(dwKeySize);
rsaCryptoServiceProvider.FromXmlString(xmlString);
int base64BlockSize = ((dwKeySize / 8 ) % 3 != 0) ?
(((dwKeySize / 8 ) / 3) * 4) + 4 : ((dwKeySize / 8 ) / 3) * 4;
int iterations = inputString.Length / base64BlockSize;
ArrayList arrayList = new ArrayList();
for (int i = 0; i < iterations; i++)
{
byte[] encryptedBytes = Convert.FromBase64String(
inputString.Substring(base64BlockSize * i, base64BlockSize));
Array.Reverse(encryptedBytes);
arrayList.AddRange(rsaCryptoServiceProvider.Decrypt(
encryptedBytes, true));
}
return Encoding.UTF32.GetString(arrayList.ToArray(
Type.GetType("System.Byte")) as byte[]);
}
}
Как всегда минимум кода.
Теперь регистрируем нашу сборку и создаём на её основе 3 функции (получение ключей, шифрование и расшифровка):
--Включаем выполнение пользовательских сборок
SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO
--Создаём тестовую БД для демострации
CREATE DATABASE TestDB
GO
--Модули базы данных (например, пользовательские функции или хранимые процедуры),
--которые используют контекст олицетворения, могут обращаться к ресурсам,
--находящимся вне базы данных.
ALTER DATABASE TestDB SET TRUSTWORTHY ON
GO
--Переходим в нашу БД
USE TestDB
GO
--Регистрируем сборку
CREATE ASSEMBLY RSACLR
FROM 'C:\RSA\RSACLR.dll'
WITH PERMISSION_SET = UNSAFE;
GO
--Получение ключей
CREATE FUNCTION GenerateKeys(@KeySize int)
RETURNS TABLE
(
publicAndPrivateKeys NVARCHAR(max),
justPublicKey NVARCHAR(max)
)
EXTERNAL NAME RSACLR.RSACLR.GenerateKeys;
GO
--Шифрование строки
CREATE FUNCTION EncryptString
(
@inputString nvarchar(MAX),
@dwKeySize int,
@xmlString nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
EXTERNAL NAME RSACLR.RSACLR.EncryptString;
GO
--Расшифровка строки
CREATE FUNCTION DecryptString
(
@inputString nvarchar(MAX),
@dwKeySize int,
@xmlString nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
EXTERNAL NAME RSACLR.RSACLR.DecryptString;
GO
Теперь продемонстрирую, как это работает:
--Создадим таблицу, в которой будут "жить" наши ключи
CREATE TABLE MyKeys(id int identity, publicAndPrivateKeys xml, justPublicKey xml)
--Добавим несколько ключей разной размерности
INSERT INTO MyKeys
SELECT * FROM GenerateKeys(512)
UNION ALL
SELECT * FROM GenerateKeys(1024)
UNION ALL
SELECT * FROM GenerateKeys(2048)
--и посмотрим в каком виде они у нас хранятся
SELECT * FROM MyKeys
Для наглядности я выбрал для хранения ключей тип XML 
Теперь можно с помощью любой из пар ключей шифровать и расшифровывать данные.
--Создаём таблицу слов
CREATE TABLE MyWords(Val nvarchar(max), EncryptVal nvarchar(max))
--и наполним её данными
INSERT INTO MyWords
SELECT 'Knyazev Alexey', null
UNION ALL
SELECT 'http://www.t-sql.ru', null
UNION ALL
SELECT 'SQL Server', null
--теперь зашифруем с помощью открытого ключа
DECLARE @k nvarchar(max), @k2 nvarchar(max)
SELECT @k=convert(nvarchar(max), justPublicKey) FROM MyKeys WHERE id=1
UPDATE MyWords
SET EncryptVal=dbo.EncryptString(Val, 512, @k)
Расшифровать можно так:
DECLARE @k nvarchar(max), @k2 nvarchar(max)
SELECT @k=convert(nvarchar(max), PublicAndPrivateKeys) FROM MyKeys WHERE id=1
SELECT *, dbo.DecryptString(EncryptVal, 512, @k) FROM MyWords
Вот и весь "велосипед", цель данного примера показать, в очередной раз, как CLR-сборки могут разнообразить и улучшить работу с SQL Server.
Подписаться