encrypt(string) Question!!

[Russ Reynolds]

SQL Server 2000:

################################################## ######
I run the following as a normal query from Analyzer:
################################################## ######

SELECT encrypt(user_password) FROM emp WHERE user_id = 1

################################################## #######
I run the following query from inside a stored proc:
################################################## #######

SELECT encrypt(user_password) FROM emp WHERE user_id = 1

################################################## #######
Question????
################################################## #######

If the data inside the emp table does not change, how can these two
queries return different values?

Any help would be much appreciated!

thanks,
Russ

[James Goodwin]

> SELECT encrypt(user_password) FROM emp WHERE user_id = 1

SELECT encrypt(user_password) FROM emp WHERE user_id = 1
If the data inside the emp table does not change, how can these two
queries return different values?

They return different values because the encrypt function 'salts' the data
to prevent someone from just encrypting a bunch of stuff to figure out the
other data in the table.

The Unix crypt function used to do this by putting two random characters on
the front of the data string and also on the front of the encryption string
using the 'salt' as part of the key.

Regards,
Jim

[David Portas]

In addition to James's reply, note that the Encrypt function is undocumented
so its behaviour can change between versions of the product. Don't rely on
it in production code. Generate a password hash client-side would be my
suggestion.

--
David Portas
SQL Server MVP
--

[Greg D. Moore \(Strider\)]

"David Portas" <RE****************************@acm.org> wrote in message
news:0e********************@giganews.com...

In addition to James's reply, note that the Encrypt function is undocumented so its behaviour can change between versions of the product. Don't rely on
it in production code. Generate a password hash client-side would be my
suggestion.

And in the at least one case I looked at, trivial to decrypt.

--
David Portas
SQL Server MVP
--