fhx
2023-05-12 7381f42dbec55951a0db5125d30886d1bdcad6a1
提交 | 用户 | age
c64e12 1 package com.hx.mybatis.aes.springbean;
C 2
3 import com.alibaba.druid.sql.SQLUtils;
4 import com.alibaba.druid.sql.ast.SQLExpr;
5 import com.alibaba.druid.sql.ast.SQLStatement;
6 import com.alibaba.druid.sql.ast.statement.*;
7 import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlDeleteStatement;
8 import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
9 import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement;
10 import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
11 import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
12 import com.alibaba.druid.sql.visitor.SQLASTOutputVisitor;
13 import com.alibaba.druid.stat.TableStat;
14 import com.alibaba.druid.util.JdbcConstants;
15 import com.alibaba.druid.util.JdbcUtils;
16 import com.hx.util.StringUtils;
72586e 17 import org.slf4j.Logger;
Z 18 import org.slf4j.LoggerFactory;
c64e12 19
C 20 import java.util.Collection;
21 import java.util.List;
22 import java.util.Map;
23
24 /**
25  * sql语句处理工具
26  * @author CJH 2022-01-12
27  */
28 public class SqlUtils {
72586e 29     //log4j日志
Z 30     private static Logger logger = LoggerFactory.getLogger(SqlUtils.class.getName());
31
c64e12 32
fb2c9f 33     /**查询加密数据处理,只对查询做处理
c64e12 34      * @param sql sql语句
C 35      * @param aesKeysTable aes秘钥
36      * @return
37      */
38     public static String selectSql(String sql,Map<String,Map<String,String>> aesKeysTable){
39
40         MySqlStatementParser parser = new MySqlStatementParser(sql);
41         SQLSelectStatement sqlStatement = (SQLSelectStatement) parser.parseSelect();
42c9e1 42
C 43         SQLSelect sqlSelect = sqlStatement.getSelect();
44         if (sqlSelect.getQuery() instanceof SQLSelectQueryBlock) {
45             // 非union的查询语句
46             return selectSqlRoutine( sqlStatement,aesKeysTable);
47         } else if (sqlSelect.getQuery() instanceof SQLUnionQuery) {
48             // union的查询语句
49             return selectSqlUnion( sql, sqlStatement, aesKeysTable);
50         }else {
51             return selectSqlRoutine( sqlStatement,aesKeysTable);
52         }
53     }
54
55     /**查询加密数据处理,只对查询做处理,select返回不做处理(Union特殊语句)
56      * @param sql sql语句
57      * @param aesKeysTable aes秘钥
58      * @return
59      */
60     public static String selectSqlUnion(String sql,SQLSelectStatement sqlStatement,Map<String,Map<String,String>> aesKeysTable){
fb2c9f 61
C 62         //获取表和别名
63         ExportTableAliasVisitor visitorTable = new ExportTableAliasVisitor();
64         sqlStatement.accept(visitorTable);
65         Map<String,String> tableMaps = visitorTable.getTableMap();
66
67         //获取所有的字段
68         MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
69         sqlStatement.accept(visitor);
70         //遍历所有字段
71         Collection<TableStat.Column> columns= visitor.getColumns();
72
73         //处理需要加密得字段
74
75         if(!StringUtils.isEmpty(sql)){
76             Map<String,String> aesKeys = null;
77             String aeskey = null;
78             //把剩下的拼接上来
79             String tableAl = null;
80             for(TableStat.Column column:columns){
81                 aesKeys= aesKeysTable.get(column.getTable());
82                 if(aesKeys == null){
83                     continue;
84                 }
85                 aeskey = aesKeys.getOrDefault(column.getName(),null);
86                 if(StringUtils.isEmpty(aeskey)){
87                     continue;
88                 }
89                 tableAl = tableMaps.get(column.getTable());
90                 if(!StringUtils.isEmpty(tableAl)){
91                     tableAl = tableAl+"."+column.getName();
92                 }else{
93                     tableAl = column.getName();
94                 }
95                 sql = sql.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
96             }
97         }
98         return sql;
99     }
100
101
42c9e1 102     /**查询加密数据处理,只对查询做处理,select返回不做处理(常规语句)
C 103      * @param sqlStatement sql语句
fb2c9f 104      * @param aesKeysTable aes秘钥
C 105      * @return
106      */
42c9e1 107     public static String selectSqlRoutine(SQLSelectStatement sqlStatement,Map<String,Map<String,String>> aesKeysTable){
72586e 108
c64e12 109         //解析select查询
C 110         //SQLSelect sqlSelect = sqlStatement.getSelect()
111         //获取sql查询块
72586e 112         SQLSelectQueryBlock sqlSelectQuery = null;
Z 113         boolean b = true;
326104 114         try{
Z 115             sqlSelectQuery = (SQLSelectQueryBlock)sqlStatement.getSelect().getQuery() ;
116         }catch (Exception e){
72586e 117             b = false;
8ab2ad 118             logger.error("解析sql报错:"+e.getMessage());
72586e 119         }
Z 120         if(!b){
121             return "err";
326104 122         }
Z 123
c64e12 124         StringBuffer out = new StringBuffer() ;
C 125         //创建sql解析的标准化输出
126         SQLASTOutputVisitor sqlastOutputVisitor = SQLUtils.createFormatOutputVisitor(out , null , JdbcUtils.MYSQL) ;
127
128         //获取表和别名
129         ExportTableAliasVisitor visitorTable = new ExportTableAliasVisitor();
130         sqlStatement.accept(visitorTable);
131         Map<String,String> tableMaps = visitorTable.getTableMap();
132
133         //获取所有的字段
134         MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
135         sqlStatement.accept(visitor);
136         //遍历所有字段
137         Collection<TableStat.Column> columns= visitor.getColumns();
138
139         StringBuilder sqlWhere = new StringBuilder();
140
141         StringBuilder sqlSelect = new StringBuilder();
142         String expr = null;
143         sqlSelect.append("SELECT ");
144         //解析select返回的数据字段项
145         for (SQLSelectItem sqlSelectItem : sqlSelectQuery.getSelectList()) {
146             if(sqlSelect.length() > 7){
147                 sqlSelect.append(",");
148             }
f35d93 149
C 150             out.delete(0, out.length()) ;
151             sqlSelectItem.accept(sqlastOutputVisitor) ;
152             expr = out.toString();
153             sqlSelect.append(expr);
154
155            /* if(expr.indexOf("SELECT") == -1){
c64e12 156                 sqlSelect.append(expr);
C 157             }else{
f35d93 158                 //sqlSelect.append("(");
C 159                 sqlSelect.append(expr);
160                 //sqlSelect.append(")");
161                *//* if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
c64e12 162                     sqlSelect.append(" AS "+sqlSelectItem.getAlias());
f35d93 163                 }*//*
C 164             }*/
c64e12 165         }
C 166
167         //解析from
346eb2 168         if(sqlSelectQuery.getFrom() != null){
C 169             out.delete(0, out.length()) ;
170             sqlSelectQuery.getFrom().accept(sqlastOutputVisitor) ;
171             sqlWhere.append(" FROM "+out);
172         }
c64e12 173
C 174         //解析where
346eb2 175         if(sqlSelectQuery.getWhere() != null){
C 176             out.delete(0, out.length()) ;
177             sqlSelectQuery.getWhere().accept(sqlastOutputVisitor) ;
178             sqlWhere.append(" WHERE "+out+" ");
179         }
c64e12 180
C 181         if(sqlSelectQuery.getGroupBy() != null){
182             out.delete(0, out.length()) ;
183             sqlSelectQuery.getGroupBy().accept(sqlastOutputVisitor) ;
184             sqlWhere.append(" "+out);
185         }
186         if(sqlSelectQuery.getOrderBy() != null){
187             out.delete(0, out.length()) ;
188             sqlSelectQuery.getOrderBy().accept(sqlastOutputVisitor) ;
189             sqlWhere.append(" "+out);
190         }
191         if(sqlSelectQuery.getLimit() != null){
192             out.delete(0, out.length()) ;
193             sqlSelectQuery.getLimit().accept(sqlastOutputVisitor) ;
194             sqlWhere.append(" "+out);
195         }
196
197         //处理where需要加密得字段
42c9e1 198         String sql = sqlWhere.toString();
c64e12 199         if(!StringUtils.isEmpty(sql)){
C 200             Map<String,String> aesKeys = null;
201             String aeskey = null;
202             //把剩下的拼接上来
203             String tableAl = null;
204             for(TableStat.Column column:columns){
205                 aesKeys= aesKeysTable.get(column.getTable());
206                 if(aesKeys == null){
207                     continue;
208                 }
209                 aeskey = aesKeys.getOrDefault(column.getName(),null);
210                 if(StringUtils.isEmpty(aeskey)){
211                     continue;
212                 }
213                 tableAl = tableMaps.get(column.getTable());
214                 if(!StringUtils.isEmpty(tableAl)){
215                     tableAl = tableAl+"."+column.getName();
216                 }else{
217                     tableAl = column.getName();
218                 }
5c933d 219                 sql = sql.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
c64e12 220             }
C 221         }
222         return sqlSelect.toString()+sql;
223     }
224
f35d93 225     /**
C 226      * 处理select返回字段的参数
227      * @param sql
228      * @param aesKeysTable
229      * @param tableMaps
230      * @param columns
231      * @return
232      */
c64e12 233     public static String selectSqlHandle(String sql,Map<String,Map<String,String>> aesKeysTable
C 234             ,Map<String,String> tableMaps,Collection<TableStat.Column> columns){
235
236
237         MySqlStatementParser parser = new MySqlStatementParser(sql);
238         SQLSelectStatement sqlStatement = (SQLSelectStatement) parser.parseSelect();
239         //获取格式化的slq语句
240         sql = sqlStatement.toString();
241
242         //解析select查询
243         //SQLSelect sqlSelect = sqlStatement.getSelect() ;
244         //获取sql查询块
245         SQLSelectQueryBlock sqlSelectQuery = (SQLSelectQueryBlock)sqlStatement.getSelect().getQuery() ;
246         StringBuffer out = new StringBuffer() ;
247         //创建sql解析的标准化输出
248         SQLASTOutputVisitor sqlastOutputVisitor = SQLUtils.createFormatOutputVisitor(out , null , JdbcUtils.MYSQL) ;
249
250         StringBuilder sqlWhere = new StringBuilder();
251
252         StringBuilder sqlSelect = new StringBuilder();
253         String expr = null;
254         sqlSelect.append("SELECT ");
255         //解析select返回的数据字段项
256         for (SQLSelectItem sqlSelectItem : sqlSelectQuery.getSelectList()) {
257             if(sqlSelect.length() > 7){
258                 sqlSelect.append(",");
259             }
260             expr = sqlSelectItem.getExpr().toString();
261             if(expr.indexOf("SELECT") == -1){
262                 sqlSelect.append(expr);
263                 if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
264                     sqlSelect.append(" AS "+sqlSelectItem.getAlias());
265                 }
266             }else{
267                 sqlSelect.append("(");
268                 selectSqlHandle(expr,aesKeysTable,tableMaps,columns);
269                 sqlSelect.append(")");
270                 if(!StringUtils.isEmpty(sqlSelectItem.getAlias())){
271                     sqlSelect.append(" AS "+sqlSelectItem.getAlias());
272                 }
273             }
274         }
275
276         //解析from
346eb2 277         if(sqlSelectQuery.getFrom() != null){
C 278             out.delete(0, out.length()) ;
279             sqlSelectQuery.getFrom().accept(sqlastOutputVisitor) ;
280             sqlWhere.append(" FROM "+out);
281         }
c64e12 282
C 283         //解析where
346eb2 284         if(sqlSelectQuery.getWhere() != null){
C 285             out.delete(0, out.length()) ;
286             sqlSelectQuery.getWhere().accept(sqlastOutputVisitor) ;
287             sqlWhere.append(" WHERE "+out+" ");
288         }
c64e12 289
C 290         if(sqlSelectQuery.getGroupBy() != null){
291             out.delete(0, out.length()) ;
292             sqlSelectQuery.getGroupBy().accept(sqlastOutputVisitor) ;
293             sqlWhere.append(" "+out);
294         }
295         if(sqlSelectQuery.getOrderBy() != null){
296             out.delete(0, out.length()) ;
297             sqlSelectQuery.getOrderBy().accept(sqlastOutputVisitor) ;
298             sqlWhere.append(" "+out);
299         }
300         if(sqlSelectQuery.getLimit() != null){
301             out.delete(0, out.length()) ;
302             sqlSelectQuery.getLimit().accept(sqlastOutputVisitor) ;
303             sqlWhere.append(" "+out);
304         }
305
306         sql = sqlWhere.toString();
307         if(!StringUtils.isEmpty(sql)){
308             Map<String,String> aesKeys = null;
309             String aeskey = null;
310             //把剩下的拼接上来
311             String tableAl = null;
312
313             for(TableStat.Column column:columns){
314                 aesKeys= aesKeysTable.get(column.getTable());
315                 if(aesKeys == null){
316                     continue;
317                 }
318                 aeskey = aesKeys.getOrDefault(column.getName(),null);
319                 if(StringUtils.isEmpty(aeskey)){
320                     continue;
321                 }
322                 tableAl = tableMaps.get(column.getTable());
323                 if(!StringUtils.isEmpty(tableAl)){
324                     tableAl = tableAl+"."+column.getName();
325                 }else{
326                     tableAl = column.getName();
327                 }
5c933d 328                 sql = sql.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
c64e12 329             }
C 330         }
331         return sqlSelect.toString()+sql;
332     }
333
334     /**新增加密数据处理
335      * @param sql sql语句
336      * @param aesKeysTable aes秘钥
337      * @return
338      */
346eb2 339     public static String insertSql(String sql,Map<String,Map<String,String>> aesKeysTable){
C 340         //装载重写的sql语句
341         StringBuilder splicingSql = new StringBuilder();
c64e12 342
346eb2 343         sql = SQLUtils.format(sql, JdbcConstants.MYSQL);
C 344         String[] datas = sql.split("VALUES",2);
c64e12 345
346eb2 346         splicingSql.append(datas[0]+"VALUES ");
c64e12 347
346eb2 348         //重新拼接SQL语句
c64e12 349
346eb2 350         //解析sql语句
C 351         MySqlStatementParser parser = new MySqlStatementParser(sql);
352         SQLStatement statement = parser.parseStatement();
353         MySqlInsertStatement insert = (MySqlInsertStatement)statement;
c64e12 354
346eb2 355         String insertName = insert.getTableName().getSimpleName();
c64e12 356
346eb2 357         //根据表名称获取到AES秘钥
C 358         Map<String,String> aesKeys= aesKeysTable.get(insertName);
359         if(aesKeys == null){
360             return sql;
361         }
c64e12 362
346eb2 363         //获取所有的字段
C 364         List<SQLExpr> columns = insert.getColumns();
c64e12 365
346eb2 366         String fildValue = null;
C 367         String aeskey = null;
368         //遍历值
369         List<SQLInsertStatement.ValuesClause> vcl = insert.getValuesList();
370         for(int j = 0; j<vcl.size(); j++){
371             if( j != 0){
372                 splicingSql.append(",");
373             }
374             for(int i = 0;i < columns.size();i++){
375                 //查询改字段是否需要加密
376                 aeskey = aesKeys.getOrDefault(columns.get(i).toString(),null);
377                 fildValue = vcl.get(j).getValues().get(i).toString();
378                 if(i == 0){
379                     splicingSql.append("(");
380                     if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
381                         splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
382                     }else{
383                         splicingSql.append(fildValue);
384                     }
385                 }else if(i == columns.size()-1){
386                     splicingSql.append(",");
387                     if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
388                         splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
389                     }else{
390                         splicingSql.append(fildValue);
391                     }
392                     splicingSql.append(")");
393                 }else{
394                     splicingSql.append(",");
395                     if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
396                         splicingSql.append("HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
397                     }else{
398                         splicingSql.append(fildValue);
399                     }
400                 }
401             }
402         }
403         return splicingSql.toString();
404     }
c64e12 405
C 406     /**更新加密数据处理
407      * @param sql sql语句
408      * @param aesKeysTable aes秘钥
409      * @return
410      */
411     public static String updateSql(String sql,Map<String,Map<String,String>> aesKeysTable){
346eb2 412
c64e12 413         //装载重写的sql语句
C 414         StringBuilder splicingSql = new StringBuilder();
415
416         //sql = SQLUtils.format(sql, JdbcConstants.MYSQL);
417         MySqlStatementParser parser = new MySqlStatementParser(sql);
418         SQLStatement sqlStatement = parser.parseStatement();
419         //获取格式化的slq语句
420         sql = sqlStatement.toString();
421
422         MySqlUpdateStatement updateStatement = (MySqlUpdateStatement)sqlStatement;
423
424         String insertName = updateStatement.getTableName().getSimpleName();
425
426         String[] datas = sql.split("WHERE",2);
427
428         Map<String,String> aesKeys = aesKeysTable.get(insertName);
346eb2 429         if(aesKeys == null){
C 430             return sql;
431         }
c64e12 432
C 433         splicingSql.append("UPDATE "+insertName+" SET ");
434         String aeskey = null;
435         String fildValue = null;
436         List<SQLUpdateSetItem> items = updateStatement.getItems();
437         for(int i = 0;i<items.size();i++){
438             if(i != 0){
439                 splicingSql.append(",");
440             }
441             SQLUpdateSetItem item = items.get(i);
442             //查询改字段是否需要加密
443             aeskey = aesKeys.getOrDefault(item.getColumn().toString(),null);
444
445             fildValue = item.getValue().toString();
446             if(aeskey != null && fildValue.indexOf("AES_ENCRYPT") == -1){
447                 splicingSql.append(item.getColumn()+" = HEX(AES_ENCRYPT("+fildValue+",'"+aeskey+"'))");
448             }else{
449                 splicingSql.append(item.getColumn()+" = "+fildValue);
450             }
451         }
452         String sqlWhere = " WHERE";
453         //把剩下的拼接上来
454         if(datas.length > 1){
455             for(int i =1;i<datas.length;i++){
456                 sqlWhere = sqlWhere+datas[i];
457             }
458
459             parser = new MySqlStatementParser("SELECT * FROM "+insertName+" "+sqlWhere);
460             sqlStatement = parser.parseStatement();
461
462             ExportTableAliasVisitor visitorTable = new ExportTableAliasVisitor();
463             sqlStatement.accept(visitorTable);
464
465             //获取表和别名
466             Map<String,String> tableMaps = visitorTable.getTableMap();
467             tableMaps.put(insertName,null);
468
469             //获取所有的字段
470             MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
471             sqlStatement.accept(visitor);
472
473             String tableAl = null;
474             //遍历所有字段
475             Collection<TableStat.Column> columns= visitor.getColumns();
476             for(TableStat.Column column:columns){
477
478                 aesKeys= aesKeysTable.get(column.getTable());
479                 if(aesKeys == null){
480                     continue;
481                 }
482                 aeskey = aesKeys.getOrDefault(column.getName(),null);
483                 if(StringUtils.isEmpty(aeskey)){
484                     continue;
485                 }
486                 tableAl = tableMaps.get(column.getTable());
487                 if(!StringUtils.isEmpty(tableAl)){
488                     tableAl = tableAl+"."+column.getName();
489                 }else{
490                     tableAl = column.getName();
491                 }
5c933d 492                 sqlWhere = sqlWhere.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
c64e12 493             }
C 494
495         }
326104 496         splicingSql.append(sqlWhere);
c64e12 497         return splicingSql.toString();
C 498     }
499
500     /**删除加密数据处理
501      * @param sql sql语句
502      * @param aesKeysTable aes秘钥
503      * @return
504      */
505     public static String deleteSql(String sql,Map<String,Map<String,String>> aesKeysTable){
506         //装载重写的sql语句
507         StringBuilder splicingSql = new StringBuilder();
508
509         //sql = SQLUtils.format(sql, JdbcConstants.MYSQL);
510         MySqlStatementParser parser = new MySqlStatementParser(sql);
511         SQLStatement sqlStatement = parser.parseStatement();
512         //获取格式化的slq语句
513         sql = sqlStatement.toString();
514
515         MySqlDeleteStatement deleteStatement = (MySqlDeleteStatement)sqlStatement;
516
517         String insertName = deleteStatement.getTableName().getSimpleName();
518
519         String[] datas = sql.split("WHERE",2);
520
521         Map<String,String> aesKeys = aesKeysTable.get(insertName);
346eb2 522         if(aesKeys == null){
C 523             return sql;
524         }
c64e12 525
C 526         splicingSql.append("DELETE FROM "+insertName);
527
528         String aeskey = null;
529
530         String sqlWhere = " WHERE";
531         //把剩下的拼接上来
532         if(datas.length > 1){
533             for(int i =1;i<datas.length;i++){
534                 sqlWhere = sqlWhere+datas[i];
535             }
536
537             parser = new MySqlStatementParser("SELECT * FROM "+insertName+" "+sqlWhere);
538             sqlStatement = parser.parseStatement();
539
540             ExportTableAliasVisitor visitorTable = new ExportTableAliasVisitor();
541             sqlStatement.accept(visitorTable);
542
543             //获取表和别名
544             Map<String,String> tableMaps = visitorTable.getTableMap();
545             tableMaps.put(insertName,null);
546
547             //获取所有的字段
548             MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
549             sqlStatement.accept(visitor);
550
551             String tableAl = null;
552             //遍历所有字段
553             Collection<TableStat.Column> columns= visitor.getColumns();
554             for(TableStat.Column column:columns){
555
556                 aesKeys= aesKeysTable.get(column.getTable());
557                 if(aesKeys == null){
558                     continue;
559                 }
560                 aeskey = aesKeys.getOrDefault(column.getName(),null);
561                 if(StringUtils.isEmpty(aeskey)){
562                     continue;
563                 }
564                 tableAl = tableMaps.get(column.getTable());
565                 if(!StringUtils.isEmpty(tableAl)){
566                     tableAl = tableAl+"."+column.getName();
567                 }else{
568                     tableAl = column.getName();
569                 }
5c933d 570                 sqlWhere = sqlWhere.replaceAll("((?<!\\.)\\b"+tableAl+"\\b(?!\\.))","AES_DECRYPT(UNHEX("+tableAl+"),'"+aeskey+"')");
c64e12 571             }
C 572
573         }
326104 574         splicingSql.append(sqlWhere);
c64e12 575         return splicingSql.toString();
C 576     }
577
578 }