1

I'm thinking about adding an additional layer of SQL injection mitigation in my usage of psycopg2

If cursor.execute is used properly and the parameters are passed like below this is not a concern.

cursor.execute("SELECT * FROM employees WHERE name = %s", (name,))

But I'm slightly worried about myself or another developer absentmindedly doing something like

cursor.execute("SELECT * FROM employees WHERE name = %s" % (name))

which would of course open the door to someone submitting their name as ''; drop table employees;

Since the second example contains a very common way of formatting a string in Python it's not outside the realm of possibility that someone could make such an error.

I'm wondering if a method similar to execute exists that would restrict the number of queries or statements being executed in a call to execute to 1. If not, does psycopg2 expose a SQL tokenizing method. If so I could write my own method that overrides execute and performs such a check before passing the query to the real execute. Or any other suggestions appreciated.

Mike
  • 58,961
  • 76
  • 175
  • 221
  • One "second layer" is to have the query string as a separate assignment above the execution line. That should then be query strings without formatting. Not foolproof, but easier to spot. – 9769953 Nov 09 '21 at 07:27
  • Run [bandit](https://github.com/PyCQA/bandit) or the like on it. Thats what it’s for. – JL Peyret Nov 09 '21 at 09:45

1 Answers1

1

You could write a wrapper that forces users to name the args exactly and clobber the cursor method early with it

def execute(*, call, args):
    return _execute_original(call, args)

I'd also be tempted to just use a linter to prevent string formatting with % by warning on some trivial regex like %[^\w] and use .format() or f-strings everywhere (this could be implemented anywhere convenient; CICD system, unit tests, grep in git pre-commit hook..)

ti7
  • 16,375
  • 6
  • 40
  • 68