UTF-8 encoding error during database migration

Question

I keep getting the silly invalid byte sequence for encoding "UTF8": 0x96 error or whatever it is. Now I've identified it as a problem with my server space.

The thing is I migrated from a very similar database which was also UTF-8. I test out my PHP scripts on the first database and they work but on the one I require they do not. It must be a problem with encoding but I'm not sure what.

My PHP queries are failing on the new database and producing that error.

PHP:

$name = $_POST["name"];
$type = $_POST["type"];
$contact = $_POST["contact"];
$address = $_POST["address"];
$postcode = $_POST["postcode"];
$phone = $_POST["phone"];
$description = $_POST["description"];
$location = $_POST["location"];

$conn = pg_connect(/*DATABASE CONNECTION DETAILS*/);

$res = pg_query("INSERT INTO address (organisation_type, contact_name, address, postcode, phone_number, description, location_to_town, organisation_name) VALUES ('$type', '$contact', '$address', '$postcode', '$phone', '$description', '$location', '$name')");

 pg_close($conn);

I mean that is a simple PHP write to database function and the only oddity is that of one UTF-8 database working and the other not. Is there any way I can compare the server/change the configurations of my PostGreSQL server at the command line?

Thanks any information would be much appreciated. Tearing my hair out over this!

@DanielD Try to insert a user with name `Jimmy O'Hara`.. it will probably get an error cause you are not escaping quotes.. unless you have magic_quotes ini option enabled (which is not recommended btw).. — Nelson, Nov 13 '12 at 17:49
@DanielD See http://xkcd.com/327/, and think about what would happen if a user tried to register with a name like that. To avoid this problem, see http://php.net/manual/en/function.pg-prepare.php. — Brian Campbell, Nov 13 '12 at 18:00
@DanielD Re Álvaro's comment: You need to read http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ , http://php.net/manual/en/security.database.sql-injection.php, http://en.wikipedia.org/wiki/SQL_injection, http://bobby-tables.com/ . Here's a related answer - about Delphi, but the principles are the same: http://stackoverflow.com/a/13171076/398670 — Craig Ringer, Nov 14 '12 at 01:08
@BrianCampbell Better links for PHP users are probably http://php.net/manual/en/security.database.sql-injection.php and http://bobby-tables.com/php.html — Craig Ringer, Nov 14 '12 at 01:10
@DanielD What is `client_encoding` set to on your PHP connections? Run `SELECT current_setting('client_encoding');` from PHP to get its value. If it's UTF-8 and your DB is too (as shown by `\l+` in `psql`), you're probably getting non-UTF-8 data from the web and failing to convert it, as Wolfgang explains in his answer. — Craig Ringer, Nov 14 '12 at 01:11

score 2 · Answer 1 · answered Nov 13 '12 at 18:24

The error message names 0x96, which does not look like UTF-8 but rather a dash in some 8 bit charset. Make sure that you are posting UTF-8, by setting the correct meta header in your HTML:

<meta http-equiv="content-type" content="text/html; charset=utf-8">

Or sending the charset with a content type HTTP header on the PHP side:

header('Content-Type: text/html; charset=utf-8');

UTF-8 encoding error during database migration

1 Answers1