Re: PDO prepared statements and LIKE escaping

Depending on what database you're using you might be able to use LIKE while maintaining security. For instance PostgreSQL has the functions quote_ident(), and quote_literal() which would allow you to use LIKE since these functions would automatically make the string suitable for comparison. So for instance in a stored procedure in PostgreSQL you could do: SELECT * FROM sometable WHERE quote_ident(column) LIKE quote_ident($1); Note that in the above line $1 would be the first parameter of the stored procedure. -Asher Adam Richardson wrote:

Like I said, I'm not 'especially pleased' with any idea up until now. I'm certainly open to any other ideas. Adam On Mon, Aug 4, 2008 at 6:57 PM, Larry Garfield <larry@garfieldtech.com>wrote:

Hm. So your solution is "don't use LIKE"? I can't say I'm wild about that. :-/ --Larry Garfield On Mon, 4 Aug 2008 15:49:52 -0400, "Adam Richardson" <adam@envisionic.com> wrote:

    
Larry,

I agree that having to escape values in a stored procedure does run counter to expectations. It's likely other developers have the potential for short-circuiting their LIKE conditions without realizing it. I've dealt with this issue, too, and haven't been especially pleased with any of the solutions I've undertaken. Recently, I've been avoiding LIKE conditions and using INSTR, LOCATE, CHARINDEX, etc. to avoid the

potential

for unescaped wildcards. Adam On Mon, Aug 4, 2008 at 12:33 PM, Larry Garfield <larry@garfieldtech.com>wrote:

      
On Mon, 4 Aug 2008 11:48:39 -0400, "Andrew Ballard" <aballard@gmail.com
        wrote:
        
On Mon, Aug 4, 2008 at 11:35 AM, Larry Garfield
          
<larry@garfieldtech.com>
      
wrote:
          
On Mon, 04 Aug 2008 08:33:44 +0200, Per Jessen <per@computer.org>
            
wrote:
        
Larry Garfield wrote:

              
IIRC, the way in SQL to circumvent that is to convert "100%" into

"100%%". However, that does rather defeat the purpose of a prepared statement if I have to do my own escaping anyway, does it not?=20

Depends on what you perceive the purpose of the prepared statement

to

be :-) In this context, I tend to think of performance only. Which

is=

generally why I can't be bothered with prepared statements in

php.=20

Actually in most cases in PHP you don't get much performance. What

you

do get is added security, because prepared statements are cleaner than cleaner and more reliable than string escaping. Of course, then we

run

into the % problem above.

-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php