This is an automated email from the ASF dual-hosted git repository.
arnold pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/fineract.git
The following commit(s) were added to refs/heads/develop by this push:
new ad2f2862c FINERACT-1747 add datatable query api
ad2f2862c is described below
commit ad2f2862cb08c85021d96e90becbcd47eb511957
Author: Zoltan Nebli <[email protected]>
AuthorDate: Wed Oct 12 23:46:19 2022 +0200
FINERACT-1747 add datatable query api
---
.gitignore | 7 +-
.../dataqueries/api/DatatablesApiResource.java | 20 ++
.../data/ResultsetColumnHeaderData.java | 6 +-
.../service/ReadWriteNonCoreDataService.java | 3 +
.../service/ReadWriteNonCoreDataServiceImpl.java | 189 +++++++++++++++++-
.../security/utils/SQLInjectionValidator.java | 104 ++++------
.../ReadWriteNonCoreDataServiceImplTest.java | 102 ++++++++++
.../security/utils/SQLInjectionValidatorTest.java | 147 ++++++++++++++
.../integrationtests/DatatableIntegrationTest.java | 218 +++++++++++++--------
9 files changed, 639 insertions(+), 157 deletions(-)
diff --git a/.gitignore b/.gitignore
index 991d4b73a..b63dd898e 100644
--- a/.gitignore
+++ b/.gitignore
@@ -24,4 +24,9 @@ fineract-provider/config/swagger/fineract-input.yaml
licenses
**/.asciidoctor/images/*
**/.asciidoctor/diagram/*
-**/images/diag-*.svg
\ No newline at end of file
+**/images/diag-*.svg
+fineract-avro-schemas/out/
+fineract-client/out/
+fineract-provider/src/main/generated/
+integration-tests/out/
+module/dummy/starter/out/
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResource.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResource.java
index a3b37dd27..dc5bdb14c 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResource.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResource.java
@@ -18,6 +18,7 @@
*/
package org.apache.fineract.infrastructure.dataqueries.api;
+import com.google.gson.JsonObject;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.enums.ParameterIn;
@@ -218,6 +219,25 @@ public class DatatablesApiResource {
return this.toApiJsonSerializer.serializePretty(prettyPrint, result);
}
+ @GET
+ @Path("{datatable}/query")
+ @Produces({ MediaType.APPLICATION_JSON })
+ @Operation(summary = "Query Data Table values", description = "Query
values from a registered data table.")
+ @ApiResponses({
+ @ApiResponse(responseCode = "200", description = "OK", content =
@Content(schema = @Schema(implementation = List.class))) })
+ public String queryValues(@PathParam("datatable") @Parameter(description =
"datatable") final String datatable,
+ @QueryParam("columnFilter") @Parameter(description =
"columnFilter") final String columnFilter,
+ @QueryParam("valueFilter") @Parameter(description = "valueFilter")
final String valueFilter,
+ @QueryParam("resultColumns") @Parameter(description =
"resultColumns") final String resultColumns,
+ @Context final UriInfo uriInfo) {
+
this.context.authenticatedUser().validateHasDatatableReadPermission(datatable);
+
+ final List<JsonObject> result =
this.readWriteNonCoreDataService.queryDataTable(datatable, columnFilter,
valueFilter,
+ resultColumns);
+
+ return
this.toApiJsonSerializer.serializePretty(ApiParameterHelper.prettyPrint(uriInfo.getQueryParameters()),
result);
+ }
+
@GET
@Path("{datatable}/{apptableId}")
@Consumes({ MediaType.APPLICATION_JSON })
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
index 7ae84bf38..5d6c6ea9d 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/ResultsetColumnHeaderData.java
@@ -87,7 +87,7 @@ public final class ResultsetColumnHeaderData implements
Serializable {
displayType = "DECIMAL";
} else if (isAnyText()) {
displayType = "TEXT";
- } else if (isBit()) {
+ } else if (isBit() || isBoolean()) {
displayType = "BOOLEAN";
} else {
throw new
PlatformDataIntegrityException("error.msg.invalid.lookup.type",
@@ -231,6 +231,10 @@ public final class ResultsetColumnHeaderData implements
Serializable {
return "bit".equalsIgnoreCase(this.columnType);
}
+ private boolean isBoolean() {
+ return "boolean".equalsIgnoreCase(this.columnType) ||
"bool".equalsIgnoreCase(this.columnType);
+ }
+
public String getColumnName() {
return this.columnName;
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataService.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataService.java
index 079982bd6..e95061c30 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataService.java
@@ -18,6 +18,7 @@
*/
package org.apache.fineract.infrastructure.dataqueries.service;
+import com.google.gson.JsonObject;
import java.util.List;
import org.apache.fineract.infrastructure.core.api.JsonCommand;
import org.apache.fineract.infrastructure.core.data.CommandProcessingResult;
@@ -31,6 +32,8 @@ public interface ReadWriteNonCoreDataService {
DatatableData retrieveDatatable(String datatable);
+ List<JsonObject> queryDataTable(String datatable, String columnFilter,
String valueFilter, String resultColumns);
+
@PreAuthorize(value = "hasAnyAuthority('ALL_FUNCTIONS',
'REGISTER_DATATABLE')")
void registerDatatable(JsonCommand command);
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
index fe60764ba..97cf62aea 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
@@ -18,6 +18,9 @@
*/
package org.apache.fineract.infrastructure.dataqueries.service;
+import static java.util.Arrays.asList;
+import static
org.apache.fineract.infrastructure.core.data.ApiParameterError.parameterErrorWithValue;
+
import com.google.common.base.Splitter;
import com.google.common.collect.ImmutableMap;
import com.google.gson.JsonArray;
@@ -30,12 +33,18 @@ import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
+import java.sql.Types;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.format.DateTimeFormatter;
+import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
+import java.util.Objects;
import java.util.Set;
import java.util.stream.Stream;
import javax.persistence.PersistenceException;
@@ -74,6 +83,7 @@ import
org.apache.fineract.infrastructure.security.service.SqlInjectionPreventer
import org.apache.fineract.infrastructure.security.utils.ColumnValidator;
import org.apache.fineract.infrastructure.security.utils.SQLInjectionValidator;
import org.apache.fineract.useradministration.domain.AppUser;
+import org.jetbrains.annotations.NotNull;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
@@ -104,11 +114,24 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
.put("number", "INT").put("boolean", "BIT").put("decimal",
"DECIMAL").put("date", "DATE").put("datetime", "DATETIME")
.put("text", "TEXT").put("dropdown", "INT").build();
private static final ImmutableMap<String, String> apiTypeToPostgreSQL =
ImmutableMap.<String, String>builder().put("string", "VARCHAR")
- .put("number", "INT").put("boolean", "BIT").put("decimal",
"DECIMAL").put("date", "DATE").put("datetime", "TIMESTAMP")
+ .put("number", "INT").put("boolean", "boolean").put("decimal",
"DECIMAL").put("date", "DATE").put("datetime", "TIMESTAMP")
.put("text", "TEXT").put("dropdown", "INT").build();
private static final List<String> stringDataTypes = Arrays.asList("char",
"varchar", "blob", "text", "tinyblob", "tinytext",
"mediumblob", "mediumtext", "longblob", "longtext");
+ private static final DateTimeFormatter DATA_TABLE_DATE_FORMAT =
DateTimeFormatter.ofPattern("yyyy-MM-dd");
+ private static final DateTimeFormatter DATA_TABLE_DATETIME_FORMAT =
DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
+ public static final String PG_BOOLEAN_TYPE = "boolean";
+ public static final String PG_BOOL_TYPE = "bool";
+ public static final String PG_INT_TYPE = "integer";
+ public static final String PG_BIGINT_TYPE = "bigint";
+ public static final String PG_DATE_TYPE = "date";
+ public static final String PG_NUMERIC_TYPE = "numeric";
+ public static final String PG_TEXT_TYPE = "text";
+ public static final String PG_VARCHAR_TYPE = "character varying";
+ public static final String BIT_TYPE = "bit";
+ public static final String MYSQL_DATETIME_TYPE = "datetime";
+ public static final String MYSQL_DATE_TYPE = "date";
private final JdbcTemplate jdbcTemplate;
private final DatabaseTypeResolver databaseTypeResolver;
@@ -160,9 +183,9 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
// PERMITTED datatables
SQLInjectionValidator.validateSQLInput(datatable);
- final String sql = "select application_table_name,
registered_table_name, entity_subtype" + " from x_registered_table "
- + " where exists" + " (select 'f'" + " from m_appuser_role ur
" + " join m_role r on r.id = ur.role_id"
- + " left join m_role_permission rp on rp.role_id = r.id" + "
left join m_permission p on p.id = rp.permission_id"
+ final String sql = "select application_table_name,
registered_table_name, entity_subtype from x_registered_table "
+ + " where exists (select 'f' from m_appuser_role ur join
m_role r on r.id = ur.role_id"
+ + " left join m_role_permission rp on rp.role_id = r.id left
join m_permission p on p.id = rp.permission_id"
+ " where ur.appuser_id = ? and registered_table_name=? and
(p.code in ('ALL_FUNCTIONS', "
+ "'ALL_FUNCTIONS_READ') or p.code = concat('READ_',
registered_table_name))) "
+ " order by application_table_name, registered_table_name";
@@ -183,6 +206,164 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
return datatableData;
}
+ @Override
+ public List<JsonObject> queryDataTable(String datatable, String
columnFilter, String valueFilter, String resultColumns) {
+ Arrays.asList(datatable, columnFilter, valueFilter,
resultColumns).forEach(SQLInjectionValidator::validateDynamicQuery);
+
+ List<ResultsetColumnHeaderData> resultsetColumnHeaderData =
genericDataService.fillResultsetColumnHeaders(datatable);
+ validateRequestParams(columnFilter, valueFilter, resultColumns,
resultsetColumnHeaderData);
+
+ String sql = "select " + resultColumns + " from " + datatable + "
where " + columnFilter + " = ?";
+ SqlRowSet rowSet = null;
+ String filterColumnType =
resultsetColumnHeaderData.stream().filter(column ->
Objects.equals(columnFilter, column.getColumnName()))
+
.findFirst().map(ResultsetColumnHeaderData::getColumnType).orElse(columnFilter
+ " does not exist in datatable");
+ if (databaseTypeResolver.isPostgreSQL()) {
+ rowSet = callFilteredPgSql(sql, valueFilter, filterColumnType);
+ } else if (databaseTypeResolver.isMySQL()) {
+ rowSet = callFilteredMysql(sql, valueFilter, filterColumnType);
+ }
+
+ String[] resultColumnNames = resultColumns.split(",");
+ List<JsonObject> results = new ArrayList<>();
+ while (rowSet.next()) {
+ extractResults(rowSet, resultColumnNames, results);
+ }
+
+ return results;
+ }
+
+ private void extractResults(SqlRowSet rowSet, String[] resultColumnNames,
List<JsonObject> results) {
+ JsonObject json = new JsonObject();
+ for (String rcn : resultColumnNames) {
+ Object rowValue = rowSet.getObject(rcn);
+ if (rowValue != null) {
+ if (rowValue instanceof Character) {
+ json.addProperty(rcn, (Character) rowValue);
+ } else if (rowValue instanceof Number) {
+ json.addProperty(rcn, new BigDecimal(rowValue.toString()));
+ } else if (rowValue instanceof Boolean) {
+ json.addProperty(rcn, (Boolean) rowValue);
+ } else if (rowValue instanceof LocalDateTime) {
+ json.addProperty(rcn,
DATA_TABLE_DATETIME_FORMAT.format((LocalDateTime) rowValue));
+ } else if (rowValue instanceof Timestamp) {
+ json.addProperty(rcn,
DATA_TABLE_DATETIME_FORMAT.format(((Timestamp) rowValue).toLocalDateTime()));
+ } else if (rowValue instanceof LocalDate) {
+ json.addProperty(rcn,
DATA_TABLE_DATE_FORMAT.format((LocalDate) rowValue));
+ } else if (rowValue instanceof Date) {
+ json.addProperty(rcn,
DATA_TABLE_DATE_FORMAT.format(((Date) rowValue).toLocalDate()));
+ } else {
+ json.addProperty(rcn, rowValue.toString());
+ }
+ }
+ }
+
+ if (json.size() > 0) {
+ results.add(json);
+ }
+ }
+
+ @NotNull
+ private SqlRowSet callFilteredMysql(String sql, String valueFilter, String
filterColumnType) {
+ Object finalValueFilter = valueFilter;
+ SqlRowSet rowSet;
+ if (BIT_TYPE.equalsIgnoreCase(filterColumnType)) {
+ int[] argType = new int[1];
+ argType[0] = Types.BIT;
+ finalValueFilter =
BooleanUtils.toString(BooleanUtils.toBooleanObject(valueFilter), "1", "0",
"null");
+ rowSet = jdbcTemplate.queryForRowSet(sql, new Object[] {
finalValueFilter }, argType);
+ } else if (MYSQL_DATE_TYPE.equalsIgnoreCase(filterColumnType)) {
+ int[] argType = new int[1];
+ argType[0] = Types.DATE;
+ try {
+ rowSet = jdbcTemplate.queryForRowSet(sql, new Object[] {
LocalDate.parse(valueFilter, DATA_TABLE_DATE_FORMAT) }, argType);
+ } catch (DateTimeParseException e) {
+ List<ApiParameterError> paramErrors = new ArrayList<>();
+ paramErrors.add(parameterErrorWithValue("400",
+ "Unsupported input type for datatable query! Use
format: 'yyyy-MM-dd'. Column filter: " + filterColumnType,
+ "valueFilter", valueFilter));
+ throw new PlatformApiDataValidationException(paramErrors, e);
+ }
+ } else if (MYSQL_DATETIME_TYPE.equals(filterColumnType)) {
+ int[] argType = new int[1];
+ argType[0] = Types.TIMESTAMP;
+ try {
+ rowSet = jdbcTemplate.queryForRowSet(sql, new Object[] {
LocalDateTime.parse(valueFilter, DATA_TABLE_DATETIME_FORMAT) },
+ argType);
+ } catch (DateTimeParseException e) {
+ List<ApiParameterError> paramErrors = new ArrayList<>();
+ paramErrors.add(parameterErrorWithValue("400",
+ "Unsupported input type for datatable query! Use
format: 'yyyy-MM-dd HH:mm:ss'. Column filter: " + filterColumnType,
+ "valueFilter", valueFilter));
+ throw new PlatformApiDataValidationException(paramErrors, e);
+ }
+ } else {
+ rowSet = jdbcTemplate.queryForRowSet(sql, finalValueFilter);
+ }
+ return rowSet;
+ }
+
+ @NotNull
+ private SqlRowSet callFilteredPgSql(String sql, String valueFilter, String
filterColumnType) {
+ Object finalValueFilter = valueFilter;
+ int[] argType = new int[1];
+ if (BIT_TYPE.equalsIgnoreCase(filterColumnType)) {
+ finalValueFilter =
BooleanUtils.toString(BooleanUtils.toBooleanObject(valueFilter), "1", "0",
"null");
+ argType[0] = Types.BIT;
+ } else if (PG_BOOLEAN_TYPE.equalsIgnoreCase(filterColumnType) ||
PG_BOOL_TYPE.equalsIgnoreCase(filterColumnType)) {
+ finalValueFilter =
BooleanUtils.toString(BooleanUtils.toBooleanObject(valueFilter), "true",
"false", "null");
+ argType[0] = Types.BOOLEAN;
+ } else if (PG_INT_TYPE.equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.INTEGER;
+ } else if (PG_BIGINT_TYPE.equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.BIGINT;
+ } else if (PG_DATE_TYPE.equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.DATE;
+ } else if (filterColumnType.toLowerCase().contains("timestamp")) {
+ argType[0] = Types.TIMESTAMP;
+ } else if (PG_NUMERIC_TYPE.equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.DECIMAL;
+ } else if (PG_TEXT_TYPE.equalsIgnoreCase(filterColumnType) ||
PG_VARCHAR_TYPE.equalsIgnoreCase(filterColumnType)) {
+ argType[0] = Types.VARCHAR;
+ } else {
+ List<ApiParameterError> paramErrors = new ArrayList<>();
+ paramErrors.add(parameterErrorWithValue("400", "Unsupported input
type for datatable query! Column filter: " + filterColumnType,
+ "valueFilter", valueFilter));
+ throw new PlatformApiDataValidationException(paramErrors);
+ }
+ return jdbcTemplate.queryForRowSet(sql, new Object[] {
finalValueFilter }, argType);
+ }
+
+ private static void validateRequestParams(String columnFilter, String
valueFilter, String resultColumns,
+ List<ResultsetColumnHeaderData> resultsetColumnHeaderData) {
+ List<ApiParameterError> paramErrors = new ArrayList<>();
+ List<String> dataTableColumnNames =
resultsetColumnHeaderData.stream().map(ResultsetColumnHeaderData::getColumnName).toList();
+ if (columnFilter == null || columnFilter.isEmpty()) {
+ paramErrors.add(parameterErrorWithValue("400", "Column filter is
empty!", "columnFilter", columnFilter));
+ } else {
+ if (!dataTableColumnNames.contains(columnFilter)) {
+ paramErrors.add(parameterErrorWithValue("400", "Column filter
not exist in datatable!", "columnFilter", columnFilter));
+ }
+ }
+
+ if (valueFilter == null || valueFilter.isEmpty()) {
+ paramErrors.add(parameterErrorWithValue("400", "Value filter is
empty!", "valueFilter", valueFilter));
+ }
+
+ if (resultColumns == null || resultColumns.isEmpty()) {
+ paramErrors.add(parameterErrorWithValue("400", "Result columns
filter is empty!", "resultColumns", resultColumns));
+ } else {
+ asList(resultColumns.split(",")).forEach(rcn -> {
+ if (!dataTableColumnNames.contains(rcn)) {
+ paramErrors.add(parameterErrorWithValue("400", "Result
column not exist in datatable!", "resultColumns", rcn));
+ }
+ });
+ }
+
+ if (!paramErrors.isEmpty()) {
+ throw new PlatformApiDataValidationException(paramErrors);
+ }
+ }
+
private void logAsErrorUnexpectedDataIntegrityException(final Exception
dve) {
LOG.error("Error occurred.", dve);
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidator.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidator.java
index a8128a485..a89c9f16a 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidator.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidator.java
@@ -35,7 +35,7 @@ public final class SQLInjectionValidator {
private static final String[] COMMENTS = { "--", "({", "/*", "#" };
- private static final String SQL_PATTERN = "[a-zA-Z_=,\\-'!><.?\"`%
()0-9*\n\r]*";
+ private static final String SQL_PATTERN = "[a-zA-Z_=,\\-:'!><.?\"`%
()0-9*\n\r]*";
public static void validateSQLInput(final String sqlSearch) {
if (StringUtils.isBlank(sqlSearch)) {
@@ -60,99 +60,72 @@ public final class SQLInjectionValidator {
}
}
- // Removing the space before and after '=' operator
- // String s = " \" OR 1 = 1"; For the cases like this
- boolean injectionFound = false;
- String inputSqlString = lowerCaseSQL;
- while (inputSqlString.indexOf(" =") > 0) { // Don't remove space before
- // = operator
- inputSqlString = inputSqlString.replaceAll(" =", "=");
- }
+ patternMatchSqlInjection(sqlSearch, lowerCaseSQL);
+ }
- while (inputSqlString.indexOf("= ") > 0) { // Don't remove space after
=
- // operator
- inputSqlString = inputSqlString.replaceAll("= ", "=");
+ public static void validateAdhocQuery(final String sqlSearch) {
+ if (StringUtils.isBlank(sqlSearch)) {
+ return;
}
-
- StringTokenizer tokenizer = new StringTokenizer(inputSqlString, " ");
- while (tokenizer.hasMoreTokens()) {
- String token = tokenizer.nextToken().trim();
- if (token.equals("'")) {
- if (tokenizer.hasMoreElements()) {
- String nextToken = tokenizer.nextToken().trim();
- if (!nextToken.equals("'")) {
- injectionFound = true;
- break;
- }
- } else {
- injectionFound = true;
- break;
- }
- }
- if (token.equals("\"")) {
- if (tokenizer.hasMoreElements()) {
- String nextToken = tokenizer.nextToken().trim();
- if (!nextToken.equals("\"")) {
- injectionFound = true;
- break;
- }
- } else {
- injectionFound = true;
- break;
- }
- } else if (token.indexOf('=') > 0) {
- StringTokenizer operatorToken = new StringTokenizer(token,
"=");
- String operand = operatorToken.nextToken().trim();
- if (!operatorToken.hasMoreTokens()) {
- injectionFound = true;
- break;
- }
- String value = operatorToken.nextToken().trim();
- if (operand.equals(value)) {
- injectionFound = true;
- break;
- }
+ String lowerCaseSQL = sqlSearch.toLowerCase().trim();
+ for (String ddl : DDL_COMMANDS) {
+ if (lowerCaseSQL.startsWith(ddl)) {
+ throw new SQLInjectionException();
}
}
- if (injectionFound) {
- throw new SQLInjectionException();
- }
- Pattern pattern = Pattern.compile(SQL_PATTERN);
- Matcher matcher = pattern.matcher(sqlSearch);
- if (!matcher.matches()) {
- throw new SQLInjectionException();
+ for (String comments : COMMENTS) {
+ if (lowerCaseSQL.contains(comments)) {
+ throw new SQLInjectionException();
+ }
}
+
+ // Removing the space before and after '=' operator
+ // String s = " \" OR 1 = 1"; For the cases like this
+ patternMatchSqlInjection(sqlSearch, lowerCaseSQL);
}
- public static void validateAdhocQuery(final String sqlSearch) {
+ public static void validateDynamicQuery(final String sqlSearch) {
if (StringUtils.isBlank(sqlSearch)) {
return;
}
- String lowerCaseSQL = sqlSearch.toLowerCase().trim();
+
+ String lowerCaseSQL = sqlSearch.toLowerCase();
for (String ddl : DDL_COMMANDS) {
- if (lowerCaseSQL.startsWith(ddl)) {
+ if (ddl.equals(lowerCaseSQL)) {
throw new SQLInjectionException();
}
}
- for (String comments : COMMENTS) {
- if (lowerCaseSQL.contains(comments)) {
+ for (String dml : DML_COMMANDS) {
+ if (dml.equals(lowerCaseSQL)) {
+ throw new SQLInjectionException();
+ }
+ }
+
+ for (String comment : COMMENTS) {
+ if (comment.equals(lowerCaseSQL)) {
throw new SQLInjectionException();
}
}
+ // Removing the space before and after '=' operator
+ // String s = " \" OR 1 = 1"; For the cases like this
+ patternMatchSqlInjection(sqlSearch, lowerCaseSQL);
+ }
+
+ private static void patternMatchSqlInjection(String sqlSearch, String
lowerCaseSQL) {
// Removing the space before and after '=' operator
// String s = " \" OR 1 = 1"; For the cases like this
boolean injectionFound = false;
String inputSqlString = lowerCaseSQL;
while (inputSqlString.indexOf(" =") > 0) { // Don't remove space before
- // = operator
+ // = operator
inputSqlString = inputSqlString.replaceAll(" =", "=");
}
while (inputSqlString.indexOf("= ") > 0) { // Don't remove space after
=
- // operator
+ // operator
inputSqlString = inputSqlString.replaceAll("= ", "=");
}
@@ -206,4 +179,5 @@ public final class SQLInjectionValidator {
throw new SQLInjectionException();
}
}
+
}
diff --git
a/fineract-provider/src/test/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImplTest.java
b/fineract-provider/src/test/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImplTest.java
new file mode 100644
index 000000000..caa3ab3b4
--- /dev/null
+++
b/fineract-provider/src/test/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImplTest.java
@@ -0,0 +1,102 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.dataqueries.service;
+
+import static org.junit.jupiter.api.Assertions.assertThrows;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.eq;
+import static org.mockito.Mockito.when;
+
+import com.google.gson.JsonObject;
+import java.util.Collections;
+import java.util.List;
+import
org.apache.fineract.infrastructure.core.exception.PlatformApiDataValidationException;
+import
org.apache.fineract.infrastructure.core.service.database.DatabaseTypeResolver;
+import
org.apache.fineract.infrastructure.dataqueries.data.ResultsetColumnHeaderData;
+import org.apache.fineract.infrastructure.security.utils.SQLInjectionException;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.mockito.ArgumentMatchers;
+import org.mockito.InjectMocks;
+import org.mockito.Mock;
+import org.mockito.Mockito;
+import org.mockito.MockitoAnnotations;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.support.rowset.SqlRowSet;
+
+public class ReadWriteNonCoreDataServiceImplTest {
+
+ @Mock
+ private JdbcTemplate jdbcTemplate;
+
+ @Mock
+ private GenericDataService genericDataService;
+
+ @Mock
+ private DatabaseTypeResolver databaseTypeResolver;
+
+ @InjectMocks
+ private ReadWriteNonCoreDataServiceImpl underTest;
+
+ @BeforeEach
+ public void setup() {
+ MockitoAnnotations.openMocks(this);
+ }
+
+ @Test
+ public void testSqlInjectionCaughtQueryDataTable() {
+ assertThrows(SQLInjectionException.class, () -> {
+ underTest.queryDataTable("table", "cf1", "vf1", "' or 1=1");
+ });
+ }
+
+ @Test
+ public void testSqlInjectionCaughtQueryDataTable2() {
+ assertThrows(SQLInjectionException.class, () -> {
+ underTest.queryDataTable("table", "cf1", "vf1", "1; DROP TABLE
m_loan; SELECT");
+ });
+ }
+
+ @Test
+ public void testQueryDataTableSuccess() {
+ SqlRowSet sqlRS = Mockito.mock(SqlRowSet.class);
+ when(jdbcTemplate.queryForRowSet(eq("select rc1,rc2 from table where
cf1 = ?"), any(Object[].class), any(int[].class)))
+ .thenReturn(sqlRS);
+ when(sqlRS.next()).thenReturn(true).thenReturn(false);
+
when(sqlRS.getObject(ArgumentMatchers.anyString())).thenReturn("value1").thenReturn("value2");
+ when(databaseTypeResolver.isPostgreSQL()).thenReturn(true);
+
+ ResultsetColumnHeaderData cf1 =
ResultsetColumnHeaderData.detailed("cf1", "text", 10L, false, false, null,
null);
+ ResultsetColumnHeaderData rc1 =
ResultsetColumnHeaderData.detailed("rc1", "text", 10L, false, false, null,
null);
+ ResultsetColumnHeaderData rc2 =
ResultsetColumnHeaderData.detailed("rc2", "text", 10L, false, false, null,
null);
+
when(genericDataService.fillResultsetColumnHeaders("table")).thenReturn(List.of(cf1,
rc1, rc2));
+
+ List<JsonObject> results = underTest.queryDataTable("table", "cf1",
"vf1", "rc1,rc2");
+
+ Assertions.assertEquals("value1",
results.get(0).get("rc1").getAsString());
+ Assertions.assertEquals("value2",
results.get(0).get("rc2").getAsString());
+ }
+
+ @Test
+ public void testQueryDataTableValidationError() {
+
when(genericDataService.fillResultsetColumnHeaders("table")).thenReturn(Collections.emptyList());
+ assertThrows(PlatformApiDataValidationException.class, () ->
underTest.queryDataTable("table", "cf1", "vf1", "rc1,rc2"));
+ }
+}
diff --git
a/fineract-provider/src/test/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidatorTest.java
b/fineract-provider/src/test/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidatorTest.java
new file mode 100644
index 000000000..a76e2ee07
--- /dev/null
+++
b/fineract-provider/src/test/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidatorTest.java
@@ -0,0 +1,147 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.security.utils;
+
+import java.util.Arrays;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+public class SQLInjectionValidatorTest {
+
+ private static final String[] DDL_COMMANDS = { "create", "drop", "alter",
"truncate", "comment", "sleep" };
+ private static final String[] DML_COMMANDS = { "select", "insert",
"update", "delete", "merge", "upsert", "call" };
+ private static final String[] COMMENTS = { "--", "({", "/*", "#" };
+
+ @Test
+ public void testValidateSqlInputQuote() {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateSQLInput("' or 1=1");
+ });
+ }
+
+ @Test
+ public void testValidateSqlInputSemicolon() {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateSQLInput("; drop table foo;");
+ });
+ }
+
+ @Test
+ public void testValidateAdhocQueryQuote() {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateAdhocQuery("' or 1=1");
+ });
+ }
+
+ @Test
+ public void testValidateAdhocQuerySemicolon() {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateAdhocQuery("; drop table foo;");
+ });
+ }
+
+ @Test
+ public void testValidateDynamicQueryQuote() {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateDynamicQuery("' or 1=1");
+ });
+ }
+
+ @Test
+ public void testValidateDynamicQuerySemicolon() {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateDynamicQuery("; drop table foo;");
+ });
+ }
+
+ @Test
+ public void testValidateSqlLInputReservedWords() {
+ Arrays.asList(DDL_COMMANDS).forEach(ddl -> {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateSQLInput(ddl);
+ });
+ });
+
+ Arrays.asList(DML_COMMANDS).forEach(dml -> {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateSQLInput(dml);
+ });
+ });
+
+ Arrays.asList(COMMENTS).forEach(comment -> {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateSQLInput(comment);
+ });
+ });
+ }
+
+ @Test
+ public void testValidateAdhocQueryReservedWords() {
+ Arrays.asList(DDL_COMMANDS).forEach(ddl -> {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateAdhocQuery(ddl);
+ });
+ });
+
+ // left out intentionally from adhocquery validation?
+ // Arrays.asList(DML_COMMANDS).forEach(dml -> {
+ // Assertions.assertThrows(SQLInjectionException.class, () -> {
+ // SQLInjectionValidator.validateAdhocQuery(dml);
+ // });
+ // });
+
+ Arrays.asList(COMMENTS).forEach(comment -> {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateAdhocQuery(comment);
+ });
+ });
+ }
+
+ @Test
+ public void testValidateDynamicQueryReservedWords() {
+ Arrays.asList(DDL_COMMANDS).forEach(ddl -> {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateDynamicQuery(ddl);
+ });
+ });
+
+ Arrays.asList(DML_COMMANDS).forEach(dml -> {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateDynamicQuery(dml);
+ });
+ });
+
+ Arrays.asList(COMMENTS).forEach(comment -> {
+ Assertions.assertThrows(SQLInjectionException.class, () -> {
+ SQLInjectionValidator.validateDynamicQuery(comment);
+ });
+ });
+ }
+
+ @Test
+ public void testValidateDynamicQueryColon() {
+ SQLInjectionValidator.validateDynamicQuery("2022-10-13 18:40:21");
+ }
+
+ @Test
+ public void testValidateDynamicQueryReservedWordsInsideValue() {
+ Arrays.asList(DDL_COMMANDS).forEach(ddl ->
SQLInjectionValidator.validateDynamicQuery("foo" + ddl));
+ Arrays.asList(DML_COMMANDS).forEach(dml ->
SQLInjectionValidator.validateDynamicQuery("foo" + dml));
+ }
+}
diff --git
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/DatatableIntegrationTest.java
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/DatatableIntegrationTest.java
index 4019b06fb..eed138d0e 100644
---
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/DatatableIntegrationTest.java
+++
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/DatatableIntegrationTest.java
@@ -19,20 +19,30 @@
package org.apache.fineract.integrationtests;
import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
+import com.google.gson.JsonArray;
+import com.google.gson.JsonParser;
import io.restassured.builder.RequestSpecBuilder;
import io.restassured.builder.ResponseSpecBuilder;
import io.restassured.http.ContentType;
import io.restassured.specification.RequestSpecification;
import io.restassured.specification.ResponseSpecification;
+import java.math.BigDecimal;
+import java.text.DateFormat;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
import java.util.ArrayList;
+import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import org.apache.fineract.client.util.Calls;
+import org.apache.fineract.integrationtests.client.IntegrationTest;
import org.apache.fineract.integrationtests.common.ClientHelper;
import org.apache.fineract.integrationtests.common.Utils;
import
org.apache.fineract.integrationtests.common.loans.LoanApplicationTestBuilder;
@@ -45,7 +55,7 @@ import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
-public class DatatableIntegrationTest {
+public class DatatableIntegrationTest extends IntegrationTest {
private static final Logger LOG =
LoggerFactory.getLogger(DatatableIntegrationTest.class);
@@ -80,15 +90,16 @@ public class DatatableIntegrationTest {
}
@Test
- public void validateCreateReadDeleteDatatable() {
+ public void validateCreateReadDeleteDatatable() throws ParseException {
// Fetch / Create tst code
- HashMap<String, Object> codeResponse =
CodeHelper.getCodeByName(this.requestSpec, this.responseSpec, "TST_TST_TST");
+ String tst_tst_tst = "TST_TST_TST".toLowerCase();
+ HashMap<String, Object> codeResponse =
CodeHelper.getCodeByName(this.requestSpec, this.responseSpec, tst_tst_tst);
Integer createdCodeId = (Integer) codeResponse.get("id");
Integer createdCodeValueId;
Integer createdCodeValueIdSecond;
if (createdCodeId == null) {
- createdCodeId = (Integer) CodeHelper.createCode(this.requestSpec,
this.responseSpec, "TST_TST_TST", "resourceId");
+ createdCodeId = (Integer) CodeHelper.createCode(this.requestSpec,
this.responseSpec, tst_tst_tst, "resourceId");
createdCodeValueId = CodeHelper.createCodeValue(this.requestSpec,
this.responseSpec, createdCodeId,
Utils.randomStringGenerator("cv_", 8), 1);
@@ -104,18 +115,29 @@ public class DatatableIntegrationTest {
// creating datatable for client entity
final HashMap<String, Object> columnMap = new HashMap<>();
final List<HashMap<String, Object>> datatableColumnsList = new
ArrayList<>();
- columnMap.put("datatableName",
Utils.randomNameGenerator(CLIENT_APP_TABLE_NAME + "_", 5));
+ columnMap.put("datatableName",
Utils.randomNameGenerator(CLIENT_APP_TABLE_NAME + "_",
5).toLowerCase().toLowerCase());
columnMap.put("apptableName", CLIENT_APP_TABLE_NAME);
columnMap.put("entitySubType", "PERSON");
columnMap.put("multiRow", false);
- DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsABoolean", "Boolean", false, null, null);
- DatatableHelper.addDatatableColumns(datatableColumnsList, "itsADate",
"Date", true, null, null);
- DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsADatetime", "Datetime", true, null, null);
- DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsADecimal", "Decimal", true, null, null);
- DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsADropdown", "Dropdown", false, null, "TST_TST_TST");
- DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsANumber", "Number", true, null, null);
- DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsAString", "String", true, 10, null);
- DatatableHelper.addDatatableColumns(datatableColumnsList, "itsAText",
"Text", true, null, null);
+ String itsABoolean = "itsaboolean";
+ String itsADate = "itsadate";
+ String itsADatetime = "itsadatetime";
+ String itsADecimal = "itsadecimal";
+ String itsADropdown = "itsadropdown";
+ String itsANumber = "itsanumber";
+ String itsAString = "itsastring";
+ String itsAText = "itsatext";
+ String tst_tst_tst_cd_itsADropdown = tst_tst_tst + "_cd_itsadropdown";
+ String dateFormat = "dateFormat";
+
+ DatatableHelper.addDatatableColumns(datatableColumnsList, itsABoolean,
"Boolean", false, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumnsList, itsADate,
"Date", true, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumnsList,
itsADatetime, "Datetime", true, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumnsList, itsADecimal,
"Decimal", true, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumnsList,
itsADropdown, "Dropdown", false, null, tst_tst_tst);
+ DatatableHelper.addDatatableColumns(datatableColumnsList, itsANumber,
"Number", true, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumnsList, itsAString,
"String", true, 10, null);
+ DatatableHelper.addDatatableColumns(datatableColumnsList, itsAText,
"Text", true, null, null);
columnMap.put("columns", datatableColumnsList);
String datatabelRequestJsonString = new Gson().toJson(columnMap);
LOG.info("map : {}", datatabelRequestJsonString);
@@ -138,17 +160,16 @@ public class DatatableIntegrationTest {
final boolean genericResultSet = true;
final HashMap<String, Object> datatableEntryMap = new HashMap<>();
- datatableEntryMap.put("itsABoolean", Utils.randomNumberGenerator(1) %
2 == 0);
- datatableEntryMap.put("itsADate",
Utils.randomDateGenerator("yyyy-MM-dd"));
- datatableEntryMap.put("itsADatetime",
Utils.randomDateTimeGenerator("yyyy-MM-dd"));
- datatableEntryMap.put("itsADecimal", Utils.randomDecimalGenerator(4,
3));
- datatableEntryMap.put("TST_TST_TST_cd_itsADropdown",
createdCodeValueId);
- datatableEntryMap.put("itsANumber", Utils.randomNumberGenerator(5));
- datatableEntryMap.put("itsAString", Utils.randomStringGenerator("",
8));
- datatableEntryMap.put("itsAText", Utils.randomStringGenerator("",
1000));
-
+ datatableEntryMap.put(itsABoolean, Utils.randomNumberGenerator(1) % 2
== 0);
+ datatableEntryMap.put(itsADate,
Utils.randomDateGenerator("yyyy-MM-dd"));
+ datatableEntryMap.put(itsADatetime,
Utils.randomDateTimeGenerator("yyyy-MM-dd"));
+ datatableEntryMap.put(itsADecimal, Utils.randomDecimalGenerator(4, 3));
+ datatableEntryMap.put(tst_tst_tst_cd_itsADropdown, createdCodeValueId);
+ datatableEntryMap.put(itsANumber, Utils.randomNumberGenerator(5));
+ datatableEntryMap.put(itsAString, Utils.randomStringGenerator("", 8));
+ datatableEntryMap.put(itsAText, Utils.randomStringGenerator("", 1000));
datatableEntryMap.put("locale", "en");
- datatableEntryMap.put("dateFormat", "yyyy-MM-dd");
+ datatableEntryMap.put(dateFormat, "yyyy-MM-dd");
String datatabelEntryRequestJsonString = new
Gson().toJson(datatableEntryMap);
LOG.info("map : {}", datatabelEntryRequestJsonString);
@@ -166,32 +187,32 @@ public class DatatableIntegrationTest {
assertEquals("client_id", ((Map) ((List)
items.get("columnHeaders")).get(0)).get("columnName"));
assertEquals(clientID, ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(0));
- assertEquals("itsABoolean", ((Map) ((List)
items.get("columnHeaders")).get(1)).get("columnName"));
- assertEquals(datatableEntryMap.get("itsABoolean"), ((List) ((Map)
((List) items.get("data")).get(0)).get("row")).get(1));
+ assertEquals(itsABoolean, ((Map) ((List)
items.get("columnHeaders")).get(1)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsABoolean), ((List) ((Map)
((List) items.get("data")).get(0)).get("row")).get(1));
- assertEquals("itsADate", ((Map) ((List)
items.get("columnHeaders")).get(2)).get("columnName"));
- assertEquals(datatableEntryMap.get("itsADate"),
+ assertEquals(itsADate, ((Map) ((List)
items.get("columnHeaders")).get(2)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsADate),
Utils.arrayDateToString((List) ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(2)));
- assertEquals("itsADatetime", ((Map) ((List)
items.get("columnHeaders")).get(3)).get("columnName"));
- assertEquals(datatableEntryMap.get("itsADatetime"),
+ assertEquals(itsADatetime, ((Map) ((List)
items.get("columnHeaders")).get(3)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsADatetime),
Utils.arrayDateTimeToString((List) ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(3)));
- assertEquals("itsADecimal", ((Map) ((List)
items.get("columnHeaders")).get(4)).get("columnName"));
- assertEquals(datatableEntryMap.get("itsADecimal"), ((List) ((Map)
((List) items.get("data")).get(0)).get("row")).get(4));
+ assertEquals(itsADecimal, ((Map) ((List)
items.get("columnHeaders")).get(4)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsADecimal), ((List) ((Map)
((List) items.get("data")).get(0)).get("row")).get(4));
- assertEquals("TST_TST_TST_cd_itsADropdown", ((Map) ((List)
items.get("columnHeaders")).get(5)).get("columnName"));
- assertEquals(datatableEntryMap.get("TST_TST_TST_cd_itsADropdown"),
+ assertEquals(tst_tst_tst_cd_itsADropdown, ((Map) ((List)
items.get("columnHeaders")).get(5)).get("columnName"));
+ assertEquals(datatableEntryMap.get(tst_tst_tst_cd_itsADropdown),
((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(5));
- assertEquals("itsANumber", ((Map) ((List)
items.get("columnHeaders")).get(6)).get("columnName"));
- assertEquals(datatableEntryMap.get("itsANumber"), ((List) ((Map)
((List) items.get("data")).get(0)).get("row")).get(6));
+ assertEquals(itsANumber, ((Map) ((List)
items.get("columnHeaders")).get(6)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsANumber), ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(6));
- assertEquals("itsAString", ((Map) ((List)
items.get("columnHeaders")).get(7)).get("columnName"));
- assertEquals(datatableEntryMap.get("itsAString"), ((List) ((Map)
((List) items.get("data")).get(0)).get("row")).get(7));
+ assertEquals(itsAString, ((Map) ((List)
items.get("columnHeaders")).get(7)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsAString), ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(7));
- assertEquals("itsAText", ((Map) ((List)
items.get("columnHeaders")).get(8)).get("columnName"));
- assertEquals(datatableEntryMap.get("itsAText"), ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(8));
+ assertEquals(itsAText, ((Map) ((List)
items.get("columnHeaders")).get(8)).get("columnName"));
+ assertEquals(datatableEntryMap.get(itsAText), ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(8));
// Read the Datatable entry generated with genericResultSet in false
List<HashMap<String, Object>> datatableEntryResponseNoGenericResult =
this.datatableHelper.readDatatableEntry(datatableName,
@@ -200,32 +221,32 @@ public class DatatableIntegrationTest {
assertEquals(1, datatableEntryResponseNoGenericResult.size());
assertEquals(clientID,
datatableEntryResponseNoGenericResult.get(0).get("client_id"));
- assertEquals(datatableEntryMap.get("itsABoolean"),
- Boolean.valueOf((String)
datatableEntryResponseNoGenericResult.get(0).get("itsABoolean")));
- assertEquals(datatableEntryMap.get("itsADate"),
- Utils.arrayDateToString((List)
datatableEntryResponseNoGenericResult.get(0).get("itsADate")));
- assertEquals(datatableEntryMap.get("itsADecimal"),
datatableEntryResponseNoGenericResult.get(0).get("itsADecimal"));
- assertEquals(datatableEntryMap.get("itsADatetime"),
- Utils.arrayDateTimeToString((List<Integer>)
datatableEntryResponseNoGenericResult.get(0).get("itsADatetime")));
- assertEquals(datatableEntryMap.get("TST_TST_TST_cd_itsADropdown"),
-
datatableEntryResponseNoGenericResult.get(0).get("TST_TST_TST_cd_itsADropdown"));
- assertEquals(datatableEntryMap.get("itsANumber"),
datatableEntryResponseNoGenericResult.get(0).get("itsANumber"));
- assertEquals(datatableEntryMap.get("itsAString"),
datatableEntryResponseNoGenericResult.get(0).get("itsAString"));
- assertEquals(datatableEntryMap.get("itsAText"),
datatableEntryResponseNoGenericResult.get(0).get("itsAText"));
+ assertEquals(datatableEntryMap.get(itsABoolean),
+ Boolean.valueOf((String)
datatableEntryResponseNoGenericResult.get(0).get(itsABoolean)));
+ assertEquals(datatableEntryMap.get(itsADate),
+ Utils.arrayDateToString((List)
datatableEntryResponseNoGenericResult.get(0).get(itsADate)));
+ assertEquals(datatableEntryMap.get(itsADecimal),
datatableEntryResponseNoGenericResult.get(0).get(itsADecimal));
+ assertEquals(datatableEntryMap.get(itsADatetime),
+ Utils.arrayDateTimeToString((List<Integer>)
datatableEntryResponseNoGenericResult.get(0).get(itsADatetime)));
+ assertEquals(datatableEntryMap.get(tst_tst_tst_cd_itsADropdown),
+
datatableEntryResponseNoGenericResult.get(0).get(tst_tst_tst_cd_itsADropdown));
+ assertEquals(datatableEntryMap.get(itsANumber),
datatableEntryResponseNoGenericResult.get(0).get(itsANumber));
+ assertEquals(datatableEntryMap.get(itsAString),
datatableEntryResponseNoGenericResult.get(0).get(itsAString));
+ assertEquals(datatableEntryMap.get(itsAText),
datatableEntryResponseNoGenericResult.get(0).get(itsAText));
// Update datatable entry
- Boolean previousBoolean = (Boolean)
datatableEntryMap.get("itsABoolean");
- datatableEntryMap.put("itsABoolean", !previousBoolean);
- datatableEntryMap.put("itsADate",
Utils.randomDateGenerator("yyyy-MM-dd"));
- datatableEntryMap.put("itsADatetime",
Utils.randomDateTimeGenerator("yyyy-MM-dd"));
- datatableEntryMap.put("itsADecimal", Utils.randomDecimalGenerator(4,
3));
- datatableEntryMap.put("TST_TST_TST_cd_itsADropdown", null);
- datatableEntryMap.put("itsANumber", Utils.randomNumberGenerator(5));
- datatableEntryMap.put("itsAString", Utils.randomStringGenerator("",
8));
- datatableEntryMap.put("itsAText", Utils.randomStringGenerator("",
1000));
+ Boolean previousBoolean = (Boolean) datatableEntryMap.get(itsABoolean);
+ datatableEntryMap.put(itsABoolean, !previousBoolean);
+ datatableEntryMap.put(itsADate,
Utils.randomDateGenerator("yyyy-MM-dd"));
+ datatableEntryMap.put(itsADatetime,
Utils.randomDateTimeGenerator("yyyy-MM-dd"));
+ datatableEntryMap.put(itsADecimal, Utils.randomDecimalGenerator(4, 3));
+ datatableEntryMap.put(tst_tst_tst_cd_itsADropdown, null);
+ datatableEntryMap.put(itsANumber, Utils.randomNumberGenerator(5));
+ datatableEntryMap.put(itsAString, Utils.randomStringGenerator("", 8));
+ datatableEntryMap.put(itsAText, Utils.randomStringGenerator("", 1000));
datatableEntryMap.put("locale", "en");
- datatableEntryMap.put("dateFormat", "yyyy-MM-dd");
+ datatableEntryMap.put(dateFormat, "yyyy-MM-dd");
datatabelEntryRequestJsonString = new Gson().toJson(datatableEntryMap);
LOG.info("map : {}", datatabelEntryRequestJsonString);
@@ -235,17 +256,41 @@ public class DatatableIntegrationTest {
assertEquals(clientID, updatedDatatableEntryResponse.get("clientId"));
- assertEquals(datatableEntryMap.get("itsABoolean"), ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsABoolean"));
- assertEquals(datatableEntryMap.get("itsADate"),
- Utils.arrayDateToString((List) ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsADate")));
- assertEquals(datatableEntryMap.get("itsADecimal"), ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsADecimal"));
- assertEquals(datatableEntryMap.get("itsADatetime"),
- Utils.arrayDateTimeToString((List<Integer>) ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsADatetime")));
- assertEquals(datatableEntryMap.get("TST_TST_TST_cd_itsADropdown"),
- ((Map)
updatedDatatableEntryResponse.get("changes")).get("TST_TST_TST_cd_itsADropdown"));
- assertEquals(datatableEntryMap.get("itsANumber"), ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsANumber"));
- assertEquals(datatableEntryMap.get("itsAString"), ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsAString"));
- assertEquals(datatableEntryMap.get("itsAText"), ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsAText"));
+ assertEquals(datatableEntryMap.get(itsABoolean), ((Map)
updatedDatatableEntryResponse.get("changes")).get(itsABoolean));
+ assertEquals(datatableEntryMap.get(itsADate),
+ Utils.arrayDateToString((List) ((Map)
updatedDatatableEntryResponse.get("changes")).get(itsADate)));
+ assertEquals(datatableEntryMap.get(itsADecimal), ((Map)
updatedDatatableEntryResponse.get("changes")).get(itsADecimal));
+ assertEquals(datatableEntryMap.get(itsADatetime),
+ Utils.arrayDateTimeToString((List<Integer>) ((Map)
updatedDatatableEntryResponse.get("changes")).get(itsADatetime)));
+ assertEquals(datatableEntryMap.get(tst_tst_tst_cd_itsADropdown),
+ ((Map)
updatedDatatableEntryResponse.get("changes")).get(tst_tst_tst_cd_itsADropdown));
+ assertEquals(datatableEntryMap.get(itsANumber), ((Map)
updatedDatatableEntryResponse.get("changes")).get(itsANumber));
+ assertEquals(datatableEntryMap.get(itsAString), ((Map)
updatedDatatableEntryResponse.get("changes")).get(itsAString));
+ assertEquals(datatableEntryMap.get(itsAText), ((Map)
updatedDatatableEntryResponse.get("changes")).get(itsAText));
+
+ List<String> columnsToValidate = List.of(itsABoolean, itsADate,
itsADatetime, itsAString, itsAText, itsADecimal,
+ tst_tst_tst_cd_itsADropdown);
+ for (String column : columnsToValidate) {
+ String valueFilter = column.equals(tst_tst_tst_cd_itsADropdown) ?
createdCodeValueId.toString()
+ : datatableEntryMap.get(column).toString();
+ String rows =
Calls.ok(fineract().dataTables.queryValues(datatableName, column, valueFilter,
column));
+ JsonArray jsonArray =
JsonParser.parseString(rows).getAsJsonArray();
+ if (itsADatetime.equals(column)) {
+ DateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ DateFormat df2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ Date parsedRequest =
df1.parse(datatableEntryMap.get(column).toString());
+ Date parsedResponse =
df2.parse(jsonArray.get(0).getAsJsonObject().get(column).getAsString());
+ assertFalse(parsedRequest.after(parsedResponse));
+ assertFalse(parsedRequest.before(parsedResponse));
+ } else if (itsADecimal.equals(column)) {
+ assertEquals(0, new
BigDecimal(datatableEntryMap.get(column).toString())
+ .compareTo(new
BigDecimal(jsonArray.get(0).getAsJsonObject().get(column).getAsString())));
+ } else if (tst_tst_tst_cd_itsADropdown.equals(column)) {
+ assertEquals(createdCodeValueId.toString(),
jsonArray.get(0).getAsJsonObject().get(column).getAsString());
+ } else {
+ assertEquals(datatableEntryMap.get(column).toString(),
jsonArray.get(0).getAsJsonObject().get(column).getAsString());
+ }
+ }
// deleting datatable entries
Integer appTableId =
this.datatableHelper.deleteDatatableEntries(datatableName, clientID,
"clientId");
@@ -383,13 +428,14 @@ public class DatatableIntegrationTest {
@Test
public void validateReadDatatableMultirow() {
// Fetch / Create TST code
- HashMap<String, Object> codeResponse =
CodeHelper.getCodeByName(this.requestSpec, this.responseSpec, "TST_TST_TST");
+ String tst_tst_tst = "tst_tst_tst";
+ HashMap<String, Object> codeResponse =
CodeHelper.getCodeByName(this.requestSpec, this.responseSpec, tst_tst_tst);
Integer createdCodeId = (Integer) codeResponse.get("id");
Integer createdCodeValueId;
Integer createdCodeValueIdSecond;
if (createdCodeId == null) {
- createdCodeId = (Integer) CodeHelper.createCode(this.requestSpec,
this.responseSpec, "TST_TST_TST", "resourceId");
+ createdCodeId = (Integer) CodeHelper.createCode(this.requestSpec,
this.responseSpec, tst_tst_tst, "resourceId");
createdCodeValueId = CodeHelper.createCodeValue(this.requestSpec,
this.responseSpec, createdCodeId,
Utils.randomStringGenerator("cv_", 8), 1);
@@ -413,7 +459,7 @@ public class DatatableIntegrationTest {
DatatableHelper.addDatatableColumns(datatableColumnsList, "itsADate",
"Date", false, null, null);
DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsADatetime", "Datetime", false, null, null);
DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsADecimal", "Decimal", false, null, null);
- DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsADropdown", "Dropdown", false, null, "TST_TST_TST");
+ DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsADropdown", "Dropdown", false, null, tst_tst_tst);
DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsANumber", "Number", false, null, null);
DatatableHelper.addDatatableColumns(datatableColumnsList,
"itsAString", "String", false, 10, null);
DatatableHelper.addDatatableColumns(datatableColumnsList, "itsAText",
"Text", false, null, null);
@@ -445,7 +491,7 @@ public class DatatableIntegrationTest {
datatableEntryMap.put("itsADate",
Utils.randomDateGenerator("yyyy-MM-dd"));
datatableEntryMap.put("itsADatetime",
Utils.randomDateTimeGenerator("yyyy-MM-dd"));
datatableEntryMap.put("itsADecimal", Utils.randomDecimalGenerator(4,
3));
- datatableEntryMap.put("TST_TST_TST_cd_itsADropdown",
createdCodeValueId);
+ datatableEntryMap.put(tst_tst_tst + "_cd_itsADropdown",
createdCodeValueId);
datatableEntryMap.put("itsANumber", Utils.randomNumberGenerator(5));
datatableEntryMap.put("itsAString", Utils.randomStringGenerator("",
8));
datatableEntryMap.put("itsAText", Utils.randomStringGenerator("",
1000));
@@ -482,8 +528,8 @@ public class DatatableIntegrationTest {
Utils.arrayDateTimeToString((List) ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(4)));
assertEquals("itsADecimal", ((Map) ((List)
items.get("columnHeaders")).get(5)).get("columnName"));
assertEquals(datatableEntryMap.get("itsADecimal"), ((List) ((Map)
((List) items.get("data")).get(0)).get("row")).get(5));
- assertEquals("TST_TST_TST_cd_itsADropdown", ((Map) ((List)
items.get("columnHeaders")).get(6)).get("columnName"));
- assertEquals(datatableEntryMap.get("TST_TST_TST_cd_itsADropdown"),
+ assertEquals(tst_tst_tst + "_cd_itsADropdown", ((Map) ((List)
items.get("columnHeaders")).get(6)).get("columnName"));
+ assertEquals(datatableEntryMap.get(tst_tst_tst + "_cd_itsADropdown"),
((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(6));
assertEquals("itsANumber", ((Map) ((List)
items.get("columnHeaders")).get(7)).get("columnName"));
assertEquals(datatableEntryMap.get("itsANumber"), ((List) ((Map)
((List) items.get("data")).get(0)).get("row")).get(7));
@@ -500,7 +546,7 @@ public class DatatableIntegrationTest {
assertEquals(datatableEntryMap.get("itsADatetime"),
Utils.arrayDateTimeToString((List) ((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(4)));
assertEquals(datatableEntryMap.get("itsADecimal"), ((List) ((Map)
((List) items.get("data")).get(1)).get("row")).get(5));
- assertEquals(datatableEntryMap.get("TST_TST_TST_cd_itsADropdown"),
+ assertEquals(datatableEntryMap.get(tst_tst_tst + "_cd_itsADropdown"),
((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(6));
assertEquals(datatableEntryMap.get("itsANumber"), ((List) ((Map)
((List) items.get("data")).get(1)).get("row")).get(7));
assertEquals(datatableEntryMap.get("itsAString"), ((List) ((Map)
((List) items.get("data")).get(1)).get("row")).get(8));
@@ -520,8 +566,8 @@ public class DatatableIntegrationTest {
assertEquals(datatableEntryMap.get("itsADecimal"),
datatableEntryResponseNoGenericResult.get(0).get("itsADecimal"));
assertEquals(datatableEntryMap.get("itsADatetime"),
Utils.arrayDateTimeToString((List<Integer>)
datatableEntryResponseNoGenericResult.get(0).get("itsADatetime")));
- assertEquals(datatableEntryMap.get("TST_TST_TST_cd_itsADropdown"),
-
datatableEntryResponseNoGenericResult.get(0).get("TST_TST_TST_cd_itsADropdown"));
+ assertEquals(datatableEntryMap.get(tst_tst_tst + "_cd_itsADropdown"),
+ datatableEntryResponseNoGenericResult.get(0).get(tst_tst_tst +
"_cd_itsADropdown"));
assertEquals(datatableEntryMap.get("itsANumber"),
datatableEntryResponseNoGenericResult.get(0).get("itsANumber"));
assertEquals(datatableEntryMap.get("itsAString"),
datatableEntryResponseNoGenericResult.get(0).get("itsAString"));
assertEquals(datatableEntryMap.get("itsAText"),
datatableEntryResponseNoGenericResult.get(0).get("itsAText"));
@@ -534,7 +580,7 @@ public class DatatableIntegrationTest {
datatableEntryMap.put("itsADate", null);
datatableEntryMap.put("itsADatetime", null);
datatableEntryMap.put("itsADecimal", null);
- datatableEntryMap.put("TST_TST_TST_cd_itsADropdown", null);
+ datatableEntryMap.put(tst_tst_tst + "_cd_itsADropdown", null);
datatableEntryMap.put("itsANumber", null);
datatableEntryMap.put("itsAString", null);
datatableEntryMap.put("itsAText", null);
@@ -555,7 +601,7 @@ public class DatatableIntegrationTest {
assertEquals(null, ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsADate"));
assertEquals(null, ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsADecimal"));
assertEquals(null, ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsADatetime"));
- assertEquals(null, ((Map)
updatedDatatableEntryResponse.get("changes")).get("TST_TST_TST_cd_itsADropdown"));
+ assertEquals(null, ((Map)
updatedDatatableEntryResponse.get("changes")).get(tst_tst_tst +
"_cd_itsADropdown"));
assertEquals(null, ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsANumber"));
assertEquals(null, ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsAString"));
assertEquals(null, ((Map)
updatedDatatableEntryResponse.get("changes")).get("itsAText"));
@@ -574,7 +620,7 @@ public class DatatableIntegrationTest {
assertEquals(null, ((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(4));
assertEquals("itsADecimal", ((Map) ((List)
items.get("columnHeaders")).get(5)).get("columnName"));
assertEquals(null, ((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(5));
- assertEquals("TST_TST_TST_cd_itsADropdown", ((Map) ((List)
items.get("columnHeaders")).get(6)).get("columnName"));
+ assertEquals(tst_tst_tst + "_cd_itsADropdown", ((Map) ((List)
items.get("columnHeaders")).get(6)).get("columnName"));
assertEquals(null, ((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(6));
assertEquals("itsANumber", ((Map) ((List)
items.get("columnHeaders")).get(7)).get("columnName"));
assertEquals(null, ((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(7));
@@ -594,8 +640,8 @@ public class DatatableIntegrationTest {
assertEquals(datatableEntryMap.get("itsADate"),
datatableEntryResponseNoGenericResult.get(0).get("itsADate"));
assertEquals(datatableEntryMap.get("itsADecimal"),
datatableEntryResponseNoGenericResult.get(0).get("itsADecimal"));
assertEquals(datatableEntryMap.get("itsADatetime"),
datatableEntryResponseNoGenericResult.get(0).get("itsADatetime"));
- assertEquals(datatableEntryMap.get("TST_TST_TST_cd_itsADropdown"),
-
datatableEntryResponseNoGenericResult.get(0).get("TST_TST_TST_cd_itsADropdown"));
+ assertEquals(datatableEntryMap.get(tst_tst_tst + "_cd_itsADropdown"),
+ datatableEntryResponseNoGenericResult.get(0).get(tst_tst_tst +
"_cd_itsADropdown"));
assertEquals(datatableEntryMap.get("itsANumber"),
datatableEntryResponseNoGenericResult.get(0).get("itsANumber"));
assertEquals(datatableEntryMap.get("itsAString"),
datatableEntryResponseNoGenericResult.get(0).get("itsAString"));
assertEquals(datatableEntryMap.get("itsAText"),
datatableEntryResponseNoGenericResult.get(0).get("itsAText"));
