1

So I followed this guide here http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx on how to setup encryption for my DB. All went fine on that side.

I'm trying to get some of the data that has been encrypted to be displayed now. I'm able to call data that is un-encrypted fine but I cannot seem to work out how to use the symmetric key to decrypt the encrypted data and display on the page.

Originally I thought I should use a stored procedure and let that catch the values but not entirely sure how to implement it. 

echo "This is the database userId field: " . $row->userId . "<p>";
echo "This is the database email field: " . $row->email . "<p>";
echo "This is the database encryptedpassword2 field: " . $row->encrypteddata . "<p>";

This is how I am calling data but unsure on how to get the encrypteddata field to be passed through my key and then be displayed. At the moment it just shows random characters.

I'm somewhat new to SQL encryption so I hope I've managed to explain myself somewhat okay.

Thanks

xalx
  • 47
  • 5

1 Answers1

1

Generally speaking, you should not have a need to decrypt a password field. You only need to hash the user's input and compare that to the previously hashed password to see if the hashes match. Please take a minute to understand the difference between hashing and encryption.

If you really want to get encrypted info out of the DB (hopefully not passwords), you should call DecryptByKey in your SQL statement (as is mentioned in your linked article) before they are returned to PHP.

Community
  • 1
  • 1
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • Thanks for the references, I think I understand that if for logging in you take the cleartext submitted apply the hashing algorithm used and then compare results if it is a password match. However if a key is being used to encrypt the data on the DB side, then how do you use this key on the submitted cleartext? Is this achieved with the help of a prepared statement? – xalx Jul 18 '12 at 15:28
  • Yes, you would have to have the DB encrypt the submitted text. You could create a proc to do this and the compare, or you could even have an extra column in your table with the encrypted last attempt if you wanted to compare outside of the DB. – Tim Lehner Jul 18 '12 at 15:31
  • I'm sorry, I am not too sure what you mean by a table with the encrypted last attempt. Would you mind elaborating on that? – xalx Jul 18 '12 at 15:45
  • You could insert the user's text into the table the same way you initially stored the password. At that point, you could compare the varbinary fields in any level of your app. It's really not a necessary thing, just another suggestion that you might find more convenient to implement. – Tim Lehner Jul 18 '12 at 15:56