Encrypt/Decrypt FUncionality
-
I have the need to encrypt/decrypt a value to be placed in a table in SQL server. I can do this via VBScript and/or VB as as function but that causes me to maintain two different sorts of procedures. One in ASP and on in VB.
I would like to do this in a SQL Server stored procedure but am having difficulty going about it and would like as much help as I can get from anyone who can. The table structure and encryption function is included below. Please keep in mind that this is a very small portion of the big picture.
Just paste the script in between the proper header tags on an html page and it will work.
***** Table ******************
CREATE TABLE dbo.Repository_Main (
mainID int IDENTITY (1,1) not null, -- The record ID
mainLoginName varbinary(275) not null, -- The actual login name (encrypted)
mainPW varbinary(275) not null, -- This is PW (encrypted)
mainAccessLevel integer, -- Defines the managerial level needed to access this login information (<= current id to access)
mainServerName varchar(50), -- The DNS user friendly name of the server
mainServerIP varchar(15), -- IP address of the server
mainAppName Varchar(50), -- If the id and password is for an application then this is it.
mainRole integer, -- The rtole of this id SQL DBA, UNix DBA wetc
mainSystemID integer, -- Defines what system this login is for
created_by varchar(50), -- The creator of this record
)
*****************************
<SCRIPT LANGUAGE="VBScript">
<!--
Dim a,CodeKey,DataIn
Dim strCodeKey
a = InputBox("Please enter password", "Password to be encrypted")
strCodeKey = InputBox("Please enter your Key", "XOr Encryption Key")
Dim lonDataPtr
Dim strDataOut
Dim intXOrValue1
Dim intXOrValue2
Dim test
Dim y
Dim temp
Dim tempstring
Dim test2
CodeKey=strCodeKey
DataIn=a
For lonDataPtr = 1 To Len(DataIn)
intXOrValue1 = Asc(Mid(DataIn, lonDataPtr, 1))
intXOrValue2 = Asc(Mid(CodeKey, ((lonDataPtr Mod Len(CodeKey)) + 1), 1))
temp = (intXOrValue1 Xor intXOrValue2)
tempstring = Hex(temp)
If Len(tempstring) = 1 Then tempstring = "0" & tempstring
strDataOut = strDataOut + tempstring
Next
document.write(strDataOut &" Encrypted Data***********")
strCodeKey = InputBox("Please enter your Key", "XOr Decryption Key")
CodeKey=strCodeKey
DataIn=strDataOut
lonDataPtr = 1
strDataOut=""
For lonDataPtr = 1 To (Len(DataIn) / 2)
y=(Mid(DataIn, (2 * lonDataPtr) - 1, 2))
intXOrValue1 = CLng("&H" & y)
intXOrValue2 = Asc(Mid(CodeKey, ((lonDataPtr Mod Len(CodeKey)) + 1), 1))
strDataOut = strDataOut + Chr(intXOrValue1 Xor intXOrValue2)
Next
document.write( strDataOut &" Decrypted Data")
-->
</SCRIPT>
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
Hi David,
Don't know if you knew but SQL Server has it's own (A little limited) Encryption routines 'built in'. Have a look at this in QA.
pwdencrypt encrypts your password.
pwdcompare will check if the two passwords are the same returning a 1 for equal and a 0 for different.
The only real drawback is - I dont think it is case sensitive, so forcing letters and numbers are the order of the day......
DECLARE @EncryptedPIN varbinary(255)
DECLARE @PINtoEncrypt varchar(255)
DECLARE @tester varchar(255)
SET @tester = 'Test'
SET @PINtoEncrypt = 'Test'
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@PINtoEncrypt))
SELECT pwdcompare(@TESTER, @EncryptedPIN, 0)
Have fun
Steve
We need men who can dream of things that never were.
-
Forgot to mention - these are
"undocumented" features. Use at your own risk....... 
Have fun
Steve
We need men who can dream of things that never were.
-
There is but a few problems.
1. I need to be able to decrypt and display the password to the user/administrators of the system.
2. I need to have it case sensitive. DaViD is different than DAVID. I need to be sure it wil store the data correctly.
3. I also found these function on-line and they were mentioned mostly with SQL-7. I am using SQL Server 2000. There were also mention many quirks and issues associated with the functions you mentioned.
Kindest Regards,
David** Obstacles are those frightening things that appear when we take our eyes off the goal. **
-
Hi David,
Yes there are limitations and 'quirks' involved with sequels encryption functionality. I haven't yet found a way to decrypt......
Rather than maintaining two different procedures, would it not be easier to wrap the encryption / decryption code into a dll, then call it from a UDF when required?
Have a quick look at "user-defined functions, MDX" in BOL.
Have fun
Steve
We need men who can dream of things that never were.
-
/*If you desire to use encryption/decryption logic for password,
then have a look at the following, hope this serves your purpose
*/
Create Table Password
([Password] binary(255))
insert into Password
Select encrypt('Vivek')
Union All
Select encrypt('vIvek')
Union All
Select encrypt('viVek')
/*All three records inserted have different binary codes,
which ensures its case sensitive nature, when compared during login authentication
*/
Select * from Password
/*
In order to decrypt, i have written simple query statement,
i think no need to explain that. Your Administrator can view now...
*/
SELECT cast(substring(password,1,1) as char (1))
+ cast(substring(password,3,1) as char (1))
+ cast(substring(password,5,1) as char (1))
+ cast(substring(password,7,1) as char (1))
+ cast(substring(password,9,1) as char (1))
+ cast(substring(password,11,1) as char (1))
+ cast(substring(password,13,1) as char (1))
FROM Password
Vivek K. Gupta
/**A strong positive mental attitude will create more miracles than any wonder drug**/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply