Postgresql PHP invalid byte sequence for encoding UTF8

Question

I have a simple SQL syntax for inserting to table. I'm using Postgresql 8.4 and already set Database encoding to be UTF8, and POSIX for Collation and Character type.

The query is fine if i run it under pgadmin3, but bring error if i execute in PHP.

"Internal Server Error: SQLSTATE[22021]:
Character not in repertoire: 7 ERROR: 
invalid byte sequence for encoding \"UTF8\": 0xd85b\nHINT:
This error can also happen if the byte sequence does not match the encoding expected by the server,
which is controlled by \"client_encoding\"

So i tried to set NAMES and client_encoding from PHP(PDO), but still have the same problem

$instance->exec("SET client_encoding = 'UTF8';");
$instance->exec("SET NAMES 'UTF8';");

pg_set_client_encoding($link, "UNICODE"); my be work if i'm using native postgresql driver pg_pconnect, but currently i'm using PDO as Driver.

and i also already set mb_internal_encoding('UTF-8');

Is there any other way to fix this issue ?

This error only appear when i trying to insert non ascii word like arabic or japanese word

Answer 1

Try to encode into utf-8 with utf8_encode().

$query = "INSERT INTO student (id, firstName, lastName, age) VALUES (1, 'myFisrtName', 'myLastName', 23)";

pg_exec($connection, utf8_encode($query ));

Answer 2

Answering on an older post but, I just had a similar situation, during a CSV import, I noticed the error: invalid byte sequence for encoding "UTF 8": 0x95 in ....

I've fixed the error by just converting encoding from Windows-1252 to UTF-8 in PHP by using: mb_convert_encoding($fieldValue,'UTF-8','Windows-1252')

    $query = "INSERT INTO student 
              (id, firstName, lastName, age) 
              VALUES 
              (1, '".mb_convert_encoding($firstName,'UTF-8','Windows-1252')."', 
                  '".mb_convert_encoding($lastName,'UTF-8','Windows-1252')."', 23)";

Hope this will help someone.

Answer 3

I'am will be can't submit correct unicode SQL-query (quercus for java vary bad work from unicode and all like "SET NAMES 'UTF8';" no working) , and I resolve this from Base64 convertation:

$name_enc = base64_encode($name);       
$res = $db->prepare(
            'INSERT INTO "MyTable"("ID", "Name") VALUES
               (   nextval(\'gen_addresses\'), 
                   convert_from(decode(?, \'base64\'), \'UTF8\'));' 
     )->execute(array($name_enc));