MySQL AES Decrypt返回值

Question

Trying to develop an AES-encrypted database for a client using PHP/MySQL. Inserting encrypted values is working, but having trouble returning usable decrypted values.

public static function auth($username="", $password="") {
    global $database;
    $fields = self::$db_fields;
    array_shift($fields);
    $username = $database->escape_value($username);
    $password = $database->escape_value($password);
    $hashed_pwd = sha1($password);
    $join_str = ", '" . AUTH_KEY . "'), AES_DECRYPT(";

    $sql  = "SELECT id, AES_DECRYPT(";
    $sql .=  join($join_str, $fields) . ", '" . AUTH_KEY . "') FROM " . self::$table_name. " ";
    $sql .= "WHERE u_name = AES_ENCRYPT('{$username}', '" . AUTH_KEY . "') ";
    $sql .= "AND u_pwd = AES_ENCRYPT('{$hashed_pwd}', '" . AUTH_KEY . "') ";
    $sql .= "LIMIT 1";

With the following query:

    $query_result = $database->query($sql);
    $result_array = $database->fetch_array($query_result);

}

Returns:

    Array ( 
        [0] => 5 
        [id] => 5 
        [1] => a_user_name 
        [AES_DECRYPT(user_name, '[PRINTS FULL AUTH KEY]')] => a_user_name 
        [2] => 0000hashedpasswordstring00000 
        [AES_DECRYPT(user_pwd, '[PRINTS FULL AUTH KEY]')] => 0000hashedpasswordstring00000  
        [3] => [e-mail] 
        [AES_DECRYPT(user_email, '[PRINTS FULL AUTH KEY]')] => [e-mail] 
    )

This was a secondary solution. Ideally the query would be object-oriented, but that approach was returning an array with the fields but no values.

Clearly, having the full auth key transferred in the results completely defeats the purpose of the encryption. Confused as to why it's returning a combined associate/indexed array, rather than just an indexed array, or just an associative array. Is there a change in SQL syntax that would return [user_email] => [e-mail] instead of [AES_DECRYPT(user_email, '[PRINTS FULL AUTH KEY]')] => [e-mail]?

Answer 1

You need to use an alias for the column that includes the function:

$sql  = "SELECT id, AES_DECRYPT(";
$sql .=  join($join_str, $fields) . ", '" . AUTH_KEY . "') AS user_email FROM " . self::$table_name. " ";
$sql .= "WHERE u_name = AES_ENCRYPT('{$username}', '" . AUTH_KEY . "') ";
$sql .= "AND u_pwd = AES_ENCRYPT('{$hashed_pwd}', '" . AUTH_KEY . "') ";
$sql .= "LIMIT 1";

From MySQL documentation on SELECT:

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example:

SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name;

Encryption like this is usually done in the application instead of in MySQL. When done in MySQL, the secret key ends up in the MySQL logs.