September 17, 2002 at 8:29 am
Hi all!
(Posted this in the T-SQL forum as well, dunno if it's off-topic here)
I just learned the-hard-way that the SQL server function ENCRYPT has stopped working (right?), so I need to replace it with something.
I do not wish to use pwdencode, to prevent stumbling upon the same problem in future releases.
So, is there any simple way using T-SQL to acomplish the same thing (hash, one way, encryption)? Could SHA or MD5 be written in T-SQL?
Or do you have any suggestions of how to write an SP myself that does this. Would checksum do the job for me?
I cannot install DLL:s or other software since this is run on a shared database server. Also, I do want the encryption to be done in the database, not in ASP/components.
Thanks a bunch!
/Fredrik
September 17, 2002 at 8:42 am
don't have a solution here. You could implement it using any algorithm, but the key management will be the weak link. You'd have to store it somewhere.
Check Google, or even email NGSSoftware and ask if they have an algorithm written in T-SQL. Shouldn't be hard to make a UDF. Not sure how to store the key, maybe encrypt the UDF and remove select rights from syscomments for public.
Steve Jones
September 17, 2002 at 9:25 am
Thanks for the advice.
The thing is I'm looking for a one-way encryption, so then I don't need a key (right?). I don't need to un-encrypt it, I just want to match the stored encrypted value (a password) to another value (the same password) using the same one-way encryption.
I understand there are algorithms that does this called hashes, like SHA (Secure Hash Algorithm) or MD4, MD5 etc...
/Fredrik
September 17, 2002 at 2:20 pm
Yes there are one way hashes. MD5 is well known and here are some implementaitons. No SQL yet
http://userpages.umbc.edu/~mabzug1/cs/md5/md5.html
Steve Jones
September 18, 2002 at 12:48 am
quote:
Also, I do want the encryption to be done in the database, not in ASP/components.
Just curious, why do you need to do the encryption in SQL? That means that anyone sniffing your network will still be able to see the passwords un-encrypted.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
September 18, 2002 at 3:22 am
Yeah, you're right.
But, this is a web app, so it's possible to sniff between the client and the server anyway, right?
It was done this way (with ENCRYPT function in SQL) before I layed my hands on it, and there are no intentions to increase the security, just to fix the problem.
Also, one reason for doing it in the database is that I want to keep it simple to maintain. And if I could simplify it even more by using a generic key/machine-independent hash instead it would make things even easier.
I'm really suprised that there are no hash encryption SP:s to download? Are they impossible to write? They should be everywhere...
/Fredrik
October 8, 2002 at 8:17 am
They're not impossible to write in T-SQL, just difficult, because T-SQL doesn't provide the variety of operators provided by a full-blown programming language. For example, hashing and encryption algorithms often rely upon bit-shifting; most programming languages provide a standard operator for doing bit-shifts, but if you want to do it in T-SQL, you have to multiply or divide by 2. If you want to move the uppermost bits to the lowermost positions when you do the bitshift, that adds additional complexity. The point is T-SQL is just not rich enough in features to implement this in an easy fashion. Additionally, hashing and encryption algorithms written in T-SQL almost certainly would not perform with the same level of performance as the same code written in a traditional programming language, such as C.
Having said that, the request for some sort of encryption algorithm pops-up so frequently on T-SQL forums, someone could probably make some good money by developing a T-SQL implementation of common hash algorithms.
Matthew Burr
October 8, 2002 at 8:57 am
Here's a script I use for RC4 encryption....
for what its worth....I don't remember where I even got....maybe it will be useful for you.
If Exists (Select * From sysobjects Where name = N'DataProtect' And user_name(uid) = N'dbo')
Drop Procedure dbo.DataProtect
Go
Create PROCEDURE DataProtect
(@product varchar(255),
@productCode varchar(32),
@productName varchar(255) OUTPUT)
AS
-- Eliminate rowcount messages.
SET NOCOUNT ON
-- Declare variables.
DECLARE
@state smallint,
@key smallint,
@tempSwap smallint,
@a smallint,
@b-2 smallint,
@N smallint,
@temp smallint,
@i smallint,
@j-2 smallint,
@k smallint,
@cipherby smallint,
@cipher varchar(255),
@code varchar(64)
-- The RC4 algorithm works in two phases, key setup and ciphering.
-- Key setup is the first and most difficult phase of this algorithm.
-- During a N-bit key setup (N being your key length),
-- the encryption key is used to generate an encrypting variable using two arrays,
-- state and key, and N-number of mixing operations.
-- These mixing operations consist of swapping bytes,
-- modulo operations, and other formulas.
-- A modulo operation is the process of yielding a remainder from division.
-- This implementation of RC4 uses temporary tables in place of arrays.
CREATE TABLE #Keys
(
ID smallint ,
state smallint ,
intKey smallint
)
-- Initialize variable values.
SET @code = @productcode + ',./;''p[\`0-=Z<?L:"P{|~!@#%^*(_'
SELECT
@N = Len(@code),
@a = 0
-- Initialize temp table with key and state values.
WHILE @a < 256
BEGIN
SELECT
@key = Ascii(Substring(@code, (@a%@N) + 1, 1)),
@state = @a
INSERT #Keys(ID, state, intKey) VALUES(@a, @state, @key)
END
-- Initialize variable values.
SELECT
@b-2 = 0,
@a = 0
-- The state array now undergoes 256 mixing operations.
WHILE @a < 256
BEGIN
SELECT @b-2 = (@b + state + intKey) % 256, @tempSwap = state
FROM #Keys
WHERE ID = @a
UPDATE #Keys
SET state = (SELECT state FROM #Keys WHERE ID = @b-2)
WHERE ID = @a
UPDATE #Keys
SET state = @tempSwap
WHERE ID = @b-2
END
-- Initialize variable values.
SELECT
@i = 0,
@j-2 = 0,
@a = 1,
@cipher = '',
@cipherby = 0
-- Once the encrypting variable is produced from the key setup,
-- it enters the ciphering phase,
-- where it is XORed with the plain text message to create and encrypted message.
-- XOR is the logical operation of comparing two binary bits.
-- If the bits are different, the result is 1.
-- If the bits are the same, the result is 0.
-- The string is decrypted by XORing the encrypted message with the same encrypting key.
WHILE @a < Len(@product) + 1
BEGIN
SET
SELECT
@i = (@i + 1) % 256,
@j-2 = (@j + state) % 256,
@temp = state
FROM #Keys
WHERE ID = @i
UPDATE #Keys
SET state = (SELECT state FROM #Keys WHERE ID = @j-2)
WHERE ID = @i
UPDATE #Keys
SET state = @temp
WHERE ID = @j-2
SELECT @k = state
FROM #Keys
WHERE ID = (((SELECT state FROM #Keys WHERE ID = @i) + (SELECT state FROM #Keys WHERE ID = @j-2)) % 256)
SET @cipherby = Ascii(Substring(@product, @a, 1)) ^ @k
SET @cipher = @cipher + Char(@cipherby)
END
-- Clean up.
DROP TABLE #Keys
-- Set ouput variable.
SET @productName = @cipher
SET NOCOUNT OFF
Go
October 14, 2002 at 7:10 am
Well, if you can install a .DLL on the server then you could create an Extended Stored Procedure to call C code. I know that
SHA etc have been written in C. And that keeps it on the backend.
October 14, 2002 at 9:05 am
There is no TSQL equivilent in SQL 7 like is being stated here, SPs work and XPs work but you cannot use them inline so technicall SELECT sp_encrypt('ssss') doe you no good. However if you are running SQL 2K then you can use Functions. But the RC4 thing I am not sure does what it says, I will try to test and see if it matches with the correct RC4 hash to be sure. The problem is when dealing with source of code on encryption you cannot be 100% sure it does what it says (sorry not trying to offend but this is a graion of salt fact you have to be carefull of).
Now as for encrypting the data into the database it is considered poor to not enforce an encrypted data stream or at the point of origination instead of backend. Also, encrypting data into the database means it will take up more space in generally all encryption cases, so you are noit utilizing resources efficiently.
What is being encrypted and why. Also, if this is similar to Credit Card data, then by acknowledging you don't encrypt it until the backend you may be setting yourself up for legal liability should anyone discover the stream is open (just needed to throw that in as most people forget it).
Without a proper environment and not being able to add XP's to the server I would say the RC4 is probably your only bet but again this needs to be checked against a valid RC4 executeable to verify it and its strength.
If any of this sounds harsh, sorry but I have had three experiences with bad security due to someone not implementing a good solid encryption layer and using code they found without verifying. Not trying to be rude.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 14, 2002 at 1:28 pm
I don't think your being harsh, Antares686, simply pragmatic, and quite honestly, I watch for responses to posts I have made to see what others have encountered that maybe I haven't or things I can improve. (Could be that I'll learn something.....hopefully....)
If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply