"ValueError: Unsupported format character ' " ' (0x22) at..." in Python / String

13,538

First, your problem has nothing to do with SQL. Throw away all the SQL-related code and do this:

var = 'foo'
" OR tble_tble.var LIKE %'%s'%" % var

You'll get the same error. It's because you're trying to do %-formatting with a string that has stray % signs in it. So, it's trying to figure out what to do with %', and failing.


You can escape these stray % signs like this:

" OR tble_tble.var LIKE %%'%s'%%" % var

However, that probably isn't what you want to do.


First, consider using {}-formatting instead of %-formatting, especially when you're trying to build formatted strings with % characters all over them. It avoids the need for escaping them. So:

" OR tble_tble.var LIKE %'{}'%".format(var)

But, more importantly, you shouldn't be doing this formatting at all. Don't format the values into a SQL string, just pass them as SQL parameters. If you're using sqlite3, use ? parameters markers; for MySQL, %s; for a different database, read its docs. So:

" OR tble_tble.var LIKE %'?'%"

There's nothing that can go wrong here, and nothing that needs to be escaped. When you call execute with the query string, pass [var] as the args.

This is a lot simpler, and often faster, and neatly avoids a lot of silly bugs dealing with edge cases, and, most important of all, it protects against SQL injection attacks.

The sqlite3 docs explain this in more detail:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations… Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) …


Finally, as others have pointed out in comments, with LIKE conditions, you have to put the percent signs inside the quotes, not outside. So, no matter which way you solve this, you're going to have another problem to solve. But that one should be a lot easier. (And if not, you can always come back and ask another question.)

Share:
13,538

Related videos on Youtube

jdero
Author by

jdero

Cofounder of hungrywolves.com

Updated on September 16, 2022

Comments

  • jdero
    jdero over 1 year

    I've seen a couple similar threads, but attempting to escape characters isn't working for me.

    In short, I have a list of strings, which I am iterating through, such that I am aiming to build a query that incorporates however many strings are in the list, into a 'Select, Like' query.

    Here is my code (Python)

    def myfunc(self, cursor, var_list):
       query = "Select var FROM tble_tble WHERE"
       substring = []
       length = len(var_list)
       iter = length
    
       for var in var_list:
          if (iter != length):
             substring.append(" OR tble_tble.var LIKE %'%s'%" % var)
          else:
             substring.append(" tble_tble.var LIKE %'%s'%" % var)
          iter = iter - 1
    
       for str in substring:
          query = query + str
    ...
    

    That should be enough. If it wasn't obvious from my previously stated claims, I am trying to build a query which runs the SQL 'LIKE' comparison across a list of relevant strings.

    Thanks for your time, and feel free to ask any questions for clarification.

  • Barmar
    Barmar over 10 years
    I don't know Python well, but I'll bet %% in a format string expands to a %.
  • abarnert
    abarnert over 10 years
    @Barmar: Which is exactly what (I assume) the OP wants.
  • jdero
    jdero over 10 years
    @abarnert Thank you. My knowledge of format strings is very ... amateur.
  • jdero
    jdero over 10 years
    @abarnert What do you mean by passing them as SQL parameters? You seem to know your stuff.
  • abarnert
    abarnert over 10 years
    @jdero: This is explained near the very top of the sqlite3 docs (and the DB-API 2.0 PEP, and similar places). See the two paragraphs and example starting with "Usually your SQL operations…"
  • abarnert
    abarnert over 10 years
    @jdero: Also see this XKCD strip for an explanation of why you might want to use SQL parameter binding (or input sanitization, but parameter binding is a whole lot simpler and safer).
  • abarnert
    abarnert over 10 years
    In standard SQL, and with most actual databases, the LIKE condition's pattern expression has to be a string value in quotes (or a bound parameter, of course).
  • jdero
    jdero over 10 years
    I actually remember that from my dbm class :D just never put it into practice until this awful moment! Thanks for your help. Great links, and solid extension in your post.
  • zs2020
    zs2020 over 10 years
    @abarnert Thank you. Updated. I am a bit slow today and was not processing somehow...
  • jdero
    jdero over 10 years
    @abarnert I know comments are not chat threads, but I just found it utterly hilarious, the intern next to me was like "Hey, I think I have an xkcd on my back, what does it say?" And it was that exact xkcd article. Coincidence? I think not :)
  • abarnert
    abarnert over 10 years
    @jdero: Sometimes you can learn more from interns' t-shirts than you can from StackOverflow or documentation. :)