A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearHow can I escape complex sql in Zend Framework? - page 1
User InfoPosts
How can I escape complex sql in Zend Framework?#1
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\%(;

posted date: 2009-04-11 16:28:00


Re: How can I escape complex sql in Zend Framework?#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-11 16:28:01


Re: How can I escape complex sql in Zend Framework?#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.

posted date: 2009-04-11 16:50:00


Re: How can I escape complex sql in Zend Framework?#4
And string concatenation is a DBMS-dependent, so check your DBMS docs.

posted date: 2009-04-11 17:01:00


Re: How can I escape complex sql in Zend Framework?#5
Hmm, yeah... + is SQL Server and || is ANSI/everyone else, IIRC. Gah, what a mess.

posted date: 2009-04-11 17:26:00


Re: How can I escape complex sql in Zend Framework?#6
A mess indeed. Will see what happens if I open a bug for that in the ZF project.

posted date: 2009-04-11 17:36:00


Re: How can I escape complex sql in Zend Framework?#7
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 functionsi 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.

posted date: 2009-04-11 17:41:00


Re: How can I escape complex sql in Zend Framework?#8
The last option is works out well for me i(ve not experienced it escaping (%(. So $db->quote((%(.$_GET[(query(].(%() outputs %queryvalue%

posted date: 2009-04-11 18:28:00


Re: How can I escape complex sql in Zend Framework?#9
This is the best way :)

posted date: 2009-04-12 15:40:00


Re: How can I escape complex sql in Zend Framework?#10
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?:)

posted date: 2009-06-24 20:30:00


Re: How can I escape complex sql in Zend Framework?#11
FWIW, it outputs '%queryvalue%' including the single-quotes.

posted date: 2009-08-05 14:16:00


Re: How can I escape complex sql in Zend Framework?#12
The problem is, we(d like to escape LIKE special charactersManually replacing them would be a bit dirty, but if there(s no solution...

posted date: 2010-01-28 01:10:00


select page: « 1 2 »
Copyright ©2008-2017 www.momige.com, all rights reserved.