3

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?...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SpaceBuzz
  • 75
  • 1
  • 2
  • 6
  • [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

2 Answers2

3

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:

  1. 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).
  2. 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.

Community
  • 1
  • 1
michaelb958--GoFundMonica
  • 4,617
  • 7
  • 31
  • 35
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();
    }
}
squirly
  • 753
  • 5
  • 13