Python String formatting - percentage symbol after string substituion

Question

I am trying to format a SQL query in python by supplying arguments that are taken from a method like this

query = "select * from employees where employee_name like '%s%'" % (name)

When I run this code, I get the following error (python 2.6)

ValueError: unsupported format character ''' (0x27) at index 886

I tried this as well in command line to figure out the problem

>>> print "hello '%s'" % ('world')
hello 'world'
>>> print "hello '%s\%'" % ('world')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: not enough arguments for format string
>>> print "hello '%s%'" % ('world')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: not enough arguments for format string
>>>

The %s works when I don't add a % within the single quotes immediately, but breaks when I add a % after that even with escaping. Is there a remedy for this, I can't subsitute variables within an SQL query without this.

@Sreekiran's answer is the best way to do it. If you really want to stick to your initial string formatting method, you need to do `%s%%` to get the `%` sign — s6hebern, Aug 22 '18 at 06:09
@s6hebern - this also works, but the format method seems to be a cleaner approach, will use that. — sysuser, Aug 22 '18 at 06:18
@sysuser - if you are using python3.6 then you can use f-string also like query = f"select * from employees where employee_name like '%{name}%'" — Vikramd, Aug 22 '18 at 06:25
**Please use a prepared statement as your code is vulnerable to SQL injection.** — fractals, Aug 22 '18 at 07:22

Michael Veksler · Accepted Answer · 2018-08-26T10:05:37.520

And this is how an SQL injection vulnerability is born. An SQL injection will let an intruder read private data, or even maybe modify data. Never ever pass a raw string into SQL query, unless you have made sure that it has no special characters such as ', %, and \. Actually, better to use a well tested function that does it for you.

You would think that:

query = "select * from employees where employee_name like '%s%%'" % (name)
# (two `%%` at the end)

solves your problems, but if somehow name == "%' or '' like '" (or something of the sort), then suddenly the query becomes:

"select * from employees where employee_name like '%' or '' like '%'"

which will match all employees. Worse, even name = '' is a nightmare in your case. I don't think that using like in such queries is a good idea, to begin with.

For some information regarding formatting safely you can read stack-overflow questions under the sql-injection tag, such as Protecting against SQL injection in python. Every database system provides its own stored-procedure interface, please use it.

Very valid point. Will look into the best practices to write safe code. — sysuser, Aug 22 '18 at 07:51

sam-6174 · Answer 2 · 2018-08-22T07:59:26.890

While your question in general is asking about the proper way to format a string in python, for your specific use case (this being a sql query), you should ensure that you are properly escaping your strings.

This is important for (1) stopping sql injection attacks, and (2) it is also helpful for when your variable-string has a quote in it.

For example, your current query will error for anyone with the name of O'Conner.

Instead, make use of your library's parametrize methods for doing a query.

You don't say which sql library you're using, so I'll give you an example with MySQLdb.

1) Basic example (without '%' wildcard):

name = "O'Conner"

query = (
    "SELECT *"
    " FROM employees"
    " WHERE employee_name = %s" # notice the lack of quotes around %s
)
params = (name,)

cursor.execute(query, params)

2) Since you're using wildcards, you need to be more explicit:

query = (
    "SELECT *"
    " FROM employees"
    " WHERE employee_name LIKE '{}%'" # must specify the quotes
    "".format(
        MySQLdb.escape_string(name).replace('%', '\\%').replace('_', '\\_')
    )
)

cursor.execute(query)

(When you supply a params argument to cursor.execute, it is using MySQLdb.escape_string, behind the scenes. It also handles wrapping with quotes. Note that %s in case 1 is not a typical python %s, as opposed to case 2 -- read the docs in the above link for more info.)

Thanks for the sophisticated examples. – sysuser Aug 22 '18 at 08:04 — sysuser, Aug 22 '18 at 08:04

score 2 · Answer 3 · edited Aug 22 '18 at 06:24

2

try

query = "select * from employees where employee_name like '%{}%'".format(name)

If you are using python 3.6, you can use f-string also, like below

query = f"select * from employees where employee_name like '%{name}%'"

edited Aug 22 '18 at 06:24

Vikramd

204
1
4

answered Aug 22 '18 at 06:05

Sreekiran A R

3,123
2
20
41

Works perfect. Are there any pointers where I can learn more about this to improve my skills in this area. – sysuser Aug 22 '18 at 06:07
@sysuser. Glad that you found what you were looking for. Please verify the answer. :) – Sreekiran A R Aug 22 '18 at 06:09
Quick update. For python 2.6 this will be query = "select * from employees where employee_name like '%{0}%'".format(name). Explicit numbering of format fields is needed there - https://stackoverflow.com/questions/21034954/valueerror-zero-length-field-name-in-format-in-python2-6-6 – sysuser Aug 22 '18 at 08:04

Python String formatting - percentage symbol after string substituion

3 Answers3