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

score 16 · Accepted Answer · answered Apr 12 '09 at 02:28

16

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

answered Apr 12 '09 at 02:28

Akeem

7,897
5
32
41

4

FWIW, it outputs '%queryvalue%' including the single-quotes. – Bill Karwin Aug 05 '09 at 22:16

score 3 · Answer 2 · answered Mar 22 '11 at 19:55

3

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 );

answered Mar 22 '11 at 19:55

Mient-jan Stelling

534
3
8

bobince · Answer 3 · 2009-04-12T01:21:02.230

2

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.

edited Apr 12 '09 at 01:21

answered Apr 12 '09 at 00:50

bobince

528,062
107
651
834

And string concatenation is a DBMS-dependent, so check your DBMS docs. – Milen A. Radev Apr 12 '09 at 01:01
Hmm, yeah... + is SQL Server and || is ANSI/everyone else, IIRC. Gah, what a mess. – bobince Apr 12 '09 at 01:26
A mess indeed. Will see what happens if I open a bug for that in the ZF project. – Itay Moav -Malimovka Apr 12 '09 at 01:36

score 1 · Answer 4 · answered Jun 25 '09 at 04:30

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?

:)

score 1 · Answer 5 · answered Jan 28 '10 at 09:10

1

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

answered Jan 28 '10 at 09:10

Gruik

31
1

score 1 · Answer 6 · answered Feb 19 '11 at 08:26

1

It is more simple:

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

answered Feb 19 '11 at 08:26

Weltkind

687
5
9

score -1 · Answer 7 · answered Apr 12 '09 at 01:41

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.

How can I escape complex sql in Zend Framework?

7 Answers7