current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I've been writing code recently in python that works with postgreSQL databases, and it looks like the following:

def insert(table, ID, date, timestamp):
    cur.execute("""INSERT INTO %s (ID, date, timest)
                   VALUES (%s, %s, %s);""", 
                   (AsIs(table), ID, AsIs(date), AsIs(timestamp)))

This seems to work fine for postgreSQL, but when I try the same thing on a mySQL server, it doesn't work. Does anybody have an idea as to why? I think it might have to do with the %s characters, as it works if I hardcode in the values for table, ID, date, and timestamp.

In addition, it gives me the following error:

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''master'' at line 2")

'master' is the name of the table I used as a variable, and I also noticed that there were two single quotes around master (like so: ' 'master' '), where there should only be one. So this might be a problem too?

Anyways, thanks for the help!

share|improve this question
    
Can you create the string before giving the execute command? –  TheSoundDefense Jul 24 '14 at 23:53
    
@TheSoundDefense how would I do that? –  jj172 Jul 24 '14 at 23:55
    
query_str = "INSERT INTO %s (ID, date, timest) VALUES (%s, %s, %s);" % (str(table), str(ID), str(date), str(timestamp)) followed by cur.execute(query_str). I dunno if this will work, I haven't used Python's SQL libraries. –  TheSoundDefense Jul 24 '14 at 23:57
4  
@TheSoundDefense -- That's vulnerable to SQL injection. –  mgilson Jul 24 '14 at 23:58
1  
AsIS is a psycopg2 adapter. What driver are you using for MySQL? –  Clodoaldo Neto Jul 25 '14 at 0:01

3 Answers 3

up vote 2 down vote

This doesn't work fine in PostgreSQL either. The table name should not be parametrized.

cur.execute("""INSERT INTO table_name_here (ID, date, timest) ...

For string concatenation: Postgres uses SQL-standard double quotes (not two single quotes!): "master", which does not work in default MySQL. Would have to be `master` - or just master to work in both.

Or activate a setting for some SQL-compliance in MySQL:

SET sql_mode = 'ANSI';
share|improve this answer
up vote 0 down vote

You can use format function:

sql = "INSERT INTO {0} (ID, date, timest) VALUES (%s, %s, %s);".format(table_name)
cursor.execute(sql, (ID, date, time_stamp))
share|improve this answer
up vote -2 down vote accepted

Actually, I was able to figure out the answer to my question. Simply using a % instead of the , notation fixed my problem.

Original code:

cur.execute("""INSERT INTO %s (ID, date, timest)
               VALUES (%s, %s, %s);""", 
               (AsIs(table), ID, AsIs(date), AsIs(timestamp)))

Fixed code:

cur.execute("""INSERT INTO %s (ID, date, timest)
               VALUES (%s, %s, %s);""" %
               (AsIs(table), ID, AsIs(date), AsIs(timestamp)))
share|improve this answer
1  
NO NO NO. Please don't do that because it's a horribly insecure SQL injection. –  Kirk Strauser Jul 25 '14 at 21:15
    
@KirkStrauser I realize it is prone to SQL injection, but it is merely a test script that I will be running for a known set of tables for my own use. –  jj172 Jul 25 '14 at 21:25
    
There's no such thing as an unloaded gun or a secured query. Both should be treated as highly dangerous and likely to explode at any moment. Doing things the right way is exactly as easy as doing them the wrong way, so it's better to get in the habit of doing it right every single time. –  Kirk Strauser Jul 25 '14 at 21:26
    
@KirkStrauser but it seems the way of doing it "right" is to manually hard code each of my table names, which would have been pretty inefficient given the way my code operates. This is a viable shortcut that meets the conditions that I need. –  jj172 Jul 25 '14 at 22:18
    
At the very least, you could write "INSERT INTO %s (var, var) VALUES (%%s, %%s)" % table_name so that the table name is injected into the string but that the values still go through safe escaping. –  Kirk Strauser Jul 25 '14 at 22:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.