Monday 23 May 2016

php - Where is the mysqli prepared statement query error?



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."
";

$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!



Answer



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.


No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...