Display encrypted data from SQL DB

Question

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

I only want to display passwords for the moment to do some testing. I'm trying to demonstrate something to someone — xalx
– xalx, Commented Jul 18, 2012 at 15:14

Community · Accepted Answer · 2017-05-23 10:34:29Z

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.

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jul 17, 2012 at 17:46

Tim Lehner

15.3k4 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

xalx Over a year ago

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?

Tim Lehner Over a year ago

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.

xalx Over a year ago

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?

Tim Lehner Over a year ago

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.

Collectives™ on Stack Overflow

Display encrypted data from SQL DB

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related