0

I have a PHP script which takes in POST params and saves them to a database. The script itself works fine, but if any of the POST params contains hebrew text, I get the following error:

error number 1366: Incorrect string value: '\xF2\xE9\xE3\xE5 \xF7...' for column 'Name' at row 1

The column definition is:

`Name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

The table and the database are also defined as CHARACTER SET utf8 COLLATE utf8_general_ci. Also, stripslashes is executed on the query before it is run.

The curious thing is that the original column definition did not include CHARACTER SET and COLLATE. When it didn't have those, running the same insert statement with hebrew characters through MySQL Workbench got me the same error - but only as a warning. Then I added the CHARACTER SET and COLLATE params to the column definitions, rebuilt the table and ran my queries again though Workbench - all good.

... but the PHP script still returns this error.

Does anyone know why this is happening? Can I suppress warnings in the SQL statement itself so as just to ignore these?

EDIT: PHP code:

function dbConnect() {
    global $host, $user, $pwd, $db, $MYSQL_ERRNO, $MYSQL_ERROR;
    $dbConn = mysql_connect($host,$user,$pwd);
if (!$dbConn) {
    $MYSQL_ERRNO = 0;
    $MYSQL_ERROR = "Could not connect to $host";
    return false;
} else if ($db && !mysql_select_db($db)) {
    $MYSQL_ERRNO = mysql_errno();
    $MYSQL_ERROR = mysql_error();
    return false;
} else {
    return $dbConn;
}
}

$link = dbConnect() or die(sql_error());
$query="INSERT INTO maillist (Name,LastName,email,status,sex,City,date_added) VALUES (N'".filter_var($_POST['Name'], FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES)."',N'',N'".filter_var($_POST['email'], FILTER_SANITIZE_EMAIL)."','0',N'',N'',CURDATE())";
$query = stripslashes($query);
$result = mysql_query($query,$link) or die(sql_error());
user884248
  • 2,134
  • 3
  • 32
  • 57
  • Show us your query and your php. – kmas Dec 10 '13 at 19:39
  • 1
    Well, it's not UTF-8 what you're trying to insert... It's `"òéãå ÷"` in ISO-8859-15. Make sure you know what your inputs are, and set the correct character set on your connection. If you'r going utf-8, your site should be [utf-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) – Wrikken Dec 10 '13 at 20:02
  • Can you confirm that xF2xE9xE3... is one or more correctly UTF-8-encoded Hebrew characters? If not, by any chance could the stripslashes() call be corrupting Hebrew characters (that happen to contain a \ ?) I'm also curious why you would be stripping slashes off a database query -- normally you would be adding slashes to disable ' in data. Anyway, try commenting out the stripslashes and see if it works then (proving that's the problem). I don't think stripslashes() is UTF-8 aware. – Phil Perry Dec 10 '13 at 20:33
  • stripslashes was actually there by mistake, thanks Phil for spotting that one, but removing it didn't help. @Wrikken - I cannot change the originating page's encoding, but running mb_convert_encoding($_POST['Name'], 'utf-8', 'ISO-8859-8') solved the problem. Thank you all for your help! – user884248 Dec 10 '13 at 21:36
  • Ah, yes, in different character sets it might as well be Hebrew, not very familiar with those. The \'s are just a representation in the error message, showing you it isn't the bare string `xF2xE9xE3` but a hexadecimal representation of the bytes. Note BTW: if your _whole_ site works as `ISO-8859-8`, it could wiser to set the database connection to that character set rather then to convert individual inputs: `SET NAMES 'hebrew';` would do it here. Run `SHOW CHARACTER SET;` in MySQL to see if you have that available. – Wrikken Dec 11 '13 at 07:47

1 Answers1

0

mark s SOLVED, I found a solution.

It is.

[mysqld] character-set-server = utf8 should be changed to latin1

restart mysql

user3228714
  • 1
  • 1