You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by GitBox <gi...@apache.org> on 2020/07/04 13:47:04 UTC
[GitHub] [fineract] thesmallstar opened a new pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
thesmallstar opened a new pull request #1123:
URL: https://github.com/apache/fineract/pull/1123
Refer: https://issues.apache.org/jira/browse/FINERACT-854 and #725 #723 for background.
The work for this part is completed, but SQLbuilder currently does not support the use of "limit" and "order by" query which I will be adding before this can be merged.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] vorburger merged pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatform
Posted by GitBox <gi...@apache.org>.
vorburger merged pull request #1123:
URL: https://github.com/apache/fineract/pull/1123
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-653944268
I am removing the part to add limit and orderby to SQL builder separate from this PR, so as to review this properly, and also we can merge this quickly then and not keep it blocked by the work and testing on that part.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on a change in pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on a change in pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#discussion_r448271930
##########
File path: fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
##########
@@ -122,55 +122,26 @@ public CenterReadPlatformServiceImpl(final PlatformSecurityContext context, fina
// 'g.' preffix because of ERROR 1052 (23000): Column 'column_name' in where
// clause is ambiguous
// caused by the same name of columns in m_office and m_group tables
- private String getCenterExtraCriteria(String schemaSl, List<Object> paramList, final SearchParameters searchCriteria) {
+ private SQLBuilder getCenterExtraCriteria(String schemaSl, final SearchParameters searchCriteria) {
- StringBuilder extraCriteria = new StringBuilder(200);
- extraCriteria.append(" and g.level_id = " + GroupTypes.CENTER.getId());
+ SQLBuilder extraCriteria = new SQLBuilder();
+ extraCriteria.addCriteria("g.level_id =", GroupTypes.CENTER.getId());
if (searchCriteria != null) {
String sqlQueryCriteria = searchCriteria.getSqlSearch();
if (StringUtils.isNotBlank(sqlQueryCriteria)) {
SQLInjectionValidator.validateSQLInput(sqlQueryCriteria);
sqlQueryCriteria = sqlQueryCriteria.replace(" display_name ", " g.display_name ");
sqlQueryCriteria = sqlQueryCriteria.replace("display_name ", "g.display_name ");
- extraCriteria.append(" and (").append(sqlQueryCriteria).append(") ");
- this.columnValidator.validateSqlInjection(schemaSl, sqlQueryCriteria);
Review comment:
@vorburger WDYT on this?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-653768457
failed due to connection error. Closing and opening this PR to retest.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatform
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-680795495
@vorburger review/merge this?
Removed SQLsearch
Fixed small error in groups
SQL concatenation remove ;)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] vorburger commented on a change in pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatform
Posted by GitBox <gi...@apache.org>.
vorburger commented on a change in pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#discussion_r459150693
##########
File path: fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
##########
@@ -122,55 +122,26 @@ public CenterReadPlatformServiceImpl(final PlatformSecurityContext context, fina
// 'g.' preffix because of ERROR 1052 (23000): Column 'column_name' in where
// clause is ambiguous
// caused by the same name of columns in m_office and m_group tables
- private String getCenterExtraCriteria(String schemaSl, List<Object> paramList, final SearchParameters searchCriteria) {
+ private SQLBuilder getCenterExtraCriteria(String schemaSl, final SearchParameters searchCriteria) {
- StringBuilder extraCriteria = new StringBuilder(200);
- extraCriteria.append(" and g.level_id = " + GroupTypes.CENTER.getId());
+ SQLBuilder extraCriteria = new SQLBuilder();
+ extraCriteria.addCriteria("g.level_id =", GroupTypes.CENTER.getId());
if (searchCriteria != null) {
String sqlQueryCriteria = searchCriteria.getSqlSearch();
if (StringUtils.isNotBlank(sqlQueryCriteria)) {
SQLInjectionValidator.validateSQLInput(sqlQueryCriteria);
sqlQueryCriteria = sqlQueryCriteria.replace(" display_name ", " g.display_name ");
sqlQueryCriteria = sqlQueryCriteria.replace("display_name ", "g.display_name ");
- extraCriteria.append(" and (").append(sqlQueryCriteria).append(") ");
- this.columnValidator.validateSqlInjection(schemaSl, sqlQueryCriteria);
Review comment:
We can't (shouldn't) merge this PR as is, because it will break support for `sqlSearch`. We need to EITHER support it here, OR (my preference) should just cleanly remove it all together - as (now) suggested in https://issues.apache.org/jira/browse/FINERACT-1095.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] xurror commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatform
Posted by GitBox <gi...@apache.org>.
xurror commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-678600955
@thesmallstar any updates on this?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-652592507
@vorburger @awasum if you like this approach Before we merge this I will add the tests for the same, if not do you have any suggestions?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] vorburger commented on a change in pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatform
Posted by GitBox <gi...@apache.org>.
vorburger commented on a change in pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#discussion_r459103023
##########
File path: fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
##########
@@ -122,55 +122,26 @@ public CenterReadPlatformServiceImpl(final PlatformSecurityContext context, fina
// 'g.' preffix because of ERROR 1052 (23000): Column 'column_name' in where
// clause is ambiguous
// caused by the same name of columns in m_office and m_group tables
- private String getCenterExtraCriteria(String schemaSl, List<Object> paramList, final SearchParameters searchCriteria) {
+ private SQLBuilder getCenterExtraCriteria(String schemaSl, final SearchParameters searchCriteria) {
- StringBuilder extraCriteria = new StringBuilder(200);
- extraCriteria.append(" and g.level_id = " + GroupTypes.CENTER.getId());
+ SQLBuilder extraCriteria = new SQLBuilder();
+ extraCriteria.addCriteria("g.level_id =", GroupTypes.CENTER.getId());
if (searchCriteria != null) {
String sqlQueryCriteria = searchCriteria.getSqlSearch();
if (StringUtils.isNotBlank(sqlQueryCriteria)) {
SQLInjectionValidator.validateSQLInput(sqlQueryCriteria);
sqlQueryCriteria = sqlQueryCriteria.replace(" display_name ", " g.display_name ");
sqlQueryCriteria = sqlQueryCriteria.replace("display_name ", "g.display_name ");
- extraCriteria.append(" and (").append(sqlQueryCriteria).append(") ");
- this.columnValidator.validateSqlInjection(schemaSl, sqlQueryCriteria);
Review comment:
@thesmallstar sorry for the huge delay in getting back to you on this one. I've re-read it again now (took me a minute to get back into it). Are you suggesting that we merge this now? Because, unless I misunderstand, this would break the currently existing functionality for these sqlQueryCriteria, agreed? But they really are a problem, huh? I need to dig more into the code to understand where this is coming from..
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] vorburger edited a comment on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
vorburger edited a comment on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-652310594
```
CenterIntegrationTest > testListCenters() FAILED
java.lang.AssertionError: 1 expectation failed.
Expected status code <200> but was <500>.```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatform
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-680281366
This build will fail now, will pass only after rebase from #1171
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar removed a comment on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar removed a comment on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-653766434
/rebase
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-651113587
Blocked by: https://issues.apache.org/jira/browse/FINERACT-1058
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] vorburger commented on a change in pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
vorburger commented on a change in pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#discussion_r449616514
##########
File path: fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
##########
@@ -122,55 +122,26 @@ public CenterReadPlatformServiceImpl(final PlatformSecurityContext context, fina
// 'g.' preffix because of ERROR 1052 (23000): Column 'column_name' in where
// clause is ambiguous
// caused by the same name of columns in m_office and m_group tables
- private String getCenterExtraCriteria(String schemaSl, List<Object> paramList, final SearchParameters searchCriteria) {
+ private SQLBuilder getCenterExtraCriteria(String schemaSl, final SearchParameters searchCriteria) {
- StringBuilder extraCriteria = new StringBuilder(200);
- extraCriteria.append(" and g.level_id = " + GroupTypes.CENTER.getId());
+ SQLBuilder extraCriteria = new SQLBuilder();
+ extraCriteria.addCriteria("g.level_id =", GroupTypes.CENTER.getId());
if (searchCriteria != null) {
String sqlQueryCriteria = searchCriteria.getSqlSearch();
if (StringUtils.isNotBlank(sqlQueryCriteria)) {
SQLInjectionValidator.validateSQLInput(sqlQueryCriteria);
sqlQueryCriteria = sqlQueryCriteria.replace(" display_name ", " g.display_name ");
sqlQueryCriteria = sqlQueryCriteria.replace("display_name ", "g.display_name ");
- extraCriteria.append(" and (").append(sqlQueryCriteria).append(") ");
- this.columnValidator.validateSqlInjection(schemaSl, sqlQueryCriteria);
Review comment:
@thesmallstar I'm not sure I fully understand what you mean here, but starting to think about adding parsing things sounds like the wrong direction - try not to have to do that (it will be a mess - trust me).
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-653762592
> 1. I don't know if this actually works (PreparedStatement), so will have to trust you that you test that it really does.
-> Yes it works! I have tested it :)
> 2. The current implementation seems to assume that `setLimit()` & Co. would only ever be called after `addCriteria()` - right? That's... not a good idea. Imagine some dumb developer coming along in a few months, not knowing how you implemented this internally (and correctly so, they should not have to). So they do `new SQLBuilder().addOrderBy().addCriteria(...).getSQLTemplate()` - and that would create invalid SQL - agreed?
->Agreed I am making the change.
> 3. `SQLBuidlerTest` must be extended to cover these new methods.
->Extending, I was waiting for the approach to be marked correct :)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] vorburger commented on a change in pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
vorburger commented on a change in pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#discussion_r449616181
##########
File path: fineract-provider/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLBuilder.java
##########
@@ -110,6 +111,39 @@ public void addNonNullCriteria(String criteria, Object argument) {
}
}
+ /**
+ * Adds the limit statement in the end of WHERE clause
+ *
+ * @param limit
+ * The value that will be used as limit
+ */
+ public void setLimit(Object limit) {
+ sb.append(" LIMIT ? ");
+ args.add(limit);
+ }
+
+ /**
+ * Adds the offset statement in the end of WHERE clause
+ *
+ * @param offset
+ * The value that will be used as offset
+ */
+ public void setOffset(Object offset) {
+ sb.append(" OFFSET ? ");
+ args.add(offset);
+ }
+
+ /**
+ * Adds the orderBy statement in the end of WHERE clause
+ *
+ * @param orderBy
+ * The value that will be used as orderBy
+ */
+ public void addOrderBY(Object orderBy) {
Review comment:
lower-case _y_:
```suggestion
public void addOrderBy(Object orderBy) {
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on a change in pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on a change in pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#discussion_r446882127
##########
File path: fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
##########
@@ -122,55 +122,26 @@ public CenterReadPlatformServiceImpl(final PlatformSecurityContext context, fina
// 'g.' preffix because of ERROR 1052 (23000): Column 'column_name' in where
// clause is ambiguous
// caused by the same name of columns in m_office and m_group tables
- private String getCenterExtraCriteria(String schemaSl, List<Object> paramList, final SearchParameters searchCriteria) {
+ private SQLBuilder getCenterExtraCriteria(String schemaSl, final SearchParameters searchCriteria) {
- StringBuilder extraCriteria = new StringBuilder(200);
- extraCriteria.append(" and g.level_id = " + GroupTypes.CENTER.getId());
+ SQLBuilder extraCriteria = new SQLBuilder();
+ extraCriteria.addCriteria("g.level_id =", GroupTypes.CENTER.getId());
if (searchCriteria != null) {
String sqlQueryCriteria = searchCriteria.getSqlSearch();
if (StringUtils.isNotBlank(sqlQueryCriteria)) {
SQLInjectionValidator.validateSQLInput(sqlQueryCriteria);
sqlQueryCriteria = sqlQueryCriteria.replace(" display_name ", " g.display_name ");
sqlQueryCriteria = sqlQueryCriteria.replace("display_name ", "g.display_name ");
- extraCriteria.append(" and (").append(sqlQueryCriteria).append(") ");
- this.columnValidator.validateSqlInjection(schemaSl, sqlQueryCriteria);
Review comment:
Also removing this part is not correct as of NOW, the problem is we are talking SQL query as a query to give users more functionality and certainly that is not supported in SQL builder currently, we probably would need to parse things find relevant queries and then add those to extra criteria still thinking on it.
Will be looked after limit and order by are supported.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-653766434
/rebase
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] github-actions[bot] commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatform
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-678566572
This pull request seems to be stale. Are you still planning to work on it? We will automatically close it in 30 days.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar commented on pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar commented on pull request #1123:
URL: https://github.com/apache/fineract/pull/1123#issuecomment-652573448
Failed due to https://issues.apache.org/jira/browse/FINERACT-1016
Looking into it.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [fineract] thesmallstar closed pull request #1123: FINERACT-854 Removed string concatenated SQL from CenterReadPlatformS…
Posted by GitBox <gi...@apache.org>.
thesmallstar closed pull request #1123:
URL: https://github.com/apache/fineract/pull/1123
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org