SQL Injection (Primer 2) - Collation / Case Insensitive and WAF bypassing

SQL injections are prevalent in all type of scenarios. With the advent of Web application Firewalls (WAF's) there are number of protection mechanisms that have been developed to prevent injections. WAF's are good solutions but these are not that good enough to prevent advanced level of SQL injections. However, during the course of my experience, I have noticed case sensitive / insensitive plays a critical role in designing bypasses.

The SQL queries are case insensitive. This feature of SQL databases (MySQL/ MSSQL)
helps a lot in designing WAF bypasses. The reason is, WAF's are mainly signature specific in most of cases which are explicitly written. By default, regular expressions are case sensitive. It is also possible to control case sensitivity within a pattern using the inline modifier (?i). However, it makes the signature really complex and hard to manage in certain scenarios. But this applied procedure is not hard to implement.

Note: The examples are taken from the real time websites and applications. The websites names have been masked for security purposes.

For example, the following SQL injection is usually filtered by the WAF in number of cases.
http://www.example.com/news_political.php?recordID=100+and+1=2+union+all+select+1,2,concat(Count(*)),4,5,6+from+information_schema.table_constraints--

The point is to break the filter at any one point so that whole of regular expression fails. The following code allows

http://www.example.com/news_political.php?recordID=100+aNd+1=2+uNioN+aLl+sElecT+1,2,CoNcaT(Count(*)),4,5,6+fRoM+information_schema.table_constraints--

In the above presented SQL injection, case sensitive approach is used to bypass WAF and at last case insensitive approach is exploited in order to render injection successfully. This injection can further by obfuscated using meta characters (/* ! */) , // , # +- , ; , # as pointed below

http://www.example.com/news_political.php?recordID=100/*!+aNd*/+1=2+uNioN+aLl+sElecT+1,2,CoNcaT(Count(*)),4,5,6+fRoM+information_schema.table_constraints--

One can design plethora of combinations based on the above discussed specifications in order to test for SQL injections in different environments.

All the informational issues discussed above are usually due to case insensitive nature of databases. There is a way, one can write SQL queries that are case sensitive using Collation procedure in databases. You can check for collations here. However, this process depends on the application design. It is something that developers implement by choice or when it is critically required.

Follow this !