1、把需要筛选的字段和条件分开，目前未支持case when以及自定义函数，实现如何下：
VerifySqlLegitimacyServiceImpl：
/**
* File Name:VerifySqlLegitimacyServiceImpl.java
* Date:2018年9月18日上午8:52:27
*/
import java.io.StringReader;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.AllComparisonExpression;
import net.sf.jsqlparser.expression.AnalyticExpression;
import net.sf.jsqlparser.expression.AnyComparisonExpression;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.CaseExpression;
import net.sf.jsqlparser.expression.CastExpression;
import net.sf.jsqlparser.expression.DateTimeLiteralExpression;
import net.sf.jsqlparser.expression.DateValue;
import net.sf.jsqlparser.expression.DoubleValue;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExtractExpression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.HexValue;
import net.sf.jsqlparser.expression.IntervalExpression;
import net.sf.jsqlparser.expression.JdbcNamedParameter;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.expression.JsonExpression;
import net.sf.jsqlparser.expression.KeepExpression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.MySQLGroupConcat;
import net.sf.jsqlparser.expression.NullValue;
import net.sf.jsqlparser.expression.NumericBind;
import net.sf.jsqlparser.expression.OracleHierarchicalExpression;
import net.sf.jsqlparser.expression.OracleHint;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.RowConstructor;
import net.sf.jsqlparser.expression.SignedExpression;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.TimeKeyExpression;
import net.sf.jsqlparser.expression.TimeValue;
import net.sf.jsqlparser.expression.TimestampValue;
import net.sf.jsqlparser.expression.WhenClause;
import net.sf.jsqlparser.expression.operators.arithmetic.Addition;
import net.sf.jsqlparser.expression.operators.arithmetic.BitwiseAnd;
import net.sf.jsqlparser.expression.operators.arithmetic.BitwiseOr;
import net.sf.jsqlparser.expression.operators.arithmetic.BitwiseXor;
import net.sf.jsqlparser.expression.operators.arithmetic.Concat;
import net.sf.jsqlparser.expression.operators.arithmetic.Division;
import net.sf.jsqlparser.expression.operators.arithmetic.Modulo;
import net.sf.jsqlparser.expression.operators.arithmetic.Multiplication;
import net.sf.jsqlparser.expression.operators.arithmetic.Subtraction;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.Between;
import net.sf.jsqlparser.expression.operators.relational.ComparisonOperator;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.expression.operators.relational.Matches;
import net.sf.jsqlparser.expression.operators.relational.MinorThan;
import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
import net.sf.jsqlparser.expression.operators.relational.RegExpMatchOperator;
import net.sf.jsqlparser.expression.operators.relational.RegExpMySQLOperator;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.ParseException;
import net.sf.jsqlparser.parser.Token;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.AllTableColumns;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SubSelect;
/**
* ClassName:VerifySqlLegitimacyServiceImpl <br/>
* Function: 验证sql的合法性<br/>
* Date: 2018年8月24日 下午2:47:18 <br/>
* @version
* @since JDK 1.8
* @see
*/
@Service
@Slf4j
public class VerifySqlLegitimacyServiceImpl implements VerifySqlLegitimacyService
{
private final static String sql_select = "SELECT ";
private final static String sql_from = " FROM table";
private final static String sql_from_where = sql_select + "*" + sql_from + " WHERE ";
/***
* verifyField:(验证sql字段). <br/>
*
* @author hewanwan
* @param sql
* @throws JSQLParserException
* @since JDK 1.8
*/
public ResultMap<ErrorMessage> verifyField(String sql)
{
CCJSqlParserManager parser = new CCJSqlParserManager();
StringBuilder buffer = new StringBuilder();
ResultMap<ErrorMessage> result = new ResultMap<>();
ErrorMessage errorMessage = new ErrorMessage();
sql = sql.trim();
String parseSql = sql_select + sql.trim() + sql_from;
try
{
Statement stmt = parser.parse(new StringReader(parseSql));
if (stmt instanceof Select)
{
Select selectStatement = (Select) stmt;
PlainSelect selectBody = (PlainSelect) selectStatement.getSelectBody();
List<SelectItem> selectItemlist = selectBody.getSelectItems();
SelectExpressionItem selectExpressionItem = null;
Expression expression = null;
AllTableColumns allTableColumns = null;
Alias alias = null;
SelectItem selectItem = null;
if (selectItemlist != null)
{
for (int i = 0; i < selectItemlist.size(); i++)
{
selectItem = selectItemlist.get(i);
if (selectItem instanceof SelectExpressionItem)
{
if(i!=0){
buffer.append(", ");
}
selectItem = (SelectExpressionItem) selectItem;
selectExpressionItem = (SelectExpressionItem) selectItemlist.get(i);
buffer.append(selectExpressionItem.toString());
alias = selectExpressionItem.getAlias();
if (alias != null && !judgeAlias(alias, buffer, selectExpressionItem, result)) // 如果没有验证通过，则进行返回
{
result.getData().setOtherField(selectBody.toString().substring(sql_select.length(),
selectBody.toString().lastIndexOf(sql_from)));
return result;
}
expression = selectExpressionItem.getExpression();// 获取字段
if (!judgEexpression(expression, buffer, selectExpressionItem, result, alias, true))
{
result.getData().setOtherField(selectBody.toString().substring(sql_select.length(),
selectBody.toString().lastIndexOf(sql_from)));
return result;
}
} else if (selectItem instanceof AllTableColumns)
{
selectItem = (AllTableColumns) selectItem;
allTableColumns = (AllTableColumns) selectItemlist.get(i);
buffer.append(allTableColumns.toString());
result.setCode(ErrorCodes.ERROR_NAME_RULE.getCode());
result.setMsg(ErrorCodes.ERROR_NAME_RULE.getDesc());
Integer startPosition = buffer.length() - allTableColumns.toString().length();
Integer endPosition = buffer.length();
errorMessage.setErrorMessage(startPosition, endPosition, allTableColumns.toString());
result.setData(errorMessage);
result.getData().setOtherField(selectBody.toString().substring(sql_select.length(),
selectBody.toString().lastIndexOf(sql_from)));
return result;
} else {
if(i!=0){
buffer.append(", ");
}
buffer.append(selectItem.toString());
}
}
}
}
} catch (JSQLParserException e)
{
log.error("verifyField Exception", e);
if (e.getCause() instanceof ParseException)
{
/*
* ParseException parseException = (ParseException)
* e.getCause(); String message = parseException.getMessage();
* String start = StringUtils.substringBetween(message,
* "column ", "."); Integer
* startPosition=Integer.parseInt(start); Integer
* endPosition=null; String wrongField=null; Integer from
* =sql.lastIndexOf("from"); String s=StringUtils.substring(sql,
* Integer.parseInt(start)-1, from); if(s.contains(",")) {
* String column=StringUtils.substringBefore(s, ",");
* if(column.contains("AS".toLowerCase())) {
* wrongField=StringUtils.substringBefore(column,
* "As".toLowerCase()).trim(); }else
* if(column.trim().contains(" ")){
* wrongField=StringUtils.substringBefore(column, " "); }else {
* wrongField=column.trim(); }
* endPosition=startPosition+wrongField.length()-1;
* errorMessage.setErrorMessage(startPosition, endPosition,
* wrongField,""); }else{ if(s.contains("AS".toLowerCase())) {
* wrongField=StringUtils.substringBefore(s,
* "As".toLowerCase()).trim(); }else {
* wrongField=StringUtils.substringBefore(s, " "); }
* endPosition=startPosition+wrongField.length()-1;
* errorMessage.setErrorMessage(startPosition, endPosition,
* wrongField,""); }
* result.setMsg(ErrorCodes.ERROR_NAME_RULE.getDesc());
* result.setCode(ErrorCodes.ERROR_NAME_RULE.getCode());
* result.setData(errorMessage); return result;
*/
if (e.getCause() instanceof ParseException)
{
ParseException parseException = (ParseException) e.getCause();
/* String message = parseException.getMessage();
String mes = StringUtils.substringBetween(message, "Encountered unexpected token: ", "at");
String wrongField = StringUtils.substringBetween(mes, "\"", "\"");
Integer end = Integer.valueOf(StringUtils.substringBetween(message, "column", ".").trim());
*/ Token token = parseException.currentToken;
errorMessage.setEndPosition(token.absoluteEnd- sql_select.length());
errorMessage.setStartPosition(token.absoluteBegin- sql_select.length());
errorMessage.setOtherField(sql);
errorMessage.setWrongField(token.toString());
result.setCode(ErrorCodes.ERROR_GRAMMAR_RULE.getCode());
result.setMsg(ErrorCodes.ERROR_GRAMMAR_RULE.getDesc());
result.setData(errorMessage);
} else
{
result.setCode(ErrorCodes.ERROR_GRAMMAR_RULE.getCode());
result.setMsg(ErrorCodes.ERROR_GRAMMAR_RULE.getDesc());
}
}
} catch (Exception e)
{
log.error("verifyField Exception", e);
result.setCode(ErrorCodes.ERROR_GRAMMAR_RULE.getCode());
result.setMsg(ErrorCodes.ERROR_GRAMMAR_RULE.getDesc());
}
return result;
}
/***
* 当return false 返回错误信息 errorInfo:(这里用一句话描述这个方法的作用). <br/>
* TODO(这里描述这个方法适用条件 – 可选).<br/>
* TODO(这里描述这个方法的执行流程 – 可选).<br/>
*
* @author hewanwan
* @param a
* @param buffer
* @param result
* @return
* @since JDK 1.8
*/
public ResultMap<ErrorMessage> errorInfo(Object a, StringBuilder buffer, ResultMap<ErrorMessage> result)
{
ErrorMessage errorMessage = new ErrorMessage();
result.setCode(ErrorCodes.ERROR_NAME_RULE.getCode());
result.setMsg(ErrorCodes.ERROR_NAME_RULE.getDesc());
Integer startPosition = buffer.length() - a.toString().length();
Integer endPosition = buffer.length();
errorMessage.setErrorMessage(startPosition, endPosition, a.toString());
result.setData(errorMessage);
return result;
}
public boolean judgEexpression(Expression expression, StringBuilder buffer,
SelectExpressionItem selectExpressionItem, ResultMap<ErrorMessage> result, Alias alias,
boolean isValidateAlias)
{
/*
* Integer startPosition = null; Integer endPosition = null;
*/
String columnName = null;
boolean flag = true;
// ErrorMessage errorMessage = new ErrorMessage();
if (expression instanceof NullValue || expression instanceof Function || expression instanceof SignedExpression
|| expression instanceof JdbcParameter || expression instanceof JdbcNamedParameter
|| expression instanceof HexValue || expression instanceof CaseExpression
|| expression instanceof WhenClause || expression instanceof DateTimeLiteralExpression
|| expression instanceof Concat || expression instanceof ExtractExpression
|| expression instanceof IntervalExpression || expression instanceof RegExpMatchOperator
|| expression instanceof NumericBind || expression instanceof KeepExpression)
{
result = errorInfo(selectExpressionItem, buffer, result);
/*
* result.setCode(ErrorCodes.ERROR_NAME_RULE.getCode());
* result.setMsg(ErrorCodes.FAILED.getDesc()); startPosition =
* buffer.length() - selectExpressionItem.toString().length();
* endPosition = buffer.length();
* errorMessage.setErrorMessage(startPosition, endPosition,
* selectExpressionItem.toString(),"");
* result.setData(errorMessage);
*/
return false;
}
if (expression instanceof DoubleValue || expression instanceof LongValue || expression instanceof DateValue
|| expression instanceof TimeValue || expression instanceof TimestampValue
|| expression instanceof StringValue)
{
if (isValidateAlias && alias == null)
{
result = errorInfo(selectExpressionItem, buffer, result);
/*
* result.setCode(ErrorCodes.ERROR_NAME_RULE.getCode());
* result.setMsg(ErrorCodes.FAILED.getDesc()); startPosition =
* buffer.length() - selectExpressionItem.toString().length();
* endPosition = buffer.length();
* errorMessage.setErrorMessage(startPosition, endPosition,
* selectExpressionItem.toString(),"");
* result.setData(errorMessage);
*/
return false;
}
} else if (expression instanceof Column)
{
Column column = (Column) expression;// 获取字段
columnName = column.getFullyQualifiedName();
// `aaaa`.`bbb.ccc`.`cvxcv`
List<String> columnNamelist = getColumnNames(columnName);
flag = isSpecialChar(columnNamelist);
if (flag == false)
{
result = errorInfo(selectExpressionItem, buffer, result);
/*
* result.setCode(ErrorCodes.ERROR_NAME_RULE.getCode());
* result.setMsg(ErrorCodes.FAILED.getDesc()); startPosition =
* buffer.length() - selectExpressionItem.toString().length();
* endPosition = buffer.length();
* errorMessage.setErrorMessage(startPosition, endPosition,
* selectExpressionItem.toString(),"");
* result.setData(errorMessage);
*/
return false;
}
} else if (expression instanceof Parenthesis)
{
Parenthesis parenthesis = (Parenthesis) expression;
if (parenthesis.isNot())
{
// TODO:需要修改
result = errorInfo(selectExpressionItem, buffer, result);
/*
* result.setCode(ErrorCodes.ERROR_NAME_RULE.getCode());
* result.setMsg(ErrorCodes.FAILED.getDesc()); startPosition =
* buffer.length() - selectExpressionItem.toString().length();
* endPosition = buffer.length();
* errorMessage.setErrorMessage(startPosition, endPosition,
* selectExpressionItem.toString(),"");
* result.setData(errorMessage);
*/
return false;
}
if (!judgEexpression(parenthesis.getExpression(), buffer, selectExpressionItem, result, alias, false))
{
return false;
}
} else if (expression instanceof Addition || expression instanceof Division || expression instanceof
Multiplication || expression instanceof Subtraction)
{
if (isValidateAlias && alias == null)
{
result = errorInfo(selectExpressionItem, buffer, result);
return false;
}
BinaryExpression binaryExpression = (BinaryExpression) expression;
if (!judgEexpression(binaryExpression.getLeftExpression(), buffer, selectExpressionItem, result,
alias, false))
{
return false;
}
if (!judgEexpression(binaryExpression.getRightExpression(), buffer, selectExpressionItem, result,
alias, false))
{
return false;
}
}
return true;
}
/**
* judgeAlias: 判断别名. <br/>
*
* @author liupingan
* @param alias
* @param buffer
* @param selectExpressionItem
* @param result
* @return
* @since JDK 1.8
*/
public boolean judgeAlias(Alias alias, StringBuilder buffer, SelectExpressionItem selectExpressionItem,
ResultMap<ErrorMessage> result)
{
List<String> columnNamelist = getColumnNames(alias.getName());
boolean flag = isSpecialChar(columnNamelist);
if (flag == false)
{
result = errorInfo(selectExpressionItem, buffer, result);
return false;
}
return true;
}
public static List<String> getColumnNames(String source)
{
if (source == null)
{
return null;
}
int start = source.indexOf("`");
if (start == -1)
{
List<String> result = new ArrayList<>();
Collections.addAll(result, source.split("\\."));
return result;
}
List<String> result = new ArrayList<>();
int end = -1;
start = 0;
int temp = 0;
String tempString = null;
for (int i = 0; i < source.split("`").length - 1; i++)
{
temp = source.indexOf("`", start);
// 如果相等，并且为第一个
if (i == 0 && start == temp)
{
end = source.indexOf("`", temp + 1);
if (end == -1)
break;
tempString = source.substring(temp + 1, end);
result.add(tempString);
start = end + 2;
} else if (i == 0 && start != temp)
{
end = source.indexOf("`", temp + 1);
// tempString = source.substring(temp+1, end);
Collections.addAll(result, source.substring(0, temp).split("\\."));
if (end == -1)
break;
tempString = source.substring(temp + 1, end);
result.add(tempString);
start = end + 2;
} else if (temp - 2 == end)
{// 如果为连续的
end = source.indexOf("`", temp + 1);
if (end == -1)
break;
tempString = source.substring(temp + 1, end);
result.add(tempString);
start = end + 2;
} else
{
Collections.addAll(result, source.substring(end + 2, temp).split("\\."));
end = source.indexOf("`", temp + 1);
if (end == -1)
break;
tempString = source.substring(temp + 1, end);
result.add(tempString);
start = end + 2;
}
i++;
}
if (end + 1 != source.length())
{
Collections.addAll(result, source.substring(end + 2).trim().split("\\."));
}
return result;
}
/** 验证where条件 */
@Override
public ResultMap<ErrorMessage> verifyCondition(String sql)
{
CCJSqlParserManager parser = new CCJSqlParserManager();
Statement stmt = null;
ResultMap<ErrorMessage> result = new ResultMap<>();
ErrorMessage errorMessage = new ErrorMessage();
sql = sql.trim();
String parseSql = sql_from_where + sql;
Expression expression = null;
try
{
stmt = parser.parse(new StringReader( parseSql));
if (stmt instanceof Select)
{
Select selectStatement = (Select) stmt;
PlainSelect selectBody = (PlainSelect) selectStatement.getSelectBody();
expression = selectBody.getWhere();
StringBuilder buffer = new StringBuilder();
if(!judgEexpression(expression, buffer, result)){
result.getData().setOtherField(expression.toString());
}
}
} catch (JSQLParserException e)
{
/*
* if (e.getCause() instanceof ParseException) { ParseException
* parseException = (ParseException) e.getCause(); String message =
* parseException.getMessage(); String start =
* StringUtils.substringBetween(message, "column ", "."); Integer
* startPosition=Integer.parseInt(start); Integer endPosition=null;
* String wrongField=null; Integer from =sql.lastIndexOf("from");
* String s=StringUtils.substring(sql, Integer.parseInt(start)-1,
* from); if(s.contains(",")) { String
* column=StringUtils.substringBefore(s, ",");
* if(column.contains("AS".toLowerCase())) {
* wrongField=StringUtils.substringBefore(column,
* "As".toLowerCase()).trim(); }else
* if(column.trim().contains(" ")){
* wrongField=StringUtils.substringBefore(column, " "); }else {
* wrongField=column.trim(); }
* endPosition=startPosition+wrongField.length()-1;
* errorMessage.setErrorMessage(startPosition, endPosition,
* wrongField,""); }else{ if(s.contains("AS".toLowerCase())) {
* wrongField=StringUtils.substringBefore(s,
* "As".toLowerCase()).trim(); }else {
* wrongField=StringUtils.substringBefore(s, " "); }
* endPosition=startPosition+wrongField.length()-1;
* errorMessage.setErrorMessage(startPosition, endPosition,
* wrongField,""); } result.setMsg(ErrorCodes.FAILED.getDesc());
* result.setCode(ErrorCodes.ERROR_NAME_RULE.getCode());
* result.setData(errorMessage); return result;
*/
log.error("verifyField Exception", e);
if (e.getCause() instanceof ParseException)
{
ParseException parseException = (ParseException) e.getCause();
/*String message = parseException.getMessage();
String mes = StringUtils.substringBetween(message, "Encountered unexpected token: ", "at");
String wrongField = StringUtils.substringBetween(mes, "\"", "\"");
Integer end = Integer.valueOf(StringUtils.substringBetween(message, "column", ".").trim());*/
Token token = parseException.currentToken;
errorMessage.setEndPosition(token.absoluteEnd- sql_from_where.length());
errorMessage.setStartPosition(token.absoluteBegin- sql_from_where.length());
/* errorMessage.setEndPosition(end - sql_from_where.length());
errorMessage.setStartPosition(end + wrongField.length() - sql_from_where.length());*/
errorMessage.setOtherField(sql.trim());
errorMessage.setWrongField(token.toString());
result.setCode(ErrorCodes.ERROR_GRAMMAR_RULE.getCode());
result.setMsg(ErrorCodes.ERROR_GRAMMAR_RULE.getDesc());
result.setData(errorMessage);
} else
{
result.setCode(ErrorCodes.ERROR_GRAMMAR_RULE.getCode());
result.setMsg(ErrorCodes.ERROR_GRAMMAR_RULE.getDesc());
}
} catch (Exception e)
{
log.error("verifyField Exception", e);
result.setCode(ErrorCodes.ERROR_GRAMMAR_RULE.getCode());
result.setMsg(ErrorCodes.ERROR_GRAMMAR_RULE.getDesc());
}
return result;
}
/**
*
* judgEexpression:(条件语句判断). <br/>
*
* @author liupingan
* @param expression
* @param buffer
* @param result
* @return
* @since JDK 1.8
*/
public boolean judgEexpression(Expression expression, StringBuilder buffer, ResultMap<ErrorMessage> result)
{
AndExpression andExpression = null;
OrExpression orExpression = null;
Modulo modulo = null;
BinaryExpression binaryExpression = null;
ComparisonOperator comparisonOperator = null;
InExpression inExpression = null;
Parenthesis parenthesis = null;
ItemsList itemsList = null;
Column column = null;
String columnName = null;
boolean flag = true;
if (expression instanceof BitwiseAnd || expression instanceof BitwiseOr || expression instanceof BitwiseXor
|| expression instanceof IsNullExpression || expression instanceof Between
|| expression instanceof NullValue || expression instanceof Function
|| expression instanceof SignedExpression || expression instanceof JdbcParameter
|| expression instanceof JdbcNamedParameter || expression instanceof HexValue
|| expression instanceof LikeExpression || expression instanceof CaseExpression
|| expression instanceof WhenClause || expression instanceof ExistsExpression
|| expression instanceof AllComparisonExpression || expression instanceof AnyComparisonExpression
|| expression instanceof Concat || expression instanceof Matches
|| expression instanceof AnalyticExpression || expression instanceof CastExpression
|| expression instanceof ExtractExpression || expression instanceof IntervalExpression
|| expression instanceof OracleHierarchicalExpression || expression instanceof RegExpMatchOperator
|| expression instanceof JsonExpression || expression instanceof RegExpMySQLOperator
|| expression instanceof NumericBind || expression instanceof KeepExpression
|| expression instanceof MySQLGroupConcat || expression instanceof RowConstructor
|| expression instanceof OracleHint || expression instanceof TimeKeyExpression
|| expression instanceof DateTimeLiteralExpression)
{
buffer.append(expression);
result = errorInfo(expression, buffer, result);
return false;
}
if (expression instanceof AndExpression)
{
andExpression = (AndExpression) expression;
if (andExpression.isNot())
{
return false;
}
if (!judgEexpression(andExpression.getLeftExpression(), buffer, result))
{
return false;
}
buffer.append(" and ");
if (!judgEexpression(andExpression.getRightExpression(), buffer, result))
{
return false;
}
} else if (expression instanceof OrExpression)
{
orExpression = (OrExpression) expression;
if (orExpression.isNot())
{
return false;
}
if (!judgEexpression(orExpression.getLeftExpression(), buffer, result))
{
return false;
}
buffer.append(" or ");
if (!judgEexpression(orExpression.getRightExpression(), buffer, result))
{
return false;
}
} else if (expression instanceof Parenthesis)
{
parenthesis = (Parenthesis) expression;
if (parenthesis.isNot())
{
buffer.append(expression.toString());
result = errorInfo(expression, buffer, result);
return false;
}
buffer.append("(");
if (!judgEexpression(parenthesis.getExpression(), buffer, result))
{
buffer.append(")");
return false;
} else
{
buffer.append(")");
return true;
}
} else if (expression instanceof Column)
{
column = (Column) expression;
columnName = column.getFullyQualifiedName();
buffer.append(columnName);
List<String> namelist = VerifySqlLegitimacyServiceImpl.getColumnNames(columnName);
flag = isSpecialChar(namelist);
if (flag == false)
{
result = errorInfo(expression, buffer, result);
return false;
}
} else if (expression instanceof Modulo)
{
// % 取模处理
modulo = (Modulo) expression;
if (modulo.isNot())
{
return false;
}
if (!judgEexpression(modulo.getLeftExpression(), buffer, result))
{
return false;
}
buffer.append(" % ");
if (!judgEexpression(modulo.getRightExpression(), buffer, result))
{
return false;
}
} else if (expression instanceof EqualsTo || expression instanceof GreaterThan
|| expression instanceof GreaterThanEquals || expression instanceof MinorThan
|| expression instanceof MinorThanEquals || expression instanceof NotEqualsTo)
{
// > >= = < <= != <>处理
comparisonOperator = (ComparisonOperator) expression;
if (comparisonOperator.isNot())
{
return false;
}
if (!judgEexpression(comparisonOperator.getLeftExpression(), buffer, result))
{
return false;
}
buffer.append(" "+comparisonOperator.getStringExpression()+" ");
if (!judgEexpression(comparisonOperator.getRightExpression(), buffer, result))
{
return false;
}
} else if (expression instanceof Addition || expression instanceof Division
|| expression instanceof Multiplication || expression instanceof Subtraction)
{
// ( + - * / )加减乘除处理
binaryExpression = (BinaryExpression) expression;
if (binaryExpression.isNot())
{
return false;
}
if (!judgEexpression(binaryExpression.getLeftExpression(), buffer, result))
{
return false;
}
buffer.append(" "+binaryExpression.getStringExpression()+" ");
if (!judgEexpression(binaryExpression.getRightExpression(), buffer, result))
{
return false;
}
} else if (expression instanceof InExpression)
{
// ( + - * / )加减乘除处理
inExpression = (InExpression) expression;
// itemsList = inExpression.getRightItemsList();
ExpressionList expressionList = (ExpressionList) inExpression.getRightItemsList();
if (inExpression.isNot())
{
buffer.append(expression.toString());
result = errorInfo(expression, buffer, result);
return false;
} else if (inExpression.getLeftExpression() == null)
{
result = errorInfo(expression, buffer, result);
return false;
}
if (!judgEexpression(inExpression.getLeftExpression(), buffer, result))
{
//buffer.append(expression.toString());
result = errorInfo(expression, buffer, result);
return false;
}
buffer.append(" IN (");
if (expressionList == null)
{
result = errorInfo(expression, buffer, result);
return false;
}
List<Expression> expressionslist = (List<Expression>) expressionList.getExpressions();
for (int i = 0; i < expressionslist.size(); i++)
{
if( i !=0){
buffer.append(", ");
}
if (!judgEexpression(expressionslist.get(i), buffer, result))
{
return false;
}
}
buffer.append(")");
if (itemsList instanceof SubSelect)
{
result = errorInfo(expression, buffer, result);
return false;
}
} else if (expression instanceof DoubleValue || expression instanceof LongValue
|| expression instanceof DateValue || expression instanceof TimeValue
|| expression instanceof TimestampValue)
{
buffer.append(expression.toString());
return true;
} else if(expression instanceof StringValue){
buffer.append("\'"+expression.toString()+"\'");
return true;
}
return true;
}
/***
* 由数字、26个英文字母或者下划线或者.组成的字符串,不能以下划线和.为开头和结尾 不能出现两次下划线
*
* @return true为包含，false为不包含
*/
public boolean isSpecialChar(String str)
{
String regEx = "^(?!_)(?!.*?_$)([a-zA-Z0-9.]|_(?!_))+$";
boolean flag = str.matches(regEx);
return flag;
}
public boolean isSpecialChar(List<String> strs)
{
boolean flag = true;
for (String name : strs)
{
if (!isSpecialChar(name))
{
return false;
}
}
return flag;
}
@Override
public ResultMap<ErrorMessage> verifyField(InputMap<RuleCheckDto> bean)
{
ResultMap<ErrorMessage> result = new ResultMap<>();
if (bean == null || bean.getConfig() == null || bean.getConfig().getUserId() == null
|| StringUtils.isBlank(bean.getConfig().getUserId()) || bean.getData() == null
|| bean.getData().getFields() == null || StringUtils.isBlank(bean.getData().getFields()))
{
result.setCode(ErrorCodes.FAILED.getCode());
result.setMsg(ErrorCodes.FAILED.getDesc());
return result;
}
return verifyField(bean.getData().getFields());
}
@Override
public ResultMap<ErrorMessage> verifyCondition(InputMap<RuleCheckDto> bean)
{
ResultMap<ErrorMessage> result = new ResultMap<>();
if (bean == null || bean.getConfig() == null || bean.getConfig().getUserId() == null
|| StringUtils.isBlank(bean.getConfig().getUserId()) || bean.getData() == null
|| bean.getData().getFields() == null || StringUtils.isBlank(bean.getData().getCondition()))
{
result.setCode(ErrorCodes.FAILED.getCode());
result.setMsg(ErrorCodes.FAILED.getDesc());
return result;
}
return verifyCondition(bean.getData().getCondition());
}
/**
*
* TODO 如果字段存在，则需要判断，如果条件不存在，则为真
* @see com.foxconn.core.pro.server.rule.engine.front.service.VerifySqlLegitimacyService#verifySql(java.lang.String, java.lang.String)
*/
@Override
public ResultMap<ErrorMessage> verifySql(String field, String condition)
{
ResultMap<ErrorMessage> result = new ResultMap<>();
if (field == null || StringUtils.isBlank(field))
{
result.setCode(ErrorCodes.FAILED.getCode());
result.setMsg(ErrorCodes.FAILED.getDesc());
return result;
}
result = verifyField(field);
if (result == null)
{
result = new ResultMap<>(ErrorCodes.FAILED);
} else if (CommonConstant.SERVICE_SUCCESS.equals(result.getCode()))
{
if(condition == null || StringUtils.isBlank(condition)){
return result;
}
result = verifyCondition(condition);
if (result == null)
{
result = new ResultMap<>(ErrorCodes.FAILED);
}
}
return result;
}
}
1036
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
