MySQL query returning non UTF8 characters

Question

I've got a login screen that checks entered username and password against a MySQL database. My problem is that it doesn't recognize Swedish characters like "ÅÄÖ".

For example, the password "lösenord" is in the database but it isn't accepted, however "losenord" is.

The database has "utf8_general_ci" connection collation and I've set the charset to UTF-8 in my index.html but not in my php scripts. I've read what feels like a million different ways to solve UTF 8 issues like this but I can't get it to work.

If someone could at least point me in the right direction I would be very thankful. Do I need to encode each mysql query, set some META tag?

Cheers

Here is a very complete article on the subject : kunststube.net/frontback , and 2 SO questions that could be of help : stackoverflow.com/questions/8906813/… and stackoverflow.com/questions/1294117/… — Laurent S.
– Laurent S., Commented May 24, 2013 at 11:38
Very related but not directly: you shouldn't store plaintext passwords in the database...!?! — deceze
– deceze ♦, Commented May 24, 2013 at 11:43
My rows are using the same charset. I'm planning to use MD5 on the passwords later, I just need to solve this UTF8 issue first since I will store other things in the database that will and should be plaintext. Thanks for the reminder though :) — Felix Hindemo
– Felix Hindemo, Commented May 24, 2013 at 12:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

Try using SET NAMES 'UTF8' after connecting to MySQL:

$con=mysqli_connect("host", "user", "pw", "db");   
if (!$con)
{
    die('Failed to connect to mySQL: ' .mysqli_connect_errno());
}

/* change character set to utf8 */
if (!$con->set_charset("utf8")) {
    printf("Error loading character set utf8: %s\n", $con->error);
}

As the manual says:

SET NAMES indicates what character set the client will use to send SQL statements to the server... It also specifies the character set that the server should use for sending results back to the client.

Also use utf8_swedish_ci in your table, otherwise string comparison will go wrong and MySQL will treat 'ö' and 'o' as the same character.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 24, 2013 at 11:49

user4035

24k11 gold badges69 silver badges101 bronze badges

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

7 Comments

Evert Over a year ago

SET NAMES is not recommended in PHP. See: php.net/manual/en/mysqli.set-charset.php

Felix Hindemo Over a year ago

I's been some time since I did any major PHP work and I have some trouble understanding your code. if (!$con->set_charset("utf8")) If the connection cannot be made, set the charset to utf8?

user4035 Over a year ago

@FelixHindemo It means: if he can't switch charset to utf8, print error message. But most often it won't fail here. Did you try anything?

Felix Hindemo Over a year ago

@user4035 Yeah, I got it partly working. $db_charset = mysql_set_charset('utf8', $db_connect); It now accepts both examples as the correct password (lösenord & losenord) Not really sure why though..

user4035 Over a year ago

@FelixHindemo I was able to reproduce your error. Looking, what's wrong now.

|

Collectives™ on Stack Overflow

MySQL query returning non UTF8 characters

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related