How can Python do string substitution without %s?

Question

Check out the following lines of Python 2.6 code I found:

key = 'hire_date' 
update_dict['key'] = update_dict[key]     #added e.g. {..., 'key': '12/31/1999'}
if key == 'hire_date':
     query_string = "UPDATE employee SET " + key + "= CAST(%(key)s AS DATE) WHERE emp_id = '" + emp.employee + "'"

I've tested this code, and it works. It successfully updates the employee's hire_date field in the database to whatever date 'key''s value in the dictionary is.

I was in the middle of parameterizing it when I noticed the %(key)s somehow manages to get the value of the dictionary at 'key'. How does it do that? I always thought you had to add % dictionaryOrTupleOrWhatever after the string for this to work.

I think you missed an interpolation somewhere; the code you posted only concatenates. — Martijn Pieters
– Martijn Pieters, Commented Aug 3, 2012 at 19:23
The effect you are seeing is not from the python code, it's the SQL query that's exhibiting this behavior. — Joel Cornett
– Joel Cornett, Commented Aug 3, 2012 at 19:23
Martijn, you're right; the interpolation happens later with an execute(query_string, update_dict) — MattSayar
– MattSayar, Commented Aug 3, 2012 at 19:37

BrenBarn · Accepted Answer · 2012-08-03 19:21:52Z

3

In the code that you pasted, no interpolation takes place. However, the variable could later be interpolated:

>>> x = "%(var)s" # no interpolation yet
>>> d = {'var': 88}
>>> x % d # interpolate into the stored string
'88'

Given that the code you posted looks like SQL, it could also be interpolated later via an SQL library call. Many SQL interface libraries provide a similar sort of string-substitution using % signs and encourage users to use these rather than the built-in string substitution, since the SQL library versions have various safeguards to prevent malicious injection attacks.

answered Aug 3, 2012 at 19:21

BrenBarn

253k39 gold badges421 silver badges392 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mattie B · Accepted Answer · 2012-08-03 19:23:21Z

2

I bet you'll find later in the code that there is a DB API execute statement that takes update_dict as a parameter. The DB API then does the substitution instead of Python string formatting and thus properly handles binding.

Have a look at this: http://furius.ca/pubcode/pub/antiorm/lib/python/dbapiext.html#escaping-in-the-dbapi-2-0

answered Aug 3, 2012 at 19:23

Mattie B

21.5k7 gold badges39 silver badges57 bronze badges

4 Comments

MattSayar Over a year ago

You're right about the execute statement using update_dict as a parameter, and I completely missed that. Is it better to do parameterization in the application, or letting the DB handle it? Or is it all preference?

Mattie B Over a year ago

Definitely let DB API do the binding; you'll avoid SQL injections that way since you rely on the library to do string escapes and the like.

MattSayar Over a year ago

Does interpolating with Python not prevent any SQL injecting? Or is it just not as bulletproof?

Mattie B Over a year ago

Python string formatting does no parameter escaping whatsoever. Try "select name from emp where name = '%s'" % "foo'; drop table emp" for an example.

Collectives™ on Stack Overflow

How can Python do string substitution without %s?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related