Java Code Examples for org.apache.commons.lang.StringEscapeUtils#escapeSql()

private boolean buildNameSearch(boolean hasAWhereClause, StringBuilder queryText, String searchString,
    String userAlias) {
if (!hasAWhereClause) {
    queryText.append(" WHERE ");
}
String[] tokens = searchString.trim().split("\\s+");
for (String token : tokens) {
    String escToken = StringEscapeUtils.escapeSql(token);
    if (hasAWhereClause) {
	queryText.append(" AND ");
    }
    queryText.append(" (").append(userAlias).append(".first_name LIKE '%").append(escToken).append("%' OR ")
	    .append(userAlias).append(".last_name LIKE '%").append(escToken).append("%' OR ").append(userAlias)
	    .append(".login LIKE '%").append(escToken).append("%') ");
}
return true;
   }
 

@Override
public String[] getParameterValues(String name) {
    String[] arr = super.getParameterValues(name);
    if(arr != null){
        long length = arr.length;
        for (int i=0;i<length;i++) {
            // HTML transformation characters
            arr[i] = JsoupUtil.clean(arr[i]);
            // SQL injection characters
            arr[i] = StringEscapeUtils.escapeSql(arr[i]);
        }
    }
    return arr;
}
 

private boolean buildRemarksSearch(boolean hasAWhereClause, StringBuilder queryText, String searchString) {
if (!hasAWhereClause) {
    queryText.append(" WHERE ");
}
String[] tokens = searchString.trim().split("\\s+");
for (String token : tokens) {
    String escToken = StringEscapeUtils.escapeSql(token);
    if (hasAWhereClause) {
	queryText.append(" AND ");
    }
    queryText.append(" (").append("le.description LIKE '%").append(escToken).append("%') ");
}
return true;
   }
 

private void buildNameSearch(String searchString, StringBuilder sqlBuilder) {
if (!StringUtils.isBlank(searchString)) {
    String[] tokens = searchString.trim().split("\\s+");
    for (String token : tokens) {
	String escToken = StringEscapeUtils.escapeSql(token);
	sqlBuilder.append(" WHERE (user.first_name LIKE '%").append(escToken)
		.append("%' OR user.last_name LIKE '%").append(escToken).append("%' OR user.login_name LIKE '%")
		.append(escToken).append("%') ");
    }
}
   }
 

private void buildNameSearch(String searchString, StringBuilder sqlBuilder) {
if (!StringUtils.isBlank(searchString)) {
    String[] tokens = searchString.trim().split("\\s+");
    for (String token : tokens) {
	String escToken = StringEscapeUtils.escapeSql(token);
	sqlBuilder.append(" AND (user.first_name LIKE '%").append(escToken)
		.append("%' OR user.last_name LIKE '%").append(escToken).append("%' OR user.login_name LIKE '%")
		.append(escToken).append("%') ");
    }
}
   }
 

private void buildNameSearch(StringBuilder queryText, String searchString) {
if (!StringUtils.isBlank(searchString)) {
    String[] tokens = searchString.trim().split("\\s+");
    for (String token : tokens) {
	String escToken = StringEscapeUtils.escapeSql(token);
	queryText.append(" WHERE (fullname LIKE '%").append(escToken).append("%' OR username LIKE '%")
		.append(escToken).append("%') ");
    }
}
   }
 

private String buildNameSearch(String searchString, String userRef) {
String filteredSearchString = null;
if (!StringUtils.isBlank(searchString)) {
    StringBuilder searchStringBuilder = new StringBuilder("");
    String[] tokens = searchString.trim().split("\\s+");
    for (String token : tokens) {
	String escToken = StringEscapeUtils.escapeSql(token);
	searchStringBuilder.append(" AND (" + userRef + ".fullname LIKE '%").append(escToken)
		.append("%' OR " + userRef + ".username LIKE '%").append(escToken).append("%') ");
    }
    filteredSearchString = searchStringBuilder.toString();
}
return filteredSearchString;
   }
 

private static String getClusterCondition( String clusterName ){
    if( clusterName == null || clusterName.trim().isEmpty() ){
        return "   AND woin.cluster_name IS NULL ";
    }
    else{
        return "   AND woin.cluster_name = '" + StringEscapeUtils.escapeSql( clusterName ) + "'";
    }
}
 

