7 Replies - 479 Views - Last Post: 03 June 2019 - 11:29 AM Rate Topic: -----

#1 UnknownCodester   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 05-March 13

PDO Question

Posted 30 May 2019 - 03:54 AM

I am back working with PDO after a few years of working with laravel eloquent and doctrine ORM. I am making a base repository class to put queries that other repositories will use to prevent reinventing the wheel. Here is an example of such a query

/**
* Get all the records from a particular db table
*
* @param string $tableName
* @return array
*/
public function all(string $tableName)
{
     $stmt = $this->db->prepare("SELECT * FROM :tableName");
     $stmt->execute([':tableName' => $tableName]);
     $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

     return $result;
}



I am getting the following error " 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 ''customers' "...

Is what I am doing better achieved using a different approach (seems I am using pdo is the wrong way)?
Also I need to ensure this function is not vulnerable to sql injection of course

Any help would be much appreciate :)/>

This post has been edited by UnknownCodester: 30 May 2019 - 03:55 AM


Is This A Good Question/Topic? 0
  • +

Replies To: PDO Question

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6798
  • View blog
  • Posts: 28,093
  • Joined: 12-December 12

Re: PDO Question

Posted 30 May 2019 - 05:21 AM

What value are you providing for $tableName when the error appears? Does the error always appear?
Was This Post Helpful? 0
  • +
  • -

#3 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2869
  • View blog
  • Posts: 11,233
  • Joined: 03-December 12

Re: PDO Question

Posted 30 May 2019 - 06:41 AM

The table name is part of the command, the parameters are for values.
Was This Post Helpful? 2
  • +
  • -

#4 ArtificialSoldier   User is online

  • D.I.C Lover
  • member icon

Reputation: 2333
  • View blog
  • Posts: 7,108
  • Joined: 15-January 14

Re: PDO Question

Posted 30 May 2019 - 09:39 AM

All identifiers (database names, table names, column names) must be in the query, they cannot be sent as parameters. Parameters are only for the data in the columns.
Was This Post Helpful? 1
  • +
  • -

#5 benanamen   User is offline

  • D.I.C Head

Reputation: 36
  • View blog
  • Posts: 239
  • Joined: 28-March 15

Re: PDO Question

Posted 31 May 2019 - 05:37 PM

You don't need the extra result variable either. Just return the fetchAll.

return $stmt->fetchAll(PDO::FETCH_ASSOC);

This post has been edited by benanamen: 31 May 2019 - 05:37 PM

Was This Post Helpful? 0
  • +
  • -

#6 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3822
  • View blog
  • Posts: 13,909
  • Joined: 08-August 08

Re: PDO Question

Posted 31 May 2019 - 07:43 PM

Since it's a base class it should know the table name.
public function all()
{
     $stmt = $this->db->prepare("SELECT * FROM ".$this->tableName);
     $stmt->execute();
     return $stmt->fetchAll(PDO::FETCH_ASSOC);
}


Was This Post Helpful? 1
  • +
  • -

#7 UnknownCodester   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 05-March 13

Re: PDO Question

Posted 03 June 2019 - 03:06 AM

View Postastonecipher, on 30 May 2019 - 06:41 AM, said:

The table name is part of the command, the parameters are for values.

Thanks for confirming what I suspected, was hoping pdo could work this way

View PostCTphpnwb, on 31 May 2019 - 07:43 PM, said:

Since it's a base class it should know the table name.
public function all()
{
     $stmt = $this->db->prepare("SELECT * FROM ".$this->tableName);
     $stmt->execute();
     return $stmt->fetchAll(PDO::FETCH_ASSOC);
}


Since I still want to use PDO I am going to go for an approach like this..

Each class that extends/implements the base class will set the table name some how.
May put the method as abstract so that each class doesn't forget to implement that method?

What are peoples thoughts on this?
Was This Post Helpful? 0
  • +
  • -

#8 ArtificialSoldier   User is online

  • D.I.C Lover
  • member icon

Reputation: 2333
  • View blog
  • Posts: 7,108
  • Joined: 15-January 14

Re: PDO Question

Posted 03 June 2019 - 11:29 AM

Quote

Thanks for confirming what I suspected, was hoping pdo could work this way

The limitation is with MySQL. When you prepare a statement MySQL builds an execution plan, and in order to do that it needs to know all of the tables and columns you're going to use. It doesn't need to know the data though.

Quote

What are peoples thoughts on this?

If there's any way for a user to supply that table name, you should check it against a whitelist to avoid attacks that way. Otherwise it should be fine if the values are only ever hard-coded.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1