9

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?

Improve this question

4 Answers 4

Reset to default
10

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

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

Improve this answer

Comments

1

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 ; --"
Improve this answer

Comments

1

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.