0

I keep getting this:

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 ''TABLE 16''

But i can't find the error in my code:

$show = $conn->query('SHOW TABLES');

$show->setFetchMode(PDO::FETCH_NUM);

while($row = $show->fetch()) {

  $tbl = "`".$row[0]."`";  

  $trunc = $conn->prepare('TRUNCATE TABLE :tbl');

  $trunc->bindParam(':tbl', $tbl, PDO::PARAM_STR, 64);// sanitize data

  $trunc->execute();
}

If i change it like this,it work:

$trunc= $conn->query('TRUNCATE TABLE `TABLE 16`');
Petru Lebada
  • 2,167
  • 8
  • 38
  • 59
  • This has been asked many times before. You cannot use parameters to bind a table name. – e4c5 Aug 23 '16 at 12:20
  • ... because the whole purpose of prepared statements is code and data separation. – Álvaro González Aug 23 '16 at 12:24
  • Your problem is based on the confusion. You have to understand that bindParam doesn't "sanitize" anything, its purpose is different – Your Common Sense Aug 23 '16 at 12:26
  • @YourCommonSense , as far as i've read it bind the variable to the placeholder and prepare it for query, does it have another purpose? By preparing i mean eliminating some security risks like injection and some others – Petru Lebada Aug 23 '16 at 12:31
  • Unfortunately, such thing like "some magic that eliminates all injection risks" just doesn't exist. Prepared statements can protect your string and numeric data. That's all. – Your Common Sense Aug 23 '16 at 12:41
  • I'm not expecting for some magic things to do the work for me.Those last few days i kept reading and asking about best practices in matter of readability and efficiency and all i could find is that i should use prepared statements instead of the old fashioned way when i was validating data with my own function before storing it,with the deprecated mysql.So that's what i've done,but seems like,despite de rumors , those built in things have a lot of problems ... – Petru Lebada Aug 23 '16 at 12:49
  • Speaking of my case , where the table names consist only of alphanumeric characters , prepared statements should be enough . That's what others also told me... – Petru Lebada Aug 23 '16 at 12:50
  • Prepared statements can't eliminate injection risk if you let the user choose the table name: your code is designed to allow wiping out arbitrary tables. – Álvaro González Aug 25 '16 at 06:38

0 Answers0