strange use of %s

[Chris Angelico]

On Mon, Apr 18, 2011 at 6:29 PM, Tracubik <affdfsdfdsfsd at 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