MySQL Query for Matching Items Help

Question

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. :)

Answer 1

MATCH () AGAINST () doesn't work like you're expecting it to. What it does is attempts to match a single string in AGAINST() against each of the columns provided in MATCH(), rather than comparing value1 against column1 and value2 against column2.

Have you tried ...WHERE user_id = '".$userId."' AND user_pass = '"$userPassProvided"' LIMIT 1?

Answer 2

The following is functionally equivalent to what you seem to want to do. (Do read "however..." below)

$query  = "SELECT user_id, AES_DECRYPT( user_pass, '".$db_aes_key."' ) AS user_pass ";
$query .= "FROM users_tbl ";
$query .= "WHERE user_id = '".$userId."' ";
$query .= "  AND AES_DECRYPT(user_pass, '".$db_aes_key."' ) = '".$userPassProvided."' ";
$query .= "LIMIT 1";

...however MySQL would have to AES-decript every single encoded password in the database. This will be both computationally expensive and prevent using any SQL index.

Alternatively, you may consider encrypting the supplied password, and match it to the ones stored in the database. Maybe something like that (note: untested):

$query  = "SELECT user_id, AES_DECRYPT( user_pass, '".$db_aes_key."' ) AS user_pass ";
$query .= "FROM users_tbl ";
$query .= "WHERE user_id = '".$userId."' ";
$query .= "  AND user_pass = AES_ENCRYPT('".$userPassProvided."', '".$db_aes_key."' ) ";
$query .= "LIMIT 1";