0

I'm creating SQL commands from given input:

def do_add(self, table, full_sql_command="INSERT INTO {} VALUES ({})"):
    """ Add a column to a specified table name """
    add_command = raw_input("INSERT INTO {} VALUES ".format(table))
    self.__create_sql_command(full_sql_command, table, add_command.split(" "))

def __create_sql_command(self, full, tablename, addable):
    print full.format(tablename, ', '.join(addable))

What I need this to output is INSERT INTO <table-name> VALUES ('<val-1>', '<val-2>', '<etc..>')

As of right now I get the following output:

INSERT INTO inventory VALUES test test test
# <= INSERT INTO inventory VALUES (test, test, test)

How can I get the quotes around the values to add?

dzm99972
  • 9
  • 1
  • 5
    Sounds like an SQL injection vulnerability in the works. Can’t you use parameterized queries? – Ry- May 18 '17 at 23:06
  • What purpose does `full_sql_command="INSERT INTO {} VALUES ({})"` have as a default argument? – roganjosh May 18 '17 at 23:08
  • @Ryan it is injectable, yes, but it's just for a demonstration. I'm trying to just get it done, it's not going anywhere and it's an in-memory DB. – dzm99972 May 18 '17 at 23:08
  • 1
    @dzm the talk of injection is missing the point: Parametrized queries take care of "adding quotes" around the inserted values. They do it so well that they protect you from injection, but that's a sort of side-effect. – alexis May 18 '17 at 23:10
  • @alexis would you give me an example then? – dzm99972 May 18 '17 at 23:14
  • An example of what? You mean will I write an answer showing how to use parametrized queries for this? No, but [see here](http://stackoverflow.com/q/3410455/699305). Or [here](http://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python). Or check the module docs for whatever database and plugin you are using. – alexis May 18 '17 at 23:21

3 Answers3

1

Instead of wrapping your arguments in quotes, use a parametrized query. It will in effect add the quotes as needed, transparently and with no chance of problems due to e.g. quotes in the input itself, either intentional (SQL injection attacks) or accidental.

This is an example of a parametrized query. 

cursor.execute("INSERT INTO table VALUES (?, ?, ?)", (var1, var2, var3))

As you can see you need to pass two separate arguments to execute(), the query template and the values, so it will require some rejigging of your code.

Note: Different database modules accept different placeholders for the parametrized query. sqlite3, for example, accepts ? as above, but also allows named parameters (passed in a dictionary instead of a tuple):

cursor.execute("INSERT INTO table VALUES (:who, :age)", {"who":"Bill", "age": 33})
alexis
  • 48,685
  • 16
  • 101
  • 161
  • I did not know you could use named params and a dict for the query. Is that as secure as `(?, ?, ..)`? – 13aal May 20 '17 at 15:09
  • Yes, they are equally secure. See the link for other possibilities, but each database module will support a different subset. – alexis May 20 '17 at 16:25
-1

I am no SQL guy but if your enter table data separated by a space then a little for loop will do your job, Say:

l=raw_input('Enter Table contents separated by space.').split(' ')
l1='('
for i in l:l1=l1+i+', '
l1=l1[:len(l1-3)] #for removing extra comma and space left after loop
l1=l1+')'

Then use l1 where ever you want. You can use private attributes or something, for more protection!

Ubdus Samad
  • 1,218
  • 1
  • 15
  • 27
-4

If anyone is curious, this is what I ended up doing:

Never do this in production it is extremely vulnerable to SQL injection

def __create_sql_command(self, full, tablename, addable):
    return full.format(tablename, ', '.join(map(lambda x: "'" + x + "'", addable)))
#<= CREATE TABLE test ('test1', 'test2', 'test3')
dzm99972
  • 9
  • 1