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 : http://kunststube.net/frontback/ , and 2 SO questions that could be of help : http://stackoverflow.com/questions/8906813/how-to-change-the-default-charset-of-a-mysql-table and http://stackoverflow.com/questions/1294117/how-to-change-collation-of-database-table-column — Laurent S., May 24 '13 at 11:38
Very related but not directly: you shouldn't store plaintext passwords in the database...!?! — deceze, May 24 '13 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, May 24 '13 at 12:49
Don't use MD5, use something real: https://github.com/ircmaxell/password_compat — deceze, May 24 '13 at 13:20

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

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 '20 at 09:12

Community

1
1

answered May 24 '13 at 11:49

user4035

22,508
11
59
94

2

SET NAMES is not recommended in PHP. See: http://php.net/manual/en/mysqli.set-charset.php – Evert May 24 '13 at 12:20
1

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? – Felix Hindemo May 24 '13 at 12:53
@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? – user4035 May 24 '13 at 13:27
@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.. – Felix Hindemo May 25 '13 at 09:20
@FelixHindemo I was able to reproduce your error. Looking, what's wrong now. – user4035 May 25 '13 at 09:32
@FelixHindemo I found the reason: use utf8_swedish_ci as collation in your table and it will work. – user4035 May 25 '13 at 09:36
@user4035 That fixed it, I would not have thought of that! Thank you so much for your help kind sir :) – Felix Hindemo May 25 '13 at 09:46

MySQL query returning non UTF8 characters

1 Answers1