Impossible to use BindValue in the SELECT field names?

Question

I have a MySQL database containing Countries

id  | de          | en          | fr   
--------------------------------------------- 
 1  | Afghanistan | Afghanistan | Afghanistan
 2  | Albanien    | Albania     | Albanie
...

I'd like to get a list of the countries, according to visitor's language.

Here's the method in my class :

public function getList($language){
    $countries = array();
    $req = ('SELECT id, :language FROM country ORDER BY :language ASC');        
    $q = $this->_db->prepare($req);
    $q->bindValue(':language', $language, \PDO::PARAM_STR);
    $q->execute();
    while ($data = $q->fetch(\PDO::FETCH_ASSOC)) {
        $countries[] = new Country($data);
    }
    return $countries;
    $q->closeCursor();
}

It returns an array, but country names are replaced by the language.

It seems bindValue is adding quotes to my variable, which is fine in the ORDER BY, but creates problems in the fields i want to SELECT.

$countryManager->getList('fr');

returns something like this

Array
(
[0] => Entities\Country Object
    (
        [id:Entities\Country:private] => 1
        [de:Entities\Country:private] =>  
        [en:Entities\Country:private] => 
        [fr:Entities\Country:private] => fr
    )

[1] => Entities\Country Object
    (
        [id:Entities\Country:private] => 2
        [de:Entities\Country:private] => 
        [en:Entities\Country:private] => 
        [fr:Entities\Country:private] => fr
    )

If you have any idea on where the problem might be, i'd be very glad to know :)

Thank you in advance for your help.

Answer 1

You can't use prepared statements to set the names of columns or tables in a query, you can only use them to inject values. You'll have to use string interpolation

Don't forget to apply thorough validation to prevent SQL injection vulnerabilities.