2

I wrote a PHP script to harvest some data that I want to enter into a database. This includes user's usernames that need to be entered, some users chose usernames with upside down text, i.e, 'uıɯpɐ'

I get a collation error when I use that username in a WHERE clause, is there a way for php to sanitize that before going into the database to avoid this? 

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

The way the script is set up I don't have much of a choice but to use the username in the where clause.

AlphaOmega
  • 117
  • 10
  • Can't you change the text codification of the database? Then it could be able to deal with it. – Héctor E Jul 01 '15 at 23:30
  • Another option I think of would be to not store the usernames, just their hash codifications, or the ASCII code, and whenever they log in, use a cookie to store and show them their usernames. – Héctor E Jul 01 '15 at 23:32
  • There's thousands of entries most are fine, is there a way to allow these type of characters, with the default character set with a SQL query? – AlphaOmega Jul 01 '15 at 23:45
  • @AlphaOmega are you able to insert into db but not use in where clause ? – Hitesh Mundra Jul 02 '15 at 00:08
  • Yes it appears it only encounters the error, when it's being used in a followup where clause AFTER an insert. – AlphaOmega Jul 02 '15 at 00:14
  • @AlphaOmega select that record & see the value for username stored correctly ? – Hitesh Mundra Jul 02 '15 at 00:20
  • No, it inserted as something like u??p? – AlphaOmega Jul 02 '15 at 01:20
  • There are some different solutions that might work: http://airbladesoftware.com/notes/fixing-mysql-illegal-mix-of-collations/, https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql – Héctor E Jul 02 '15 at 09:58
  • http://stackoverflow.com/questions/279170/utf-8-all-the-way-through/279279#279279 – Rick James Jul 02 '15 at 21:17

1 Answers1

0

uıɯpɐ comes from someone messing with your head. It is characters scrounged from various UTF-8 charsets to make what looks like an upside down admin. But note that he used a "dotless i" (a Turkish character) to sort of create the upside down i.

You should change any columns that need to have arbitrary characters from latin1 to utf8mb4. If you still have collation errors, we can discuss what else needs changing. I can't be more specific without seeing the statement causing the error, SHOW CREATE TABLE and the connection parameters.

More UTF-8 tips: Trouble with UTF-8 characters; what I see is not what I stored

Rick James
  • 135,179
  • 13
  • 127
  • 222