[Twisted-Python] Strange error: SQL-Server tries to rollback

Tim Allen screwtape at froup.com
Fri Sep 10 19:21:14 MDT 2010


On Fri, Sep 10, 2010 at 06:54:34PM +0300, Pantelis Theodosiou wrote:
>         sql = ''.join(
>             [
>              "INSERT INTO fts_data VALUES ("
>                     ,  "'" , serialNumber , "'"
>             , "," , "'" , customerAccount , "'"
>             , "," , "'" , dateAndTime , "'"
>             , "," , camcount
>             , "," , fpv
>             , "," , "'" , reff , "'"
>             , "," , "'" , restOfFrame , "'"
>             , ") "
>             ] )

This is unrelated to your Twisted problem, but *please* tell me you are
not building SQL like this in production code.

If you need a reminder, the best possible way to do this in Python[1]
is:

    sqlPattern = """
	INSERT INTO fts_data VALUES (
	    %s, %s, %s, %s, %s, %s, %s
	);
	"""

    # if you're using a DB-API module directly:
    cursor.execute(sqlPattern, [serialNumber, customerAccount,
	    dateAndTime, camcount, fpv, reff, restOfFrame])

    # if you're using ADBAPI:
    deferred = pool.runOperation(sqlPattern, [serialNumber,
	    customerAccount, dateAndTime, camcount, fpv, reff,
	    restOfFrame])

Note that there's no "%" formatting operator between sqlPattern and the
list of values to be substituted in; the substitution is done with all
the proper quoting and escaping, not with Python's naïve formatting
operator.

Apologies if you already knew this, but apart from correctness it's one
of the few security issues where doing things the safe way is actually
easier than doing it the unsafe way - for a start, the SQL is all
together in one string. :)

[1]: The examples here use what DB-API calls the "format" quoting
style. Not all DB-API modules use it, but pymssql and psycopg2 do.




More information about the Twisted-Python mailing list