CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
user2113422
user2113422

Reputation:

psycopg2 variables format for creating queries

This is not that relevant so I'm just curious about the following (Python 2.7):

I just began using psycopg and reading through the docs they always use strings (%s) and tuples for passing values to a query.

The variables placeholder must always be a %s

So considering the following example-

In a table named 'test' with the fields value_1 (varchar) and value_2 (int) a query is created as:

value_1 = "test"
value_2 = "100"
cur.execute("INSERT INTO test (value_1,value_2) VALUES (%s,%s)",\
           (value_1,value_2))

My question is if it's a bad practice or even problematic to use the 'format' method instead (as follows):

cur.execute("INSERT INTO test (value_1,value_2) VALUES ('{value1}',{value2})".\
           format(value1=value_1,value2=value_2))

What do you say based on your experience, is it really dangerous or problematic?

Upvotes: 9

Views: 15410

Answers (4)

mLstudent33
mLstudent33

Reputation: 1175

I am on Python 3 and psycopg2 but when inserting only 1 value, don't forget a trailing comma or it throws, not all arguments converted during string formatting.

So my line is cur.execute(sql, (plan_name,)) and not cur.execute(sql, (plan_name))

Upvotes: 1

Jasen
Jasen

Reputation: 12412

Yes, Psycopg2 uses %s for all types, psycopg2 converts the parameters to their string representation and uses that in the query

INSERT INTO test (value_1,value_2) VALUES('test','100');

Sometimes you might need to cast some of the values to the apropriate type.

 cur.execute("""INSERT INTO test (value_1,value_2) 
       VALUES (%s,%s::integer)""",
       (value_1,value_2))

Your proposed method is very bad practice, you've got sql injection for several possible values of value_1 and value_2. eg:

 value_1="',0); rollback; drop table test ; --"

Upvotes: 1

user2113422
user2113422

Reputation:

After reading some docs and source and testing this on my own, got this straight. Simple answer: It is damn unsafe. Not only for psycopg2 but for any db module (at least for those I tested). Relating this to psycopg2, the use of (%s),(variable,) allows psycopg2 to automatically escape those variables to prevent such a mess.

My test for figuring out the little mess it can create:

cur.execute("INSERT INTO test (value_1,value_2) VALUES ('{value1}','{value2}')".\
format(value1=value_1,value2='1); drop table test; --killer instinct'))

That's it, adiós test table :-P

Upvotes: 4

Don Roby
Don Roby

Reputation: 41137

The call

cur.execute("INSERT INTO test (value_1,value_2) VALUES (%s,%s)",\
           (value_1,value_2))

has a string parameter and an additional parameter which is a tuple of values for the substitution. This lets psycopg2 interpolate the values, and do so more safely than simple string interpolation.

The usage docs for psycopg2 say

♯ Pass data to fill a query placeholders and let Psycopg perform

♯ the correct conversion (no more SQL injections!)

>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)", ... (100, "abc'def"))

In the call

cur.execute("INSERT INTO test (value_1,value_2) VALUES ('{value1}',{value2})".\
           format(value1=value_1,value2=value_2))

you are interpolating the values yourself, and just passing the resulting string to the cursor execute method.

The simple interpolation is susceptible to SQL injection. You are likely better off using the first form.

You should always consider "Little Bobby Tables".

Upvotes: 10

Related Questions