[mod_python] String format in "LIKE" clause ( SOLVED! )

Please note that it's not the right way to do it. You should never never 
use "your_query %s" % (something,). With this kind of syntax you're 
vulnerable to SQL injection and other things.
I don't know for MysqlDB, but with Postgresql and psycopg the right way 
to do this is :
cursor.execute('INSERT INTO tables(foo, bar) VALUES (%s, %s)', (col1, 
col2)). With this syntax the driver will quote the problematic strings 
before sending them to the database.
It should be the same with mysqldb (see: 
http://www.python.org/peps/pep-0249.html)

Luis M. Gonzalez wrote:

> Thank you Graham!!
> That works now! Adding double percent signs instead of single ones 
> solved the problem.
>
> Thanks again and best regards,
> Luis
>
>
>
> ----- 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 9:47 PM
> Subject: Re: [mod_python] String format in "LIKE" clause
>
>
>> 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
>


-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Work: jcigar at ulb.ac.be
Personal: mage at mordor.ath.cx