RC4 Encryption in a Stored Procedure

,

RC4 Encryption SP's/UDF's with TSQL calling activeX

These are stored procedures and functions to encrypt/decrypt

data using the RC4 or Rijndael encryption algorithm. This is an

interesting and useful example of calling COM objects with TSQL.

For learning purposes the examples are very simple and based

on a few lines of code, something similar to:

declare @hr int

exec @hr =sp_oacreate 'OLEComponent.Object', @object out

exec @hr =sp_oamethod @object, 'RC4ED', ReturnValue out,@parameter,@parameter

EXEC @hr = sp_OADestroy @object

The variable @hr will store the return value from the system

stored procedure call to ensure that the call was successful.

If the value is other than zero it means that an error occurred.

sp_oacreate will create an instance of the object previously created

with VB6 and properly registered as an ActiveX control. VB6 will

do that automatically when compiling the code. The next line of

code uses sp_oamethod which will call a method of the ActiveX

control. In this example, there are only two methods, encrypt

and decrypt but there could be properties too. Finally, sp_OADestroy

will destroy the instance we used before.

The system stored procedures used are the following:

sp_oacreate-Creates an instance of the OLE object.

sp_OAMethod-Calls a method of an OLE object.

sp_OADestroy-Destroys an instance of an OLE object.

Other useful ones:

sp_OAGetProperty-Gets a property value of an OLE object.

sp_OASetProperty-Sets a property of an OLE object.

sp_OAGetErrorInfo-Returns an error code from the latest OLE Automation

operation.

 

RC4 Encryption

Stored Procedures

Sp_RC4- Encrypts any data type input to a varchar data type.

Used to encrypt character data, such as text.

Sp_RC4Bin- Encrypts any data type input to a varbinary data

type. Used to encrypt binary data, such as numbers or pictures.

User Defined Functions

XRC4- Encrypts any data type input to a varchar data type.

Used to encrypt character data, such as text.

XRC4Bin- Encrypts any data type input to a varbinary data type.

Used to encrypt binary data, such as numbers or pictures.

Rijndael Encryption

Stored Procedures

Sp_EncRijndael- Encrypts any data type input to a varchar data

type. Used to encrypt character data, such as text.

Sp_DecRijndael- Decrypts any data type input to a varchar data

type.

Sp_EncRijndaelBin- Encrypts any data type input to a varbinary

data type. Used to encrypt binary data, such as pictures.

Sp_DecRijndaelBin - Decrypts any data type input to a varbinary

data type.

User Defined Functions

EncRijndael- Encrypts any data type input to a varchar data

type. Used to encrypt character data, such as text.

DecRijndael- Decrypts any data type input to a varchar data

type.

EncRijndaelBin- Encrypts any data type input to a varbinary

data type. Used to encrypt binary data, such as pictures.

DecRijndaelBin - Decrypts any data type input to a varbinary

data type.

It works by calling an activeX DLL which has to be created

first.

Files

RC4 files
Rijndael files

To create and use the activeX DLL:

1-Open the project in VB6.

2-Compile the DLL.

3-Copy the DLL to c:\winnt\system32

4-Register the DLL using the START MENU: START/RUN regsvr32 c:\winnt\system32\crypt.Dll

The stored procedures will work fine now.

RC4 code based on Eric Hodges' RC4 Implementation
http://www.planet-source-code.com/vb/scripts/showcode.asp?lngWId=1&txtCodeId=29691

He deserves the credit for it.

I used the code from Queen City Software - Rijndael Encryption

- to create the activeX dll

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=24820&lngWId=1

They deserve the credit for it.

Joseph Gama

Rate

Share

Share

Rate