[Bypass] MySQL Error: illegal mix of collation

[Bypass] MySQL Error: illegal mix of collation

Collations and character sets enable MySQL to do a comparison of the strings and this error probably come when you try mixing different collations/character sets in a select statement.

If you have no idea about SQL Injection consider reading the Basics of SQL Injection first.

Illegal mix of collation
Illegal mix of collation

What you can do to solve the problem and get rid of this error?

Well, you can solve this error by changing the default values of collation/ character sets of the database you are working with following query:

Note: It will only effect on the tables you will create in the future. To make it work with existing tables, use following query:

How to bypass this error as an SQL Injector or In SELECT statement?

There are several ways you can bypass illegal mix of collations in SELECT statements.

  1. Using Cast function.
  2. Using convert function.
  3. Using HEX/ UNHEX functions.
  4. Using Compress/uncompress functions.
  5. Using encode/decode functions
  6. Using AES Encryption

 


Usage of Cast():

One can overcome this error by using the CAST function. The CAST() function converts a value of any type into a value that has a specified type. Type can be one of the following:

  • Binary
  • Datetime
  • Char
  • Date
  • Time
  • Decimal
  • Signed or Unsigned
 
Syntax:

Example:


Bypass illegal mix of collations with AES_Encrypt() and AES_DECRYPT().

AES_DECRYPT() function encrypts a given string using a key and returns a binary string containing the encrypted output.
And the function AES_DECRYPT takes input of encrypted string and the same key to decrypt it. These two functions can also help us in overcoming illegal mix of collation problems.
Syntax

Example:


Bypass illegal mix of collations with Convert function
The MySQL CONVERT function converts a value from one datatype to another, or one character set to another.
Syntax:

Example:


Bypassing illegal mix of collations using ENCODE(), DECODE()
MySQL ENCODE() function encrypts a string. This function returns a binary string of the same length of the original string. And MySQL DECODE() function decodes an encoded string and returns the original string

Syntax:

Example:


COMPRESS() and UNCOMPRESS() Functions
Compress () functions compress a string and give the result as  a binary string.
And that compressed string can be uncompressed by uncompressed() function later.

Syntax: 

Example:


Bypassing illegal mix of collations using the HEX() and UNHEX() functions

HEX() returns a string representation of the hexadecimal value of a decimal or string value specified as an argument. If the argument is a string, each character in the argument is converted to two hexadecimal digits. If the argument is decimal, the function returns a hexadecimal string representation of the argument, and treated as a longlong(BIGINT) number and the UNHEX() reverse this hexadecimal string back to the Original string.

Syntax:

Example:

And it will return version info. 

Thank you for reading the article, I hope it helps you. And If you find any other method or idea, Share it with us. 




Comments are closed.