From 8ab2ad5580212b91df848e4c127f2a682485fde3 Mon Sep 17 00:00:00 2001
From: zhouxiang <zhouxiang>
Date: 星期一, 25 四月 2022 01:49:15 +0800
Subject: [PATCH] 修改common
---
src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java | 230 +++++++++++++++++++++++++++++++++------------------------
1 files changed, 134 insertions(+), 96 deletions(-)
diff --git a/src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java b/src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java
index a2ad79e..e92cd8c 100644
--- a/src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java
+++ b/src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java
@@ -2,11 +2,9 @@
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
+import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.SQLStatement;
-import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
-import com.alibaba.druid.sql.ast.expr.SQLInListExpr;
import com.alibaba.druid.sql.ast.statement.*;
-import com.alibaba.druid.sql.dialect.mysql.ast.clause.MySqlSelectIntoStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlDeleteStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement;
@@ -17,7 +15,10 @@
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.druid.util.JdbcUtils;
import com.hx.util.StringUtils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
@@ -27,6 +28,9 @@
* @author CJH 2022-01-12
*/
public class SqlUtils {
+ //log4j鏃ュ織
+ private static Logger logger = LoggerFactory.getLogger(SqlUtils.class.getName());
+
/**鏌ヨ鍔犲瘑鏁版嵁澶勭悊锛屽彧瀵规煡璇㈠仛澶勭悊锛宻elect杩斿洖涓嶅仛澶勭悊
* @param sql sql璇彞
@@ -40,10 +44,24 @@
//鑾峰彇鏍煎紡鍖栫殑slq璇彞
sql = sqlStatement.toString();
+
+
//瑙ｆ瀽select鏌ヨ
//SQLSelect sqlSelect = sqlStatement.getSelect()
//鑾峰彇sql鏌ヨ鍧�
- SQLSelectQueryBlock sqlSelectQuery = (SQLSelectQueryBlock)sqlStatement.getSelect().getQuery() ;
+ SQLSelectQueryBlock sqlSelectQuery = null;
+ boolean b = true;
+ try{
+ sqlSelectQuery = (SQLSelectQueryBlock)sqlStatement.getSelect().getQuery() ;
+ }catch (Exception e){
+ b = false;
+ logger.error("瑙ｆ瀽sql鎶ラ敊锛�"+e.getMessage());
+ }
+
+ if(!b){
+ return "err";
+ }
+
StringBuffer out = new StringBuffer() ;
//鍒涘缓sql瑙ｆ瀽鐨勬爣鍑嗗寲杈撳嚭
SQLASTOutputVisitor sqlastOutputVisitor = SQLUtils.createFormatOutputVisitor(out , null , JdbcUtils.MYSQL) ;
@@ -69,31 +87,37 @@
if(sqlSelect.length() > 7){
sqlSelect.append(",");
}
- expr = sqlSelectItem.getExpr().toString();
- if(expr.indexOf("SELECT") == -1){
+
+ out.delete(0, out.length()) ;
+ sqlSelectItem.accept(sqlastOutputVisitor) ;
+ expr = out.toString();
+ sqlSelect.append(expr);
+
+ /* if(expr.indexOf("SELECT") == -1){
sqlSelect.append(expr);
- if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
- sqlSelect.append(" AS "+sqlSelectItem.getAlias());
- }
}else{
- sqlSelect.append("(");
- sqlSelect.append(selectSqlHandle(expr,aesKeysTable,tableMaps,columns));
- sqlSelect.append(")");
- if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
+ //sqlSelect.append("(");
+ sqlSelect.append(expr);
+ //sqlSelect.append(")");
+ *//* if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
sqlSelect.append(" AS "+sqlSelectItem.getAlias());
- }
- }
+ }*//*
+ }*/
}
//瑙ｆ瀽from
- out.delete(0, out.length()) ;
- sqlSelectQuery.getFrom().accept(sqlastOutputVisitor) ;
- sqlWhere.append(" FROM "+out);
+ if(sqlSelectQuery.getFrom() != null){
+ out.delete(0, out.length()) ;
+ sqlSelectQuery.getFrom().accept(sqlastOutputVisitor) ;
+ sqlWhere.append(" FROM "+out);
+ }
//瑙ｆ瀽where
- out.delete(0, out.length()) ;
- sqlSelectQuery.getWhere().accept(sqlastOutputVisitor) ;
- sqlWhere.append(" WHERE "+out+" ");
+ if(sqlSelectQuery.getWhere() != null){
+ out.delete(0, out.length()) ;
+ sqlSelectQuery.getWhere().accept(sqlastOutputVisitor) ;
+ sqlWhere.append(" WHERE "+out+" ");
+ }
if(sqlSelectQuery.getGroupBy() != null){
out.delete(0, out.length()) ;
@@ -133,12 +157,20 @@
}else{
tableAl = column.getName();
}
- sql = sql.replaceAll("( |\\n|\\()"+tableAl+"( |\\n|\\))"," AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"') ");
+ sql = sql.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
}
}
return sqlSelect.toString()+sql;
}
+ /**
+ * 澶勭悊select杩斿洖瀛楁鐨勫弬鏁�
+ * @param sql
+ * @param aesKeysTable
+ * @param tableMaps
+ * @param columns
+ * @return
+ */
public static String selectSqlHandle(String sql,Map<String,Map<String,String>> aesKeysTable
,Map<String,String> tableMaps,Collection<TableStat.Column> columns){
@@ -183,14 +215,18 @@
}
//瑙ｆ瀽from
- out.delete(0, out.length()) ;
- sqlSelectQuery.getFrom().accept(sqlastOutputVisitor) ;
- sqlWhere.append(" FROM "+out);
+ if(sqlSelectQuery.getFrom() != null){
+ out.delete(0, out.length()) ;
+ sqlSelectQuery.getFrom().accept(sqlastOutputVisitor) ;
+ sqlWhere.append(" FROM "+out);
+ }
//瑙ｆ瀽where
- out.delete(0, out.length()) ;
- sqlSelectQuery.getWhere().accept(sqlastOutputVisitor) ;
- sqlWhere.append(" WHERE "+out+" ");
+ if(sqlSelectQuery.getWhere() != null){
+ out.delete(0, out.length()) ;
+ sqlSelectQuery.getWhere().accept(sqlastOutputVisitor) ;
+ sqlWhere.append(" WHERE "+out+" ");
+ }
if(sqlSelectQuery.getGroupBy() != null){
out.delete(0, out.length()) ;
@@ -230,7 +266,7 @@
}else{
tableAl = column.getName();
}
- sql = sql.replaceAll("( |\\n|\\()"+tableAl+"( |\\n|\\))"," AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"') ");
+ sql = sql.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
}
}
return sqlSelect.toString()+sql;
@@ -241,72 +277,72 @@
* @param aesKeysTable aes绉橀挜
* @return
*/
- public static String insertSql(String sql,Map<String,Map<String,String>> aesKeysTable){
- //瑁呰浇閲嶅啓鐨剆ql璇彞
- StringBuilder splicingSql = new StringBuilder();
+ public static String insertSql(String sql,Map<String,Map<String,String>> aesKeysTable){
+ //瑁呰浇閲嶅啓鐨剆ql璇彞
+ StringBuilder splicingSql = new StringBuilder();
- sql = SQLUtils.format(sql, JdbcConstants.MYSQL);
- String[] datas = sql.split("VALUES",2);
+ sql = SQLUtils.format(sql, JdbcConstants.MYSQL);
+ String[] datas = sql.split("VALUES",2);
- splicingSql.append(datas[0]+"VALUES ");
+ splicingSql.append(datas[0]+"VALUES ");
- //閲嶆柊鎷兼帴SQL璇彞
+ //閲嶆柊鎷兼帴SQL璇彞
- //瑙ｆ瀽sql璇彞
- MySqlStatementParser parser = new MySqlStatementParser(sql);
- SQLStatement statement = parser.parseStatement();
- MySqlInsertStatement insert = (MySqlInsertStatement)statement;
+ //瑙ｆ瀽sql璇彞
+ MySqlStatementParser parser = new MySqlStatementParser(sql);
+ SQLStatement statement = parser.parseStatement();
+ MySqlInsertStatement insert = (MySqlInsertStatement)statement;
- String insertName = insert.getTableName().getSimpleName();
+ String insertName = insert.getTableName().getSimpleName();
- //鏍规嵁琛ㄥ悕绉拌幏鍙栧埌AES绉橀挜
- Map<String,String> aesKeys= aesKeysTable.get(insertName);
- if(aesKeys == null){
- return sql;
- }
+ //鏍规嵁琛ㄥ悕绉拌幏鍙栧埌AES绉橀挜
+ Map<String,String> aesKeys= aesKeysTable.get(insertName);
+ if(aesKeys == null){
+ return sql;
+ }
- //鑾峰彇鎵�鏈夌殑瀛楁
- List<SQLExpr> columns = insert.getColumns();
+ //鑾峰彇鎵�鏈夌殑瀛楁
+ List<SQLExpr> columns = insert.getColumns();
- String fildValue = null;
- String aeskey = null;
- //閬嶅巻鍊�
- List<SQLInsertStatement.ValuesClause> vcl = insert.getValuesList();
- for(int j = 0; j<vcl.size(); j++){
- if( j != 0){
- splicingSql.append(",");
- }
- for(int i = 0;i < columns.size();i++){
- //鏌ヨ鏀瑰瓧娈垫槸鍚﹂渶瑕佸姞瀵�
- aeskey = aesKeys.getOrDefault(columns.get(i).toString(),null);
- fildValue = vcl.get(j).getValues().get(i).toString();
- if(i == 0){
- splicingSql.append("(");
- if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
- splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
- }else{
- splicingSql.append(fildValue);
- }
- }else if(i == columns.size()-1){
- splicingSql.append(",");
- if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
- splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
- }else{
- splicingSql.append(fildValue);
- }
- splicingSql.append(")");
- }else{
- splicingSql.append(",");
- if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
- splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
- }else{
- splicingSql.append(fildValue);
- }
- }
- }
- }
- return splicingSql.toString();
- }
+ String fildValue = null;
+ String aeskey = null;
+ //閬嶅巻鍊�
+ List<SQLInsertStatement.ValuesClause> vcl = insert.getValuesList();
+ for(int j = 0; j<vcl.size(); j++){
+ if( j != 0){
+ splicingSql.append(",");
+ }
+ for(int i = 0;i < columns.size();i++){
+ //鏌ヨ鏀瑰瓧娈垫槸鍚﹂渶瑕佸姞瀵�
+ aeskey = aesKeys.getOrDefault(columns.get(i).toString(),null);
+ fildValue = vcl.get(j).getValues().get(i).toString();
+ if(i == 0){
+ splicingSql.append("(");
+ if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
+ splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
+ }else{
+ splicingSql.append(fildValue);
+ }
+ }else if(i == columns.size()-1){
+ splicingSql.append(",");
+ if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
+ splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
+ }else{
+ splicingSql.append(fildValue);
+ }
+ splicingSql.append(")");
+ }else{
+ splicingSql.append(",");
+ if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
+ splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
+ }else{
+ splicingSql.append(fildValue);
+ }
+ }
+ }
+ }
+ return splicingSql.toString();
+ }
/**鏇存柊鍔犲瘑鏁版嵁澶勭悊
* @param sql sql璇彞
@@ -314,6 +350,7 @@
* @return
*/
public static String updateSql(String sql,Map<String,Map<String,String>> aesKeysTable){
+
//瑁呰浇閲嶅啓鐨剆ql璇彞
StringBuilder splicingSql = new StringBuilder();
@@ -323,7 +360,6 @@
//鑾峰彇鏍煎紡鍖栫殑slq璇彞
sql = sqlStatement.toString();
-
MySqlUpdateStatement updateStatement = (MySqlUpdateStatement)sqlStatement;
String insertName = updateStatement.getTableName().getSimpleName();
@@ -331,9 +367,11 @@
String[] datas = sql.split("WHERE",2);
Map<String,String> aesKeys = aesKeysTable.get(insertName);
+ if(aesKeys == null){
+ return sql;
+ }
splicingSql.append("UPDATE "+insertName+" SET ");
-
String aeskey = null;
String fildValue = null;
List<SQLUpdateSetItem> items = updateStatement.getItems();
@@ -341,9 +379,7 @@
if(i != 0){
splicingSql.append(",");
}
-
SQLUpdateSetItem item = items.get(i);
-
//鏌ヨ鏀瑰瓧娈垫槸鍚﹂渶瑕佸姞瀵�
aeskey = aesKeys.getOrDefault(item.getColumn().toString(),null);
@@ -354,7 +390,6 @@
splicingSql.append(item.getColumn()+" = "+fildValue);
}
}
-
String sqlWhere = " WHERE";
//鎶婂墿涓嬬殑鎷兼帴涓婃潵
if(datas.length > 1){
@@ -395,11 +430,11 @@
}else{
tableAl = column.getName();
}
- sqlWhere = sqlWhere.replaceAll("( |\\n|\\()"+tableAl+"( |\\n|\\))"," AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"') ");
+ sqlWhere = sqlWhere.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
}
}
- splicingSql.append(sqlWhere.toString());
+ splicingSql.append(sqlWhere);
return splicingSql.toString();
}
@@ -425,6 +460,9 @@
String[] datas = sql.split("WHERE",2);
Map<String,String> aesKeys = aesKeysTable.get(insertName);
+ if(aesKeys == null){
+ return sql;
+ }
splicingSql.append("DELETE FROM "+insertName);
@@ -470,11 +508,11 @@
}else{
tableAl = column.getName();
}
- sqlWhere = sqlWhere.replaceAll("( |\\n|\\()"+tableAl+"( |\\n|\\))"," AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"') ");
+ sqlWhere = sqlWhere.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
}
}
- splicingSql.append(sqlWhere.toString());
+ splicingSql.append(sqlWhere);
return splicingSql.toString();
}
--
Gitblit v1.8.0