0

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

johnfk3
  • 469
  • 2
  • 5
  • 15
  • 2
    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 Dec 07 '15 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 Dec 07 '15 at 13:50

2 Answers2

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • 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 – johnfk3 Dec 07 '15 at 11:07
  • That will only work if `data` itself is a `tuple`. Check why in [my answer](http://stackoverflow.com/a/30985541/131874) – Clodoaldo Neto Dec 07 '15 at 12:54
  • @johnfk3: are you saying your elements each are already sequences? Can you update your question to show a sample of what `mylist` contains? – Martijn Pieters Dec 07 '15 at 13:00
  • Updated mylist object – johnfk3 Dec 07 '15 at 13:11
  • Again, there are no tuple there. Try it out; print the list you defined out. You defined a list of strings. – Martijn Pieters Dec 07 '15 at 13:31
  • Yes that what I meant a list of strings, which are formatted beforehand. They are to be added to a new table each in a row of their own. Must I pass in a tuple to make it secure? – johnfk3 Dec 07 '15 at 13:34
  • @johnfk3: yes, because the second argument to `cursor.execute()` *must* be a sequence of values, the same number of values as there are placeholders. So if you have just *one* `%s` placeholder, you must provide a sequence containing one value. `(data,)` creates a tuple with one element for that. – Martijn Pieters Dec 07 '15 at 13:47
  • @johnfk3: if yo use `cursor.executemany()` then you give it a sequence of sequences; each element in the outermost sequence will be used to execute the statement once, so that again must contain all the values to be inserted. – Martijn Pieters Dec 07 '15 at 13:48
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')
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 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 Dec 07 '15 at 14:37
  • 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 – johnfk3 Dec 07 '15 at 16:38
  • @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. – Clodoaldo Neto Dec 07 '15 at 16:46
  • 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 – johnfk3 Dec 07 '15 at 16:51