Failing to insert Foreign Language characters into DB

Question

I'm trying to insert non-english characters (eg:Josef Kricenský) into my mysql database through php

Here is my code

$name=addslashes("Josef Kricenský");
$id=1;
mysql_query("'SET NAMES 'utf8'");
mysql_query("insert ignore into names (id,value) values ('$id','$name')");

Data is being inserted into the table but it is only inserting upto the 'ý', i.e : Josef Kricensk

utf8_general_ci is the table collation, MyISAM is the storage engine, This code has worked before on my other site, but there seems to be something missing with this.

If I insert it through phpMyadmin it works, so my guess is that the code is lacking something, BTW php version is 5.2.7

Is your PHP file also UTF8? Because if you are doing the editing in a non unicode plaintext file, the ý has a very different code. If the names come from the web, are you sure they are encoded correctly, before (is the page in UTF8)? Does temporarily remove the addslashes() change anything? — FrancescoMM, Dec 05 '12 at 11:27
This will only work if your php page has the correct encoding as well. Depending on the code editor you are using, most of them will give you an option to promote to a different encoding. — Tim Joyce, Dec 05 '12 at 11:28
Ditto above. Plus I also do a "SET CHARACTER SET utf8" in addition to SET NAMES utf8 - note I do not put a quote around utf8 - not sure if that may be an issue, but just looked at what I do in my code that works — KevInSol, Dec 05 '12 at 11:30
possible duplicate of [UTF-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Pekka, Dec 05 '12 at 11:40
`addslashes()` is NOT suitable protection against SQL injection. You need to use `mysql_real_escape_string()`. — Pekka, Dec 05 '12 at 11:41
The size of the column is large enough for the fullname, correct? And the charset of the name is UTF-8 also? If it is in a different charset you will need to convert it — Leandro Barreto, Dec 05 '12 at 11:43
tried with and without ' around utf8, still the same result, probably you guys are right about the php editor not supporting non-english character, — Mr poppins, Dec 05 '12 at 11:43
For example, while using Notepad++ you can set the page charset. The default is ANSI, but i rather use UTF-8 — Leandro Barreto, Dec 05 '12 at 11:46

VolkerK · Accepted Answer · 2012-12-05T11:55:47.103

self-contained example that seems to work fine on my machine...

<?php
// added numeric code entity to reflect actual code of OP
// $param = 'Josef Kricenský';
$param = 'Josef Kricensk&#253;';
$param = html_entity_decode($param , ENT_XML1, 'UTF-8');

// this is not how you check for utf-8 encoding
// ...except  in this simple example ;-)
// all characters but the ý are encoded in one byte, ý uses two bytes 
// -> 16 bytes -> strlen($param) should be 16
if ( 16!=strlen($param) ) { 
    die("this doesn't seem to be utf-8 encoded");
}

// the mysql_ api is deprecated. Take a look at e.g. http://docs.php.net/pdo for alternatives
$mysql = mysql_connect('localhost', 'localonly', 'localonly') or die(mysql_error());
mysql_select_db('test', $mysql) or die(mysql_error($mysql));
// SET NAMES 'utf8' doesn't tell the client library about the changed encoded
// so e.g. mysql_real_escape_string() may fail
// see http://docs.php.net/mysql_set_charset
mysql_set_charset('utf8', $mysql) or die(mysql_error($mysql));

// using a temporary table for this example...
setup($mysql);


// insert a record
echo "insert a record\n";
$query = sprintf("INSERT INTO soFoo (name) VALUES ('%s')",
    mysql_real_escape_string($param, $mysql)
);
mysql_query($query, $mysql) or die(mysql_error($mysql));

// retrieve the record
echo "retrieve the record\n";
$result = mysql_query('SELECT name FROM soFoo', $mysql) or die(mysql_error($mysql));
while( false!==($row=mysql_fetch_assoc($result)) ) {
    echo 'strlen($row[name])==', strlen($row['name']), "\n";
}

function setup($mysql) {
    mysql_query('
        CREATE TEMPORARY TABLE soFoo(
            id int auto_increment,
            name varchar(32),
            primary key(id)
        ) DEFAULT CHARSET=utf8
    ', $mysql) or die(mysql_error($mysql));
}

prints

insert a record
retrieve the record
strlen($row[name])==16

I tried to fetch a foreign character string from database and then insert it into another table, it is working fine. But in my actual code I am converting hex code to special characters using html_entity_decode() and then inserting it into table — Mr poppins, Dec 05 '12 at 11:49
hi volkerk, thanks for your effort pal, try ý instead of ý html_entity_decode doesn't seem to convert it to corresponding character — Mr poppins, Dec 05 '12 at 12:06
Using ý it still works fine for me. I'm using a PHP 5.4.7/win32 build from php.net — VolkerK, Dec 05 '12 at 12:10
html_entity_decode() expects parameter 2 to be long, string given in — Mr poppins, Dec 05 '12 at 12:16
"5.4.0 The constants ENT_HTML401, ENT_XML1, ENT_XHTML and ENT_HTML5 were added." You're using php<5.4.0. But even with `html_entity_decode($param , 0, 'UTF-8');` it works here. — VolkerK, Dec 05 '12 at 12:18
Thanks mate, I tried, html_entity_decode($string,ENT_QUOTES,'UTF-8'); and it is working, thank you very much, Have a good day — Mr poppins, Dec 05 '12 at 12:21

score 0 · Answer 2 · answered Dec 05 '12 at 11:56

0

Try running this.

<?php

$name = "Josef Kricenský";
$id = 1;

if( substr( $name, 14, 2 ) !== "\xC3\xBD" ) {
    trigger_error( "This file was not saved in UTF-8. Set your text editor to save the file in UTF-8" );
}

//Connect to mysql

mysql_set_charset( "utf8" );


$name_safe = "'" . mysql_real_escape_string( $name ) . "'";
$id_safe = (int)$id;

mysql_query( "insert ignore into names (id,value) values ($id_safe, $name_safe)" );

answered Dec 05 '12 at 11:56

Esailija

138,174
23
272
326

Hi Esailija Thanks for the code, I tried it and the error was triggered – Mr poppins Dec 05 '12 at 12:01

Failing to insert Foreign Language characters into DB

2 Answers2