0

DB and fields are UTF8_unicode_ci collation - MySql v 5.6

HTML charset is utf 8

Double byte script was entered successfully prior to this issue (Chinese, Japanese, plus Cyrillic etc) when using plain non encrypted input.

But trying to use AES_ENCRYPT on input mangles all non-english script. DB field is BLOB.

 UPDATE mytable SET bodytext=AES_ENCRYPT('任車心是,防少劇和的;子反華不就','aeskey') WHERE t_id=123

produces strings of question marks - English is fine though

I'm not sure if I should try to CONVERT the input to UTF8 first - but when I try that I get an error. Any suggestions of what to review?

  • 3
    Which DBMS is this? MySQL? SQL Server? And which version? You should tag the question with this info as different DBMSes and versions behave differently. – dbdemon May 8 '18 at 16:17
  • What do you mean by "double byte script"? – Rick James May 23 '18 at 21:15
-1

Forcing utf8mb4 does the trick. Run a query against MySQL immediately after making the connection with:

SET NAMES'utf8mb4';

Alternatively, use these settings in your my.cnf file to get the same effect:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

(Note: These settings are defaults (for the most part) in MySQL 8.0 and MariaDB 10.3, so this probably isn't a problem there.)

Chinese (and other) scripts are multibyte - standard utf8 allows 3 bytes per character - fine for plain text but using AES_ENCRYPT ON 3 bytes requires another byte - thus setting utf8mb4 allows the required 4 bytes per character.

  • 1
    Can you clarify how this solves the problem, and where it's applied? – RDFozz May 8 '18 at 17:20
  • Updated the answer - I'm not used to answering my own questions so not sure why I got the downvotes. – anoldermark May 8 '18 at 18:07
  • What? AES_ENCRYPT does not look at characters. It looks at byte strings. You need utf8mb4 because some Chinese characters are 4 bytes. – Rick James May 23 '18 at 21:17
  • I don't think those my.cnf settings are an accurate replacement for the SET NAMES. – Rick James May 23 '18 at 21:18
  • I didn't add that abut my cnf - some editor here did that. Bad habit if you ask me. (insert paragraph break - oh right you can't add one of the most semantically useful styling breaks) I don't know about utf8mb4 being required for chinese - it's not- the database has been taking Chinese input (and Japanese and Korean) perfectly fine with uft8 for the past two years and outputting to PDF and web - and lots of people have read the output to verify it was fine. – anoldermark May 24 '18 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.