private void buildNameSearch(StringBuilder queryText, String searchString) {
if (!StringUtils.isBlank(searchString)) {
    String[] tokens = searchString.trim().split("\\s+");
    for (String token : tokens) {
	String escToken = StringEscapeUtils.escapeSql(token);
	queryText.append(" AND (user.first_name LIKE '%").append(escToken)
		.append("%' OR user.last_name LIKE '%").append(escToken).append("%' OR user.login_name LIKE '%")
		.append(escToken).append("%')");
    }
}
   }
 

private String toHQL(QueryFilter filter) {
	Assert.assertNotNull(filter, "Filter is null");
	if (filter instanceof QueryStaticFilter) {
		QueryStaticFilter staticFilter = (QueryStaticFilter) filter;
		boolean ignoreCase = staticFilter.isIgnoreCase();
		IConditionalOperator conditionalOperator = (IConditionalOperator) HQLStatement.conditionalOperators.get(staticFilter.getOperator());
		String actualFieldName = AvailableFiltersOnUsersList.valueOf(staticFilter.getField()).toString();
		String leftHandValue = ignoreCase ? "upper(" + actualFieldName + ")" : actualFieldName;
		String value = staticFilter.getValue() != null ? staticFilter.getValue().toString() : "";
		String escapedValue = StringEscapeUtils.escapeSql(value);
		String[] rightHandValues = new String[] { "'" + (ignoreCase ? escapedValue.toUpperCase() : escapedValue) + "'" };
		return conditionalOperator.apply(leftHandValue, rightHandValues);
	} else if (filter instanceof FinalUsersFilter) {
		StringBuffer buffer = new StringBuffer();
		buffer.append(" id in (");
		buffer.append(" select ur.id.id ");
		buffer.append("	from ");
		buffer.append("		SbiExtUserRoles ur, SbiExtRoles r ");
		buffer.append("	where ");
		buffer.append("		ur.id.extRoleId = r.extRoleId ");
		buffer.append("	group by ur.id.id ");
		buffer.append("	having sum(case when r.roleType.valueCd = 'USER' then 0 else 1 end) = 0) ");
		buffer.append(") ");
		return buffer.toString();
	} else {
		throw new SpagoBIRuntimeException("Cannot handle filter of type [" + filter.getClass().getName() + "]");
	}
}
 

private void buildNameSearch(String searchString, StringBuilder sqlBuilder) {
if (!StringUtils.isBlank(searchString)) {
    String[] tokens = searchString.trim().split("\\s+");
    for (String token : tokens) {
	String escToken = StringEscapeUtils.escapeSql(token);
	sqlBuilder.append(" WHERE (user.first_name LIKE '%").append(escToken)
		.append("%' OR user.last_name LIKE '%").append(escToken).append("%' OR user.login_name LIKE '%")
		.append(escToken).append("%') ");
    }
}
   }
 

@RequestMapping("classification")
public void searchClassification(HttpServletRequest request, HttpServletResponse response) {
	final ID user = getRequestUser(request);
	final String entity = getParameterNotNull(request, "entity");
	final String field = getParameterNotNull(request, "field");

	Field fieldMeta = MetadataHelper.getField(entity, field);
	ID useClassification = ClassificationManager.instance.getUseClassification(fieldMeta, false);

	String q = getParameter(request, "q");
	// 为空则加载最近使用的
	if (StringUtils.isBlank(q)) {
	    String type = "d" + useClassification;
		ID[] recently = Application.getRecentlyUsedCache().gets(user, "ClassificationData", type);
		if (recently.length == 0) {
			writeSuccess(response, JSONUtils.EMPTY_ARRAY);
		} else {
			writeSuccess(response, RecentlyUsedSearch.formatSelect2(recently, null));
		}
		return;
	}
	q = StringEscapeUtils.escapeSql(q);

       int openLevel = ClassificationManager.instance.getOpenLevel(fieldMeta);

	String sql = "select itemId,fullName from ClassificationData" +
               " where dataId = '%s' and level = %d and (fullName like '%%%s%%' or quickCode like '%%%s%%') order by fullName";
       sql = String.format(sql, useClassification.toLiteral(), openLevel, q, q);
	List<Object> result = resultSearch(sql, null, null);
	writeSuccess(response, result);
}
 

