CODEWITHSUNDEEP
python
sysuser
sysuser

Reputation: 1100

Python String formatting - percentage symbol after string substituion

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.

Upvotes: 3

Views: 2553

Answers (3)

Michael Veksler
Michael Veksler

Reputation: 8475

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.

Upvotes: 5

sam2426679
sam2426679

Reputation: 3817

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

Upvotes: 3

Sreekiran A R
Sreekiran A R

Reputation: 3421

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}%'"

Upvotes: 2

Related Questions