SqlClr_IndexCryptoProtector - криптографическая защита индексов SQL-сервера с помощью SQL CLR сборки.
Я с удовольствием решаю на протяжении последних пяти лет задачи с помощью SQL CLR Assembly (в тех случаях, когда пользуюсь MS SQL сервером):
- До 2005-го года (когда сборок еще не было) я создал множество COM-обьетов, которые работали внутри MS SQL-сервера и выполняли множество полезных функций. Например, вот здесь лежит код COM-обьекта (позднее превратившийся в мою первую сборку) - скрины работы моего Notification-сервера - когда SQL CLR Assembly еще не существовало.
- После появления сборок в 2005-м году, я продолжал публиковать код некоторых своих сборок. Например, вот здесь у меня выложен код сразу трех сборок - одна из них делает динамический джоин в стиле Hibernate (только не на уровне кода, а прямо внутри SQL), вторая сборка рассылает письма, а третья сборка определяет формат рекордсета (перечень и тип полей), выдаваемого произвольной процедурой.
- Вот здесь я выложил код сборки, без которой категорически не получается работать со стандартными профилями ASP.NET-пользователей. Ибо по Property, определенным в Web-конфиге, нельзя делать отборы в SQL - а тогда зачем эти свойства в профиле пользователя, если их в SQL не видно?
- Вот здесь у меня опубликована простейшая сборка, которая умеет выполнять реквесты на сайт
- Вот здесь у меня опубликована сборка, которая выполняет не просто реквесты на сайт, а после отработки выполненного кода сайта (вызванного из SQLJOB) выполняет еще и редирект на другой сайт
- Вот здесь у меня опубликована сборка, которая позволяет считывать XML-страничку с удаленного Web-сервера SQL CLR-сборкой, исполняемой внутри SQL-сервера, затем считанный XML парсится с помощью встроенного в MS SQL парсера XML и распарсенный результат (фактически считанный с удаленного Web-сервера самим SQL-сервером) укладывается по нужным табличкам табличкам. Фактически SQL-CLR нам позволила просто вызвать процедуру с параметров в виде URL удаленного Web-сервера - после чего в нашей базе появились все нужные данные.
- Вот здесь я рассказал как парсить cтандартный SOAP с помощью сборки, возвращающей табличные данные.
- Вот здесь еще в 2005-м году я опубликовал код защиты параметров в URL от подделки. Но в 2005-м году я не решился опубликовать код SQL-CLR сборки (это было мое важное know how, на которых я строил свои системы). Но по прошествии пяти лет - я решился частично опубликовать код моих наработок пятилетней давности.
Итак, перед вами одна из моих важных сборок, на которых построено множество сайтов в рунете, в первую очередь ведуший туристический портал и ведущая социальная сеть России - http://www.votpusk.ru/ (сразу предупреждаю любителей взломов - что реальная сборка ВОТПУСКА устроена существенно сложнее - класс pp8_helper здесь не публикуется). Тем не менее - я публикую отличное автономное решение, имееющее самостоятельную ценность, которое надеюсь еще послужит мне и другим на многих других сайтах.
Важно понять назначение этой опубликованной сборки - тогда вы сможете взять мой код за основу и развивать его в своих проектах.
Если вы зайдете на страничку http://foto.votpusk.ru/, то увидите, что номеров рисунков в открытом виде не видно - вместо них стоят цифры, подобные 83298DD4FA2A06E41B43F5708CE235221209. В этой цифре и зашифрован номер рисунка - как Identity-ключа в базе. Причем если вы зайдете на следующий день, то заметите, что цифры стали другие.
Приложение каждые сутки выполняет рестарт и при рестаре выполнился следующий код, встроенный Application_Start Global.asax:1: Imports System
2: Imports System.Data
3: Imports System.Data.SqlClient
4: Imports System.Data.SqlTypes
5: Imports Microsoft.SqlServer.Server
6:
7:
8: Partial Public Class StoredProcedures
9: <Microsoft.SqlServer.Server.SqlProcedure()> _
10: Public Shared Sub CreatenewKey()
11: Dim DES As New System.Security.Cryptography.DESCryptoServiceProvider
12: SaveKeyToSQL(DES)
13: End Sub
14:
15: ''' <summary>
16: ''' При рестарте Web-приложения ключи будут новые и все старое расшифровать не удастся
17: ''' Этот метод позволяет сохранить текущие ключи в базу. Методу надо отдать имя строки коннекта к базе
18: ''' Если ключи потом удалить - восстановить из криптопараметра оригинальные номера записей не удасться
19: ''' CREATE TABLE [dbo].[ParmProtector8](
20: ''' [i] [int] IDENTITY(1,1) NOT NULL,
21: ''' [key] [binary](8) NOT NULL,
22: ''' [IV] [binary](8) NOT NULL,
23: '''CONSTRAINT [PK_ParmProtector8] PRIMARY KEY CLUSTERED
24: ''' ( [i] ASC ) ON [PRIMARY]
25: ''' ) ON [PRIMARY]
26: ''' </summary>
27: Public Shared Function SaveKeyToSQL(ByRef DES As System.Security.Cryptography.DESCryptoServiceProvider) As Integer
28: Dim I As Integer
29: Using CN As New SqlConnection("context connection=true")
30: Using CMD As New System.Data.SqlClient.SqlCommand("INSERT [ParmProtector8]([key],[IV],[Date]) VALUES (@Key,@IV, GetDate()); select scope_identity() as [i]")
31: CMD.CommandType = Data.CommandType.Text
32: CMD.Parameters.Add("Key", Data.SqlDbType.Binary, 8)
33: CMD.Parameters.Add("IV", Data.SqlDbType.Binary, 8)
34: CN.Open()
35: CMD.Connection = CN
36: CMD.Parameters("Key").Value = DES.Key
37: CMD.Parameters("IV").Value = DES.IV
38: Dim RDR = CMD.ExecuteReader(System.Data.CommandBehavior.SingleRow)
39: If RDR.Read Then
40: I = CInt(RDR("I"))
41: End If
42: RDR.Close()
43: CN.Close()
44: End Using
45: End Using
46: Return I
47: End Function
48: End Class
Как вы видите на скрине, к моменту составления этой заметки произошло примерно 50 тысяч рестартов моего Web-приложения. Соотвественно, существует 50 тысяч вариантов криптографического индекса, каждый из которых соответствует одной реальной записи в базе.
С помощью класса pp8_helper (который здесь не публикуется) можно делать различные манипуляции с наборами криптоиндексов, например удалить индексы, сгенерированные 1-го мая 2005-го года. Тогда все криптоиндексы перестанут ссылаться на какие-либо индексы в базе.
Примитивные парсеры так и не сумели воспроизввести контент сайта ВОТПУСКА, ибо сайт имеет 50 тысяч вариантов ссылок на один и тот же контент и примитивные парсеры тупо зацикливаются - не умея вырезать рекламу и сверять контент сложными методами, какими умеют сравнивать поисковые машины. С другой стороны, поисковые машины со сложными алгоритмами сравнения значимого контента, умеют нормально индексировать странички сайта (о чем свидетельсвует самые высокие позиции рейтинга ВОТПУСКА). Ну а если даже поисковик увидел не 50 тысяч комплектов ссылок, а всего 20 штук на тот же контент - это не мешает индексации контента, зато полностью убивает примитивные парсеры контента.
Излишне наверное говорить, что в этом числе 83298DD4FA2A06E41B43F5708CE23522120 чрезвычайно сложно (ну практически невозможно) заменить какой-либо знак и получить следующую (или вообще какую-нибудь) запись в базе. Даже простой GUID (типа CF1B26FC-8AF8-4607-B861-D98CFD0ABB92) - это уже количество атомов во вселенной, а как вы видите - у меня разрядность еще выше.
Соответственно, даже сгенерировал миллиарды и миллиарды новых комплектов крипто-параметров - они все равно не будут располложены на числовой оси даже столь часто, как в GUID.
Но GUID строго и однозначно соответствует номеру записи в базе, а мои криптографические номера меняются каждый день и в любой момент могут быть в любом количестве аннулированы. Надеюсь теперь вы поняли назначение моей сборки (и моей идеологии защиты URL-параметров от подделки и перебора).
Реалиация этого кода именно в сборке (а не просто в коде сайта) позволяет мне работать именно на уровне SQL - как вы видите на скрине. Например в коде того же ВОТПУСКА есть старинные компоненты, реализованные не на NET. Переписывать их на NET чрезвычайно трудоемко (практически невозможно), вызвать из них NET-код тоже практически невозмножно - а как им дать криптографические номера записей в базе иначе, чем с помощью сборки?
Меня часто спрашивают - почему я не люблю NHIBERNATE? Отвечаю - именно потому, что в нем все реализовано в NET-коде. И действительно, в NET коде одни обьекты красиво наследуют другие. Но в базе получается сплошной мусор. Фактически разработчики NHIBERNATE сделали клон HIBERNATE совершенно без учета возможностей MS SQL. А вот если бы NHIBERNATE был реализован в виде сборок внутри MS SQL - тогда на уровне MS SQL тоже можно было работать так же, как работаю я со своими сборками.
Вот у меня есть множество фрагментов отличного кода для PostgreSQL, например Remote SQL execute for PostgreSQL on GSM/GPRS channel with extreme compress and cryptography, но увы, утопить MONO-код во внутрь PostgreSQL в виде сборки невозможно. Что я и отметил как одну из существенных (с моей точки зрения) недостатков PostgreSQL - //www.vb-net.com/.
Ну и вот, собственно, перед вами основной код моей сборки:
1: Imports System
2: Imports System.Data
3: Imports System.Data.SqlClient
4: Imports System.Data.SqlTypes
5: Imports Microsoft.SqlServer.Server
6:
7: Partial Public Class UserDefinedFunctions
8: <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _
9: Public Shared Function Mask(ByVal I As SqlInt32) As SqlString
10: Dim Restart_DB_ID As Integer 'номер записи в базе, куда сохранен ключ и IV симметричного шифрования (защита от рестарта)
11: Dim DES As New System.Security.Cryptography.DESCryptoServiceProvider
12: '
13: Restart_DB_ID = LoadLastKeyFromSQL(DES)
14: Return MaskInternal(DES, I.ToString) + CType(Restart_DB_ID.ToString("X2"), SqlString)
15: End Function
16:
17: <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _
18: Public Shared Function UnMask(ByVal CryptParm As SqlString) As SqlTypes.SqlInt32
19: Dim Restart_DB_ID As Integer 'номер записи в базе, куда сохранен ключ и IV симметричного шифрования (защита от рестарта)
20: Dim DES As New System.Security.Cryptography.DESCryptoServiceProvider
21: '
22: Restart_DB_ID = Integer.Parse(CryptParm.ToString.Substring(32), Globalization.NumberStyles.HexNumber)
23: LoadOneKeyFromSQL(DES, Restart_DB_ID)
24: '
25: Return UnMaskInternal(DES, CryptParm.ToString.Substring(0, 32))
26: End Function
27:
28: 'Загружает текущие клчишифрования из таблицы ключей
29: Public Shared Function LoadLastKeyFromSQL(ByRef DES As System.Security.Cryptography.DESCryptoServiceProvider) As Integer
30: Dim I As Integer
31: Using CN As New SqlConnection("context connection=true")
32: Using CMD As New System.Data.SqlClient.SqlCommand("SELECT top 1 i,[key],[IV] From [ParmProtector8] order by i desc")
33: CMD.CommandType = Data.CommandType.Text
34: CN.Open()
35: CMD.Connection = CN
36: Dim RDR = CMD.ExecuteReader(System.Data.CommandBehavior.SingleRow)
37: If RDR.Read Then
38: DES.Key = CType(RDR("key"), Byte())
39: DES.IV = CType(RDR("IV"), Byte())
40: I = CType(RDR("i"), Integer)
41: End If
42: RDR.Close()
43: CN.Close()
44: RDR = Nothing
45: CN.Close()
46: End Using
47: End Using
48: Return I
49: End Function
50:
51: 'Загружает текущие клчишифрования из таблицы ключей
52: Public Shared Sub LoadOneKeyFromSQL(ByRef DES As System.Security.Cryptography.DESCryptoServiceProvider, ByVal Keynumber As Integer)
53: Dim I As Integer
54: Using CN As New SqlConnection("context connection=true")
55: Using CMD As New System.Data.SqlClient.SqlCommand("SELECT [key],[IV] From [ParmProtector8] where i=" & Keynumber.ToString)
56: CMD.CommandType = Data.CommandType.Text
57: CN.Open()
58: CMD.Connection = CN
59: Dim RDR = CMD.ExecuteReader(System.Data.CommandBehavior.SingleRow)
60: If RDR.Read Then
61: DES.Key = CType(RDR("key"), Byte())
62: DES.IV = CType(RDR("IV"), Byte())
63: End If
64: RDR.Close()
65: CN.Close()
66: RDR = Nothing
67: CN.Close()
68: End Using
69: End Using
70: End Sub
71:
72:
73: Shared ReadOnly _Delimiter As Char = CChar("*")
74: ''' <summary>
75: ''' Параметр для шифрования - не более восьми символов длиной ()
76: ''' </summary>
77: Public Shared Function MaskInternal(ByRef DES As System.Security.Cryptography.DESCryptoServiceProvider, ByVal RawParm As String) As SqlString
78: If RawParm = "" Then Return ""
79: If RawParm.Length > 8 Then Throw New Exception("Шифрование параметров длины более 8 символов не поддерживается." & vbCrLf & RawParm)
80: Dim [In] As New System.Text.StringBuilder
81: [In].Append(Now.Ticks.ToString.Substring(10, 2).Replace("0", "#"))
82: [In].Append(_Delimiter)
83: [In].Append(RawParm)
84: [In].Append(_Delimiter)
85: [In].Append("PASSWORD")
86: [In].Length = 12 'эта длина соотвествует буферу для расшифровки в 16 байт.
87: Dim CryptBytes As Byte() = Encrypt(DES, [In].ToString)
88: If CryptBytes IsNot Nothing Then
89: Dim [Out] As New System.Text.StringBuilder
90: For Each One As Byte In CryptBytes
91: [Out].Append(One.ToString("X2"))
92: Next
93: Return [Out].ToString
94: Else
95: Return ""
96: End If
97: End Function
98:
99: ''' <summary>
100: ''' Расшифровка параметра
101: ''' </summary>
102: Public Shared Function UnMaskInternal(ByRef DES As System.Security.Cryptography.DESCryptoServiceProvider, ByVal CryptParm As String) As SqlTypes.SqlInt32
103: If CryptParm.Length <> 32 Then Return SqlInt32.Null
104: Dim Buf(15) As Byte 'буфер для расшифровки
105: For Z As Integer = 1 To CryptParm.Length - 1 Step 2
106: Dim b As Byte = Byte.Parse(Mid(CryptParm, Z, 2), System.Globalization.NumberStyles.HexNumber)
107: Buf(CInt((Z - 1) / 2)) = b
108: Next
109: Dim Out As String = Decrypt(DES, Buf)
110: If Out.IndexOf(_Delimiter) = 2 Then
111: Dim EndParmPos As Integer = Out.IndexOf(_Delimiter, 3)
112: If EndParmPos > 0 Then
113: Return CInt(Out.Substring(3, EndParmPos - 3))
114: Else
115: Return SqlInt32.Null
116: End If
117: Else
118: Return SqlInt32.Null
119: End If
120: End Function
121:
122: 'Encrypt the string to array of bytes
123: Private Shared Function Encrypt(ByRef DES As System.Security.Cryptography.DESCryptoServiceProvider, ByVal PlainText As String) As Byte()
124: Dim ms As New System.IO.MemoryStream()
125: If ms.CanRead Then
126: Dim encStream As New System.Security.Cryptography.CryptoStream(ms, DES.CreateEncryptor(), System.Security.Cryptography.CryptoStreamMode.Write)
127: If encStream.CanWrite Then
128: Dim sw As New System.IO.StreamWriter(encStream)
129: sw.WriteLine(PlainText)
130: sw.Close()
131: encStream.Close()
132: Dim buffer As Byte() = ms.ToArray()
133: ms.Close()
134: Return buffer
135: End If
136: End If
137: End Function
138:
139:
140: 'Decrypt the byte array to string
141: Private Shared Function Decrypt(ByRef DES As System.Security.Cryptography.DESCryptoServiceProvider, ByVal CypherText() As Byte) As String
142: Dim ms As New System.IO.MemoryStream(CypherText)
143: If ms.CanRead Then
144: Dim encStream As New System.Security.Cryptography.CryptoStream(ms, DES.CreateDecryptor(), System.Security.Cryptography.CryptoStreamMode.Read)
145: If encStream.CanRead Then
146: Dim sr As New System.IO.StreamReader(encStream)
147: Dim val As String
148: Try
149: val = sr.ReadToEnd
150: Catch ex As System.Security.Cryptography.CryptographicException
151: 'ну просто нерасшифровали - подделали что-то
152: Dim Debug3 As String
153: For Each One As Byte In CypherText
154: Debug3 &= One.ToString("X2")
155: Next
156: Return ""
157: End Try
158: sr.Close()
159: encStream.Close()
160: ms.Close()
161: Return val
162: Else
163: Return ""
164: End If
165: Else
166: Return ""
167: End If
168: End Function
169: End Class
Для тех кто в танке (и вообще не в курсе как работать с MS SQL на микрософтовской платформе) - показываю step-by-step как создать проект сборки и убедится что исполнение SQLCLR в MS SQL сервере разрешено (по умолчанию в целях безопасности оно выключено):
|
|
|
|
|
|
|
|
|
|
Сгрузить мою сборку в откомпилированном виде можно отсюда.
)
|
|