At 9:25 -0700 8/11/02, Michael Collins wrote: >I want to securely store a value that is used as a password to log >someone into a Web application. I also want to be able to allow the >user to search for their email address and have their password sent >back to them (in readable form). Encrypt, MD5, and Password are >non-reversible and thus will not work for my needs. > >The Encode function creates a value that is stored as binary. It >seems that I cannot do a match type search, but I have to convert >the stored password on each row as follows: > >SELECT * FROM MYDB >WHERE EmailAddress=$EmailEntered AND >DECODE(LoginPassword,'MySalt')=$PasswordEntered
If you write the query like that, yes, it will use a complete scan. But you could also use ... AND LoginPassword = ENCODE($PasswordEntered,'MySalt') which doesn't perform a calculation on the LoginPassword column and thus can use an index. This is similar to your query below, but I think you want ENCODE(), not DECODE(), since you're storing encrypted strings. > >I would think that using the SQL shown would require a table scan, >meaning that each and every record in the visitors table must be >examined, the LoginPassword decoded and compared. There is also no >way to index this field. I do not think this is the best solution >after adding 100,000 records. > >I see that MySQL 4 offers AES_ENCRYPT() and AES_DECRYPT(), will this >offer a solution? I believe this will allow me to store the password >as a string of characters (and not binary data) so that a match can >be made without having to decode the password, since I can decode >what is entered by the user using the same salt and compare the two >encrypted strings. > >SELECT * FROM MYDB >WHERE EmailAddress=$EmailEntered AND >LoginPassword=DECODE($PasswordEntered,'MySalt') > >The decrypt process would only be used when needing to send the >result back to the user. > >Am I understanding this correctly? > >-- >Michael >__ >||| Michael Collins ||| >||| Kuwago Inc ||| mailto:[EMAIL PROTECTED] >||| Seattle, WA, USA ||| http://www.lassodev.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
