strange use of %s

[Tim Golden]

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