How to truncate a table using prepared statement in MySQL?

Question

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.

Answer 1

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

Answer 2

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.