0

(Massively edited because the responses were focusing on the wrong thing, which means I didn't formulate the question properly.)

I have a MySQL table with id, username and password fields. The password is encrypted. Part of the encryption uses the id of the record, specifically (in PHP):

md5 (md5('id') . $formPasswordCleaned)

To a validate a login form, I do the following (pseudo-code):

  1. query database for the email address from login form
  2. if num_rows > 0, $row = fetch_array
  3. use $row['id'] to encrypt the password provided in the login form.
  4. compare encrypted password with the one found in $row 4.

But, what I'd really like to do is one single SELECT command, that looks for a match on both email address and the encrypted password. But, since the password was encrypted using the row's id.....

Question: is it possible to write a SELECT statement that uses part of the forthcoming result as some of the arguments? Something like: SELECT * from users where email=$email and password=md5(md5($futureResult['id]).$password)

I realise it's self-referential, but I know just the basics of database usage and so I don't know what to search for (google or here) to find out if it's possible or how to do it, if it is indeed possible.

Zonker.in.Geneva
  • 1,389
  • 11
  • 19
  • Did you try it? – Ocaso Protal Aug 10 '17 at 08:44
  • $result['id]).$password ???? – Malek Zarkouna Aug 10 '17 at 09:08
  • 1
    You can do what you are explaining with `... md5(concat(md5(id), '".$password."'))`. The cleartext-password comes from php as a constant in your query text, `md5()` and `id` are evaluated in mysql per row. But you should really check if that is what you are expected to do. NOONE should "insert the clear-text password to update it with the encrypted one" (not even temporarily or to show how not to do it), nor should a cleartext password ever be in the query text. EVER! If that is what the course teaches you, take a different one. It's a very basic process you can find in any tutoral on the web. – Solarflare Aug 10 '17 at 10:17
  • 1
    Rereading it, your problem seems to arise from the part "it appears I have to do the same things". No, you don't. While I am not entirely sure about the use of the id (email would work as salt too, or rand() or an actual salt), the process to verify a password is: user enters email + password, you get the row with `where email = '$email'` (but with prepared statements please!), then you compare the result of that (the encrypted pw) with `md5($password)` (plus id/salt/...) you calculate in php(!!). NEVER add a cleartext password to the query. If you have to do it, you misunderstood something. – Solarflare Aug 10 '17 at 10:46
  • @OcasoProtal Try what? I'm asking how to do it. I can't use $result, because that variable doesn't exist when I'm running the query. – Zonker.in.Geneva Aug 10 '17 at 13:31
  • @Solarflare thanks for the first line of your reply. That's what I was looking for. – Zonker.in.Geneva Aug 10 '17 at 13:45
  • Haha, well, it wasn't supposed to sound disturbingly exciting, just wanted to emphasize it. It's just that (especially if there are no warnings) things like that, that work and even need less lines of code, are never unlearned (just like using the mysql-api instead of mysqli/pdo). Also one additional point (I just mentioned shortly before): you should actually not use the code as I wrote it there, but use [prepared statements](https://stackoverflow.com/questions/24988867/when-should-i-use-prepared-statements). Test e.g. using a `'` in your password-input, it would break the query. – Solarflare Aug 10 '17 at 14:14

0 Answers0