Eclipse Community Forums: BIRT » Preventing SQL Injection

Home

Home » Archived » BIRT » Preventing SQL Injection

Preventing SQL Injection [message #656884]

Mon, 28 February 2011 16:35

Eclipse User

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 16:55

Eclipse User

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 17:23

Eclipse User

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 10:11

Eclipse User

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 16:18

Eclipse User

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 16:52

Eclipse User

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 09:58

Eclipse User

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 17:26

Eclipse User

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:

-=] Back to Top [=-

Current Time: Wed Jul 23 08:01:11 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter