删除记录会返回SQLSTATE [42000]错误

Question

I have this small PHP script for deleting a record from a MySQL database, and it returns SQLSTATE[42000].

include("connectDB.php");

$recordID = $_POST["ID"];
$table = $_POST["table"];
$URL = $_POST["URL"];

$deleteRecordQuery = "DELETE FROM :table WHERE ID=:ID";
$deleteRecord = $conn->prepare($deleteRecordQuery);
$deleteRecord->bindParam(':table',$table);
$deleteRecord->bindParam(':ID',$recordID);
$deleteRecord->execute();

header("Location: ".$URL);

The script is working if I comment out binding of the :table parameter, and directly use $table variable in the statement:

$deleteRecordQuery = "DELETE FROM $table WHERE ID=:ID";

So binding of the ID works. Why doesn't binding of the table work?

The return message is:

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 ''Osobe' WHERE ID='1'' at line 1

Answer 1

Your question is: "Why doesn't binding of the table work?" I think you have already figured out that binding table and column names doesn't work.

I think this is easiest to understand in terms of a SELECT statement, rather than a DELETE statement. Processing SQL statements basically occurs in two phases. The first phase is the "prepare" (or "compile" phase). The second is the "execution" phase. The values of bind variables are not available for the "prepare" phase, only for the "execution" phase.

The "prepare" phase determines what data needs to accessed, what the execution path will be (such as the use of indexes), and what the result set will look like. If the query engine does not know what tables or columns are being accessed, then it cannot do the necessary work in the compile stage. So, tables and columns are required for preparing the statement.

In your case, there is an easy fix, which is the embed the table name directly in the SQL. I do note that this is not satisfying, because the resulting statement could then be vulnerable to injection attacks.