Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Preventing SQL Injection
Preventing SQL Injection [message #656884] Mon, 28 February 2011 21:35 Go to next message
Josh Hall is currently offline Josh Hall
Messages: 31
Registered: October 2010
Member
Typically I use query parameters in my BIRT data sets to avoid SQL injection attacks:-

select *
from user
where organization_id = ?


However, using the above example, if I need to allow 'All' organizations as a possible selection from the user, I (poorly) handle it like this:-

select *
from user


and I add the where clause manually in the event handler if the user actually selects an Organization.

dataSet.setQueryText(dataSet.getQueryText() + myWhereClauseIncludingUserEnteredData);



This leaves my reports vulnerable to injection attacks. Any suggestions on how to handle the 'All' option without the security risk of the injection attack?
Re: Preventing SQL Injection [message #656889 is a reply to message #656884] Mon, 28 February 2011 21:55 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Josh,

Why not just check for the all before you append it to the sql in script?

Jason

On 2/28/2011 4:35 PM, Josh wrote:
> Typically I use query parameters in my BIRT data sets to avoid SQL
> injection attacks:-
>
> select *
> from user
> where organization_id = ?
>
> However, using the above example, if I need to allow 'All' organizations
> as a possible selection from the user, I (poorly) handle it like this:-
>
> select *
> from user
>
> and I add the where clause manually in the event handler if the user
> actually selects an Organization.
> dataSet.setQueryText(dataSet.getQueryText() +
> myWhereClauseIncludingUserEnteredData);
>
>
> This leaves my reports vulnerable to injection attacks. Any suggestions
> on how to handle the 'All' option without the security risk of the
> injection attack?
Re: Preventing SQL Injection [message #656892 is a reply to message #656889] Mon, 28 February 2011 22:23 Go to previous messageGo to next message
Josh Hall is currently offline Josh Hall
Messages: 31
Registered: October 2010
Member
Yes I do do that (I was just trying to keep my examples brief). It's when the user selects a specific organization that I have to append the SQL. But because the where clause is optional (because of the possibility of 'All' being selected) I cannot use a query parameter.
Re: Preventing SQL Injection [message #657087 is a reply to message #656892] Tue, 01 March 2011 15:11 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Is the all in a report parameter? If so just check its value in the
dataset beforeOpen script before you modify the sql. You can check it
by using params["myparameter"].value

Jason

On 2/28/2011 5:23 PM, Josh wrote:
> Yes I do do that (I was just trying to keep my examples brief). It's
> when the user selects a specific organization that I have to append the
> SQL. But because the where clause is optional (because of the
> possibility of 'All' being selected) I cannot use a query parameter.
Re: Preventing SQL Injection [message #657187 is a reply to message #657087] Tue, 01 March 2011 21:18 Go to previous messageGo to next message
Josh Hall is currently offline Josh Hall
Messages: 31
Registered: October 2010
Member
Yes, I am doing that. I think you're missing the point. What I'm saying is:-
*Having the 'All' option means I have to programatically concatenate the SQL.
*Concatenating the SQL = injection vulnerability.
**Is there a better way to handle this?
Re: Preventing SQL Injection [message #657197 is a reply to message #657187] Tue, 01 March 2011 21:52 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

I do not think it is better, but you could create 2 datasets, one with a
query parameter and one without and in the beforeFactory set the dataset
for the table that uses it.

Jason

On 3/1/2011 4:18 PM, Josh wrote:
> Yes, I am doing that. I think you're missing the point. What I'm saying
> is:-
> *Having the 'All' option means I have to programatically concatenate the
> SQL.
> *Concatenating the SQL = injection vulnerability.
> **Is there a better way to handle this?
Re: Preventing SQL Injection [message #657363 is a reply to message #656884] Wed, 02 March 2011 14:58 Go to previous messageGo to next message
Richard A. Polunsky is currently offline Richard A. Polunsky
Messages: 199
Registered: July 2009
Location: Houston TX
Senior Member

I reference the parameter twice, and code the WHERE clause as
"WHERE stringfield = ? or ? = 'ALL'"

If the parameter is numeric, I generally use zero as the ALL equivalent.

As long as the parameter is single-select, that seems to work well for me.

Re: Preventing SQL Injection [message #657480 is a reply to message #657363] Wed, 02 March 2011 22:26 Go to previous message
Josh Hall is currently offline Josh Hall
Messages: 31
Registered: October 2010
Member
Thanks Richard. That seems like a great solution. I'll give it a go!
Previous Topic:BIRT leaves behind db2fmp.exe
Next Topic:Periodic platform restart
Goto Forum:
  


Current Time: Tue Sep 16 05:08:45 GMT 2014

Powered by FUDForum. Page generated in 0.03674 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software