0

I am working on a script that updates a database table (ENGINE=InnoDB DEFAULT CHARSET=latin1) from data obtained through a socket stream. The problem is that I have some strings that may contain French accented characters and that building an INSERT query gives me this error :

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

I've been trying different ways to replace these character with correctly encoded accented characters but I can only get ? and É. Anything else I've tried gave me the previous error message. I've changed my files encoding from ISO-8859-1 to UTF-8 to ANSI and nothing gives.

This is a sample code generating a query from the list I get where I try to replace the character É. At this point, I'm not sure whether it is a PHP or a MySQLi issue honestly.

$clients = $avantage->getListeClients(true, true);

$inserts = [];
foreach($clients AS $c) {
  $c["archive"] = $c["archive"] == "F"?"0":"1";
  $c["langue"] = in_array($c["langue"], ["T", "FR", "français"])?"FR":"EN";

  if(stripos($c["nom"], "É") !== false) {
    $lpos = 0;
    $nom = "";
    while($pos = stripos($c["nom"], "É", $lpos)) {
      $nom .= utf8_encode(substr($c["nom"], $lpos, $pos - $lpos)).utf8_encode("É");
      $lpos = $pos + 1;
    }
    $nom .= utf8_encode(substr($c["nom"], $lpos));
    $c["nom"] = $nom;
    echo $nom."<br />";
  }
  $inserts[] = "id_client = \"".$c["id"]."\", nom = \"".$c["nom"]."\", langue = \"".$c["langue"]."\",         archive = \"".$c["archive"]."\"";
}

$query = "INSERT INTO clients (id_client, nom, langue, archive) VALUES (".implode("),(", $inserts).") ON DUPLICATE KEY UPDATE archive = values(archive)");
Frank Malenfant
  • 126
  • 1
  • 11
  • Looks like a duplicate of https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql – hdiogenes Feb 27 '20 at 20:39
  • Does this answer your question? [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) – Dave Feb 27 '20 at 21:09
  • 1
    **Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Feb 27 '20 at 22:56
  • @Dharman This is a one time script in a controlled environment. – Frank Malenfant Feb 28 '20 at 15:01
  • @hdiogenes I've seen this post in my searches. This post is about SELECT and comparisons while mine is about an INSERT statement. – Frank Malenfant Feb 28 '20 at 15:02
  • @Dave We are currently in the process of putting everything in UTF-8 but we won't be ready for the transision before a few more weeks and this update script must be run quickly. Plus, the stream I'm importing from comes from a black box application that is not mine. – Frank Malenfant Feb 28 '20 at 15:05
  • It doesn't matter what the script is used for. You should never put any variable input directly in the SQL. Use prepared statements, they are cleaner, safer and easier to use than what you are doing right now. – Dharman Feb 28 '20 at 15:10
  • @FrankMalenfant: the principle is the same, and you should really try the many solutions provided there. – hdiogenes Feb 28 '20 at 19:53

0 Answers0