Strange MySQL Problem

Thu May 20 12:56:59 EDT 2010

Victor Subervi wrote:
> Hi;
> I have this code:
> 
> #!/usr/bin/python
> 
> import cgitb; cgitb.enable()
> import cgi
> import sys,os
> sys.path.append(os.getcwd())
> import MySQLdb
> from login import login
> 
> def create_edit_passengers4():
>   print "Content-Type: text/html"
>   print
>   print '''
> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN" 
> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd">
> <head xmlns="http://www.w3.org/1999/xhtml">
> </head>
> <body>
> '''
>   user, passwd, db, host = login()
>   database = MySQLdb.connect(host, user, passwd, db)
>   cursor = database.cursor()
>   form = cgi.FieldStorage()
>   cursor.execute('create table if not exists Passengers (id int(11) 
> auto_increment primary key, flights_id int(11) not null, customer_id 
> int(11) not null, foreign key (id) references Flights (flights_id), 
> foreign key (id) references Customers (customer_id), name varchar(40), 
> weight int) engine=InnoDB;')
>   new_passengers = int(form.getfirst('new_passengers'))
>   i = 0
>   while i < new_passengers:
>     cursor.execute('insert into Passengers values (Null, %s, %s, "%s", 
> %s);' % (form.getfirst('%d:flight' % i), form.getfirst('%d:customer' % 
> i), form.getfirst('%d:name' % i, ''), form.getfirst('%d:weight' % i)))

When performing SQL operations, don't insert the values using Python's
string formatting, because that makes it vulnerable to SQL-injection
attacks, ie don't do this:

     cursor.execute(sql_command % values)

do this:

     cursor.execute(sql_command, values)

>     i += 1
>   print "All passenger information has successfully been added."
>   cursor.close()
>   print "</body>\n</html>"
> 
> create_edit_passengers4()
> 
> Now, it throws no errors; however, it doesn't insert. If I print out the 
> insert statement to screen and then manually insert it in MySQL it 
> inserts. Huh??
> 
I think you need to 'commit' any changes to do to the database.