Where is mysqli query request error?

Question

I am trying to create a prepared mysqli statement in which I import tables from an odbc-linked database into a mysql database, I get this error with a 106-column table query.

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use near '? (ID, column1, column2, column3, column4, 'in row 1 "

When I repeat the request here, he ...

INSERT IN? (ID, column1, column2, column3, column4, ... 106 full columns ...) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,? [Alpha], [alpha], [alpha], [alpha], [alpha], [alpha], [alpha], [alpha], [alpha], [alpha], [alpha], [alpha], & alpha ;, alpha ;, alpha ;, alpha ;, alpha ;, alpha ;, alpha ;, alpha; alpha ;, alpha ;, alpha ;, alpha; alpha ;, α, α, α, α, α, α, α, α, α, α, α, α , α, α, α, α, α, α, α,?

$sql = "SELECT * FROM $table WHERE $key = '$acct'"; $link = getODBCConnection(); $result = odbc_do($link, $sql); $data = array(); while ($row = odbc_fetch_array($result)) { //store all query rows as array array_push($data, $row); } //insert into mysql table of the same name //get column count from first row $columns = count($data[0]); $params = str_repeat(" ?,",$columns); $params = rtrim($params,','); $types = str_repeat("s",$columns+1); $fields = implode(", ", array_keys($data[0])); $sql = "INSERT INTO ? ($fields) VALUES ($params) ON DUPLICATE KEY UPDATE"; echo $sql."<br>"; $link = getSalesConnection(); $stmt = $link->prepare($sql); var_dump($link->error); foreach ($data as $row) { $stmt->bind_param($types, $table, implode(", ",array_values($row))); $stmt->execute(); } 

I tried this with the standard bind_param and also with the call_user_func_array () method. I tried referencing parameter strings and column names without effect. If there was an error with my bind_param types, I should not have an error in the preparation statement, should I? But there are some problems with SQL going to the preparation team, which I cannot determine. Please, help!

Answer 1

Query parameters can be used instead of scalar values. You cannot parameterize table names, column names, SQL expressions, keywords, value lists, etc.

• INCORRECT: SELECT ?, b, c FROM t WHERE a = 1 ORDER BY b ASC
  The parameter value will be a literal, not a column.

• WRONG: SELECT a, b, c FROM ? WHERE a = 1 ORDER BY b ASC SELECT a, b, c FROM ? WHERE a = 1 ORDER BY b ASC
  Syntax error.

• INCORRECT: SELECT a, b, c FROM t WHERE ? = 1 ORDER BY b ASC SELECT a, b, c FROM t WHERE ? = 1 ORDER BY b ASC
  The parameter value will be a literal, not a column.

• INCORRECT: SELECT a, b, c FROM t WHERE a IN (?) ORDER BY b ASC
  The parameter value will be the only literal value, not a list of values, even if you pass a string of comma-separated values.

• INCORRECT: SELECT a, b, c FROM t WHERE a = 1 ORDER BY ? ASC SELECT a, b, c FROM t WHERE a = 1 ORDER BY ? ASC
  The parameter value will be a literal, not a column.

• INCORRECT: SELECT a, b, c FROM t WHERE a = 1 ORDER BY b ?
  Syntax error.

Basically, if you can write a string literal, a date literal, or a numeric literal instead of a query parameter, everything should be fine. Otherwise, you need to interpolate the dynamic content into the SQL string before preparing () it.

Answer 2

The bind_param () function does not seem to replace the first "?" which defines the name of the table. First try manually putting the table name in the prepared row and use only ?? markers where it expects values.