psycopg2 variables format for creating queries

Question

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?

Solution 1

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

Solution 2

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

Solution 3

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