ERROR:SQLSTATE[42000]: Syntax error or access violation: 1064

Question

I was looking for answer for last 3 hours, and i don't know what to do. Here is the code:

    function get_data($tablename)
    {
        try
        {
            $conn = $this->conn();
            $stmt = $conn->prepare("SELECT * FROM :tablename ORDER BY id");
            $stmt->bindParam(':tablename', $tablename, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        }
        catch (Exception $e)
        {
            echo "ERROR:" . $e->getMessage();
        }
    }

And here is the 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 ''products' ORDER BY id' at line 1

What I've done wrong?...

[Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?](http://stackoverflow.com/a/15991422/285587) — Your Common Sense, Jun 08 '13 at 08:59

score 3 · Accepted Answer · edited May 23 '17 at 12:28

As noted here (thanks @YourCommonSense), you can't use a parameter as a table name; and if you do, one of two things will happen:

With proper prepared statements, the prepared-statement module will throw an exception (and quite rightly so, as you've asked it to do the impossible).
With emulated prepared statements, the parameter will be blindly escaped, single-quoted, and substituted in, causing an SQL syntax error. This is what's happened here.

That's the problem. As for solutions:

Reevaluate your database design. Do you really need to split data across different tables like that? If not, combine the relevant data into a single table, and query accordingly.

If you're happy with the design (or can't change it), you'll need an ugly insecure hack like the following:

function get_data($tablename, $acceptable_tablenames = array()) {
  /* $acceptable_tablenames is an array of strings, containing
   *  table names that you'll accept. It's your job to make sure
   *  these are safe; this is a much easier task than arbitrary
   *  sanitization.
   */
  if (array_search($tablename, $acceptable_tablenames, true) === FALSE) {
    throw new Exception("Invalid table name"); /* Improve me! */
  } else {
    /* your try/catch block with PDO stuff in it goes here
     * make sure to actually return the data
     */
  }
}

Call it as get_data($table, array("my_datatable_1", "my_datatable_2")). Credit to the post linked to at the start of my answer for inspiration.

+1 for "Reevaluate your database design". I should have mentioned it myself. — Your Common Sense, Jun 08 '13 at 09:39
michaelb958 and (mostly :) @YourCommonSense thank you so much. You, guys, save my mind from self destruction. — SpaceBuzz, Jun 08 '13 at 11:25

squirly · Answer 2 · 2013-06-08T09:24:51.977

0

PDO escapes parameters with single quotes ('). MySql table names need to be escaped with backticks (`).

In the example provided the tablename parameter, Products, is being escaped single quotes. This is a safety feature of the PDO engine to prevent injection attacks.

Assuming that value of $tablename makes sense in applications context (eg. validating that the current user can see all the data in the specified table.).

The following would work:

function get_data($tablename)
{
    try
    {
        $conn = $this->conn();
        $stmt = $conn->prepare("SELECT * FROM `" . str_replace("`","``",$tablename) . "` ORDER BY id;");
        $stmt->execute();
        return $stmt;
    }
    catch (Exception $e)
    {
        echo "ERROR:" . $e->getMessage();
    }
}

edited Jun 08 '13 at 09:24

answered Jun 08 '13 at 08:52

squirly

753
5
13

"This is hacky" in the meaning of "injectable" – Your Common Sense Jun 08 '13 at 08:58
I guess more in the fact that I rarely write a SQL statement in code and when I do I try to take all measures to make sure it is fully and properly escaped. – squirly Jun 08 '13 at 09:01
That's good of you. But I can't see no measures in the code provided in the answer here. – Your Common Sense Jun 08 '13 at 09:15
I reworded my answer and made the MySql Statement safer. – squirly Jun 08 '13 at 09:23
@squirly thank you for your time and your answer. Will remember it for future. – SpaceBuzz Jun 08 '13 at 11:21

ERROR:SQLSTATE[42000]: Syntax error or access violation: 1064

2 Answers2