views:

147

answers:

2

Unfortunately, I have plaintext passwords in a database. I want to pass these plaintext values around as little as possible for, say, comparisons and updates. To this end, I'm trying to create a view of my Users table that excludes the plaintext passwords and instead provides a hashed value of that password.

Here's my current SQL Server view, which doesn't work:

SELECT CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) AS PasswordSalt
       HashBytes('SHA1', PasswordSalt + u.Password) AS PasswordHash
FROM dbo.Users AS u

I'd be happy to hear about alternatives to this approach, but otherwise the problem seems to be concatenating the virtual column PasswordSalt with.. anything. For instance, the following simple view works:

SELECT u.Login AS Column1, u.Login + 'b' AS Column2

but this one does not:

SELECT u.Login AS Column1, Column1 + 'b' AS Column2

The error I'm receiving from Management Studio is

Invalid column name 'Column1'.

Thanks in advance for any ideas about what I'm doing wrong!

+1  A: 

The problem is occurring because the FROM clause of your statement indicates that the data to be selected comes from the Users table, but the SELECT part references a column named PasswordSalt. SQL Server cannot find a column with this name on the Users table, hence the error.

Alternative approach may be to generate the Salt in a subquery. For example

SELECT x.PasswordSalt,  HashBytes('SHA1', x.PasswordSalt + x.Password) AS PasswordHash FROM ( SELECT  CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) AS PasswordSalt, Password FROM dbo.Users) x

Julian Martin
That did the trick! Thanks for the elegant solution!
ladenedge
A: 

What about

SELECT CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) AS PasswordSalt 
       HashBytes('SHA1', CAST(CAST(32768 * RAND() AS INT) AS NVARCHAR) + u.Password) AS PasswordHash 
FROM dbo.Users AS u 
unclepaul84
This one seems problematic because I need to ensure that the *PasswordSalt* virtual column contains the same value that is hashed with the password. If they ever differ, consumers won't be able to verify the passwords match.
ladenedge
I believe that no matter how many times you call Rand() it will return same value for each row in the set
unclepaul84