[jira] Created: (OFBIZ-837) EntityFunction.UPPER will crash if its argument contains apostrophes

[Nicolas Malin (Jira)]

EntityFunction.UPPER will crash if its argument contains apostrophes
--------------------------------------------------------------------

                 Key: OFBIZ-837
                 URL: https://issues.apache.org/jira/browse/OFBIZ-837
             Project: OFBiz (The Open for Business Project)
          Issue Type: Bug
          Components: framework
    Affects Versions: SVN trunk
            Reporter: Leon Torres
            Priority: Blocker


If one makes a LIKE condition such as the following,

EntityExpr("firstName", true, EntityOperator.LIKE, "O'Donnell", true);

It gets mapped into an SQL expression:

FIRST_NAME LIKE UPPER('O'Donnell')

Which crashes because the apostrophe in O'Donnell was not escaped.

The reason for this is that when the condition is created by EntityFunction.UPPER, it bypasses the usual string escaping that is performed by the JDBC.  That is, the entity engine is constructing the UPPER('O'Donnell') string by hand and inserting it directly into an SQL instruction, rather than using a safer prepared statement technique.

This bug crashes a bunch of screens all over that use the LIKE operation.  It also permits SQL injection attacks, which is the reason I made this a blocker issue.  

This issue was discovered on a client site running an older version of ofbiz and has been confirmed in SVN.  You can try it by searching for "O'Donnell" or anything with an apostrophe in the party manager's find party screen.

I have a very simple fix which I'll attach after this that can be applied to OFBiz since 3.0 at least.

Those of us who have older versions in production should probably consider fixing this bug.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

     [ https://issues.apache.org/jira/browse/OFBIZ-837?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Leon Torres updated OFBIZ-837:
------------------------------

    Attachment: EntityFunctionUpper.patch

The EntityFunctionUpper.patch fixes the issue by escaping apostrophes in the input string when the EntityFunction.UPPER or LOWER is created.

> EntityFunction.UPPER will crash if its argument contains apostrophes
> --------------------------------------------------------------------
>
>                 Key: OFBIZ-837
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-837
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: SVN trunk
>            Reporter: Leon Torres
>            Priority: Blocker
>         Attachments: EntityFunctionUpper.patch
>
>
> If one makes a LIKE condition such as the following,
> EntityExpr("firstName", true, EntityOperator.LIKE, "O'Donnell", true);
> It gets mapped into an SQL expression:
> FIRST_NAME LIKE UPPER('O'Donnell')
> Which crashes because the apostrophe in O'Donnell was not escaped.
> The reason for this is that when the condition is created by EntityFunction.UPPER, it bypasses the usual string escaping that is performed by the JDBC.  That is, the entity engine is constructing the UPPER('O'Donnell') string by hand and inserting it directly into an SQL instruction, rather than using a safer prepared statement technique.
> This bug crashes a bunch of screens all over that use the LIKE operation.  It also permits SQL injection attacks, which is the reason I made this a blocker issue.  
> This issue was discovered on a client site running an older version of ofbiz and has been confirmed in SVN.  You can try it by searching for "O'Donnell" or anything with an apostrophe in the party manager's find party screen.
> I have a very simple fix which I'll attach after this that can be applied to OFBiz since 3.0 at least.
> Those of us who have older versions in production should probably consider fixing this bug.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

     [ https://issues.apache.org/jira/browse/OFBIZ-837?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Si Chen reassigned OFBIZ-837:
-----------------------------

    Assignee: Si Chen

> EntityFunction.UPPER will crash if its argument contains apostrophes
> --------------------------------------------------------------------
>
>                 Key: OFBIZ-837
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-837
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: SVN trunk
>            Reporter: Leon Torres
>         Assigned To: Si Chen
>            Priority: Blocker
>         Attachments: EntityFunctionUpper.patch
>
>
> If one makes a LIKE condition such as the following,
> EntityExpr("firstName", true, EntityOperator.LIKE, "O'Donnell", true);
> It gets mapped into an SQL expression:
> FIRST_NAME LIKE UPPER('O'Donnell')
> Which crashes because the apostrophe in O'Donnell was not escaped.
> The reason for this is that when the condition is created by EntityFunction.UPPER, it bypasses the usual string escaping that is performed by the JDBC.  That is, the entity engine is constructing the UPPER('O'Donnell') string by hand and inserting it directly into an SQL instruction, rather than using a safer prepared statement technique.
> This bug crashes a bunch of screens all over that use the LIKE operation.  It also permits SQL injection attacks, which is the reason I made this a blocker issue.  
> This issue was discovered on a client site running an older version of ofbiz and has been confirmed in SVN.  You can try it by searching for "O'Donnell" or anything with an apostrophe in the party manager's find party screen.
> I have a very simple fix which I'll attach after this that can be applied to OFBiz since 3.0 at least.
> Those of us who have older versions in production should probably consider fixing this bug.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

    [ https://issues.apache.org/jira/browse/OFBIZ-837?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12483273 ]

Adrian Crum commented on OFBIZ-837:
-----------------------------------

I verified the bug, applied the patch, and verified that it fixes the bug.

> EntityFunction.UPPER will crash if its argument contains apostrophes
> --------------------------------------------------------------------
>
>                 Key: OFBIZ-837
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-837
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: SVN trunk
>            Reporter: Leon Torres
>         Assigned To: Si Chen
>            Priority: Blocker
>         Attachments: EntityFunctionUpper.patch
>
>
> If one makes a LIKE condition such as the following,
> EntityExpr("firstName", true, EntityOperator.LIKE, "O'Donnell", true);
> It gets mapped into an SQL expression:
> FIRST_NAME LIKE UPPER('O'Donnell')
> Which crashes because the apostrophe in O'Donnell was not escaped.
> The reason for this is that when the condition is created by EntityFunction.UPPER, it bypasses the usual string escaping that is performed by the JDBC.  That is, the entity engine is constructing the UPPER('O'Donnell') string by hand and inserting it directly into an SQL instruction, rather than using a safer prepared statement technique.
> This bug crashes a bunch of screens all over that use the LIKE operation.  It also permits SQL injection attacks, which is the reason I made this a blocker issue.  
> This issue was discovered on a client site running an older version of ofbiz and has been confirmed in SVN.  You can try it by searching for "O'Donnell" or anything with an apostrophe in the party manager's find party screen.
> I have a very simple fix which I'll attach after this that can be applied to OFBiz since 3.0 at least.
> Those of us who have older versions in production should probably consider fixing this bug.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

    [ https://issues.apache.org/jira/browse/OFBIZ-837?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12483341 ]

Si Chen commented on OFBIZ-837:
-------------------------------

If there are no objections I will commit this tomorrow morning.

> EntityFunction.UPPER will crash if its argument contains apostrophes
> --------------------------------------------------------------------
>
>                 Key: OFBIZ-837
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-837
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: SVN trunk
>            Reporter: Leon Torres
>         Assigned To: Si Chen
>            Priority: Blocker
>         Attachments: EntityFunctionUpper.patch
>
>
> If one makes a LIKE condition such as the following,
> EntityExpr("firstName", true, EntityOperator.LIKE, "O'Donnell", true);
> It gets mapped into an SQL expression:
> FIRST_NAME LIKE UPPER('O'Donnell')
> Which crashes because the apostrophe in O'Donnell was not escaped.
> The reason for this is that when the condition is created by EntityFunction.UPPER, it bypasses the usual string escaping that is performed by the JDBC.  That is, the entity engine is constructing the UPPER('O'Donnell') string by hand and inserting it directly into an SQL instruction, rather than using a safer prepared statement technique.
> This bug crashes a bunch of screens all over that use the LIKE operation.  It also permits SQL injection attacks, which is the reason I made this a blocker issue.  
> This issue was discovered on a client site running an older version of ofbiz and has been confirmed in SVN.  You can try it by searching for "O'Donnell" or anything with an apostrophe in the party manager's find party screen.
> I have a very simple fix which I'll attach after this that can be applied to OFBiz since 3.0 at least.
> Those of us who have older versions in production should probably consider fixing this bug.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

     [ https://issues.apache.org/jira/browse/OFBIZ-837?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Si Chen closed OFBIZ-837.
-------------------------

    Resolution: Fixed

> EntityFunction.UPPER will crash if its argument contains apostrophes
> --------------------------------------------------------------------
>
>                 Key: OFBIZ-837
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-837
>             Project: OFBiz (The Open for Business Project)
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: SVN trunk
>            Reporter: Leon Torres
>         Assigned To: Si Chen
>            Priority: Blocker
>         Attachments: EntityFunctionUpper.patch
>
>
> If one makes a LIKE condition such as the following,
> EntityExpr("firstName", true, EntityOperator.LIKE, "O'Donnell", true);
> It gets mapped into an SQL expression:
> FIRST_NAME LIKE UPPER('O'Donnell')
> Which crashes because the apostrophe in O'Donnell was not escaped.
> The reason for this is that when the condition is created by EntityFunction.UPPER, it bypasses the usual string escaping that is performed by the JDBC.  That is, the entity engine is constructing the UPPER('O'Donnell') string by hand and inserting it directly into an SQL instruction, rather than using a safer prepared statement technique.
> This bug crashes a bunch of screens all over that use the LIKE operation.  It also permits SQL injection attacks, which is the reason I made this a blocker issue.  
> This issue was discovered on a client site running an older version of ofbiz and has been confirmed in SVN.  You can try it by searching for "O'Donnell" or anything with an apostrophe in the party manager's find party screen.
> I have a very simple fix which I'll attach after this that can be applied to OFBiz since 3.0 at least.
> Those of us who have older versions in production should probably consider fixing this bug.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.