Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


  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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter