| |
 |
|
SQL Server Tips by Burleson |
Symmetric Encryption / Decryption
Symmetric cryptography ciphers are the most common type of
encryption algorithm. They are called ‘symmetric’ because the same
key is used for both encryption and decryption. This key is often
referred to as a session key. Symmetric algorithms can be divided
into two categories, ‘Stream Ciphers’ which encrypts data, one bit
at a time and ‘Block Ciphers’ which encrypt data in discrete units
(called blocks), rather than as a continuous stream of bits.
Block ciphers due to their nature can produce encrypted data, which
is larger than the data to encrypt. You should be aware of this
issue which declaring SQL variables or column sizes for storing
encrypted data. The MS CryptoAPI automatically handles removal of
this padding when you perform the decryption process. This size will
usually be the next largest modulus of the block size of the cipher
chosen. In block ciphers, because data is encoded a block at a time
there is the potential for ‘Block Replay’. This is where an
adversary can monitor previous encrypted data passing by which it
may know some history about. Then unless other special precautions
the intruder can simply reinsert another copy of any previous block
in the middle of a message, and it will be accepted as ok by the
receiver. When a block cipher operates in this mode, it is called
‘Electronic Cookbook Mode’ or ECB. To avoid this problem, a block
cipher is usually operated in a so-called ‘Cipher Block Chaining’ or
CBC mode. This is where the next block of data to encrypt is XORed
with the previous encrypted block before it is encrypted. The
previous value is stored in a sample variable, which is feed back
into the algorithm. This is normally called a ‘feedback register’.
If the data you are encrypting contains a common header each time,
then even standard CBC may not be enough, as each encrypted block
for the header will encrypt to the same data. To prevent this you
can initialize the feedback register with some random data such as a
timestamp. This is called an ‘Initialization Vector’. This
‘Initialization Vector’ would then also be included in the data sent
to the receiver. The XP’s we implement in this Chapter which use the
MS CryptoAPI use CBC mode, but do not allow ‘Initialization
Vector’s’ to be set. We leave this as an exercise for the user.
A stream cipher as we have mentioned already operates on a bit at a
time. The simplest implementation of a steam cipher is where each
incoming bit of the data to encrypt is XORed with a stream of bits
from a so-called ‘Keystream Generator’. The receiver can then
decrypt the data, by performing the same XOR operation with the
encrypted bit stream and the same stream of bits from the keystream
generator. In fact if we could produce a completely random stream of
bits both in sync at the sender and receiver, then we have a
completely secure encryption algorithm. This is called a ‘One-Time
Pad’. The problem with this mechanism is that it is very hard to
produce truly random data, plus the sender and receiver must have
the same random stream available to them, plus none of the bits of
the pad can be reused. This need for synchronisation limits its
usefulness to low bandwidth connections. In reality the bits
generated by the keystream are pseudo-random and are dependent on
the session key used.
Each encryption algorithm implemented in this chapter uses the
syntax ‘XP_AlgorithmName_ENCRYPT’ and the corresponding decryption
XP uses the syntax ‘XP_AlgorithmName_DECRYPT’.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
