Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #3472 > unrolled thread

strange use of %s

Back to article view | Back to comp.lang.python

Contents

  strange use of %s Tracubik <affdfsdfdsfsd@b.com> - 2011-04-18 08:29 +0000
    Re: strange use of %s Tim Golden <mail@timgolden.me.uk> - 2011-04-18 09:44 +0100
      Re: strange use of %s John Nagle <nagle@animats.com> - 2011-04-25 15:01 -0700
        Re: strange use of %s Chris Angelico <rosuav@gmail.com> - 2011-04-26 08:10 +1000
    Re: strange use of %s Chris Angelico <rosuav@gmail.com> - 2011-04-18 18:50 +1000
    Re: strange use of %s Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2011-04-18 22:22 -0700
    Re: strange use of %s Chris Angelico <rosuav@gmail.com> - 2011-04-19 15:31 +1000
    Re: strange use of %s Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2011-04-19 21:01 -0700

#3472 — strange use of %s

Hi all,
i'm reading a python tutorial in Ubuntu's Full Circle Magazine and i've 
found this strange use of %s:

sql = "SELECT pkid,name,source,servings FROM Recipes WHERE name like '%%%s%
%'" %response

response is a string. I've newbie in sql.

why do the coder use %%%s%% instead of a simple %s?
why he also use the ''?

thanks in advance for you attention/replies

Nico

[toc] | [next] | [standalone]

#3475

On 18/04/2011 09:29, Tracubik wrote:
> Hi all,
> i'm reading a python tutorial in Ubuntu's Full Circle Magazine and i've
> found this strange use of %s:
>
> sql = "SELECT pkid,name,source,servings FROM Recipes WHERE name like '%%%s%
> %'" %response
>
> response is a string. I've newbie in sql.
>
> why do the coder use %%%s%% instead of a simple %s?
> why he also use the ''?

Two parts to this answer.

The straightforward one: because the SQL string needs to end
up looking like this: "... WHERE name LIKE '%abcd%'" and
since it's being generated by Python's string substitution,
the surrounding percents need to be doubled up in the original
string to be left as single in the final string.

An alternative in a modern Python might be to use string formatting:
"... WHERE name LIKE '%{}%'".format (response)

HOWEVER... this is not the best way to introduce Python values into
a SQL string. It's better to use the db module's string substitution
flag (often ? or :field or, confusingly, %s). This is because the
approach above lends itself to what's called SQL injection.
Obligatory xkcd reference: http://xkcd.com/327/

The code would be better if written something like this:

   sql = "SELECT ... WHERE name LIKE '%' + ? + '%'"
   q = db.cursor ()
   q.execute (sql, [response])

(The details will vary according to the database being used etc.)

TJG

[toc] | [prev] | [next] | [standalone]

#4004

On 4/18/2011 1:44 AM, Tim Golden wrote:
> On 18/04/2011 09:29, Tracubik wrote:
>> Hi all,
>> i'm reading a python tutorial in Ubuntu's Full Circle Magazine and i've
>> found this strange use of %s:
>>
>> sql = "SELECT pkid,name,source,servings FROM Recipes WHERE name like
>> '%%%s%
>> %'" %response
>>
>> response is a string. I've newbie in sql.
>>
>> why do the coder use %%%s%% instead of a simple %s?
>> why he also use the ''?
>
> Two parts to this answer.
>
> The straightforward one: because the SQL string needs to end
> up looking like this: "... WHERE name LIKE '%abcd%'" and
> since it's being generated by Python's string substitution,
> the surrounding percents need to be doubled up in the original
> string to be left as single in the final string.
>
> An alternative in a modern Python might be to use string formatting:
> "... WHERE name LIKE '%{}%'".format (response)
>
> HOWEVER... this is not the best way to introduce Python values into
> a SQL string. It's better to use the db module's string substitution
> flag (often ? or :field or, confusingly, %s). This is because the
> approach above lends itself to what's called SQL injection.
> Obligatory xkcd reference: http://xkcd.com/327/
>
> The code would be better if written something like this:
>
> sql = "SELECT ... WHERE name LIKE '%' + ? + '%'"
> q = db.cursor ()
> q.execute (sql, [response])
>
> (The details will vary according to the database being used etc.)

(For those of you who don't know, "%" is a wildcard character in MySQL.)

    That's written for MySQL as
	
	searchkey = "smith" # Whatever you're looking for.
	sql = "SELECT ... WHERE name LIKE CONCAT('%',%s,'%')"
	values = (searchkey,)
	q = db.cursor ()
	q.execute (sql, searchkey)

MySQLdb will fill in the %s with the value from "searchkey", and
there's no possibility of MySQL injection.

Note that such a search requires scanning the entire table.
LIKE with wildcards at the beginning can't use indices.  So
this is very slow for large tables.

Don't worry about having MySQL do the CONCAT.  That happens
once during query parsing here, because all the arguments to
CONCAT are defined in the statement.

				John Nagle

[toc] | [prev] | [next] | [standalone]

#4005

On Tue, Apr 26, 2011 at 8:01 AM, John Nagle <nagle@animats.com> wrote:
> Don't worry about having MySQL do the CONCAT.  That happens
> once during query parsing here, because all the arguments to
> CONCAT are defined in the statement.
>

Good point. Although the abstraction is still a little leaky in that
you can't use this to search for a percent character anywhere in the
string (come to think of it, how _would_ you do that? I think you'd
have to backslash-escape it, prior to escaping it for the string, but
I'd have to check), so it doesn't make a lot of difference where the
percent signs are added.

Chris Angelico

[toc] | [prev] | [next] | [standalone]

#3476

On Mon, Apr 18, 2011 at 6:29 PM, Tracubik <affdfsdfdsfsd@b.com> wrote:
> Hi all,
> i'm reading a python tutorial in Ubuntu's Full Circle Magazine and i've
> found this strange use of %s:
>
> sql = "SELECT pkid,name,source,servings FROM Recipes WHERE name like '%%%s%
> %'" %response
>
> response is a string. I've newbie in sql.
>
> why do the coder use %%%s%% instead of a simple %s?
> why he also use the ''?

Python supports printf-style filling-in of strings. Simple example:

print "Hello, %s!" % "world"

You can also use %d for decimal numbers, %x for hex, and so on (%s
means string). One consequence of this is that the percent character
needs to be escaped - so to display a percentage, you would use
something like:

print "Current progress: %d %%" % 72

which will display "Current progress: 72 %". The percent sign outside
the quotes is the operator.

In the SQL example, the response is bracketed by percent signs. So if
response is "beef", the sql variable will be set to "SELECT
pkid,name,source,servings FROM Recipes WHERE name like '%beef%" -
which is the correct SQL syntax to search for the string 'beef'
anywhere inside the name (the percent signs there are like an asterisk
in a glob).

See for instance:
http://docs.python.org/library/stdtypes.html#string-formatting-operations
http://www.w3schools.com/sql/sql_like.asp

There's a serious issue in this code, in that it allows dodgy
responses to embed SQL code. I don't know what your context is, but
embedding what appears to be a user-provided response unsanitized into
an SQL statement is asking for SQL injection exploits down the track.

http://en.wikipedia.org/wiki/SQL_injection

If it's just a toy for demonstrative purposes that's fine, but it's
good to be aware of these issues. Check out the library you're using
for database access; it's quite possible that you'll be able to embed
variable references in a different way, and let the library escape
them for you - otherwise, look for some kind of escape_string
function.

Hope that helps!

Chris Angelico

[toc] | [prev] | [next] | [standalone]

#3537

On Mon, 18 Apr 2011 09:44:40 +0100, Tim Golden <mail@timgolden.me.uk>
declaimed the following in gmane.comp.python.general:


>    sql = "SELECT ... WHERE name LIKE '%' + ? + '%'"
>    q = db.cursor ()
>    q.execute (sql, [response])
>
	That won't work properly either (at least not in MySQLdb -- which
quotes the values put into the placeholder; you'd end up with
	'%''value''%'
)

	sql = "select ... where name like ?" (or for MySQLdb: ...like %s )
	...
	q.execute(sql, [ "%%s%" % response])
which wraps the % around the parameter data itself before it gets into
the DB-API adapter.
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#3538

On Tue, Apr 19, 2011 at 3:22 PM, Dennis Lee Bieber
<wlfraed@ix.netcom.com> wrote:
> On Mon, 18 Apr 2011 09:44:40 +0100, Tim Golden <mail@timgolden.me.uk>
> declaimed the following in gmane.comp.python.general:
>
>
>>    sql = "SELECT ... WHERE name LIKE '%' + ? + '%'"
>>    q = db.cursor ()
>>    q.execute (sql, [response])
>>
>        That won't work properly either (at least not in MySQLdb -- which
> quotes the values put into the placeholder; you'd end up with
>        '%''value''%'

You'd end up with "... LIKE '%' + 'value' + '%'" which is perhaps
overkill (it forces the database engine to concatenate three strings),
but at least it's safe.

Chris Angelico

[toc] | [prev] | [next] | [standalone]

#3647

On Tue, 19 Apr 2011 15:31:44 +1000, Chris Angelico <rosuav@gmail.com>
declaimed the following in gmane.comp.python.general:

> On Tue, Apr 19, 2011 at 3:22 PM, Dennis Lee Bieber
> <wlfraed@ix.netcom.com> wrote:
> > On Mon, 18 Apr 2011 09:44:40 +0100, Tim Golden <mail@timgolden.me.uk>
> > declaimed the following in gmane.comp.python.general:
> >
> >
> >>    sql = "SELECT ... WHERE name LIKE '%' + ? + '%'"
> >>    q = db.cursor ()
> >>    q.execute (sql, [response])
> >>
> >        That won't work properly either (at least not in MySQLdb -- which
> > quotes the values put into the placeholder; you'd end up with
> >        '%''value''%'
> 
> You'd end up with "... LIKE '%' + 'value' + '%'" which is perhaps
> overkill (it forces the database engine to concatenate three strings),
> but at least it's safe.
> 
	Ah... must have been wearing the wrong glasses at the time and
overlooked that the +s were /in/ the SQL statement...

	I tend not to think of string concatenation in SQL... So end up with
the nasty pre-wrapping I showed.
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web