SQL Injection risks with entity API

[Leon Torres-2]

How robust is the entity engine API against sql injection attacks?  Consider the
following scenario:

// get the field to order by from the request parameters
orderByField = parameters.get("orderByField");
if (orderByField == null || orderByField.trim().length() == 0) {
   orderByFeild = "partyId"; // default
}

...

parties = delegator.findByAnd("Party", conditions,
UtilMisc.toList(orderByField)); // order by this field


What happens if the user tries to inject SQL into the orderByField parameter?
Is there a risk?  Should I be protecting myself by validating the orderByField
parameter or does ofbiz/JDBC already do this?

- Leon

[BJ Freeman]

In my configurations, each SQL Database has its own login that is unique.
With out that login no other user can access the SQL database.

are you saying that ofbiz may create these attacks? if so how?

Leon Torres sent the following on 7/7/2006 12:00 PM:

> How robust is the entity engine API against sql injection attacks?  
> Consider the following scenario:
>
> // get the field to order by from the request parameters
> orderByField = parameters.get("orderByField");
> if (orderByField == null || orderByField.trim().length() == 0) {
>   orderByFeild = "partyId"; // default
> }
>
> ...
>
> parties = delegator.findByAnd("Party", conditions,
> UtilMisc.toList(orderByField)); // order by this field
>
>
> What happens if the user tries to inject SQL into the orderByField
> parameter? Is there a risk?  Should I be protecting myself by validating
> the orderByField parameter or does ofbiz/JDBC already do this?
>
> - Leon
>

[Leon Torres-2]

Ok I just tried to do it and it doesn't work because ofbiz validates the field
names, which is great.  Here's what I tried:

opportunitiesOrderBy=opportunityStageId;delete%20from%20party%20where%201=1

Results in:

Target exception: org.ofbiz.entity.GenericModelException: Field with name
opportunityStageId;delete from party where 1=1 not found in the
PartyRelationshipAndSalesOpportunity Entity

So there is no need to worry about using request parameters directly in the
entity engine API.

- Leon



Leon Torres wrote:

> How robust is the entity engine API against sql injection attacks?  
> Consider the following scenario:
>
> // get the field to order by from the request parameters
> orderByField = parameters.get("orderByField");
> if (orderByField == null || orderByField.trim().length() == 0) {
>   orderByFeild = "partyId"; // default
> }
>
> ...
>
> parties = delegator.findByAnd("Party", conditions,
> UtilMisc.toList(orderByField)); // order by this field
>
>
> What happens if the user tries to inject SQL into the orderByField
> parameter? Is there a risk?  Should I be protecting myself by validating
> the orderByField parameter or does ofbiz/JDBC already do this?
>
> - Leon
>

[Adrian Crum]

Leon,

I'm glad you brought this up. It has always been a question in the back of my mind.

-Adrian


Leon Torres wrote:

> Ok I just tried to do it and it doesn't work because ofbiz validates the
> field names, which is great.  Here's what I tried:
>
> opportunitiesOrderBy=opportunityStageId;delete%20from%20party%20where%201=1
>
> Results in:
>
> Target exception: org.ofbiz.entity.GenericModelException: Field with
> name opportunityStageId;delete from party where 1=1 not found in the
> PartyRelationshipAndSalesOpportunity Entity
>
> So there is no need to worry about using request parameters directly in
> the entity engine API.
>
> - Leon
>
>
>
> Leon Torres wrote:
>
>> How robust is the entity engine API against sql injection attacks?  
>> Consider the following scenario:
>>
>> // get the field to order by from the request parameters
>> orderByField = parameters.get("orderByField");
>> if (orderByField == null || orderByField.trim().length() == 0) {
>>   orderByFeild = "partyId"; // default
>> }
>>
>> ...
>>
>> parties = delegator.findByAnd("Party", conditions,
>> UtilMisc.toList(orderByField)); // order by this field
>>
>>
>> What happens if the user tries to inject SQL into the orderByField
>> parameter? Is there a risk?  Should I be protecting myself by
>> validating the orderByField parameter or does ofbiz/JDBC already do this?
>>
>> - Leon
>>
>