Changes between Version 1 and Version 2 of TracDev/DatabaseApi

Timestamp:

May 27, 2005, 12:52:22 PM (19 years ago)

Author:

Christopher Lenz

Comment:

Comments on string formatting

TracDev/DatabaseApi

@@ -14 +14 @@
 Code accessing the database in Trac go through this layer simply by using the {{{Environment}}} method {{{get_db_cnx()}}}:
 
-
 {{{
 #!python
@@ -23 +22 @@
 cursor = db.cursor()
 # Execute some SQL statements
+
 db.commit()
 }}}
@@ -57 +57 @@
 }}}
 
+At any cost, try avoiding the user of [http://docs.python.org/lib/typesseq-strings.html string formatting] to get values into the SQL statement. The database automatically escapes values you pass using {{{execute()}}} arguments, the same is not true if you use string formatting. If you '''absolutely cannot avoid''' it, be sure to apply the {{{sql_escape}}} function in [source:/trunk/trac/util.py trac.util] to all parameters you're passing in, to avoid possible [http://en.wikipedia.org/wiki/SQL_injection SQL injection] attacks:
+
+{{{
+#!python
+from trac.util import sql_escape
+cursor.execute("SELECT author,ipnr,comment FROM wiki WHERE name=%s" % sql_escape(thename))
+}}}
+
+On the other hand, you '''must''' use string formatting to dynamically specify names of tables or columns, i.e. anything that is not a value as such:
+
+{{{
+#!python
+cursor.execute("SELECT time FROM %s WHERE name=%%s" % table, (thename,))
+}}}
+
 === Retrieving results ===