Unicode charactered filename SELECT SQL parameter issue

Question

I have a webapp/webservice method created in django, which stores unicode character filenames on a DB-table. I am doing it by this method on the views.py script:

SubmissionContents(id=subid, filename=fn.decode('unicode_escape')).save()

I am doing it this way because I've encountered some unicode-charactered filenames, which are not properly stored on the DB (Whatever the reason, I still don't know). And this is also the suggestion on this stackoverflow thread/link:

Now on the backend, I have an ordinary python script, which uses MySQLdb-python module to query on the said DB. My query is like this:

filename = (u"%s").encode('unicode_escape')
cursor.execute('select * from `submissioncontents` 
                where `submissioncontents`.`filename` = "%s"' 
                % filename.decode('unicode_escape'))

The problem is there are no matches returned though I am very sure that the DB-table is populated with such value. How should I do the query properly?

Thanks in advance!

Can you output the SQL command to be executed by the cursor? I'm guessing you may not be able to but getting the value from the field in the row it should match would let you eyeball any obvious differences encoding has caused. — m.brindley, Feb 02 '13 at 07:26
Wait, what's going on here: `(u"%s").encode('unicode_escape')`? Did you mean to format that with an actual filename with unicode before escaping it? — m.brindley, Feb 02 '13 at 07:34

score 0 · Answer 1 · answered Feb 02 '13 at 18:39

Check right syntax:

cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])

For your code:

cursor.execute('select * from `submissioncontents` 
                where `submissioncontents`.`filename` = %s' 
                , [ filename.decode('unicode_escape') ] )

Quoting docs:

"Do not use string formatting on raw queries!"

Unicode charactered filename SELECT SQL parameter issue

1 Answers1