PHP - SQL query containing unicode is returning NULL for some reason

Question

I am trying to run this query:

SELECT trans FROM `dictionary` WHERE `word` LIKE 'Çiçek'

like this (relevant code):

function gettranslation($word){
    return $this->query("SELECT trans FROM `dictionary` WHERE `word` LIKE '$word'");
}
function query($query){
    $result=mysqli_query($this->conn, "set character_set_results='utf8'");
    $result=mysqli_query($this->conn, $query);
    return $row = mysqli_fetch_row($result)[0];
}

My mySQL table is made like this:

Word  | Trans
-------------
Flower| Çiçek
-------------
Çiçek | Flower

When the $word I pass to the gettranslation function is Flower, the result is Çiçek. However when I do the reverse, the result is NULL.

Also if I do var_dump on it, I get nothing. Not even an empty array.

Strangely enough, when I run the same query in PHPmyAdmin it runs fine.

Help?

Not sure if this is your issue, but you are risking sql injections, since you do not use the mysqli extension together with "prepared statements". This issue might be an effect of that. Apart from that you should check the encoding setting of all components: php, mysql connection, table creation, ... — arkascha
– arkascha, Commented Jun 12, 2015 at 19:48
@arkascha It's on a local system for now. I will secure it properly later. — Meeh
– Meeh, Commented Jun 12, 2015 at 19:51
@jaycp Apache/2.4.7 (Ubuntu) PHP/5.5.9-1ubuntu4.9 OpenSSL/1.0.1f Database client version: libmysql - 5.5.43 PHP extension: mysqli (Taken from PHPmyAdmin) — Meeh
– Meeh, Commented Jun 12, 2015 at 19:53
That approach is pretty risky, since you probably will not secure it before taking it "online". :-) Why not code it right now? It is not more effort. — arkascha
– arkascha, Commented Jun 12, 2015 at 19:53

Jayesh Chandrapal · Accepted Answer · 2015-06-12 20:02:47Z

2

As far as I remember, mysqli_query($con, "SET NAMES 'utf8'"); was required, like this:

function gettranslation($word){
    return $this->query("SELECT trans FROM `dictionary` WHERE `word` LIKE '$word'");
}
function query($query){
    //$result=mysqli_query($this->conn, "set character_set_results='utf8'");
    mysqli_query($con, "SET NAMES 'utf8'");
    $result=mysqli_query($this->conn, $query);
    return $row = mysqli_fetch_row($result)[0];
}

answered Jun 12, 2015 at 20:02

Jayesh Chandrapal

6847 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bobince · Accepted Answer · 2015-06-13 10:42:49Z

$result=mysqli_query($this->conn, "set character_set_results='utf8'");

This only affects the character set used for returned strings, not the character set for incoming queries. So your query is interpreted as if it were ISO-8859-1-encoded: LIKE 'ÃiÃ§ek'. This doesn't match any data in the table.

Instead, set the character set for the whole connection:

$this->conn->set_charset('utf-8');

It's better to do this once when you connect, rather than on every query.

(Never use SET NAMES. This sets the connection encoding for MySQL without letting PHP's mysqli know that the encoding has changed, so when it comes to mysqli_real_escape_string some content it will use the wrong encoding. If the encoding in question is an East Asian multibyte encoding that means you get an SQL injection hole.)

Collectives™ on Stack Overflow

PHP - SQL query containing unicode is returning NULL for some reason

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related