Mysqli Update UTF8

Question

So, when I update stuff in my database it turns for example this: Š to this: u0160 it would be ok if it turned into this: \u0160 so it would display right but it doesn’t.

This is my connection file:

<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', 'pwd');
define('DB_DATABASE', 'database');
$db = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
mysqli_set_charset($db,"utf8");
mysqli_query("set names utf8");
?>

And this is the query:

mysqli_query($db, "UPDATE schools SET subjectData='$json' WHERE id='$schoolID'");

Value of $json: {"subject":"A","desc":"A\u017d"}
Value of db entry: {"subject":"A","desc":"Au017d"}

shownplease your table charset declaration? and value of your `$json` variable? and the code where you try to output values from databasE? — Alex, Feb 12 '15 at 23:44
Is this really in your db or just when you output it?Look here http://stackoverflow.com/questions/279170/utf-8-all-the-way-through — Mihai, Feb 12 '15 at 23:46

score 4 · Answer 1 · edited May 23 '17 at 11:56

4

This has nothing to do with UTF-8 at all, you're only handling pure ASCII values. Your issue is that a backslash, a necessary part of the JSON encoding, is disappearing as you enter it into the database. The reason for that would be that you're not escaping your data correctly. A backslash has a special escaping meaning in SQL, if you want to insert a literal backslash into the database it requires escaping. With mysqli, you should be using prepared statements:

$stmt = $db->prepare('UPDATE schools SET subjectData = ? WHERE id = ?');
$stmt->bind_param('ss', $json, $schoolID);
$stmt->execute();

See How can I prevent SQL injection in PHP? and The Great Escapism (Or: What You Need To Know To Work With Text Within Text).

edited May 23 '17 at 11:56

Community

1
1

answered Feb 13 '15 at 01:17

deceze

510,633
85
743
889

you just missed `$` dollar sign in `$schoolID` – Alex Feb 13 '15 at 02:27
Although your answer is correct, I am using mysqli procedural! – Uranium Developers Feb 13 '15 at 08:01

Alex · Accepted Answer · 2015-02-13T02:28:19.923

So you showed the variable value, but you didn't show the code where you extract values from database.

And another note : your value is not really utf8, it is json encoded string:

{"subject":"A","desc":"A\u017d"}

As you see, the original string is not equal to value stored in database:

{"subject":"A","desc":"Au017d"}

Saving raw json in database is not very best practice. I would suggest to save just values not objects.

But if you need this, you should use this when you insert and/or update records:

mysqli_query($db, "UPDATE schools SET subjectData='".mysqli_escape_string($json)."' WHERE id='$schoolID'");

Mysqli Update UTF8

2 Answers2