We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How can i encrypt passwords stored in my database?

chrisV
chrisV asked
on
Medium Priority
1,949 Views
Last Modified: 2007-12-19
At present, my passwords are stored in my database in plain text.

There is a webpage on my intranet that runs a SQL query to display the usernames and password in a table. The usernames and password are entered into the database through the submission of a form.  This is the only things that hat are does with the passwords, i  mean i do not use them to authenticate users etc.

I am wondering is there a way I can encrypt them so that you cannot just run a quesry and get them .... and then a way to decrypts them to display them in the page on the intranet.

How to do this or a good tutorial about it would be great, thanks
Comment
Watch Question

Commented:
You will need to buy a third-party utility or get some security specalist company involved such as Baltimore (alternativly you could try people like Ronald Rivest -- inventor of MD5 for some suitable programs/licensing agreement).

Commented:
MD5 is free all you have to do is provide a txtfile that gives credit for it.

I store passwords in my DB all the time, as char(32) and they are hashed using MD5. MD5 creates a unique 128bit fingerprint for data provided to it. Usually used to guarentee files, it's also used as a one way hash for passwords.

The string "EE is Cool" will create this string
48f2ec5cdf2cbacbb2772e49591c6ce3

The string "EE is Cool " will create this string
51a206a293456bd2559e0111ac674840

It's mathimatically "very very very difficult" to reverse the hash to the original string.

So for all my passwords I md5sum the entry and store in the database a 32char string. Then to verify the password I take the users password input apply the md5 to that string and then compare the two hashed strings. Non match is bad password, match is correct password.

Commented:
http://userpages.umbc.edu/~mabzug1/cs/md5/md5.html

and it's not that difficult, they have ports almost everywhere...I ported the C version here to a C++ dll that I call from Visual Basic...but I understand you could also use the built in version in the WIN32 sdk

Commented:
of course if you wanted real encryption that I would do this

use blowfish to encrypt your string, and then apply a BASE64 encoding to it and store it as char() in the database. Blowfish can also be found easily
Top Expert 2004

Commented:
If you just want encryption for obscurity and you're not worried about access to the database, you could also do a convert on your data and change it to binary--then store it in your varchar field--like I said, this is mainly for display and not security.

select convert(varbinary(10),'arbert')      --returns 0x617262657274


select convert(varchar(10),0x617262657274)  --returns arbert




Brett

Commented:
I can't wait till we convert this to an xp_ using the CLR  in Yukon...

Author

Commented:
Thanks .... I will read up on MD5 and the other comments,

... the password are not that important really, i just want to impress the boss a bit (who knows very little about encryption and the like (just like me))

The only time the password will be used is inserting them in the database and then doing a select statement and displaying them on a prowser.

So if i could do something like apply some simple encryption into my INSERT statement to put the password in my database and then reverse apply the enryption in my SELECT statement to retrieve the password that woould be fine.

Is this easy/possible to do?

Commented:
yes....undocumented feature

DECLARE @Encrypt varbinary(256)
DECLARE @Decrypt varchar(256)
SET @Encrypt = (SELECT pwdencrypt('foo'))
PRINT @Encrypt
PRINT pwdcompare('foo',@Encrypt,0)
PRINT pwdcompare('foo1',@Encrypt,0)

type this in the query analyzer and see....

pwdencrypt(STRING) returns a varbinary(256) hash

pwdcompare(PASSWORD_TEST,HASHVALUE,0) returns 0 for false and 1 for match and true

Commented:
this works with SQL server 2000, and with all undocumented features this is here today, but may be gone tommorow....so third party is better...written by your team is even better....as in md5
Top Expert 2004

Commented:
It will be gone tomorrow (well, next year).  Those procs aren't in Yukon....

If you just want to insert jumbled data--do the convert statement to binary on the insert...
Top Expert 2004

Commented:
Take that back--just found it in Yukon--wrong server...Still undocumented procs/functions should be avoided.

Commented:
The official license agreement is here: http://www.ietf.org/ietf/IPR/RSA-MD-all
from Rons home-website - http://theory.lcs.mit.edu/~rivest/

Author

Commented:
g0rath, is that i stored procedure oe something, it's looks alien to me !?!
and ican't download anything form the net at work so i'll have to take a look at MD5 at home
____________
arbert,
how can i do:
select convert(varbinary(10),'arbert')

and
select convert(varchar(10),0x617262657274)

in ASP/VbScript

I when i tryed doing it, as above, i get an error:
Type mismatch: 'varbinary'
___________________
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Instead you could:

1) rename the table
2) create a view the same name as the existing table but *without* the password column

Give others only access to the view.  Then they can't even *see* the password, which is much safer.

Commented:
yes that is an undocumented stored procedures....which is why I provided the T-SQL so that you could see it's usage
Top Expert 2004

Commented:
"arbert,
how can i do:
select convert(varbinary(10),'arbert')

and
select convert(varchar(10),0x617262657274)

in ASP/VbScript"


How do you do your selects and inserts now?  Same way--against the connection object or with a stored proc....

Author

Commented:
g0rath i have never userd a stored procedure, or T-Sql before, so do i :
1) save:
____
DECLARE @Encrypt varbinary(256)
DECLARE @Decrypt varchar(256)
SET @Encrypt = (SELECT pwdencrypt('foo'))
PRINT @Encrypt
PRINT pwdcompare('foo',@Encrypt,0)
PRINT pwdcompare('foo1',@Encrypt,0)
____
as a stored procudure in SQL server called "encrypt"

2) when inserting the passsward do:
INSERT INTO table (password) VALUES ('" & encrypt(strpass) & "')

3) when retrieving the password do:
SELECT ....<how would I do this>
____________________________________________________________________________________
arbert, I tried it like this:
INSERT INTO table (password) VALUES ('" & convert(varbinary(10),strpass)  & "')

and
SELECT password FROM table ............
then do convert(varchar(10),<sql output>) on the results of the SQL statement

but i get an error on the INSERT statement:
Type mismatch: 'varbinary'

why is this?
________________________________________

Sorry if these are dump questions, but I really appreciate the help...



Top Expert 2004
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.