/**
 * Escape SQL content. i.e. replace characters with &values;
 *
 * @param content content
 * @return escaped content
 */
public static String escapeSql(String content ) {
  if ( Utils.isEmpty( content ) ) {
    return content;
  }
  return StringEscapeUtils.escapeSql( content );
}
 

@SuppressWarnings("unchecked")
   @Override
   public List<Object[]> getPagedUsers(Long toolSessionId, Integer page, Integer size, int sorting,
    String searchString) {

String GET_USERS_FOR_SESSION = "SELECT user.uid, user.hidden, CONCAT(user.firstName, ' ', user.lastName) FROM "
	+ PeerreviewUser.class.getName() + " user WHERE user.session.sessionId = :toolSessionId ";

String sortingOrder = "";
switch (sorting) {
    case PeerreviewConstants.SORT_BY_NO:
	sortingOrder = " ORDER BY user.uid";
	break;
    case PeerreviewConstants.SORT_BY_USERNAME_ASC:
	sortingOrder = " ORDER BY user.firstName ASC";
	break;
    case PeerreviewConstants.SORT_BY_USERNAME_DESC:
	sortingOrder = " ORDER BY user.firstName DESC";
	break;
}

   	StringBuilder bldr =  new StringBuilder(GET_USERS_FOR_SESSION);
if (!StringUtils.isBlank(searchString)) {
    String[] tokens = searchString.trim().split("\\s+");
    for (String token : tokens) {
	String escToken = StringEscapeUtils.escapeSql(token);
	bldr.append(" AND ( ").append("user.firstName LIKE '%").append(escToken)
		.append("%' OR user.lastName LIKE '%").append(escToken).append("%' OR user.loginName LIKE '%")
		.append(escToken).append("%') ");
    }
}
   	bldr.append(sortingOrder);
   	
String queryString = bldr.toString();
Query query = getSession().createQuery(queryString)
	.setParameter("toolSessionId", toolSessionId);
if ( page != null && size != null ) {
    query.setFirstResult(page * size).setMaxResults(size);
}
return (List<Object[]>) query.list();
   }
 

public static String escapeStringConstant(String pattern) {
    return StringEscapeUtils.escapeSql(pattern); // Need to escape double quotes
}
 

/**
 * Generate optimized sql given a specific cql query, tenant, index column name hint and configurable size to hinge the optimization on.
 *
 * @param column the column that has an index to be used for sorting
 * @param preparedCql the cql query
 * @param offset start index of objects to return
 * @param limit max number of objects to return
 * @throws QueryValidationException
 * @return the generated SQL string, or null if the CQL query is not suitable for optimization.
 */
