Thoughts by Techxplorer

I’ve written before about my experiences with CodeIgniter and Oracle databases. When I started my project CodeIgniter was the only framework that worked Oracle databases. I noticed an issue today, it really isn’t a bug, and thought it worth sharing.

When using SQL queries with CodeIgniter (version 1.7) the framework will automatically check your SQL string for invisible characters it uses the following regular expressions.

// every control character except newline (dec 10),
//carriage return (dec 13), and horizontal tab (dec 09),
$non_displayables = array(
    '/%0[0-8bcef]/',// url encoded 00-08, 11, 12, 14, 15
    '/%1[0-9a-f]/',    // url encoded 16-31
    '/[\x00-\x08]/',// 00-08
    '/\x0b/', '/\x0c/',// 11, 12
    '/[\x0e-\x1f]/'    // 14-31
);

The issue for me was caused by the fact that the Oracle multi character wildcard operator is % and I legitmately needed to search for records that had a field that contained ‘%01%’. So for example my SQL code looked like:

SELECT field1, field2, field3
FROM table1
WHERE field4 LIKE '%01%'

Unfortunately once the query was prepared for sending to the Oracle database it had become:

SELECT field1, field2, field3
FROM table1
WHERE field4 LIKE '%'

Which is not what I had intended.

To fix the issue all I needed was to insert the single character wildcard _ into the query like this:

SELECT field1, field2, field3
FROM table1
WHERE field4 LIKE '%_01%'

In this way the value that I was using in the LIKE clause wasn’t affected by the escape routine because it no longer matched the URL encoded pattern.

This isn’t a bug as such, as the issue I had was rather specific to my code. It is better, I think, for a framework to be proactive about this type of thing and perhaps cause someone some issues in specific circumstances than not escape this type of thing at all.