Hi Matt,
Am 26.02.2006 um 04:31 schrieb Matthew Good:
Starting with Trac 0.9.4 reports use parameterized queries for
substituting the report variables instead of inserting them directly
into the SQL. This led to the discovery of problems like the one
reported in http://projects.edgewall.com/trac/ticket/2773
It appears that this problem is conventional in all the Python DB-API
implementations that support the "%s" parameter substitution
method. It
seems that they all use the Python string interpolation directly for
substituting parameters, which means that the parameter
substitution is
done regardless of whether it's inside a literal string or not.
So, if
you want to use SQL's LIKE wildcards, or the date formatting
strings in
SQLite ("%m/%d/%Y") along with parameterized queries you need to
escape
all the "%" signs with another like "%%".
The main place this problem presents itself is in the reports where
the
user is allowed to input SQL. If the user wants to make use of a
"%" in
the report it needs to be escaped, so it seems like there are 3
options:
* make the user escape it
* escape them specifically for reports
* wrap the cursors in Trac's db layer to handle the escaping
The third solution seems the most thorough, and I've actually
implemented this relatively easily in r2941. Of course this also
means
that every SQL statement in Trac would be going through a small layer
for parsing and escaping these strings, though without it there is a
potential for this subtle error to present itself in other SQL as
well.
I'm not sure, if the problem is specific to reports, I think we
should probably limit the overhead of the workaround to reports, too.
In SQL statements in code (Trac or plugins), the author should be
escaping the format character anyway.
Thanks for working on this BTW, my attempt to fix reports to use
proper parameters was not really thought through very well.
Cheers,
Chris
--
Christopher Lenz
cmlenz at gmx.de
http://www.cmlenz.net/
_______________________________________________
Trac-dev mailing list
[email protected]
http://lists.edgewall.com/mailman/listinfo/trac-dev
