From fb2c9fa355cc2e09bc051677dba89f86e9c0bd00 Mon Sep 17 00:00:00 2001 From: chenjiahe <763432473@qq.com> Date: 星期一, 25 四月 2022 17:45:21 +0800 Subject: [PATCH] 修复MYSQL查询语句不兼容问题 --- src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java | 282 +++++++++++++++++++++++++++++++++++++------------------- 1 files changed, 185 insertions(+), 97 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..f872b42 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,8 +28,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 @@ -37,13 +41,77 @@ MySqlStatementParser parser = new MySqlStatementParser(sql); SQLSelectStatement sqlStatement = (SQLSelectStatement) parser.parseSelect(); + + //鑾峰彇琛ㄥ拰鍒悕 + 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 sql sql璇彞 + * @param aesKeysTable aes绉橀挜 + * @return + */ + public static String selectSqlDemo(String sql,Map<String,Map<String,String>> aesKeysTable){ + + MySqlStatementParser parser = new MySqlStatementParser(sql); + SQLSelectStatement sqlStatement = (SQLSelectStatement) parser.parseSelect(); //鑾峰彇鏍煎紡鍖栫殑slq璇彞 sql = sqlStatement.toString(); + + //瑙f瀽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("瑙f瀽sql鎶ラ敊锛�"+e.getMessage()); + } + + if(!b){ + return "err"; + } + StringBuffer out = new StringBuffer() ; //鍒涘缓sql瑙f瀽鐨勬爣鍑嗗寲杈撳嚭 SQLASTOutputVisitor sqlastOutputVisitor = SQLUtils.createFormatOutputVisitor(out , null , JdbcUtils.MYSQL) ; @@ -69,31 +137,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()); - } - } + }*//* + }*/ } //瑙f瀽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); + } //瑙f瀽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 +207,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 +265,18 @@ } //瑙f瀽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); + } //瑙f瀽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 +316,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 +327,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璇彞 - //瑙f瀽sql璇彞 - MySqlStatementParser parser = new MySqlStatementParser(sql); - SQLStatement statement = parser.parseStatement(); - MySqlInsertStatement insert = (MySqlInsertStatement)statement; + //瑙f瀽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 +400,7 @@ * @return */ public static String updateSql(String sql,Map<String,Map<String,String>> aesKeysTable){ + //瑁呰浇閲嶅啓鐨剆ql璇彞 StringBuilder splicingSql = new StringBuilder(); @@ -323,7 +410,6 @@ //鑾峰彇鏍煎紡鍖栫殑slq璇彞 sql = sqlStatement.toString(); - MySqlUpdateStatement updateStatement = (MySqlUpdateStatement)sqlStatement; String insertName = updateStatement.getTableName().getSimpleName(); @@ -331,9 +417,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 +429,7 @@ if(i != 0){ splicingSql.append(","); } - SQLUpdateSetItem item = items.get(i); - //鏌ヨ鏀瑰瓧娈垫槸鍚﹂渶瑕佸姞瀵� aeskey = aesKeys.getOrDefault(item.getColumn().toString(),null); @@ -354,7 +440,6 @@ splicingSql.append(item.getColumn()+" = "+fildValue); } } - String sqlWhere = " WHERE"; //鎶婂墿涓嬬殑鎷兼帴涓婃潵 if(datas.length > 1){ @@ -395,11 +480,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 +510,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 +558,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