python mysql delete statement not working

Question

here I am trying to remove any users which containt a " in their email/username.

    def removeQuote(self, tbl,record):
            """ Updates the record """
            statmt="select id from %s WHERE `email` LIKE '%%\"%%'" % (tbl)
            self.cursor.execute(statmt)
            rows=list(self.cursor.fetchall())
            for idx, val in enumerate(rows):
                    id= val[0]
                    delstatmt = "DELETE FROM `maillist_subscription` WHERE id = '%s'" % id
                    print delstatmt
                    self.cursor.execute(delstatmt)

The output of this shows as if the action completed successfully, but the record remains in the database. Output also shows a correct mysql statement:

DELETE FROM `maillist_subscription` WHERE id = '8288754'

Thanks for all your help!

Answer 1

You need to commit the change, using the commit() method on the connection object. Most DBAPI interfaces use implicit transactions.

Also, don't use string formatting for SQL query generation! It will open you up to SQL injections:

UNSAFE!!

# What happens if id = "1'; DROP DATABASE somedb" ?
delstatmt = "DELETE FROM `maillist_subscription` WHERE id = '%s'" % (id,)
cursor.execute(delstatmt)
conn.commit()

SAFE!

delstatmt = "DELETE FROM `maillist_subscription` WHERE id = ?"
cursor.execute(delstatmt, (id,))
conn.commit()