#305 closed defect (invalid)
sql code was not white space safe?
Reported by: | Owned by: | Adrian Holovaty | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.0 |
Severity: | normal | Keywords: | quoting strings for 'get_object(<field>__exact = ...)' |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
Using the postgresql backend I have a model which has "albums" which only has two fields. The important field is:
meta.CharField('name', maxlength = 512)
so I create the model fun. Now in python code I do the following:
album = albums.get_object(name__exact = album_name)
Where album was the string "Dungeon Keeper 2"
This died with the error:
File "/usr/local/lib/python2.4/site-packages/django/utils/functional.py", line 3, in _curried return args[0](*(args[1:]+moreargs), **dict(kwargs.items() + morekwargs.items())) File "/usr/local/lib/python2.4/site-packages/django/core/meta/__init__.py", line 1013, in function_get_object obj_list = function_get_list(opts, klass, **kwargs) File "/usr/local/lib/python2.4/site-packages/django/core/meta/__init__.py", line 1053, in function_get_list return list(function_get_iterator(opts, klass, **kwargs)) File "/usr/local/lib/python2.4/site-packages/django/core/meta/__init__.py", line 1036, in function_get_iterator cursor.execute("SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql, params) File "/usr/local/lib/python2.4/site-packages/django/core/db/base.py", line 10, in execute result = self.cursor.execute(sql, params) psycopg.ProgrammingError: ERROR: syntax error at or near "Keeper" at character 123 SELECT music_albums.id,music_albums.name,music_albums.simplified_name FROM music_albums WHERE music_albums.name = Dungeon Keeper 2
Now if I modified my call to be:
album = albums.get_object(name__exact = "'%s'" % album_name)
it works. (Note the " ' %s ' "
) thus quoting the string. I would have thought it would do sql safe quoting of my strings for me?
Attachments (3)
Change History (11)
comment:1 by , 19 years ago
comment:3 by , 19 years ago
In this code:
album = albums.get_object(name__exact = album_name)
...is album_name
a normal string, a Unicode object, or what?
by , 19 years ago
Attachment: | scanfiles.py added |
---|
program that scans a 'music root' for files, extracts id3 tags, and fills in the db from that
comment:4 by , 19 years ago
I added two attachments. One is the model the other is the program that I am testing with
to fill in the db - which is where the error actually happens.
The reason I suspected white-space safety was that the track name given in the example
did not work (as the error indicated) but when I replaced the statement that creates
the track with:
track = tracks.Track(title = "'%s'" % title, filename = filename,
last_scanned = datetime.datetime.now(),
play_time = playtime, bit_rate = bitrate, vbr = vbr,
musicroot_id = music_root.id)
it did work. The only difference being the { "'%s'" % title } bit. Now I have another track
that dies that has a single quote (" ' ") in its title. I was going to write a generic
quoting function to pass all these strings through to see if that made it happier.
Hope I am not wasting your time with this and thanks again.
comment:5 by , 19 years ago
Another problem that is I think related is shown by the track title: "Don't Fade Away"
This is the same exact code that I attached to this ticket earlier. I notice also that the file
name looks curious in the failed insert statement:
'/locker/music/goth/Dead_Can_Dance_-_Toward_The_Within/15_Dont_Fade_Away.mp3'
This one I find curious because I do not do my hack of: { "'%s'" % filename } here.
The line in question is:
track = tracks.Track(title = "'%s'" % title, filename = filename,
last_scanned = datetime.datetime.now(),
play_time = playtime, bit_rate = bitrate, vbr = vbr,
musicroot_id = music_root.id)
Here is how it dies:
Traceback (most recent call last):
File "./scanfiles.py", line 339, in ?
main()
File "./scanfiles.py", line 331, in main
run(opts.verbosity)
File "./scanfiles.py", line 270, in run
scan_file(check_file, music_root, verbosity = verbosity)
File "./scanfiles.py", line 220, in scan_file
add_file(filename, af, music_root, verbosity)
File "./scanfiles.py", line 165, in add_file
track.save()
File "/usr/local/lib/python2.4/site-packages/django/utils/functional.py", line 3, in _curried
return args[0](*(args[1:]+moreargs), dict(kwargs.items() + morekwargs.items()))
File "/usr/local/lib/python2.4/site-packages/django/core/meta/init.py", line 747, in method_save
','.join(field_names), ','.join(placeholders)), db_values)
File "/usr/local/lib/python2.4/site-packages/django/core/db/base.py", line 10, in execute
result = self.cursor.execute(sql, params)
psycopg.ProgrammingError: ERROR: syntax error at or near "t" at character 224
INSERT INTO music_tracks (title,filename,simplified_name,year,play_time,bit_rate,vbr,track_number,disc_number,play_count,last_scanned,last_played,bpm,grouping,comments,artist_id,album_id,genre_id,musicroot_id) VALUES ('Don't Fade Away','/locker/music/goth/Dead_Can_Dance_-_Toward_The_Within/15_Dont_Fade_Away.mp3',NULL,NULL,372,128,False,0,0,0,'2005-08-11 21:17:32.314466',NULL,NULL,NULL,NULL,4,5,NULL,2)
(obviously my hack is wrong because I do nothing about checking the quote-safety inside the
string I stuck insdie single quotes.)
by , 19 years ago
Attachment: | scanfiles.2.py added |
---|
Okay, new version of scanfiles.py with a small sql safe quoting function..
comment:6 by , 19 years ago
Okay, added a small function to quote out all quotes in a string (and surround the string in
single quotes) and gets a lot further now (before causing python to core dump.. )
Figured I would put this up here because it does get further.
comment:7 by , 19 years ago
Okay, finally had time to get back to this one specifically.
Yes, the string was a unicode object. In the case where it caused python to core dump (inside psycopg) the issue was that it could
not deal with the unicode object. I am doing a:
if isinstance(foo, unicode):
foo = foo.encode("utf-8")
I am sure the basic problem stems from that as you mentioned it above.
comment:8 by , 19 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
There's got to be something else going on here -- spaces in strings don't cause any problems (see http://www.djangoproject.com/documentation/models/lookup/#sample-usage which is one of our tests). Can you attach your entire model to this ticket so we can take a look?