-2

In my script I loop through an array and create a single INSERT INTO string from it, with multiple values. When executing I get this error:

.. right syntax to use near 'INSERT INTO

As you can see, PHP adds a strange character in front of the string. The string is created with 

$sql = "INSERT INTO table(`ItemID`, `Quantity`) VALUES "; 

foreach($articles as $item) 
{ 
    $sql .= "(".$item['ItemID'].",".$item['Quantity']."),"; 
} 

$sql = substr($sql, 0, -1); // to eliminate the trailing ","

$sql .= ";";

Why does PHP add this character and how can I detect and remove it? The file is saved as UTF-8 without BOM.

Ceriana
  • 57
  • 2
  • 6
  • 3
    The character looks like a Byte Order Mark, as in [this question](http://stackoverflow.com/q/3255993/1426891). – Jeff Bowman Mar 15 '15 at 02:25
  • The file is saved as UTF-8 without BOM. But I thought BOM is only added to the beginning of files, why does PHP add it to a string value? The project consists of much more files, and everyone works just fine with strings and querys. – Ceriana Mar 15 '15 at 02:30
  • The `BOM` is not visible. It's possible (improbable, but possible) that the code contains the 3-bytes BOM sequence between the opening quote and `INSERT`. Delete the `INSERT` keyword together with the quote before it then type the deleted characters again and see what happens. PHP doesn't add characters in the queries. – axiac Mar 15 '15 at 15:43
  • Tried that a dozen times, nothing changes. – Ceriana Mar 15 '15 at 17:07

2 Answers2

1

The characters you're seeing are probably a misread Byte Order Mark, a Unicode character (U+FEFF) meant to indicate which endianness is used for UTF-16 files. To quote Wikipedia:

The UTF-8 representation of the BOM is the byte sequence 0xEF,0xBB,0xBF. A text editor or web browser interpreting the text as ISO-8859-1 or CP1252 will display the characters  for this.

Though your PHP file may easily be saved without a BOM, that doesn't mean one isn't prepended to your query. Without seeing how your $sql variable is passed into mysql, though, I only have a few guesses:

  • Byte order doesn't matter in UTF-8, but BOMs are valid and are sometimes used to identify a string as Unicode. You may need to manually instruct MySQL to interpret your string as UTF-8 instead of ISO-8859-1 (using mysql_set_charset or equivalent). Setting client and server encodings explicitly is a good idea anyway.
  • If this is a machine-specific problem, also check the versions of PHP and your MySQL client and server are the same as production (or your other machines). It is very plausible for a default encoding to evolve over server/client versions, or for some servers (but not others) to detect a byte order mark and automatically switch to UTF-8.
  • If your query is saved to a file temporarily (e.g. for batch execution or caching) you may find it written with a Byte Order Mark automatically.
Jeff Bowman
  • 90,959
  • 16
  • 217
  • 251
  • The $sql is passed just as it is with mysqli_query. No prepared statements, just "INSERT INTO table(field1, field2) VALUES (x1, y1),(x2,y2).." etc. I set the character set of the mysqli connection to UTF-8 with both "SET NAMES utf-8" and "SET CHARARCTER SET utf-8", but nothing changes. Checked the code on other machines, same result. I edited the query "making-of" in my first post. – Ceriana Mar 15 '15 at 15:30
0

By adding this line worked for me

$string_query = str_replace("\xEF\xBB\xBF", "", $string_query);
gojobs
  • 33
  • 5