July 15, 2001 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bknight/encryptfunction.asp
Brian Knight
Free SQL Server Training Webinars
July 15, 2001 at 11:01 pm
Encrypt function in T-SQL is fine what about Decryption of encrytion columns.
kindly tell us how to do it!
July 16, 2001 at 7:23 am
quote:
Encrypt function in T-SQL is fine what about Decryption of encrytion columns.kindly tell us how to do it!
As I mentioned in the article, the only way to decrypt is with a comparion techinque with a WHERE clause. That's why if you would like to use a more advanced encryption/decryption system, you may have to resort to COM. Les Smith will be having some excellent articles coming out this week and next about how to use this.
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars
July 19, 2001 at 11:32 am
Supposing im creating a personnel database and want to store national insurance numbers etc , i want this to be encrypted in the table., But throught the client application front end , a personnel details administrator should be able to see the unencrypted data .. how would i do this ?
Santosh Benjamin
Santosh Benjamin
July 30, 2001 at 12:49 pm
This function does not exsist in 2000.
So inless your living in the Stone age move on.
July 31, 2001 at 10:40 am
quote:
Supposing im creating a personnel database and want to store national insurance numbers etc , i want this to be encrypted in the table., But throught the client application front end , a personnel details administrator should be able to see the unencrypted data .. how would i do this ?
Your best bet then would be to create some type of custom middle-ware that encrypts/decrypts your data. Les Smith (http://www.sqlservercentral.com/columnists/lsmith) has developed a few methods on doing this. You can use these to get ideas. You could possibly create a user defined function to do something like this as well. Possibly wrap an extended stored procedure into it to encrypt/decrypt it.
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars
August 7, 2001 at 3:49 am
I'm suprised noone has mentioned xp_crypt :-
August 7, 2001 at 8:18 pm
Now that's a cool tool. Frebbie's not too bad, and $98.00 for the full version seems a fair price for someone who needs this functionality! Are you connected with the author?
August 9, 2001 at 10:36 am
quote:
Are you connected with the author?
No, in fact I haven't even tried using it myself yet. I cant even remember when I found it, but have keep a bookmark to it, I'm sure it will come in usefull one day.
September 12, 2001 at 3:47 am
I have little doubt about this. Though data is encrypted, that can be read easily.
If I inserted data like 'app' with encryption, that is shown as 'a[Odd Char]p[Odd Char]p[Odd Char]'.
I highly appreciate your reply.
September 15, 2001 at 7:49 am
Hi guys,
Nice to meet you here,
I'm author of xp_crypt(www.vtc.ru/~andrey/xp_crypt). You can easily encrypt with strong RSA encryption all what you want just with simple sql scripts.
On my web page, i included all needed examples. And if you dont need encrypt strings longer then 21 chars and with key length more then 256 bits , so for you its free
Besides, it contains DES and SHA1 hashes with unlimited string length.
Thank you for attention.
Edited by - Andrey Kubyshev on 09/15/2001 07:51:13 AM
Edited by - Andrey Kubyshev on 09/15/2001 07:53:25 AM
February 21, 2002 at 5:08 pm
La encripción que hace esa función es muy fácilmente decifrable, como para usarla en cualquier ambiente. Solo guarda los caracteres en hexadecimal (2 bytes en hexa para cada uno, donde el segundo es 00) Lo que pasa al hacer select, es que solo ves el primer byte. Si te fijas, (en tu ejemplo) al hacer:
select * from users where UserPW=0x5400650073007400500057003200
T e s T P W 2
te devuelve:
TestUser2T
Me parece muy malo que recomiendes esto como método de encripción.
Rafael Picchi
Argentina
May 13, 2003 at 12:44 pm
This may be useful, feedback is certainly most welcome.
1) How does one perform validation processes in SQL2K?
Below is a script that demonstrates a column that has accepted encrypted values, it then returns a row based on input that undergoes an encryption.
THIS IS NOT SUPPORTED BY MICROSOFT >>WAS IT EVER???<<
<I hope it fits>
SET NOCOUNT ON
GO
/*
Author:Shaun Tinline-Jones
Create Date:2003/05/13
Purpose:
Testing some logic around the Encrypt function
*/
USE Northwind
GO
IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Test_Encrypt'), N'IsTable') = 1
DROP TABLE dbo.Test_Encrypt
GO
-- We need to store the values in a field that holds the result of the encryption
CREATE TABLE dbo.Test_Encrypt
(
Gambler sql_variant NOT NULL
--Gambler nvarchar(25) NOT NULL
)
GO
/**************************** We have some gamblers *********************************/
DECLARE @Name nvarchar(25) --sql_variant
SELECT @Name = ENCRYPT(N'Shaun')
INSERT INTO dbo.Test_Encrypt(Gambler) VALUES (ENCRYPT(@Name))
INSERT INTO dbo.Test_Encrypt(Gambler) VALUES (ENCRYPT(N'Grant'))
INSERT INTO dbo.Test_Encrypt(Gambler) VALUES (ENCRYPT(N'Jacye'))
SELECT Gambler FROM dbo.Test_Encrypt
GO
/******************************** Now let's get that winner ****************************/
DECLARE @Winner nvarchar(25)
SET @Winner = N'Shaun'
SELECT N'and the lotto winner is.......' + @Winner FROM dbo.Test_Encrypt WHERE Gambler = ENCRYPT(@Winner) --@Encrypted_Winner
GO
SET NOCOUNT OFF
GO
2) How does one deal with the upgrade?
The encrypt function is now, correctly stated by Jacye, using the windows CryptoAPI. So yes it is different from other versions of SQL Server and also suffers the same exposure to cracking>>Whatever that may be<< as this API .
The best is to use a query that takes a first time user, that is first time since upgrade, check it against a SQL Server 7.0 with the password table. If it succeeds, run customer created encryption algorithm, even if it is the straight Windows CryptoAPI, (as opposed to the SQL2K function) and store the result in the SQL Server instance and record that the user has upgraded.
This will handle the upgrade in a controlled fashion, while remaining transparent to the user community as well as protect the customer from the possible deprecation of the ENCRYPT function.
May 15, 2003 at 11:21 am
quote:
La encripción que hace esa función es muy fácilmente decifrable, como para usarla en cualquier ambiente. Solo guarda los caracteres en hexadecimal (2 bytes en hexa para cada uno, donde el segundo es 00) Lo que pasa al hacer select, es que solo ves el primer byte. Si te fijas, (en tu ejemplo) al hacer:select * from users where UserPW=0x5400650073007400500057003200
T e s T P W 2
te devuelve:
TestUser2T
Me parece muy malo que recomiendes esto como método de encripción.
Rafael Picchi
Argentina
May 15, 2003 at 11:23 am
quote:
La encripción que hace esa función es muy fácilmente decifrable, como para usarla en cualquier ambiente. Solo guarda los caracteres en hexadecimal (2 bytes en hexa para cada uno, donde el segundo es 00) Lo que pasa al hacer select, es que solo ves el primer byte. Si te fijas, (en tu ejemplo) al hacer:select * from users where UserPW=0x5400650073007400500057003200
T e s T P W 2
te devuelve:
TestUser2T
Me parece muy malo que recomiendes esto como método de encripción.
Rafael Picchi
Argentina
Try this one to confirm your idea:
SET NOCOUNT ON
SELECT ENCRYPT('TestPW1')
SELECT ENCRYPT('TestPW2')
SELECT ENCRYPT('TestPW3')
SET NOCOUNT ON
SELECT ENCRYPT('TestPW1')
SELECT ENCRYPT('UestPW1')
SELECT ENCRYPT('VestPW1')
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply