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:mcollins@stripped
>||| Seattle, WA, USA ||| http://www.lassodev.com
