esql: How to protect table/column name parameters from exploits?

[Lars Huttar <la...@sil.org>]

Hi all,

We've got ESQL code in XSP pages. Request parameters are
used to pass in various things, including ID's to match against,
as well as table and column names to operate on.
As experienced database developers know, any parameter received
from a URL needs to be guarded against, so a malicious (or
very unlucky) URL doesn't insert SQL code that one didn't
intend to allow.
(See http://www.devarticles.com/c/a/MySQL/SQL-Injection-Attacks-Are-You-Safe/1/)

There's a straightforward way to deal with this in esql for
parameters that represent literal values: wrap the
<xsp-request:get-parameter .../> element in an
<esql:parameter> element. (http://cocoon.apache.org/2.1/userdocs/xsp/esql.html#Dynamic+Queries)
According to the docs, "Since the driver is supposed to keep parameters distinct from the
statement, no code can be injected this way."
For example,
   SELECT Article_ID, Title, Summary, Date_Submitted FROM WNN
   WHERE Ethnologue_Code = <esql:parameter><xsp-request:get-parameter
name="code"/></esql:parameter>
works fine.

HOWEVER...
if the request parameter is not to be used as a literal value
but as a table or column name, <esql:parameter> doesn't
work. E.g. in
  SELECT count(*) TOTAL FROM <xsp-request:get-parameter name="table"/>
         where <xsp-request:get-parameter name="column" /> is not null

If I try to wrap the above <xsp-request:get-parameter> elements
with <esql:parameter> elements, the query fails.
If I treat the table parameter that way, I get an "invalid table name"
error. If I do that to the column parameter, the Select selects all
rows.
In other words, it's acting as though the table name or column name
were put in quotes.

SO...
Given that the table name and column name could be vulnerable
to URL exploits, how do I protect them in esql?
Any suggestions?
Would it make sense to do processing on the parameter values
to make sure they don't contain suspicious strings like "--"
and ";" and "select"?
Does anybody already have code to do that?

Thanks,
Lars
(a database novice)


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org

RE: esql: How to protect table/column name parameters from exploits?

[Lars Huttar <la...@sil.org>]

On Cocoon Users list, Conal Tuohy wrote:
> 
> Lars Huttar wrote:
> 
> > HOWEVER...
> > if the request parameter is not to be used as a literal value
> > but as a table or column name, <esql:parameter> doesn't
> > work. E.g. in
> >   SELECT count(*) TOTAL FROM <xsp-request:get-parameter 
> name="table"/>
> >          where <xsp-request:get-parameter name="column" /> 
> is not null
> >
> > If I try to wrap the above <xsp-request:get-parameter> elements
> > with <esql:parameter> elements, the query fails.
> > If I treat the table parameter that way, I get an "invalid table name"
> > error. If I do that to the column parameter, the Select selects all
> > rows.
> > In other words, it's acting as though the table name or column name
> > were put in quotes.
> >
> > SO...
> > Given that the table name and column name could be vulnerable
> > to URL exploits, how do I protect them in esql?
> > Any suggestions?
> 
> Do you really want users to be able to access any table? I 
> suggest probably not.

Hmm, good point. 

> You could use a Selector to check that the "table" parameter 
> falls into a
> set of allowed values.
> http://cocoon.apache.org/2.1/userdocs/selectors/requestparameter-selector.html
>
> Or the WildcardRequestParameterMatcher which would allow you specify the
> list of tables as a single regular expression I believe (though I've not
> done this myself).
> http://cocoon.apache.org/2.1/userdocs/concepts/matchers_selectors.html#Using+Matchers

Thanks for the suggestions and the insight.
Come to think of it, some of the cases where table/column
names are passed as parameters could be done as internal-only pipelines.
But not all.
So we might have to do some pattern-matching as you suggested.

I *think* this is only an issue in our "sanity checks" utility
(checking that all the tables and columns have some data in them,
that sort of thing). So those particular XSP pages needn't be
part of the application when it goes into production.

Anyway, thanks again.

Lars


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org

RE: esql: How to protect table/column name parameters from exploits?

[Conal Tuohy <co...@paradise.net.nz>]

Lars Huttar wrote:

> HOWEVER...
> if the request parameter is not to be used as a literal value
> but as a table or column name, <esql:parameter> doesn't
> work. E.g. in
>   SELECT count(*) TOTAL FROM <xsp-request:get-parameter name="table"/>
>          where <xsp-request:get-parameter name="column" /> is not null
>
> If I try to wrap the above <xsp-request:get-parameter> elements
> with <esql:parameter> elements, the query fails.
> If I treat the table parameter that way, I get an "invalid table name"
> error. If I do that to the column parameter, the Select selects all
> rows.
> In other words, it's acting as though the table name or column name
> were put in quotes.
>
> SO...
> Given that the table name and column name could be vulnerable
> to URL exploits, how do I protect them in esql?
> Any suggestions?

Do you really want users to be able to access any table? I suggest probably
not.

You could use a Selector to check that the "table" parameter falls into a
set of allowed values.
http://cocoon.apache.org/2.1/userdocs/selectors/requestparameter-selector.ht
ml

Or the WildcardRequestParameterMatcher which would allow you specify the
list of tables as a single regular expression I believe (though I've not
done this myself).
http://cocoon.apache.org/2.1/userdocs/concepts/matchers_selectors.html#Using
+Matchers


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org