escapeLike issue on postgre

[cdujeu]

Hi David,

First of all, thanks for your great work. As you maybe already know, Dibi is our historical ORM for the Pydio project, and we love it: doing the job efficiently, and very lightweight (we embed the minified version).
I'm not totally sure this is an issue, or maybe a postgre config or version problem, I must admit I'm not a PG expert. We are encountering an issue with LIKE queries containing an underscore, and I happened to fix this by changing the escapeLike function of the PG driver to replace '' by _ instead of \ .
Which gives for the escapeLike function

function escapeLike($value,$pos){
    $value=pg_escape_string($this->connection,$value);
    $value=strtr($value,array('%'=>'\\%','_'=>'\\_'));
    return($pos<=0?"'%":"'").$value.($pos>=0?"%'":"'");
}

Do you know if it's a bug or something wrong on my -db- side?
Using PG 9.3 BTW.

Cheers
Charles

[milo]

The PostgreSQL specifies two special chars in LIKE pattern: the _ and %. So, this two must be escaped in user input.

Problem may be the standard_conforming_strings directive.

Imho, the right solution is the:

return 'E' . ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");

It should work with any PostgreSQL setting. Can you confirm that?

[JanTvrdik]

Does this directly affect only LIKE?

[cdujeu]

hi guys
sorry I was not available yesterday.
from my initial debugging / testing, escaping with \ was finally executing a request with eg. _ instead of _
That's why I changed the line.
I'll try with your last commits and tell you.
And yes it was affecting only like, as _ is a specific wildcard used in LIKE queries.
-c

[milo]

@cdujeu I hope it is solved by #160

[cdujeu]

@milo currently using the minified version, could you generate such a file including your latest commits? Will be more straightforward to test... thx in advance

[milo]

@cdujeu Unfortunately not. @dg has the minifier. The /dibi only directory and the dibi/dibi.php loading should work...

[cdujeu]

@dg any quick help on this ;-) ?

[cdujeu]

Confirmed. Thank you so much to all for your reactivity.