Variable being passed with single quotes using PHP and MYSQL

Question

I have the following variable I want to pass to a prepare statement: $subject. It is done using PDO. Unfortunately it is being passed in with single quotes around it.Example is that i pass in maths and the query uses 'maths' instead. I have tried other answers such as bindParam, bindValue as well as specifying it is a string attribute, however I cannot get it to work. Thanks in advance if anyone knows what is wrong My code is below.

$query = "SELECT * FROM :subject;";
        $sql = $connection->prepare($query);
        $sql->bindParam(':subject', $subject); 
        try{                
            $sql->execute();
        }catch(Exception $e){
            echo $e;
        }

And i get the following error:

exception 'PDOException' with message '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 ''maths'' at line 1' in D:\xampp\htdocs\acards\functions.php:18
Stack trace:
#0 D:\xampp\htdocs\acards\functions.php(18): PDOStatement->execute()
#1 D:\xampp\htdocs\acards\getMathsQuestions.php(13): Functions->getFeed('maths')
#2 {main}[]

Remove the semi colon from your query. `SELECT * FROM :subject ` — Zooly, Jul 09 '17 at 14:22

score 1 · Accepted Answer · answered Jul 09 '17 at 14:23

1

The issue is here:

"SELECT * FROM :subject;";

bindParam is used with the parameters which are used in where clause, not in the table name.

The correct syntax is like:

$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

Reference

answered Jul 09 '17 at 14:23

Mayank Pandeyz

25,704
4
40
59

So if i didnt want to use WHERE in the statement there is no way of doing it? – Nicky Schranz Jul 09 '17 at 14:26
In that case do not use `bindParam`, just use `execute()` with query like `"SELECT * FROM table";` – Mayank Pandeyz Jul 09 '17 at 14:28
Cannot I not specify the table name by passing it as a parameter? – Nicky Schranz Jul 09 '17 at 14:28
In sort "no", check the answer: https://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement – Mayank Pandeyz Jul 09 '17 at 14:31
@NickySchranz do you really need to use prepared statement for the table name ? – Accountant م Jul 09 '17 at 14:32
Ok thanks I found a work around using a switch statement and having a case for each subject to be passed but its not so neat. Thanks anyway – Nicky Schranz Jul 09 '17 at 14:33
@Accountantم is it not safer against mysql injection? – Nicky Schranz Jul 09 '17 at 14:37
@NickySchranz table names are well known while developing unless you are dynamically building your tables with unpredictable names. no need for prepared statements with the table name since the name of the table is not coming from the client. `select * from mytable` is just fine – Accountant م Jul 09 '17 at 14:41
@Accountantم Thanks for the advice. I am however getting it from the client as it is sort of a quiz app and depending on which subject he chooses a different POST variable will be sent. Is there a better way of doing this. Much appreciated for any feedback. – Nicky Schranz Jul 09 '17 at 14:45
@NickySchranz since the table name comes from the client you can filter it against all the possible names of the tables that you already **know** while developing . you can do some thing like this `in_array($clientTableName, ['users','sales','payments','companies','purchases','collections']);` – Accountant م Jul 09 '17 at 14:51
@Accountantم Ok great will do that thanks :) – Nicky Schranz Jul 09 '17 at 14:53

score 1 · Answer 2 · answered Jul 09 '17 at 15:26

If you want to create a "flexible" query, allowing the user to input a table name you can do so by providing some PHP logic before you get to the prepare statement like

 $query="SELECT * FROM $subject";

But of course, this would open up your query to any kind of SQL-injection. But who is to say that you are not allowed to create your own "input-sanitization" on $subject before you use it in this statement? Just be aware, that this needs to be done very carefully!

Variable being passed with single quotes using PHP and MYSQL

2 Answers2