strange use of %s

[John Nagle]

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