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

Question

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.

Answer 1

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.)