Need Help with MySQL Query for Matching Items
-
Hello. I'm having a little trouble getting this query to work:
$userId = mysql_real_escape_string( $_SESSION['user_id'] );
$userPassProvided = mysql_real_escape_string( $_POST['oldPassword'] );
$query = "SELECT user_id, AES_DECRYPT( user_pass, '".$db_aes_key."' ) AS user_pass ";
$query .= "FROM users_tbl WHERE MATCH( user_id, user_pass ) ";
$query .= "AGAINST( '".$userId."', '".$userPassProvided."' IN BOOLEAN MODE ) LIMIT 1";
$result = mysql_query( $query, $mysql_db );What I would like to do is query users_tbl for the record wherein user_id and user_pass are the same as $userId and $userPassProvided, respectively. Can someone please tell me what is wrong with my query? Thanks. :)
-
Hello. I'm having a little trouble getting this query to work:
$userId = mysql_real_escape_string( $_SESSION['user_id'] );
$userPassProvided = mysql_real_escape_string( $_POST['oldPassword'] );
$query = "SELECT user_id, AES_DECRYPT( user_pass, '".$db_aes_key."' ) AS user_pass ";
$query .= "FROM users_tbl WHERE MATCH( user_id, user_pass ) ";
$query .= "AGAINST( '".$userId."', '".$userPassProvided."' IN BOOLEAN MODE ) LIMIT 1";
$result = mysql_query( $query, $mysql_db );What I would like to do is query users_tbl for the record wherein user_id and user_pass are the same as $userId and $userPassProvided, respectively. Can someone please tell me what is wrong with my query? Thanks. :)
Hi, not completely sure, but I think your SQL statement will search for $userPassProvided in field user_pass, and only when some row matches, will it return what you specified, including a decrypted password. If so, you should encrypt $userPassProvided. And I don't like two-way encryption for passwords; most often all you need is validating the user, which can be done with a one-way encryption (i.e. get password as entered, encrypt it, search it in the DB). BTW: I'm baffled by the MATCH...AGAINST in there too; you do want a full match, don't you? :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
Hi, not completely sure, but I think your SQL statement will search for $userPassProvided in field user_pass, and only when some row matches, will it return what you specified, including a decrypted password. If so, you should encrypt $userPassProvided. And I don't like two-way encryption for passwords; most often all you need is validating the user, which can be done with a one-way encryption (i.e. get password as entered, encrypt it, search it in the DB). BTW: I'm baffled by the MATCH...AGAINST in there too; you do want a full match, don't you? :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Thanks, Luc. If I understand correctly, I should be doing something like this (untested of course):
$userId = mysql_real_escape_string( $_SESSION['user_id'] );
$userPassProvided = mysql_real_escape_string( $_POST['oldPassword'] );
$query = "SELECT user_id, AES_DECRYPT(user_pass, '".$db_aes_key."' ) AS user_pass ";
$query .= "FROM users_tbl WHERE MATCH( user_id, user_pass ) ";
$query .= "AGAINST( '".$userId."', AES_ENCRYPT( '".$userPassProvided."', '".$db_aes_key."' ) IN BOOLEAN MODE ) LIMIT 1";
$result = mysql_query( $query, $mysql_db );Yes, I do want an exact match. This is going to be used in a User Control Panel on my website when the user wants to change their password.
-
Thanks, Luc. If I understand correctly, I should be doing something like this (untested of course):
$userId = mysql_real_escape_string( $_SESSION['user_id'] );
$userPassProvided = mysql_real_escape_string( $_POST['oldPassword'] );
$query = "SELECT user_id, AES_DECRYPT(user_pass, '".$db_aes_key."' ) AS user_pass ";
$query .= "FROM users_tbl WHERE MATCH( user_id, user_pass ) ";
$query .= "AGAINST( '".$userId."', AES_ENCRYPT( '".$userPassProvided."', '".$db_aes_key."' ) IN BOOLEAN MODE ) LIMIT 1";
$result = mysql_query( $query, $mysql_db );Yes, I do want an exact match. This is going to be used in a User Control Panel on my website when the user wants to change their password.
for an exact match, you don't need MATCH...AGAINST, a simple "... WHERE field1 = 'value1' AND field2 = 'value2' ..." would do. And you don't need to SELECT the password, the user_id should be sufficient. After all, you either get zero or one rows that match. :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages