Right syntax to use near '?' ORDER BY

Question

$array = array();
$stmt = $connection->prepare("SELECT * 
                              FROM ?
                              ORDER BY `score` DESC");
$stmt->bind_param("s", $name);
$stmt->execute();
$data = $stmt->get_result();

while($row = $data->fetch_assoc()){
    array_push($array, $row);
}

Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? ORDER BY `score` DESC' at line 2 in...

target_leaderboard is the database I connect to in my separate "connection.php" file I include at the top of this php page.

I am trying to prevent SQL-Injection attacks by using the method described in How can I prevent SQL injection in PHP? but I'm not sure how to solve this error.

I've looked at right syntax to use near '?' but I think my issue is different.

Oh. Do I simply put $name, or would that be at risk to SQL-INJECTION? — James, Jun 09 '20 at 23:35
^^ You need something more like `SELECT * FROM $database WHERE field = ? ORDER BY \`score\` DESC` — Zak, Jun 09 '20 at 23:36

score 1 · Accepted Answer · answered Jun 09 '20 at 23:36

You intent is OK. However, databases do not support passing table names as variables; keep in mind that the query planner needs to be able to prepare the statement (that is, generate its execution plan) by looking at the parameterized query only (without seeing the parameters). Parameters are meant to pass literal values to the query.

So you are left with performing the validation in your application first (against a fixed list of values, or by querying information_schema.tables), and then concatenating the table name in your query:

$stmt = $connection->prepare("SELECT * FROM `$name` ORDER BY `score` DESC");
$stmt->bind_param("s", $name);
$stmt->execute();

Right syntax to use near '?' ORDER BY

1 Answers1