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\%';

Solution 1

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

Solution 2

The solution is really simple. Zend_Db has een Expression class that helps you work arround it.

$select = $this->select()
->where('value LIKE("?")', new Zend_Db_Expr('%' . $value . '%'))

$this->fetchAll( $select );

Solution 3

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.

Solution 4

It is more simple:

$table->select()->where("myname LIKE ?", '%'.$input.'%');

Solution 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...

View more solutions

Comments

[Milen A. Radev]

And string concatenation is a DBMS-dependent, so check your DBMS docs.

[Amit Patil]

Hmm, yeah... + is SQL Server and || is ANSI/everyone else, IIRC. Gah, what a mess.

[Bill Karwin]

FWIW, it outputs '%queryvalue%' including the single-quotes.