CODEWITHSUNDEEP
pythonmysql
Alon_T
Alon_T

Reputation: 1440

TypeError: not all arguments converted during string formatting string python

You probably have seen it before, but I'm basically trying to avoid MySQL Injection, so I'm formatting my query as follows using Python:

if "username" in form:
    username = form["username"].value
else:
    success = 0
    error = "User Name is Missing"

cur.execute("SELECT COUNT(*) FROM users WHERE screenName=':1'",[username])
results = int(cur.fetchall()[0][0])

This throws an error saying:

<type 'exceptions.TypeError'>: not all arguments converted during string formatting 
  args = ('not all arguments converted during string formatting',) 
  message = 'not all arguments converted during string formatting'

Any idea what's wrong? Thanks

Upvotes: 0

Views: 937

Answers (1)

khampson
khampson

Reputation: 15356

You don't specify the exact library you're using, but assuming it's Python DB API compliant, you probably want to change the execute line to this:

cur.execute("SELECT COUNT(*) FROM users WHERE screenName=%s",[username])

Edit in response to comment from OP:

The use of %s is the current standard in terms of preventing SQL Injection. I'm not sure what the post in the answer you linked is getting at there... A couple things to keep in mind about that are that the thread has been closed as "not constructive", and that answer is also ~7 years old, so it's quite possible that there were issues back then that that answer may have been referencing that no longer apply.

But %s means that the library (in the execute method) handles all the escaping and quoting, and is the way to prevent injection. (As opposed to, say, using regular interpolation, say, via format, which would leave it exposed to injection.)

Note that is very specifically not '%s' and then using foo % bar in the execute call, but an unquoted %s and passing the parameters as the 2nd argument to execute.

For example, I use psycopg2, which is fully DB API compliant, and its current doc describes using %s to prevent injection.

Upvotes: 2

Related Questions