List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 11 2002 5:07pm
Subject:Re: Need reversible encryption as string
View as plain text  
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

Thread
Need reversible encryption as stringMichael Collins11 Aug
  • Re: Need reversible encryption as stringMike Hall11 Aug
    • Re: Need reversible encryption as stringMichael Collins11 Aug
    • Re: [OT] assigning new passwords (was: Need reversible encryption as string)Benjamin Pflugmann11 Aug
  • Re: Need reversible encryption as stringPaul DuBois11 Aug
    • Re: Need reversible encryption as stringBenjamin Pflugmann11 Aug
      • Re: Need reversible encryption as stringPaul DuBois11 Aug
  • Re: [OT] assigning new passwords (was: Need reversible encryption as string)Mike Hall11 Aug
    • Re: [OT] assigning new passwords (was: Need reversible encryptionas string)Joe Shear12 Aug
Re: Need reversible encryption as stringMichael Collins11 Aug