SQL Improvements

[Martin Scharrer]

Hi,

I would change the SQL code as follows to e.g. make sure that user input cannot be taken as SQL commands. The trick is to insert the values which need to be constant for SQL first with pythons string substitution, but then provide the user input as arguments to cursor.execute().

I didn't had the time and chance to fully test the patch yet, but I should get the point.

renametracusersscript_modified/0.11/renametracusers/main.py

@@ -48 +48 @@
         # ticket_change require special attention
         db = self.env.get_db_cnx()
         cur = db.cursor()
-        cur.execute("UPDATE ticket_change SET  oldvalue='%s' WHERE field='owner' AND oldvalue='%s'" % (new_login, old_login))
-        cur.execute("UPDATE ticket_change SET  newvalue='%s' WHERE field='owner' AND newvalue='%s'" % (new_login, old_login))
+        cur.execute("UPDATE ticket_change SET  oldvalue=%s WHERE field='owner' AND oldvalue=%s", (new_login, old_login))
+        cur.execute("UPDATE ticket_change SET  newvalue=%s WHERE field='owner' AND newvalue=%s", (new_login, old_login))
         db.commit()
         db.close()
 
@@ -73 +73 @@
                 if field in self.unique.get(table, []):
                     db = self.env.get_db_cnx()
                     cur = db.cursor()
-                    cur.execute("DELETE FROM %s WHERE %s='%s'" % (table, field, old_login))
+                    cur.execute("DELETE FROM %s WHERE %s=%%s" % (table, field), (old_login,))
                     db.commit()
                     db.close()
 
@@ -84 +84 @@
                     # XXX this should work, but it doesn't, so instead do this the retarded way (thank you, SQL!)
                     # cur.execute("UPDATE %s SET %s=%s WHERE %s=%s", (table, field, new_login, field, old_login))
                     
-                    cur.execute("UPDATE %s SET %s='%s' WHERE %s='%s'" % (table, field, new_login, field, old_login))
+                    cur.execute("UPDATE %s SET %s=%%s WHERE %s=%%s" % (table, field, field), (new_login, old_login))
                     db.commit()
                     db.close()
                 except:

[Ryan J Ollos]

Plugin is deprecated, see #10901.