Using %s commands in execute statements (python) in mySQL vs postgreSQL?

Question

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!

Can you create the string before giving the execute command? — TheSoundDefense
– TheSoundDefense, Commented Jul 24, 2014 at 23:53
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
– TheSoundDefense, Commented Jul 24, 2014 at 23:57
AsIS is a psycopg2 adapter. What driver are you using for MySQL? — Clodoaldo Neto
– Clodoaldo Neto, Commented Jul 25, 2014 at 0:01

Erwin Brandstetter · Accepted Answer · 2014-07-25 00:11:27Z

2

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

edited Jul 25, 2014 at 0:11

answered Jul 24, 2014 at 23:58

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Peeyush · Accepted Answer · 2014-07-26 13:53:22Z

0

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

answered Jul 26, 2014 at 13:53

Peeyush

7264 silver badges11 bronze badges

Comments

jj172 · Accepted Answer · 2014-07-25 21:08:22Z

-3

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

answered Jul 25, 2014 at 21:08

jj172

8093 gold badges11 silver badges37 bronze badges

5 Comments

Kirk Strauser Over a year ago

NO NO NO. Please don't do that because it's a horribly insecure SQL injection.

jj172 Over a year ago

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

Kirk Strauser Over a year ago

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.

jj172 Over a year ago

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

Kirk Strauser Over a year ago

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.

Collectives™ on Stack Overflow

Using %s commands in execute statements (python) in mySQL vs postgreSQL?

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related