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.

score 1 · Accepted Answer · answered Nov 21 '18 at 13:52

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.

answered Nov 21 '18 at 13:52

Joni

108,737
14
143
193

Thank you for your quick answer. I just saw it in the link suggested by Nigel Ren above (which i didn't find while browsing SO). Any idea why it works like that ? – Daaaaa Nov 21 '18 at 13:53
1

Prepared statements exist so the database can validate it and prepare a query plan to be executed with different parameters. If the column and table references change that's not possible. Also it would open prepared statements to SQL injection bugs – Joni Nov 21 '18 at 14:03

Impossible to use BindValue in the SELECT field names?

1 Answers1