Prevent sql injection with lists in python

Question

So I have a list of formatted strings that i want to insert into a database, They are formatted so the database will accept this type of string.

Here is an example of the format:

mylist = [('123456789'), ('987654321'), ('1234554321'),....('9999999999')]
mylist[0] = ('123456789')

The format must be kept to ensure they are entered correctly.

I want to ensure that it is secure against sql injection,

I know this works:

database = connection.cursor()
for data in mylist:
    command = " INSERT INTO my_table(my_value) VALUES %s" %data
    database.execute(command)
connection.commit()

However I'm unsure if this is correct way to prevent sql injection

What I would prefer to do but it wont work is:

database = connection.cursor()
for data in mylist:
    command = " INSERT INTO my_table(my_value) VALUES %s"
    database.execute(command, (data))
connection.commit()

The error I receive is:

'str' object is not callable

I've seen online here that this is correct so why wont it work

Please give us the full traceback. Note that (data) is not a tuple, it is just the same thing as data, grouped in parentheses. Use (data,) to make it a tuple. — Martijn Pieters
– Martijn Pieters, Commented Dec 7, 2015 at 10:49
Again, you may as well drop all the (...) parentheses for all the good that they do. mylist = [('123456789'), ('987654321'), ('1234554321'),....('9999999999')] is the same thing as mylist = ['123456789', '987654321', '1234554321',....'9999999999']. — Martijn Pieters
– Martijn Pieters, Commented Dec 7, 2015 at 13:50

Martijn Pieters · Accepted Answer · 2015-12-07 11:01:50Z

3

You need to pass in a tuple:

database.execute(command, (data,))

Note the comma! You can also pass in a list:

database.execute(command, [data])

Since you have a list of values to insert, you can use cursor.executemany() to have the database apply all values in one go, but you need to make each element in your list a tuple or list:

with connection:
    with connection.cursor() as cursor:
        command = "INSERT INTO my_table(my_value) VALUES %s"
        cursor.executemany(command, ((data,) for data in mylist))

Note that you can use both the connection and cursor as a context managers. When the with block for the connection ends, the transaction is automatically committed for you, unless an exception has occurred at which point the transaction is rolled back instead. The cursor context manager closes the cursor, freeing the resources associated with the cursor. See the psycopg2 documentation.

edited Dec 7, 2015 at 11:01

answered Dec 7, 2015 at 10:50

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

8 Comments

johnfk3 Over a year ago

The format of the data is to take my list and add """ (' """ before an element and """ ') """ after and elment and append to the list, ie. ('data') and this will be correct for inserting tot he database. in your executemany command the data is being entered as """ '(''data'')' """ [both single quotes inside the brackets]. How do i get it in the right format

Clodoaldo Neto Over a year ago

That will only work if data itself is a tuple. Check why in my answer

Martijn Pieters Over a year ago

@johnfk3: are you saying your elements each are already sequences? Can you update your question to show a sample of what mylist contains?

johnfk3 Over a year ago

Updated mylist object

Martijn Pieters Over a year ago

Again, there are no tuple there. Try it out; print the list you defined out. You defined a list of strings.

|

Clodoaldo Neto · Accepted Answer · 2015-12-07 15:57:42Z

1

Very hard to understand what you mean. This is my try:

mylist = ['123456789','987654321','1234554321','9999999999']
mylist = [tuple(("('{0}')".format(s),)) for s in mylist]
records_list_template = ','.join(['%s'] * len(mylist))
insert_query = '''
    insert into t (s) values
    {0}
'''.format(records_list_template)
print cursor.mogrify(insert_query, mylist)
cursor.execute(insert_query, mylist)
conn.commit()

Output:

insert into t (s) values
('(''123456789'')'),('(''987654321'')'),('(''1234554321'')'),('(''9999999999'')')

The inserted tuples:

select * from t;
       s        
----------------
 ('123456789')
 ('987654321')
 ('1234554321')
 ('9999999999')

edited Dec 7, 2015 at 15:57

answered Dec 7, 2015 at 14:25

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

4 Comments

johnfk3 Over a year ago

That is a good answer and it gives me the same error as @Martijn Pieters solution. Its the format of the value going into the database thats the problem. I want to give it ('123456789') exactly but when converting the list to tuples i get a syntax error and the data now looks like '(''123456789'')' That is two single quotes inside the brackets

johnfk3 Over a year ago

Ok I see how it works and thats fine if the values are string but my table and values are cidr type. The only way i can insert them into my table is in the format ('my_cidr_value') and not the way you described with '(''my_cidr_value'')'. This is my problem

Clodoaldo Neto Over a year ago

@johnfk3 You finally start to reveal what is your data. You have been misleading we all. We are not clowns. Either you start behaving or get out.

johnfk3 Over a year ago

Chill out big man @Clodoaldo Neto, I appreciate the help, I didn't realize it was a big deal, I was just looking for a way to make it more secure in python which I'm new to, I didn't imagine that would be a problem

Collectives™ on Stack Overflow

Prevent sql injection with lists in python

2 Answers 2

8 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related