static String generateOptimizedSql(String column, PreparedCQL preparedCql,
    int offset, int limit) throws QueryValidationException {

  if (limit == 0) {
    return null;
  }
  String cql = preparedCql.getCqlWrapper().getQuery();
  CQLSortNode cqlSortNode = checkOptimizedCQL(cql, column);
  if (cqlSortNode == null) {
    return null;
  }
  List<ModifierSet> sortIndexes = cqlSortNode.getSortIndexes();
  ModifierSet modifierSet = sortIndexes.get(0);
  String ascDesc = getAscDesc(modifierSet);
  cql = cqlSortNode.getSubtree().toCQL();
  String lessGreater = "";
  if (ascDesc.equals("DESC")) {
    lessGreater = ">" ;
  } else {
    lessGreater = "<";
  }
  String tableName = preparedCql.getFullTableName();
  String where = preparedCql.getCqlWrapper().getField().toSql(cql).getWhere();
  // If there are many matches use a full table scan in data_column sort order
  // using the data_column index, but stop this scan after OPTIMIZED_SQL_SIZE index entries.
  // Otherwise use full text matching because there are only a few matches.
  //
  // "headrecords" are the matching records found within the first OPTIMIZED_SQL_SIZE records
  // by stopping at the data_column from "OFFSET OPTIMIZED_SQL_SIZE LIMIT 1".
  // If "headrecords" are enough to return the requested "LIMIT" number of records we are done.
  // Otherwise use the full text index to create "allrecords" with all matching
  // records and do sorting and LIMIT afterwards.
  String wrappedColumn =
    "lower(f_unaccent(jsonb->>'" + column + "')) ";
  String cutWrappedColumn = "left(" + wrappedColumn + ",600) ";
  String countSql = preparedCql.getSchemaName()
    + ".count_estimate('"
    + "  SELECT " + StringEscapeUtils.escapeSql(wrappedColumn) + " AS data_column "
    + "  FROM " + tableName + " "
    + "  WHERE " + StringEscapeUtils.escapeSql(where)
    + "')";
  String sql =
    " WITH "
      + " headrecords AS ("
      + "   SELECT jsonb, (" + wrappedColumn + ") AS data_column FROM " + tableName
      + "   WHERE (" + where + ")"
      + "     AND " + cutWrappedColumn + lessGreater
      + "             ( SELECT " + cutWrappedColumn
      + "               FROM " + tableName
      + "               ORDER BY " + cutWrappedColumn + ascDesc
      + "               OFFSET " + optimizedSqlSize + " LIMIT 1"
      + "             )"
      + "   ORDER BY " + cutWrappedColumn + ascDesc
      + "   LIMIT " + limit + " OFFSET " + offset
      + " ), "
      + " allrecords AS ("
      + "   SELECT jsonb, " + wrappedColumn + " AS data_column FROM " + tableName
      + "   WHERE (" + where + ")"
      + "     AND (SELECT COUNT(*) FROM headrecords) < " + limit
      + " ),"
      + " totalCount AS (SELECT " + countSql + " AS count)"
      + " SELECT jsonb, data_column, (SELECT count FROM totalCount)"
      + "   FROM headrecords"
      + "   WHERE (SELECT COUNT(*) FROM headrecords) >= " + limit
      + " UNION"
      + " (SELECT jsonb, data_column, (SELECT count FROM totalCount)"
      + "   FROM allrecords"
      + "   ORDER BY data_column " + ascDesc
      + "   LIMIT " + limit + " OFFSET " + offset
      + " )"
      + " ORDER BY data_column " + ascDesc;

  logger.info("optimized SQL generated from CQL: " + sql);
  return sql;
}
 

@RequestMapping("search")
public void search(HttpServletRequest request, HttpServletResponse response) {
	final ID user = getRequestUser(request);
	final String entity = getParameterNotNull(request, "entity");
	
	String q = getParameter(request, "q");
	// 为空则加载最近使用的
	if (StringUtils.isBlank(q)) {
		String type = getParameter(request, "type");
		ID[] recently = Application.getRecentlyUsedCache().gets(user, entity, type);
		if (recently.length == 0) {
			writeSuccess(response, JSONUtils.EMPTY_ARRAY);
		} else {
			writeSuccess(response, RecentlyUsedSearch.formatSelect2(recently, null));
		}
		return;
	}
	q = StringEscapeUtils.escapeSql(q);
	
	Entity metaEntity = MetadataHelper.getEntity(entity);
	Field nameField = MetadataHelper.getNameField(metaEntity);
	if (nameField == null) {
		LOG.warn("No name-field found : " + entity);
		writeSuccess(response, ArrayUtils.EMPTY_STRING_ARRAY);
		return;
	}
	
	// 查询字段，未指定则使用名称字段和 quickCode
	String qfields = getParameter(request, "qfields");
	if (StringUtils.isBlank(qfields)) {
		qfields = nameField.getName();
		if (metaEntity.containsField(EntityHelper.QuickCode)) {
			qfields += "," + EntityHelper.QuickCode;
		}
	}
	
	List<String> or = new ArrayList<>();
	for (String field : qfields.split(",")) {
		if (!metaEntity.containsField(field)) {
			LOG.warn("No field for search : " + field);
		} else {
			or.add(field + " like '%" + q + "%'");
		}
	}
	if (or.isEmpty()) {
		writeSuccess(response, ArrayUtils.EMPTY_STRING_ARRAY);
		return;
	}
	
	String sql = "select {0},{1} from {2} where ({3})";
	sql = MessageFormat.format(sql, 
			metaEntity.getPrimaryField().getName(), nameField.getName(), metaEntity.getName(), StringUtils.join(or, " or "));
	if (metaEntity.containsField(EntityHelper.ModifiedOn)) {
		sql += " order by modifiedOn desc";
	}
	
	List<Object> result = resultSearch(sql, metaEntity, nameField);
	writeSuccess(response, result);
}
 

