Textpattern CMS

#1 2006-02-22 14:28:14

plw

New Member

Registered: 2006-01-28

Posts: 3

PostgreSQL: "LIKE" queries are case sensitive

Hello, apparently contrary to MySQL, queries with “LIKE” in PostgreSQL are normally case sensitive (correctly,
according to the SQL Spec).

This shows up when using sections and/or categories, as they can be entered as e.g. “Category”. Publish.php
then looks into the database with a lower-case string, yielding no results on PostgreSQL.

I added a function “db_like” into lib/mdb/pq.php which uses the Postgres-specific case-insensitive ILIKE and
modified publish.php to use this on the category case (see below). I did not do any futher hacking to other
DBs and/or other cases.

Is this the correct way? If yes, I’d dig deeper into the code to look for LIKEs etc. Can’t
test on other DBs though.

publish.php:
<pre>
—- publish.php.was Wed Feb 22 15:10:11 2006
+++ publish.php Wed Feb 22 15:02:38 2006
-913,7 +913,7 // ——————————————————————————————- function ckEx($table,$val,$debug=’‘) {
- return safe_field(“name”,‘txp_’.$table,“name like ‘“.doSlash($val).”’ limit 1”,$debug);
+ return safe_field(“name”,‘txp_’.$table,“name “.db_like().“ ‘“.doSlash($val).”’ limit 1”,$debug); }

// ——————————————————————————————- </pre>

mdb/pg.php:
<pre>
—- pg.php.was Wed Feb 22 15:11:39 2006
+++ pg.php Wed Feb 22 15:02:05 2006
-155,6 +155,10

return ‘~*’; } +function db_like() { + + return ‘ILIKE’; +} function db_affected_rows(){

</pre>

hth

#2 2006-02-22 15:32:50

plw

New Member

Registered: 2006-01-28

Posts: 3

Re: PostgreSQL: "LIKE" queries are case sensitive

Investigating this further, there’s more:

suppose there is a category ‘Foobar’. it gets written into the DB (at least on Postgres) as ‘Foobar’.
Now when quering for articles, publish.php always uses the lower-cased names, which results in queries like:

<pre>
$category = (!$category) ? ‘’ : “ and ((Category1=’”.doslash($category).”’) or (Category2=’”.doSlash($category).”’)) “;
</pre>

ofcourse not working, as this would look for articles in the DB with a category=‘foobar’. Postgres does not find
any with those, which is clear since the Category is called ‘Foobar’ and not ‘foobar’.

I’m a little bit stuck where the right place to fix this is.

Changing the above line in publish.php to
<pre>
$category = (!$category) ? ‘’ : “ and ((Category1 “.db_like().” ‘”.doslash($category).”’) or (Category2 “.db_like().” ‘”.doSlash($category)
.”’)) “;
</pre>

with db_like() from mdb/pg.php fixes the problem here.