Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #35073 > unrolled thread

Why Doesn't This MySQL Statement Execute?

Back to article view | Back to comp.lang.python

Contents

  Why Doesn't This MySQL Statement Execute? Tom Borkin <borkintom@gmail.com> - 2012-12-18 17:34 -0400
    Re: Why Doesn't This MySQL Statement Execute? Hans Mulder <hansmu@xs4all.nl> - 2012-12-18 23:57 +0100
      Re: Why Doesn't This MySQL Statement Execute? Tom Borkin <borkintom@gmail.com> - 2012-12-18 20:11 -0400
        Re: Why Doesn't This MySQL Statement Execute? John Gordon <gordon@panix.com> - 2012-12-19 03:57 +0000
          Re: Why Doesn't This MySQL Statement Execute? Chris Angelico <rosuav@gmail.com> - 2012-12-19 15:49 +1100
          Re: Why Doesn't This MySQL Statement Execute? Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2012-12-19 00:40 -0500
    Re: Why Doesn't This MySQL Statement Execute? Alister <alister.ware@ntlworld.com> - 2012-12-19 13:28 +0000

#35073 — Why Doesn't This MySQL Statement Execute?

[Multipart message — attachments visible in raw view] — view raw

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

[toc] | [next] | [standalone]

#35079

On 18/12/12 22:34:08, Tom Borkin wrote:
> 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

[toc] | [prev] | [next] | [standalone]

#35084

[Multipart message — attachments visible in raw view] — view raw

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

[toc] | [prev] | [next] | [standalone]

#35086

In <mailman.1037.1355875874.29569.python-list@python.org> Tom Borkin <borkintom@gmail.com> writes:

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

-- 
John Gordon                   A is for Amy, who fell down the stairs
gordon@panix.com              B is for Basil, assaulted by bears
                                -- Edward Gorey, "The Gashlycrumb Tinies"

[toc] | [prev] | [next] | [standalone]

#35088

On Wed, Dec 19, 2012 at 2:57 PM, John Gordon <gordon@panix.com> wrote:
> In <mailman.1037.1355875874.29569.python-list@python.org> Tom Borkin <borkintom@gmail.com> writes:
>
>> 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).

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

[toc] | [prev] | [next] | [standalone]

#35089

On Wed, 19 Dec 2012 15:49:41 +1100, Chris Angelico <rosuav@gmail.com>
declaimed the following in gmane.comp.python.general:

> On Wed, Dec 19, 2012 at 2:57 PM, John Gordon <gordon@panix.com> wrote:
> > In <mailman.1037.1355875874.29569.python-list@python.org> Tom Borkin <borkintom@gmail.com> writes:
> >
> >> 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).
> 
> 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...
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#35109

On Tue, 18 Dec 2012 17:34:08 -0400, Tom Borkin wrote:

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

Please don't build your sql strings like this but pass the data as 
paramaters

something like

sql="Insert into table (`field1`,`field2`) Values %s,%s"
cursor.execute(sql,(data1,data2))

And Goolge SQL injection

-- 
I can hire one half of the working class to kill the other half.
		-- Jay Gould

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web