Encrypting Data With the Encrypt Function

Question

Encrypting Data With the Encrypt Function

Viewing 13 posts - 16 through 27 (of 27 total)

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

rpicchi SSC Enthusiast Points: 155 More actions · Answer 1

Probé en un sql 2000 SP2 y anda mal (la encripción es la que yo comenté anteriormente y no debe usarse).

En cambio, en un sql 7.0 SP4, aparentemente anda ok.

Lo que me parece que no es muy bueno en basarse en funciones no documentadas como ENCRYPT(), viendo que cambian con las versiones del motor.

Saludos,

Rafael Picchi

rafap@uol.com.ar

quote:
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
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')

nicerguy Grasshopper Points: 11 More actions · Answer 2

What the hell are you thinking? You guys are just converting the string to a double-byte character string and type-casting it as a numerical. Hello?

0x5400 = 84 = 'T'

0x6500 = 101 = 'e'

0x7300 = 115 = 's'

0x7400 = 116 = 't'

0x5000 = 80 = 'P'

0x5700 = 119 = 'w'

0x3100 = 49 = '1'

Andy Warren SSC Guru Points: 119902 More actions · Answer 3

Please keep your comments professional. Disagreeing is fine. Being disrespectful is not. Thanks.

Andy

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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Oskar Austegard-253928 SSC Enthusiast Points: 151 More actions · Answer 4

Uhm - executing this in SQL 2000

select ENCRYPT('abc123')

yields

0x610062006300310032003300

So I'd have to say you're wrong.

Oskar Austegard-253928 SSC Enthusiast Points: 151 More actions · Answer 5

Oskar Austegard-253928

SSC Enthusiast

Points: 151

May 30, 2006 at 10:41 am

#640737

Yikes. So much for that idea, then.

JunkMail Victim Old Hand Points: 392 More actions · Answer 6

Hi:

Is there a reference link that explains Microsoft's support limitations with the encrypt function? Also, is it the same encryption used to encrypt stored procedures.

Thanks.

Oskar Austegard-253928 SSC Enthusiast Points: 151 More actions · Answer 7

JunkMailVictim: As nicerguy crudely points out, the ENCRYPT function does NOT encrypt anything, it merely hex ENCODES the string, which is quite useless from a security standpoint. If you need encryption it looks like the free version of xp_crypt would be the way to go.

JunkMail Victim Old Hand Points: 392 More actions · Answer 8

Hi Oskar:

Thanks for the reply. In my organization we have some people who are looking into adding the encrypt clause to stored procedures prevent tampering. This may prove reasonable, but perhaps not. I guess what I'm looking to know are a few things about the encrypt clause:

A recent SQL Server Central article says that Microsoft does not provide support for it. I'm wondering where I can find Microsoft's statement to that effect.
If this is in fact encoding, and not encryption where could I go to corroborate this?
If you turn on the encrypt clause will applications running those stored procedures go belly-up or will SQL Server handle it seamlessly.
Lastly, can this be applied to triggers without issue?

Thanks for the expertise.

Oskar Austegard-253928 SSC Enthusiast Points: 151 More actions · Answer 9

Crap. I had an eloquent reply to this, but Firefox/sqlservercentral swallowed it.

Ok - I'm too lazy to type it all again, have a look at the sql and come to your own conclusions:

--See the pattern?

SELECT ENCRYPT (''), ENCRYPT ('a'), ENCRYPT ('ab'), ENCRYPT ('abc')

--Simple hex encoding of unicode values

SELECT ENCRYPT('a') / (0x0100 * 1), UNICODE('a')

SELECT ENCRYPT('b') / (0x0100 * 1), UNICODE('b')

--better, also undocumented alternative - for more use google

SELECT PWDENCRYPT (''), PWDENCRYPT ('a'), PWDENCRYPT ('ab'), PWDENCRYPT ('abc')

Oskar Austegard-253928 SSC Enthusiast Points: 151 More actions · Answer 10

For the record, PWDENCRYPT is also not secure:

http://www.theregister.co.uk/2002/07/08/cracking_ms_sql_server_passwords/

JunkMail Victim Old Hand Points: 392 More actions · Answer 11

That's quite a misnomer calling it encryption. Thanks for the example. I had fun with that.

JunkMail Victim Old Hand Points: 392 More actions · Answer 12

I can confirm what you're saying for the encrypt function, but not when encrypting a stored procedure. A true test of encryption over encoding is whether or not the same result is produced each time you perform the function.

I created several stored procedures to see what happens, using this simple bit of SQL code:

CREATE PROCEDURE "sp_Test" WITH ENCRYPTION AS
--This is a test
GO

Then I would read out the entry from the syscomments table, drop the stored procedure and repeat it. Each time, the entry was different. This looks like encryption to me. What do you think?

Thanks.

ikjeft01 SSC Journeyman Points: 96 More actions · Answer 13

encrypt is not a recognized function name in sql 2005. pwdencrypt() is, however