Why Doesn't This MySQL Statement Execute?

[Tom Borkin]

Hi;
I have this test code:

if i_id == "1186":
sql = 'insert into interactions values(Null, %s, "Call Back", "%s")'
% (i_id, date_plus_2)
cursor.execute(sql)
db.commit()
print sql
It prints the sql statement, but it doesn't execute. If I copy and paste
the sql into the mysql command line it does execute without warnings or
errors. What gives?
TIA,
Tom

 

[Hans Mulder]

Hi;
I have this test code:

if i_id == "1186":
sql = 'insert into interactions values(Null, %s, "Call Back",
"%s")' % (i_id, date_plus_2)
cursor.execute(sql)
db.commit()
print sql
It prints the sql statement, but it doesn't execute. If I copy and paste
the sql into the mysql command line it does execute without warnings or
errors. What gives?

What happens if you do:


if i_id == "1186":
sql = 'insert into interactions values(Null, %s, "Call Back", %s)'
cursor.execute(sql, (i_id, date_plus_2))
db.commit()
print sql

Note the absence of quotes around the second %s in the sql command.

This should work correctly even if date_plus_2 happens to contain

Robert"); DROP TABLE interactions; --


For background information, see http://bobby-tables.com/python.html


Hope this helps,

-- HansM

 

[Tom Borkin]

Actually, what I originally had was:
cursor.execute("""insert into interactions values(Null, %s, "Call Back",
%s)""", (i_id, date_plus_2))
and that didn't work, either. I tried your variation like:
cursor.execute("""insert into interactions values(Null, %s, "Call Back",
%s)""" % (i_id, date_plus_2))
and no cigar
Tom

 

[John Gordon]

Actually, what I originally had was:
cursor.execute("""insert into interactions values(Null, %s, "Call Back",
%s)""", (i_id, date_plus_2))
and that didn't work, either. I tried your variation like:
cursor.execute("""insert into interactions values(Null, %s, "Call Back",
%s)""" % (i_id, date_plus_2))
and no cigar
Tom

Have you tried using single-quotes around Call Back, instead of
double quotes? I've noticed that SQL statements prefer single-quoted
strings (although that may be Oracle specific, as that's all I've really
worked with).

 

[Chris Angelico]

Have you tried using single-quotes around Call Back, instead of
double quotes? I've noticed that SQL statements prefer single-quoted
strings (although that may be Oracle specific, as that's all I've really
worked with).

The SQL standard specifies single quotes, but MySQL and the SQL
standard aren't always on speaking terms. It depends on the MySQL
settings as to whether "asdf" means 'asdf' or means a column named
asdf.

But if that's what the problem is, there ought to be an exception
coming back, surely? I'm not familiar with the Python MySQL bindings,
but that's what I would expect. What, specifically, does "no cigar"
mean? It executes without errors but does nothing? It purchases a gun,
aims at your shoe, and pulls the trigger?

ChrisA

 

[Dennis Lee Bieber]

The SQL standard specifies single quotes, but MySQL and the SQL
standard aren't always on speaking terms. It depends on the MySQL
settings as to whether "asdf" means 'asdf' or means a column named
asdf.

But if that's what the problem is, there ought to be an exception
coming back, surely? I'm not familiar with the Python MySQL bindings,
but that's what I would expect. What, specifically, does "no cigar"
mean? It executes without errors but does nothing? It purchases a gun,
aims at your shoe, and pulls the trigger?

Well... off the wall...

I'm going to presume the first field -- the Null -- is for a primary
key (autoincrement)...

What happens if you change the SQL to actually specify the columns
being filled AND you ignore the primary key/Null entry?

And since I don't like hard-coding the SQL...

cursor.execute("""insert into interactions (someID, action, somedate)
values (%s, %s, %s)""",
(i_id, "Call Back", date_plus_2) )

Of course you need to use the actual table column names...