user6391870 user6391870 - 6 months ago 16
SQL Question

Use table name from static variable in PDO

I know, that PDO won't let me use the "param system" on tables. My problem is, that I store all table names as variables / static variables in an object named "Tables" (so I can update table names centrally).

I just can't find an answer to the question, if it's a good idea / not bad practise to build the query by using my static variables (users can't change the table names / browse through tables, so that schouldn't be a security problem)

Example:

$statement = $this->pdo->prepare('SELECT `category-id`, `icon`, `name` FROM ' . Tables::$BOARD_CATEGORIES);


Can I use this technique, or should I stick with the normal "static" way?
Thanks :)

Answer

Answer:

It depends on the code you are using to develop and modify your Tables object. In general though, conventional methods are more tested and safer. The only real difference between your code and normally used PDO code is how you are generating your sql statement. If you use your own code to create the sql statement that is being prepared, you have to be completely sure that user input will not modify the structure of that sql statement. It is riskier and if not needed would be advised against, however if you absolutely need it for your site, then make absolute sure that the code generating the Tables object can't be maliciously manipulated by users.


Elaborating on the answer:

The real difference between your code and the general way PDO is used is that you are generating the sql statement differently. Normally, you would see code as follows:

$sql = "SELECT `category-id`, `icon`, `name` FROM myTable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(":id", $id);
$stmt->execute();

(I am assuming that you are also binding your parameters in your code since that would be after the preparing process you show in your question).

So since the only real difference between what you are doing with your table is generating the sql statement, you just have to make complete sure that there is absolutely no way for user input to influence your sql structure, because if there is, then the prepare statement will not keep your database safe.

In general, people tend to prefer to use conventional and tested methods, however if you absolutely need to use your table object to generate the sql statement, make sure that the code that creates and updates the object is defended from user input.

Comments