@RequestMapping({ "reference", "quick" })
public void referenceSearch(HttpServletRequest request, HttpServletResponse response) {
	final ID user = getRequestUser(request);
	final String entity = getParameterNotNull(request, "entity");
	final String field = getParameterNotNull(request, "field");
	
	Entity metaEntity = MetadataHelper.getEntity(entity);
	Field referenceField = metaEntity.getField(field);
	if (referenceField.getType() != FieldType.REFERENCE) {
		writeSuccess(response, JSONUtils.EMPTY_ARRAY);
		return;
	}
	
	Entity referenceEntity = referenceField.getReferenceEntity();
	Field referenceNameField = MetadataHelper.getNameField(referenceEntity);
	if (referenceNameField == null) {
		LOG.warn("No name-field found : " + referenceEntity.getName());
		writeSuccess(response, JSONUtils.EMPTY_ARRAY);
		return;
	}

	// 引用字段数据过滤仅在搜索时有效
	// 启用数据过滤后最近搜索将不可用
	final String protocolFilter = new ProtocolFilterParser(null).parseRef(field + "." + entity);

	String q = getParameter(request, "q");
	// 为空则加载最近使用的
	if (StringUtils.isBlank(q)) {
		ID[] recently = null;
		if (protocolFilter == null) {
			String type = getParameter(request, "type");
			recently = Application.getRecentlyUsedCache().gets(user, referenceEntity.getName(), type);
		}

		if (recently == null || recently.length == 0) {
			writeSuccess(response, JSONUtils.EMPTY_ARRAY);
		} else {
			writeSuccess(response, RecentlyUsedSearch.formatSelect2(recently, null));
		}
		return;
	}
	q = StringEscapeUtils.escapeSql(q);
	
	// 可搜索字符
	Set<String> searchFields = new HashSet<>();
	DisplayType referenceNameFieldType = EasyMeta.getDisplayType(referenceNameField);
	if (!(referenceNameFieldType == DisplayType.DATETIME || referenceNameFieldType == DisplayType.DATE
			|| referenceNameFieldType == DisplayType.NUMBER || referenceNameFieldType == DisplayType.DECIMAL
			|| referenceNameFieldType == DisplayType.ID)) {
		searchFields.add(referenceNameField.getName());
	}
	if (referenceEntity.containsField(EntityHelper.QuickCode) && StringUtils.isAlphanumericSpace(q)) {
		searchFields.add(EntityHelper.QuickCode);
	}
	for (Field seriesField : MetadataSorter.sortFields(referenceEntity, DisplayType.SERIES)) {
		searchFields.add(seriesField.getName());
	}

	if (searchFields.isEmpty()) {
		LOG.warn("No fields of search found : " + referenceEntity);
		writeSuccess(response, JSONUtils.EMPTY_ARRAY);
		return;
	}

	String like = " like '%" + q + "%'";
	String searchWhere = StringUtils.join(searchFields.iterator(), like + " or ") + like;
	if (protocolFilter != null) {
		searchWhere = "(" + searchWhere + ") and (" + protocolFilter + ')';
	}

	String sql = MessageFormat.format("select {0},{1} from {2} where ( {3} )",
			referenceEntity.getPrimaryField().getName(), referenceNameField.getName(), referenceEntity.getName(), searchWhere);
	if (referenceEntity.containsField(EntityHelper.ModifiedOn)) {
		sql += " order by modifiedOn desc";
	}

	List<Object> result = resultSearch(sql, metaEntity, referenceNameField);
	writeSuccess(response, result);
}
 

private String escapeString(String value) {
	if (value == null) {
		return null;
	}
	return StringEscapeUtils.escapeSql(value);
}
 

/**
 * Escape SQL content. i.e. replace characters with &values;
 *
 * @param content
 *          content
 * @return escaped content
 */
public static String escapeSQL( String content ) {
  if ( Utils.isEmpty( content ) ) {
    return content;
  }
  return StringEscapeUtils.escapeSql( content );
}