Charset Issues With Users

[peterlaursen]

Now I tried something more:

Logged on local server vith direct connection. Create user 'ølhund'. This word 'ølhund' is one of those that I cannot write with HTTP. I can now log on as user 'ølhund' usin direct connetion.

Tried next to log on as 'ølhund' with HTTP with three diferent default charset settings for the server: latin1, utf8 and ucs2. All three failed, but note that error messages is different with each case!

[peterlaursen]

now: creating this user: 'schäferhund'. This is a word that I CAN write with HTTP.

howerver logging on as that user with HTTP gives exactly the same as first post.

[peterlaursen]

And this is independent of PHP versions from 4.3.11 to 5.1.2 (the ones that my Apache 2.0.55 accepts)

[peterlaursen]

Now a little more.

1) Using 'ølhund' as password of course fails too with HTTP. *Access denied …etc*. That is true if user is created with direct connection! If user is created with HTTP-connection you can connect with HTTP, but not with direct connection!

2) let's have a look at the MySQL user table. It ALWAYS (no matter the actual charset settings) starts like

Code:

*Column Information For – mysql.user*/
—————————————

Field Type Collation
——————— ——————————— —————
Host char(60) utf8_bin
User char(16) utf8_bin
Password char(41) latin1_bin
Select_priv enum('N','Y') utf8_general_ci
Insert_priv enum('N','Y') utf8_general_ci

and all following columns are utf8_general_ci

Note particularly that Password is ALWAYS latin1! Other columns are UTF8.

Undoubtedly the C-API handles all this. But how to make PHP do?

php_mysqli extension has the mysqli_set_charset() function. php_mysql extension has not.

maybe this is not working with at all as of now. Conversion to UNICODE/UTF8 codebase with SQLyog should solve the issues – except possibly for password (i don't recall if ANSI and UTF maps characters 128-255 identically – but I doubt!) – but maybe MySQL converts itself?

3) Have a look at attached! Four users are created (created with SQLyog and direct connection):

'ølhund','øresundsfærge','tøsedræng','schäferhund'. They display like it shows in SQLyog. I can connect with all 4 with 'MySQL administrator' so they undoubtedly are saved correctly – SQLyog probably just is not aware that it is UNICODE! But all characters here are ANSI/latin1 – I would not try to guess what would happen if a character outside the ANSI/Latin1 range was used. Other users that have a change to try?

Also note that 'ølhund here is displayed as an empty string. And actually sometimes when I try to insert 'ølhund' MySQL returns the error 'query was empty'!

A Quote: http://www.acko.net/php-no-unicode-support

Quote:

This means that if you want to make a PHP application which supports any language and runs on the average PHP host out there, that there's only one option: use UTF-8 internally, and write your own functions for string truncation, email header encoding, validation, etc. Using UTF-8 ensures that you only have one encoding to worry about and because it's Unicode it is guaranteed to be able to represent any language.

Screenshot above shows user table as it ooks from SQLyog using HTTP.[attachment=387:attachment]

And this is with direct connection (as it should be): [attachment=388:attachment]

[peterlaursen]

a little search:

http://support.microsoft.com/default.aspx?…Ben-us%3B138813

http://www.datamystic.com/textpipe/unicode_conversions.html

http://www.codeguru.com/forum/archive/inde…p/t-231165.html

http://www.firstobject.com/dn_markutf8ansiconv.htm

Conversion between ANSI and UTF-8 with the UTF8ToA and AToUTF8 functions is now supported in the Developer version of Release 6.6 available to those who have purchased a CMarkup Developer License. See UTF-8 ANSI Conversion Functions

[peterlaursen]

and:

http://dk.php.net/utf8-decode

http://dk.php.net/utf8-encode

I think usernames must be utf8-encode()d

so use something like mysql_connect(host,utf8-encode(user),password)

This works with a brief test in tunnelling file

$mysql= mysql_connect ( “$host:$port”, “utf8-encode($username)”, $pwd );

In a similar fashion they must be decode(d) when read from user table if SET NAMES does not SET utf8.

And actually the SET NAMES as of now does SET NAMES per database. As different columns may have different charset it should be done on a column-basis (ideally)

unfortunately I can't find a similar somple way for passwords.

But I also am not Ri Coder 😀

maybe it is necessary to hard-code a conversion table from ISO-8859-1 or utf-8 (if used with the tunnelling file) to MySQL latin1 into tunnel-file if pw's with special character shall work.

[peterlaursen]

want to add: I did not check the CHARSET of user table with early MySQL version (3.23 and 4.0) It might be utf7!

[Author]

Posts