From 346eb22b5ab622064d1f61819240656529800f2b Mon Sep 17 00:00:00 2001 From: chenjiahe <763432473@qq.com> Date: 星期五, 14 一月 2022 13:05:53 +0800 Subject: [PATCH] Mysql数据库AES加密工具 --- src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java | 163 ++++++++++++++++++++++++++++-------------------------- 1 files changed, 85 insertions(+), 78 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..9281129 100644 --- a/src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java +++ b/src/main/java/com/hx/mybatis/aes/springbean/SqlUtils.java @@ -3,10 +3,7 @@ import com.alibaba.druid.sql.SQLUtils; import com.alibaba.druid.sql.ast.SQLExpr; 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; @@ -86,14 +83,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()) ; @@ -183,14 +184,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()) ; @@ -241,72 +246,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 +319,7 @@ * @return */ public static String updateSql(String sql,Map<String,Map<String,String>> aesKeysTable){ + //瑁呰浇閲嶅啓鐨剆ql璇彞 StringBuilder splicingSql = new StringBuilder(); @@ -323,7 +329,6 @@ //鑾峰彇鏍煎紡鍖栫殑slq璇彞 sql = sqlStatement.toString(); - MySqlUpdateStatement updateStatement = (MySqlUpdateStatement)sqlStatement; String insertName = updateStatement.getTableName().getSimpleName(); @@ -331,9 +336,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 +348,7 @@ if(i != 0){ splicingSql.append(","); } - SQLUpdateSetItem item = items.get(i); - //鏌ヨ鏀瑰瓧娈垫槸鍚﹂渶瑕佸姞瀵� aeskey = aesKeys.getOrDefault(item.getColumn().toString(),null); @@ -354,7 +359,6 @@ splicingSql.append(item.getColumn()+" = "+fildValue); } } - String sqlWhere = " WHERE"; //鎶婂墿涓嬬殑鎷兼帴涓婃潵 if(datas.length > 1){ @@ -425,6 +429,9 @@ String[] datas = sql.split("WHERE",2); Map<String,String> aesKeys = aesKeysTable.get(insertName); + if(aesKeys == null){ + return sql; + } splicingSql.append("DELETE FROM "+insertName); -- Gitblit v1.8.0