Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Charset Issues With Users

forums forums SQLyog SQLyog BETA Discussions Charset Issues With Users

  • This topic is empty.
Viewing 6 reply threads
  • Author
    Posts
    • #9518
      peterlaursen
      Participant

      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!

    • #20793
      peterlaursen
      Participant

      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.

    • #20794
      peterlaursen
      Participant

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

    • #20795
      peterlaursen
      Participant

      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]

    • #20796
      peterlaursen
      Participant

      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

    • #20797
      peterlaursen
      Participant

      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.

    • #20798
      peterlaursen
      Participant

      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!

Viewing 6 reply threads
  • You must be logged in to reply to this topic.