4

I was reading this SO question: psycopg2: insert multiple rows with one query and I found there was an excellent answer included that used cursor.mogrify to speed up a series of sql insertions. It got me wondering, does cursor.mogrify successfully escape all sql injection vulnerabilities?

The code for the answer posted by Alex Riley was as follows:

args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str)

Does anyone know of any vulnerabilities to this method of using psychopg2's cursor.mogrify method and then following it up with a string interpolation in the cursor.execute function like this?

Lionel
  • 136
  • 7

1 Answers1

5

psycopg2 doesn't use server-side prepared statements and bind parameters at all. It actually does all queries via string interpolation, but it respects quoting rules carefully and does so in a secure manner.

cursor.mogrify is just a manual invocation of exactly the same logic that psycopg2 uses when it interpolates parameters into the SQL string its self, before it sends it to the server.

It is safe to do this. Just make sure your code has comments explaining why you're doing it and why it's safe.

However, personally I recommend avoiding this approach in favour of using its COPY support.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    I had the feeling that this was acceptable, but I greatly appreciate the sanity check, thanks! – Lionel Oct 18 '17 at 17:02