Encrypting Data With the Encrypt Function

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bknight/encryptfunction.asp

  • Encrypt function in T-SQL is fine what about Decryption of encrytion columns.

    kindly tell us how to do it!

  • 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

    bknight@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bknight

  • 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

  • This function does not exsist in 2000.

    So inless your living in the Stone age move on.

  • 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

    bknight@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bknight

  • I'm suprised noone has mentioned xp_crypt :-

    http://www.vtc.ru/~andrey/xp_crypt/

  • 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?

  • 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.

  • 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.

  • 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

  • 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:

    TestUser2 T

    Me parece muy malo que recomiendes esto como método de encripción.

    Rafael Picchi

    Argentina

    rafap@uol.com.ar

  • 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.

  • 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:

    TestUser2 T

    Me parece muy malo que recomiendes esto como método de encripción.

    Rafael Picchi

    Argentina

    rafap@uol.com.ar


  • 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:

    TestUser2 T

    Me parece muy malo que recomiendes esto como método de encripción.

    Rafael Picchi

    Argentina

    rafap@uol.com.ar


    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 28 total)

You must be logged in to reply to this topic. Login to reply