Where is the mysqli prepared statement query error?

Question

I'm trying to create a mysqli prepared statement where I import tables from an odbc connected database into a mysql database, I'm getting this error with 106-column wide table query.

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 '? (ID, column1, column2, column3, column4, ' at line 1"

When I echo out the query here it is...

INSERT INTO ? (ID, column1, column2, column3, column4, ...106 total columns... ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

$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've tried this using standard bind_param and also using the call_user_func_array() method. I've tried quoting my parameter strings and the column names, without effect. If there was an error with my bind_param types I should not have an error on the prepare statement should I? But there is some problem with the SQL going to the prepare command that I can't pinpoint. Please help!

score 5 · Accepted Answer · answered Jul 10 '13 at 22:41

Query parameters can be used in place of scalar values only. You can't parameterize table names, column names, SQL expressions, keywords, lists of values, etc.

WRONG: SELECT ?, b, c FROM t WHERE a = 1 ORDER BY b ASC
The parameter value will be a literal value, not the name of a column.
WRONG: SELECT a, b, c FROM ? WHERE a = 1 ORDER BY b ASC
Syntax error.
WRONG: SELECT a, b, c FROM t WHERE ? = 1 ORDER BY b ASC
The parameter value will be a literal value, not the name of a column.
WRONG: SELECT a, b, c FROM t WHERE a IN (?) ORDER BY b ASC
The parameter value will be a single literal value, not a list of values, even if you pass a string of comma-separated values.
WRONG: SELECT a, b, c FROM t WHERE a = 1 ORDER BY ? ASC
The parameter value will be a literal value, not the name of a column.
WRONG: SELECT a, b, c FROM t WHERE a = 1 ORDER BY b ?
Syntax error.

Basically if you could write a string literal, date literal, or numeric literal in place of the query parameter, it should be okay. Otherwise you have to interpolate the dynamic content into the SQL string before you prepare() it.

score 2 · Answer 2 · answered Jul 10 '13 at 22:32

2

It looks as though the bind_param() function does not replace the very first '?' that defines the table name. Try manually putting the table name into the prepared string first and only use '?' markers where it is expecting values.

answered Jul 10 '13 at 22:32

Lochemage

3,974
11
11

Where is the mysqli prepared statement query error?

2 Answers2