[mod_python] String format in "LIKE" clause

Two things:
1) you can escape your user input with: db.escape( company )
2) you can use MySQL's INSTR() function instead of LIKE, not requiring 
special formatting

examples:
cursor.execute( "SELECT * FROM table WHERE column LIKE '%%%s%%'" % 
db.escape( value ) )
    In this example your value us escaped, and safe. In this case you 
can't use the second argument to execute, which automatically escapes 
and wraps the value in single quotes for you.

or

cursor.execute( "SELECT * FROM table WHERE INSTR(column, %s)", (value,) )
    In this example, you dont need to worry about the fact that quotes 
are wrapped by execute, as you dont need to wrap your value with %, you 
also dont need to worry about the less than readable '%%%s%%'

Sean

Graham Dumpleton wrote:

>Luis M. Gonzalez wrote ..
>  
>
>>Thanks Graham, but no, this is not what I want to do...
>>I'll try to explain it better:
>>
>>Lets say that I want to return a list of companies whose names include
>>the 
>>word "Co" ("Co" is pased as a parameter to the query), so I can get:
>>
>>Acme Corporation Ltd.
>>Barnes Corp.  Inc.
>>Corporative Management S.A.
>>Fresh Corpse Foods, Ltd.
>>etc, etc...
>>
>>So, how should I include this parameter into the LIKE clause.
>>I guess it should be enclosed between "%" and "%", but what should I put
>>in 
>>between?
>>    
>>
>
>Ultimately you use the substring of string you are looking for.
>
>Quoting from a Google search, as that way it looks authoritative:
>
>  The LIKE pattern matching operator can also be used in the conditional selection
>  of the where clause. Like is a very powerful operator that allows you to select
>  only rows that are "like" what you specify. The percent sign "%" can be used as
>  a wild card to match any possible character that might appear before or after the
>  characters specified. For example:
>
>  select first, last, city
>     from empinfo
>     where first LIKE 'Er%';
> 
>What you were doing is basically correct, although you were missing single quotes.
>
>The real issue as I pointed out was that because you were using '%' operator to
>compose the string, you need to have "%%" in the string where you want a real '%'.
>
>Adding the missing quotes:
>
>  "select * from customers where company like '%%%s%%'"  % "Co"
>
>Which yields an actual SQL string of:
>
>  "select * from customers where company like '%Co%'"
>
>BTW, if the substring you want to search for is coming from a web page or other
>user input this could be dangerous. For example, they could include a single quote
>in the substring for searching and stuff it all up. Thus, you need to perform
>appropriate escaping to avoid such problems.
>
>Graham
>
>  
>
>>----- Original Message ----- 
>>From: "Graham Dumpleton" <grahamd at dscpl.com.au>
>>To: "Luis M. Gonzalez" <luismg at gmx.net>
>>Cc: <mod_python at modpython.org>
>>Sent: Thursday, January 26, 2006 8:46 PM
>>Subject: Re: [mod_python] String format in "LIKE" clause
>>
>>
>>    
>>
>>>Luis M. Gonzalez wrote ..
>>>      
>>>
>>>>I tried this:
>>>>("select * from customers where company like %s%"  %company ) # doesn't
>>>>work
>>>>("select * from customers where company like '%(%s)%"  %company  # 
>>>>doesn't
>>>>work either
>>>>
>>>>The question is:
>>>>How should a include the parameter into de LIKE clause in order to accept
>>>>only a part of the full company name and return the matching results?
>>>>        
>>>>
>>>Double up the"%" to get an actual percentage passed through.
>>>
>>> ("select * from customers where company like %s%%"  %company )
>>>
>>>At least that would be the problem if it is simply using "%" operator
>>>      
>>>
>>on
>>    
>>
>>>string:
>>>
>>>      
>>>
>>>>>>"select * from customers where company like %s%" % "ME"
>>>>>>            
>>>>>>
>>>Traceback (most recent call last):
>>> File "<stdin>", line 1, in ?
>>>ValueError: incomplete format
>>>
>>>      
>>>
>>>>>>"select * from customers where company like %s%%"  % "ME"
>>>>>>            
>>>>>>
>>>'select * from customers where company like ME%'
>>>
>>>Graham
>>>
>>>      
>>>
>_______________________________________________
>Mod_python mailing list
>Mod_python at modpython.org
>http://mailman.modpython.org/mailman/listinfo/mod_python
>
>
>  
>