Pdo parameter error in prepared statement

Question

Sorry for my english I'm french :D,

Okay, i use pdo for my connexion with MySQL, but i have one error with PDO prepare statement.

I try to include my table name into the prepare request but i have an error :

Error : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''NbClient' LIMIT 2) as sr' at line 1 Error Code : 42000

My code :

<html>
    <head>
    </head>

    <body>
    <?php

    const HOST='localhost';
    const DBNAME='GameWave';
    const USER='';
    const PASSWORD='';
    const TABLE='NbClient';

    echo GetNbClientByHour(2);


    function GetNbClientByHour($nb_heure)
    {       
        $hookConstTable = TABLE;


        try
        {
            $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
            $connexion = new PDO('mysql:host='.HOST.';dbname='.DBNAME, USER, PASSWORD, $pdo_options);

            $prepare=$connexion->prepare('select SUM(record) from (select v_tot as record from :tab LIMIT :nb_heure) as sr;');
            $prepare->bindParam(':tab', $hookConstTable, PDO::PARAM_STR);
            $prepare->bindParam(':nb_heure', $nb_heure, PDO::PARAM_INT);
            $prepare->execute();

            $resultat = $prepare->fetch();
            $prepare->closeCursor();

            return $resultat[0];
        }            
        catch(Exception $e)
        {
            echo 'Error : '.$e->getMessage().'<br />';
            echo 'Error Code : '.$e->getCode();
        }
    }
    ?>
    </body>
</html>

But if i try to write table name in request, its work ...

$prepare=$connexion->prepare('select SUM(record) from (select v_tot as record from NbClient LIMIT :nb_heure) as sr;');
$prepare->bindParam(':nb_heure', $nb_heure, PDO::PARAM_INT);

Could you help me please ?

if nothing else, why not just `select sum(v_tot) as record from nbclient`? There's no reason for the sub-select — Marc B, Nov 28 '13 at 20:16

score 2 · Accepted Answer · answered Nov 28 '13 at 20:16

2

Problem is here:

select v_tot as record from :tab LIMIT :nb_heure

Because, according to manual, you can't use table name as variable in prepared statements.

answered Nov 28 '13 at 20:16

Damaged Organic

8,175
6
58
84

This is a really annoying limitation of PDO that someone should fix. – tadman Nov 28 '13 at 20:55
Agreed. I'm using some `$table` variable and sometimes `switch()` to sanitize it, but it's far from a versatile solution. – Damaged Organic Nov 28 '13 at 21:01

Pdo parameter error in prepared statement

1 Answers1