encrypted password problem

[Ian Davies]

Hello
I would like to query the user table of the mysql database from my VB
application to check that a user's password entered in a text field on a
form corresponds to that users password in the mysql database. However, when
I retreive the password using an sql statement into a recordset, it is
encrypted. How can I decrypt it so I can make the comparison.
Ian

[Gordon Burditt]

>I would like to query the user table of the mysql database from my VB

application to check that a user's password entered in a text field on a
form corresponds to that users password in the mysql database. However, when
I retreive the password using an sql statement into a recordset, it is
encrypted. How can I decrypt it so I can make the comparison.

You DON'T. The encryption wouldn't be worth much if you could.
Try encrypting the password entered and see if the result matches
what's in the database.

Gordon L. Burditt

[Bill Karwin]

Ian Davies wrote:

Hello
I would like to query the user table of the mysql database from my VB
application to check that a user's password entered in a text field on a
form corresponds to that users password in the mysql database. However, when
I retreive the password using an sql statement into a recordset, it is
encrypted. How can I decrypt it so I can make the comparison.

We should never decrypt passwords stored in a database. Instead, we
should encrypt the string that a user enters, and then check if that
encrypted string matches the encrypted string that is in the database.

This way we never write code that can read a user's password from the
database. There is always a slim possibility that a bug in our code
might allow the wrong person to read that password. The best way to be
safe is never to write any code that can decrypt a password.

Password-encryption functions are normally one-way. That is, there is
no way to get the original string from the encrypted string. This is
preferable because one-way encryption is more secure, and for reasons
above, there's no need for code to decrypt the password.

Regards,
Bill K.

[Ian Davies]

I thought maybe that was the case
I presume that using the PASSWORD() function would encrypt my string so I
could compare
I didnt realise that the encryption would be the same each time

My problen now is that the string input by the user is in my VB application
and VB doesnt recognise PASSWORD().
How can I get mysql to encrypt the string from withing VB?
Ian

"Gordon Burditt" <go***********@burditt.org> wrote in message
news:11*************@corp.supernews.com...

I would like to query the user table of the mysql database from my VB
application to check that a user's password entered in a text field on a
form corresponds to that users password in the mysql database. However, whenI retreive the password using an sql statement into a recordset, it is
encrypted. How can I decrypt it so I can make the comparison.

You DON'T. The encryption wouldn't be worth much if you could.
Try encrypting the password entered and see if the result matches
what's in the database.

Gordon L. Burditt

[Malcolm Dew-Jones]

Ian Davies (ia********@virgin.net) wrote:
: I thought maybe that was the case
: I presume that using the PASSWORD() function would encrypt my string so I
: could compare
: I didnt realise that the encryption would be the same each time

: My problen now is that the string input by the user is in my VB application
: and VB doesnt recognise PASSWORD().
: How can I get mysql to encrypt the string from withing VB?

by using password().

For example (not tested, syntax likely wrong)

select
user_name ,
users_encrypted_password,
password($claimed_password) as claimed_password
from
all_users
where
user_name = $the_user_name

VB runs the above query and gets back a result. Examine the values in the
result set to see if there is such a user, and then compare the columns
users_encrypted_password with claimed_password to see if the password was
correct.

You can also get the encrypted value for use within VB using something
like

select password($claimed_password);

as above, you run that just like you run any other query from within VB
and get back a result set containing the calculated value.
--

This programmer available for rent.

[Bill Karwin]

Ian Davies wrote:

My problen now is that the string input by the user is in my VB application
and VB doesnt recognise PASSWORD().
How can I get mysql to encrypt the string from withing VB?

SELECT PASSWORD("string");

You can also do this:

SELECT IF(PASSWORD("string") = encryptedPasswordField, 1, 0) AS
password_is_correct
FROM tableStoringPasswords;

By the way, you should be aware of issues related to using the
PASSWORD() function in MySQL. Read the entry about PASSWORD() on this
web page: http://dev.mysql.com/doc/refman/5.0/...functions.html

"Note: The PASSWORD() function is used by the authentication system in
MySQL Server; you should not use it in your own applications. For that
purpose, use MD5() or SHA1() instead. Also see RFC 2195 for more
information about handling passwords and authentication securely in your
applications."

The recommendation against using MySQL's PASSWORD function for your
applications is that they can change the algorithm between versions of
MySQL (e.g. between 4.0 and 4.1 the encryption algorithm changed). That
could cause your application to break as you upgrade the MySQL software,
and the only solution would be to reset all your users' passwords, and
tell each user to go change their password.

I use MD5() when I need application-specific password encryption.

Regards,
Bill K.

[Ian Davies]

By the way, you should be aware of issues related to using the
PASSWORD() function in MySQL. Read the entry about PASSWORD() on this
web page: http://dev.mysql.com/doc/refman/5.0/...functions.html

"Note: The PASSWORD() function is used by the authentication system in
MySQL Server; you should not use it in your own applications. For that
purpose, use MD5() or SHA1() instead. Also see RFC 2195 for more
information about handling passwords and authentication securely in your
applications."

The recommendation against using MySQL's PASSWORD function for your
applications is that they can change the algorithm between versions of
MySQL (e.g. between 4.0 and 4.1 the encryption algorithm changed). That
could cause your application to break as you upgrade the MySQL software,
and the only solution would be to reset all your users' passwords, and
tell each user to go change their password.

I use MD5() when I need application-specific password encryption.

Thanks Bill

re the above. I read this, but assumed that, as the mysql database
containing the user table encrypts the passwords in it using PASSWORD() and
I need to check my users input in VB against it I would therefore need to
use the same encryption on my string from VB in order for the comparing of
the two to work (or is that not the case?).
I thought about better encryption using the other functions and using my own
encrypted table of passwords and building my own security system into my app
but the extra work, and the fact that the data is not that sensitive (just
school kids test results) I thought I'd stick to what mysql supplies.

Ian
Again thanks all for the quick responses, will look into them tomorrow