From 220da5159c5ce32f9096e02316b4c9019b09dba7 Mon Sep 17 00:00:00 2001
From: fhx <308050795@qq.com>
Date: 星期一, 06 十一月 2023 17:27:15 +0800
Subject: [PATCH] 1.新增工具类
---
src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java | 134 +++++++++++++++++++++++++++++++++++++-------
1 files changed, 112 insertions(+), 22 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 9281129..8d978db 100644
--- a/src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java
+++ b/src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java
@@ -14,6 +14,8 @@
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.Collection;
import java.util.List;
@@ -24,8 +26,11 @@
* @author CJH 2022-01-12
*/
public class SqlUtils {
+ //log4j鏃ュ織
+ private static Logger logger = LoggerFactory.getLogger(SqlUtils.class.getName());
- /**鏌ヨ鍔犲瘑鏁版嵁澶勭悊锛屽彧瀵规煡璇㈠仛澶勭悊锛宻elect杩斿洖涓嶅仛澶勭悊
+
+ /**鏌ヨ鍔犲瘑鏁版嵁澶勭悊锛屽彧瀵规煡璇㈠仛澶勭悊
* @param sql sql璇彞
* @param aesKeysTable aes绉橀挜
* @return
@@ -34,13 +39,88 @@
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLSelectStatement sqlStatement = (SQLSelectStatement) parser.parseSelect();
- //鑾峰彇鏍煎紡鍖栫殑slq璇彞
- sql = sqlStatement.toString();
+
+ SQLSelect sqlSelect = sqlStatement.getSelect();
+ if (sqlSelect.getQuery() instanceof SQLSelectQueryBlock) {
+ // 闈瀠nion鐨勬煡璇㈣鍙�
+ return selectSqlRoutine( sqlStatement,aesKeysTable);
+ } else if (sqlSelect.getQuery() instanceof SQLUnionQuery) {
+ // union鐨勬煡璇㈣鍙�
+ return selectSqlUnion( sql, sqlStatement, aesKeysTable);
+ }else {
+ return selectSqlRoutine( sqlStatement,aesKeysTable);
+ }
+ }
+
+ /**鏌ヨ鍔犲瘑鏁版嵁澶勭悊锛屽彧瀵规煡璇㈠仛澶勭悊锛宻elect杩斿洖涓嶅仛澶勭悊锛圲nion鐗规畩璇彞锛�
+ * @param sql sql璇彞
+ * @param aesKeysTable aes绉橀挜
+ * @return
+ */
+ public static String selectSqlUnion(String sql,SQLSelectStatement sqlStatement,Map<String,Map<String,String>> aesKeysTable){
+
+ //鑾峰彇琛ㄥ拰鍒悕
+ ExportTableAliasVisitor visitorTable = new ExportTableAliasVisitor();
+ sqlStatement.accept(visitorTable);
+ Map<String,String> tableMaps = visitorTable.getTableMap();
+
+ //鑾峰彇鎵�鏈夌殑瀛楁
+ MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
+ sqlStatement.accept(visitor);
+ //閬嶅巻鎵�鏈夊瓧娈�
+ Collection<TableStat.Column> columns= visitor.getColumns();
+
+ //澶勭悊闇�瑕佸姞瀵嗗緱瀛楁
+
+ if(!StringUtils.isEmpty(sql)){
+ Map<String,String> aesKeys = null;
+ String aeskey = null;
+ //鎶婂墿涓嬬殑鎷兼帴涓婃潵
+ String tableAl = null;
+ for(TableStat.Column column:columns){
+ aesKeys= aesKeysTable.get(column.getTable());
+ if(aesKeys == null){
+ continue;
+ }
+ aeskey = aesKeys.getOrDefault(column.getName(),null);
+ if(StringUtils.isEmpty(aeskey)){
+ continue;
+ }
+ tableAl = tableMaps.get(column.getTable());
+ if(!StringUtils.isEmpty(tableAl)){
+ tableAl = tableAl+"."+column.getName();
+ }else{
+ tableAl = column.getName();
+ }
+ sql = sql.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
+ }
+ }
+ return sql;
+ }
+
+
+ /**鏌ヨ鍔犲瘑鏁版嵁澶勭悊锛屽彧瀵规煡璇㈠仛澶勭悊锛宻elect杩斿洖涓嶅仛澶勭悊锛堝父瑙勮鍙ワ級
+ * @param sqlStatement sql璇彞
+ * @param aesKeysTable aes绉橀挜
+ * @return
+ */
+ public static String selectSqlRoutine(SQLSelectStatement sqlStatement,Map<String,Map<String,String>> aesKeysTable){
//瑙ｆ瀽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) ;
@@ -66,20 +146,22 @@
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
@@ -113,7 +195,7 @@
}
//澶勭悊where闇�瑕佸姞瀵嗗緱瀛楁
- sql = sqlWhere.toString();
+ String sql = sqlWhere.toString();
if(!StringUtils.isEmpty(sql)){
Map<String,String> aesKeys = null;
String aeskey = null;
@@ -134,12 +216,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){
@@ -235,7 +325,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;
@@ -399,11 +489,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();
}
@@ -477,11 +567,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