How can I escape complex sql in Zend Framework?

Question

I have the following sql (a simplification of the real problem):

SELECT *
FROM t
WHERE myname LIKE '%{$input}%';

How do I escape the $input?
I can't use the quoteInto (unless I miss something).
As

$sql=$DB->quoteInto("SELECT *
                     FROM t
                     WHERE myname LIKE '%?%'",$input);

Will give me

SELECT *
FROM t
WHERE myname LIKE '%'my input'%';

and

$sql=$DB->quoteInto("SELECT *
                     FROM t
                     WHERE myname LIKE ?",'%'.$input.'%');

Will give me something on the lines:

SELECT *
FROM t
WHERE myname LIKE '\%my input\%';

Answer 1

You can do the concatenation of $input at the SQL level:

$sql=$DB->quoteInto("SELECT * FROM t WHERE myname LIKE '%'|| ? ||'%'",$input);

Unfortunately this isn't usable when you want $input to be able to contain literal ‘%’ or ‘_’ characters. To get round this, specify an explicit LIKE-ESCAPE character and escape them yourself:

$inputlike= '%'.preg_replace('[%_=]', '=$0', $input).'%';
$sql=$DB->quoteInto("SELECT * FROM t WHERE myname LIKE ? ESCAPE '='", $inputlike);

(It can be any character, not necessarily '='. This also works around a bug where ESCAPE defaults to ‘\’ when not specified in MySQL.)

Unfortunately SQL Server also takes the ‘[’ character as special, to do a regexp-like character group. So if your DB is SQL Server you have to include ‘[’ in the group in preg_replace. Unfortunately it is not valid ANSL SQL to escape ‘[’ on other DBMSs where it doesn't need to be escaped.

Answer 2

you could just use the function that zf uses on the string which is addcslashes($value, "\000\n\r\'\"\032"); that would replace the string in the same way that zf uses or you could (in the case of mysql) use mysql_real_escape_string.

either way you wouldn't use one of the db quote functions

i do wonder if there's a method in the db class to do this but i don't know of one there should be though.

Answer 3

The last option is works out well for me i've not experienced it escaping '%'. So $db->quote('%'.$_GET['query'].'%') outputs %queryvalue%

Answer 4

It is very simple:

$sql=$DB->quoteInto("SELECT *
                     FROM t
                     WHERE myname LIKE ?",'%' . $input . '%');

//Will output: SELECT FROM t WHERE myname LIKE '%inputtedvalue%'


$sql=$DB->quoteInto("SELECT *
                     FROM t
                     WHERE myname LIKE ?",'%' . $input);

//Will output: SELECT FROM t WHERE myname LIKE '%inputtedvalue'


$sql=$DB->quoteInto("SELECT *
                     FROM t
                     WHERE myname LIKE ?", $input . '%');

//Will output: SELECT FROM t WHERE myname LIKE 'inputtedvalue%'


$sql=$DB->quoteInto("SELECT *
                     FROM t
                     WHERE myname LIKE ?", $input);

//Will output: SELECT FROM t WHERE myname LIKE 'inputtedvalue'

What is the prolem?

:)

Answer 5

The problem is, we'd like to escape LIKE special characters Manually replacing them would be a bit dirty, but if there's no solution...