Questions for Meredith

Meredith,

Now that your slides are up, let me give you the list of comments and
questions from the PUG that you asked for:

1) Sugggestion: the query template notation should distinguish between
literals and non-literals. Currently, you have:

SELECT {col1} FROM table;
and SELECT '{col}' FROM table;

... with the same notation, allowing an attacker to try to swap a literal
with an object reference if they can defeat the escaping mechanism,
especially if the programmer makes makes the mistake of omitting the
quotes or putting them inside the brackets.

2) Reporting queries: In the course of designing a number of applications
with sophisticated web reporting interfaces, I've found myself in a
position where I'd want to escape more than a single column or literal.
For example, one search interface might require something like this:

SELECT {stock_price} as col1, {sale_month} as col2, {region} as col3
FROM stock_report_view
WHERE { year between 1999 and 2001 AND sic LIKE 'I3%' }
ORDER BY { col3, col2 DESC }
LIMIT { 30 } OFFSET { 61 };

Given such a template, I'd like to ensure that no attacker can (a) add
anything to the FROM clause, or (b) insert subselects into the SELECT
clause. Would that work?

3) We suggested that actually the best way to generate query templates woud
be by example. That is, as part of unit testing, you'd generate three
"known good" queries, compare them, and get a resulting template out of
the comparison. This would require two command-line tools:
a) a tool to generate and store a parse tree from a query, and
b) a tool which can compate two or more parse trees and generate a query
template or cached parse tree which reflects the overlap.

4) It was also suggested that for production applications you'd want to
cache the parse tree generated by the query template rather than
re-parsing it ever time you check a query.

5) Finally, the biggest potential value of this is if we could find a way
to retrofit it onto known-insecure scripting-language applications,
without needing to change the application -- that is, implement it
entirely at the driver or back-end level. I don't see an obvious way to
do that, but it's work thinking about.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco