Python 2.5 String formatting problem!

Question

I am trying to use the python 2.5 string formatting however I have run into problem in the following example:

values = {
          'url': 'http://blabla.com',
          'link' : 'http://blabla.com',
          'username' : 'user',
          'spot'    : 0,
          'views'    : 10,
          'date'    : 3232312,
          'private' : 1, 

          }

query = """insert into hyves.Image (URL, StaticLink , HostUsername, SpotCount, ViewCount, UploadDate) values ('%(url)','%(link)','%(username)',%(spot),%(views),%(date), %(private) )""" % values

print query

It gives me the following error:ValueError: unsupported format character ''' (0x27) at index 106. Can anyone help me?

The Sqlite3 documentation gives an example of how to do parameter substitution for SQL queries. — Cristian Ciupitu
– Cristian Ciupitu, Commented Sep 13, 2010 at 13:47

Radomir Dopieralski · Accepted Answer · 2010-09-13 13:15:07Z

9

Never use string formatting for composing sql queries like that! Use your database module to do the interpolation -- it will do it with correct escaping, so that this doesn't happen to you: http://xkcd.com/327/

In case you want to use that formatting for different things than sql, use %(foo)s (or d, or whatever format you need).

answered Sep 13, 2010 at 13:15

Radomir Dopieralski

2,60318 silver badges14 bronze badges

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

3 Comments

Jim Brissom Over a year ago

+1 for mentioning the dangers of formatting SQL queries like that

Nasir Over a year ago

Well the reason I am doing this is that the SQL files are outputed to files and are going to be executed manually by me. So I don't care about sanitizing the input.

Radomir Dopieralski Over a year ago

I think you should still care -- you are not going to validate it all by hand, and there always may be some stray ' in the data.

Jim Brissom · Accepted Answer · 2010-09-13 13:17:00Z

3

You are missing the format characters, i.e.:

"INSERT INTO ... %(url)s, ..." % values

...if you want to format URL as a string.

answered Sep 13, 2010 at 13:17

Jim Brissom

33.2k4 gold badges41 silver badges33 bronze badges

Comments

Mark · Accepted Answer · 2010-09-13 13:20:13Z

3

You need to specify explicit conversion flags:

query = """insert into hyves.Image (URL, StaticLink , HostUsername, SpotCount, ViewCount, UploadDate) values (%(url)s,%(link)s,%(username)s,%(spot)i,%(views)i,%(date)i, %(private)i )""" % values

answered Sep 13, 2010 at 13:20

Mark

109k20 gold badges180 silver badges238 bronze badges

Collectives™ on Stack Overflow

Python 2.5 String formatting problem!

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related