September 13, 2012 at 7:40 am
hi
I practically hit the wall, I am new to sql and I want to know how can i store a password in the database by encrypting it.
I want to use insert into a "table_name", then retrieve the password by using select form "table_name"
New kid on the block
September 13, 2012 at 8:47 am
Sounds like two way column encryption
This should help
September 13, 2012 at 9:25 am
Generally when encrypting a password you will use an algorythmn (spelling...) like SHA1
The point of this is it is a one way encryption. When you come to validate it, you take the entered password and encrypt it using the same key and if the hash result matches then the right password is entered. It is theoretically possible for two passwords to give the same key the likelyhood is in the 1 in billions range.
Why do you need to be able to unencrypt the encoded data?
September 13, 2012 at 9:38 am
And some examples for you:
--Two way example
--http://www.databasejournal.com/features/mssql/article.php/3714031/SQL-Server-2005-Encryption-types.htm
-- EncryptByPassPhrase(@password,@DataToEncrypt )
select EncryptedData = EncryptByPassPhrase('My$ecret|<ey', '123456789' )
-- DecryptByPassPhrase(@password,@DataToDecrypt )
declare @val varbinary(max)
SET @val = 0x0100000084F9C2DF2BC4518DFDA73E9C054E709F4039E7B831FF695F12621BEC05509E34
select convert(varchar(100),DecryptByPassPhrase('My$ecret|<ey',@val))
--one way example: compare the hash:
declare @val varbinary(max)
SELECT @val = HashBytes('SHA1','MySecret Phrase')
SELECT
CASE
WHEN @val = HashBytes('SHA1','MyOther Phrase')
THEN 'True'
ELSE 'FALSE'
END
Lowell
September 13, 2012 at 9:40 am
thank a lot
New kid on the block
September 13, 2012 at 9:48 am
aaron.reese (9/13/2012)
...Why do you need to be able to unencrypt the encoded data?
Interesting point - if you can't decrypt it, is there any argument for keeping it?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 13, 2012 at 1:34 pm
If you can't unencrypt it, is there any point in keeping it
Yes, an example is in my original post; where you convert another value and compare the results. Most website user logons (SSC for example) don't store your password. If you forget it they will set a temporary one, but they can't tell you yours.
There are cases where you need to encrypt data and decrypt it again (e.g. stored credit card numbers) but it is a PITA because you have to decrypt it every time you want to display it in a form, on a report etc. But it does mean that you can't just select field from table if you get hacked. The only way to read the value is with the encryption secret key.
September 13, 2012 at 1:57 pm
aaron.reese (9/13/2012)
If you can't unencrypt it, is there any point in keeping it
Yes, an example is in my original post; where you convert another value and compare the results. Most website user logons (SSC for example) don't store your password. If you forget it they will set a temporary one, but they can't tell you yours.
There are cases where you need to encrypt data and decrypt it again (e.g. stored credit card numbers) but it is a PITA because you have to decrypt it every time you want to display it in a form, on a report etc. But it does mean that you can't just select field from table if you get hacked. The only way to read the value is with the encryption secret key.
Just to add to Aaron's response. Make sure that if you use encryption you salt your "secret key". Not salting your encryption is dangerous, that is what allowed the recent crack at LinkedIn.
Encrypt your passwords using a one-way hash with salt. Then when attempting to login, you calculate the same one-way hash and compare that to your stored value. If they match, you are good. If not, the password was incorrect. Actually website like SSC do store your password. In fact, any website that authenticates you stores your password. At SSC and most other sites the value is encrypted before it is stored. This is why it can't be recovered, it is a one-way hash. It is not stored in plain text.
DO NOT STORE CREDIT CARD NUMBERS unless you are very very very certain that you know what you are doing. Storing credit card numbers is NOT something to take lightly. You are subject to massive repayments if the data you are storing is hacked. You must be PCI compliant and have evaluations once a year that your process is secure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy