1

What is the right way to escape text that is passed to query with LIKE pattern matching? The problem is that

select()->where('field LIKE ?', $input . '%');

will be incorrect with

$input = '%sometext';

UPDATED: 'vulnerable to' -> 'incorrect with'

eater
  • 408
  • 3
  • 9

1 Answers1

0

It's not vulnerability, is it? It's valid content. If it poses vulnerability to your application (like WHERE user LIKE '%admin%') you should consider validating/filtering the input yourselves using sth like:

if (strpos('%', $input)){
     $input = strtr($input, '%', '');
}
Tomáš Fejfar
  • 11,129
  • 8
  • 54
  • 82
  • OK, maybe 'vulnerable' is not the right word, I think better would be 'incorrect' (updated in question). The problem is that if user wants to find text '100% sure' he will receive result with '100 miles away, for sure' (just an example). This is not exactly what user expected to see. And even more, there are more characters that are special in different dialects of SQL. So I think it is obvious that plain replacing special symbols is not very good, the whole point of ORM will be lost if I'll hard-code these for MySQL. – eater Jun 04 '11 at 21:37
  • Ok. So how should the DBLayer decide if you want to search for '100%' and where for '100(.*)' ? – Tomáš Fejfar Jun 05 '11 at 21:22
  • I think DB layer must have method to escape input that is passed to LIKE queries. In MySQL it can be simple addcslashes($str, '_%'); But set of special characters in different DBs can be different and it would be great to have this kind of function for each DB (in adapter, I think). F.e. in MSSQL LIKE queries can include [a=z] to match against list of character, and without proper escaping it would be impossible to search for string with '[10]' in it. – eater Jun 05 '11 at 22:22
  • So answering your question I can write pseudo-code like this: $input = $db->escapeForLike($inputFromUser); select()->where('text LIKE ?', $input . '%'); to search all texts that begin with string passed from user, even those that begins with '%'. – eater Jun 05 '11 at 22:22
  • 1
    It's always good idea to create issue for missing features in ZF. And that's what I did: http://framework.zend.com/issues/browse/ZF-11445 – Tomáš Fejfar Jun 06 '11 at 10:25
  • This is great Tomáš, thanks. I didn't know whether to do that because I just started to use Zend Framework and was not sure what is the right way to queries with LIKE. I thought that maybe I'm wrong and this is not the right way to do LIKE queries. – eater Jun 10 '11 at 12:16