5

This returns true but it didn't truncate the table:

$this->db->query("TRUNCATE TABLE $tablename");

But it works before creating a database connection object for prepared statement.

How to fix it? Also, I want to know how to truncate the table using prepared statement.

dakab
  • 5,379
  • 9
  • 43
  • 67
Karan
  • 75
  • 1
  • 7
  • See http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement – Sean Jan 27 '17 at 13:27
  • but why the default db query execution i mean "$this->db->query" this one didn't work?.. but it returns true .. but it doesn't impact the table !! – Karan Jan 27 '17 at 13:36
  • updated my answer , please try once – Deep Kakkar Jan 27 '17 at 13:41
  • Is the table in question located in the default schema? Maybe you have to qualify the table name with the schema (`schema.tablename`). – Tom Regner Jan 27 '17 at 13:52

2 Answers2

6

NO, A prepared statement would not be a solution because it is not possible to bind the table name. So avoid to use prepared statement for Truncate Table.

You cannot bind any SQL literal but data one. So keywords, operators and any identifier can not be bind using prepared statement. You can only bind data.

PDO prepared statements are useful when running queries with user input as they allow you to use features such as bound parameters to sanitise user input.

So In my suggestion you should not use prepared statement for truncate table.

If you really want to truncate using prepared , In case of Opencart which you are using, Use the code:

$sql = sprintf('TRUNCATE TABLE %s%s', DB_PREFIX, $table);
$this->db->query($sql);

try with this once and let me know

Deep Kakkar
  • 5,831
  • 4
  • 39
  • 75
  • ok. thanks for valuable information. but this query returns true..it didn't impact the table after i create a object for prepared stament.. why? – Karan Jan 27 '17 at 13:41
  • Did not get your question... It should truncate table ...Is it ? – Deep Kakkar Jan 27 '17 at 13:42
  • No. it did nt truncate the table. this is the result of above code .TRUNCATE TABLE tablename bool(true).. but when i execute the query directly in db.. it works – Karan Jan 27 '17 at 13:47
  • what about $this->db->query($sql); Is this working ? If not then for sure you can not use prepared statement for truncate table. – Deep Kakkar Jan 27 '17 at 13:52
  • No. now in my code it has two different db connection object.. one opencart default and another one is for preapared statement. when i created the another obj it does nt work.. – Karan Jan 27 '17 at 13:57
  • If it is not working with both (default and db connection object) then you really cannot use prepared statement to truncate table for Sure. – Deep Kakkar Jan 27 '17 at 13:59
  • ok. but why it returns true but doesn't impact the table? while executing default one. can i open a two connection to db ? or not? using two different object – Karan Jan 27 '17 at 14:04
  • check the link here truncating all sample tables, https://gist.github.com/mgirouard/605619 – Deep Kakkar Jan 27 '17 at 14:05
  • true means that it contains object which is getting return. – Deep Kakkar Jan 27 '17 at 14:06
  • most welcome, so please approve my answer there. Thank you! – Deep Kakkar Jan 27 '17 at 14:09
3

For TRUNCATE TABLE, you can still use both PDO::prepare and PDOStatement::execute:

$tablename = 'tblName';
$PDOStatement = $PDO->prepare("TRUNCATE TABLE $tablename;");
$PDOStatement->execute();

Sadly, you can’t use named (:name) or question mark (?) parameter markers. Regarding TRUNCATE however, you usually don’t use it as often as queries for which prepared statements are intended.

dakab
  • 5,379
  • 9
  • 43
  • 67