[
https://issues.apache.org/jira/browse/OFBIZ-6567?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jacques Le Roux updated OFBIZ-6567:
-----------------------------------
Description:
This was reported to me by Gareth Carter, the investigation is mine.
If for instance you use this SQL expression
{code}
select * from Party_Role where role_Type_Id LIKE '%CA%'
{code}
It will be interpreted (and returned to UI) as
{code}
select * from Party_Role where role_Type_Id LIKE 'Ê%'
{code}
And no result will be returned when OOTB there is 6 <PartyRole partyId="***"
roleTypeId="CARRIER"/> entities
This is because in UtilHttp.canonicalizeParameterMap()
UtilHttp.canonicalizeParameter() is called. And inside the later
UtilCodec.canonicalize() is used. So 2 ESAPI codecs are tested
HTMLEntityCodec.decode() and PercentCodec.decode(). Only PercentCodec.decode()
does a change so it's picked. In this case it should not, because nothing
should be decoded. At this point, nothing has been encoded, the String the
codec decodes is still "select * from Party_Role where role_Type_Id LIKE
'%CA%'"
I read at https://en.wikipedia.org/wiki/Percent-encoding that though mostly
planned for URL encoding percent encoding
bq. is also used in the preparation of data of the
application/x-www-form-urlencoded media type, as is often used in the
submission of HTML form data in HTTP requests.
But in the specific case of a like in an SQL expression coming from the text
area of webtools/control/EntitySQLProcessor it should not be used because the %
followed by some chars, may be wrongly decoded.
Because there are no other ways provided by the percent codec to prevent the
decoding (it's supposed to have been encoded before), I'm not quite proud of it
but I found only this workaround so far
{code}
Index: framework/base/src/org/ofbiz/base/util/UtilCodec.java
===================================================================
--- framework/base/src/org/ofbiz/base/util/UtilCodec.java (revision
1693397)
+++ framework/base/src/org/ofbiz/base/util/UtilCodec.java (working copy)
@@ -164,16 +164,24 @@
while (i.hasNext()) {
Codec codec = i.next();
String old = working;
- working = codec.decode(working);
- if (!old.equals(working)) {
- if (codecFound != null && codecFound != codec) {
- mixedCount++;
+ String upperWorking = working.toUpperCase();
+ if (codec instanceof PercentCodec
+ && upperWorking.contains("WHERE")
+ && upperWorking.contains("LIKE")
+ && upperWorking.contains("%")) {
+ continue;
+ } else {
+ working = codec.decode(working);
+ if (!old.equals(working)) {
+ if (codecFound != null && codecFound != codec) {
+ mixedCount++;
+ }
+ codecFound = codec;
+ if (clean) {
+ foundCount++;
+ }
+ clean = false;
}
- codecFound = codec;
- if (clean) {
- foundCount++;
- }
- clean = false;
}
}
}
{code}
Better ideas?
was:
This was reported to me by Gareth Carter, the investigation is mine.
If for instance you use this SQL expression
{code}
select * from Party_Role where role_Type_Id LIKE '%CA%'
{code}
It will be interpreted (and returned to UI) as
{code}
select * from Party_Role where role_Type_Id LIKE 'Ê%'
{code}
And no result will be returned when OOTB there is 6 <PartyRole partyId="***"
roleTypeId="CARRIER"/> entities
This is because in UtilHttp.canonicalizeParameterMap()
UtilHttp.canonicalizeParameter() is called. And inside the later
UtilCodec.canonicalize() is used. So 2 ESAPI codecs are tested
HTMLEntityCodec.decode() and PercentCodec.decode(). Only PercentCodec.decode()
does a change so it's picked. In this case it should not, because nothing
should be decoded. At this point, nothing has been encoded, the String the
codec decodes is still "select * from Party_Role where role_Type_Id LIKE
'%CA%'"
I read at https://en.wikipedia.org/wiki/Percent-encoding that though mostly
planned for URL encoding percent encoding
bq. is also used in the preparation of data of the
application/x-www-form-urlencoded media type, as is often used in the
submission of HTML form data in HTTP requests.
But in the specific case of a like in an SQL expression coming from the text
area of webtools/control/EntitySQLProcessor it should not be used because the %
followed by some chars, may be wrongly decoded.
Because there are no other ways provided by the percent codec to prevent the
decoding (it's supposed to have been encoded before), I'm not quite proud of it
but I found only this workaround so far
Index: framework/base/src/org/ofbiz/base/util/UtilCodec.java
===================================================================
--- framework/base/src/org/ofbiz/base/util/UtilCodec.java (revision
1693397)
+++ framework/base/src/org/ofbiz/base/util/UtilCodec.java (working copy)
@@ -164,16 +164,24 @@
while (i.hasNext()) {
Codec codec = i.next();
String old = working;
- working = codec.decode(working);
- if (!old.equals(working)) {
- if (codecFound != null && codecFound != codec) {
- mixedCount++;
+ String upperWorking = working.toUpperCase();
+ if (codec instanceof PercentCodec
+ && upperWorking.contains("WHERE")
+ && upperWorking.contains("LIKE")
+ && upperWorking.contains("%")) {
+ continue;
+ } else {
+ working = codec.decode(working);
+ if (!old.equals(working)) {
+ if (codecFound != null && codecFound != codec) {
+ mixedCount++;
+ }
+ codecFound = codec;
+ if (clean) {
+ foundCount++;
+ }
+ clean = false;
}
- codecFound = codec;
- if (clean) {
- foundCount++;
- }
- clean = false;
}
}
}
Better ideas?
> Wrong percent encoding in Webtool/SQL Processor
> -----------------------------------------------
>
> Key: OFBIZ-6567
> URL: https://issues.apache.org/jira/browse/OFBIZ-6567
> Project: OFBiz
> Issue Type: Bug
> Components: framework
> Affects Versions: Release Branch 12.04, Release Branch 13.07, Release
> Branch 14.12, Trunk
> Reporter: Jacques Le Roux
> Priority: Minor
>
> This was reported to me by Gareth Carter, the investigation is mine.
> If for instance you use this SQL expression
> {code}
> select * from Party_Role where role_Type_Id LIKE '%CA%'
> {code}
> It will be interpreted (and returned to UI) as
> {code}
> select * from Party_Role where role_Type_Id LIKE 'Ê%'
> {code}
> And no result will be returned when OOTB there is 6 <PartyRole partyId="***"
> roleTypeId="CARRIER"/> entities
> This is because in UtilHttp.canonicalizeParameterMap()
> UtilHttp.canonicalizeParameter() is called. And inside the later
> UtilCodec.canonicalize() is used. So 2 ESAPI codecs are tested
> HTMLEntityCodec.decode() and PercentCodec.decode(). Only
> PercentCodec.decode() does a change so it's picked. In this case it should
> not, because nothing should be decoded. At this point, nothing has been
> encoded, the String the codec decodes is still "select * from Party_Role
> where role_Type_Id LIKE '%CA%'"
> I read at https://en.wikipedia.org/wiki/Percent-encoding that though mostly
> planned for URL encoding percent encoding
> bq. is also used in the preparation of data of the
> application/x-www-form-urlencoded media type, as is often used in the
> submission of HTML form data in HTTP requests.
> But in the specific case of a like in an SQL expression coming from the text
> area of webtools/control/EntitySQLProcessor it should not be used because the
> % followed by some chars, may be wrongly decoded.
> Because there are no other ways provided by the percent codec to prevent the
> decoding (it's supposed to have been encoded before), I'm not quite proud of
> it but I found only this workaround so far
> {code}
> Index: framework/base/src/org/ofbiz/base/util/UtilCodec.java
> ===================================================================
> --- framework/base/src/org/ofbiz/base/util/UtilCodec.java (revision
> 1693397)
> +++ framework/base/src/org/ofbiz/base/util/UtilCodec.java (working copy)
> @@ -164,16 +164,24 @@
> while (i.hasNext()) {
> Codec codec = i.next();
> String old = working;
> - working = codec.decode(working);
> - if (!old.equals(working)) {
> - if (codecFound != null && codecFound != codec) {
> - mixedCount++;
> + String upperWorking = working.toUpperCase();
> + if (codec instanceof PercentCodec
> + && upperWorking.contains("WHERE")
> + && upperWorking.contains("LIKE")
> + && upperWorking.contains("%")) {
> + continue;
> + } else {
> + working = codec.decode(working);
> + if (!old.equals(working)) {
> + if (codecFound != null && codecFound != codec) {
> + mixedCount++;
> + }
> + codecFound = codec;
> + if (clean) {
> + foundCount++;
> + }
> + clean = false;
> }
> - codecFound = codec;
> - if (clean) {
> - foundCount++;
> - }
> - clean = false;
> }
> }
> }
> {code}
> Better ideas?
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
