-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathDML审计.html
391 lines (363 loc) · 21.9 KB
/
DML审计.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
<!DOCTYPE html>
<html lang="" class="loading">
<head><meta name="generator" content="Hexo 3.8.0">
<!-- hexo-inject:begin --><!-- hexo-inject:end --><meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, minimum-scale=1.0, maximum-scale=1.0, user-scalable=no">
<title>有朋自远方来不亦乐乎</title>
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
<meta name="google" content="notranslate">
<meta name="keywords" content="huhu,">
<meta name="author" content="Mr.shi">
<link rel="alternative" href="atom.xml" title="有朋自远方来不亦乐乎" type="application/atom+xml">
<link rel="icon" href="/img/favicon.png">
<link rel="stylesheet" href="//cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.css">
<link rel="stylesheet" href="/css/diaspora.css">
<link rel="stylesheet" href="/css/prism-atom-dark.css" type="text/css"><!-- hexo-inject:begin --><!-- hexo-inject:end --></head>
</html>
<body class="loading">
<!-- hexo-inject:begin --><!-- hexo-inject:end --><div id="loader"></div>
<div id="single" class="page">
<div id="top">
<a class="icon-left image-icon" href="javascript:history.back()"></a>
</div>
<div class="section">
<div class="article">
<div class="main">
<div class="content">
<h3 id="四张图了解一下DDL，DML，DCL，TCL"><a href="#四张图了解一下DDL，DML，DCL，TCL" class="headerlink" title="四张图了解一下DDL，DML，DCL，TCL"></a>四张图了解一下DDL，DML，DCL，TCL</h3><p><img src="https://ws3.sinaimg.cn/large/005BYqpgly1g0rxxcpj2hj30m0075wep.jpg" alt="DDL"></p>
<p><img src="https://ws3.sinaimg.cn/large/005BYqpgly1g0rxtwg90vj30g506ndfz.jpg" alt="DML"></p>
<p><img src="https://ws3.sinaimg.cn/large/005BYqpgly1g0rxywd975j30ec06raa5.jpg" alt="DCL"> <img src="https://ws3.sinaimg.cn/large/005BYqpgly1g0ry1ckdwgj30fa06pjrk.jpg" alt="TCL"></p>
<h3 id="PageHelper是怎么实现分页的？"><a href="#PageHelper是怎么实现分页的？" class="headerlink" title="PageHelper是怎么实现分页的？"></a>PageHelper是怎么实现分页的？</h3><blockquote>
<p>在了解dml审计之前，我觉得先以我们熟悉的pageHelper来说，会好理解点。</p>
</blockquote>
<h4 id="Start"><a href="#Start" class="headerlink" title="Start"></a>Start</h4><pre class=" language-lang-java"><code class="language-lang-java">PageHelper.doPage(page, pageSize, () -> XXXMapper.queryXXX(param1,param2))
</code></pre>
<p>我们经常使用这句来完成数据分页展示，我们首先猜测pageHelper的本质就是在我们的sql后面加个limit page*pageSize,page+pageSize，让我们去证实这个猜测。</p>
<h4 id="First"><a href="#First" class="headerlink" title="First"></a>First</h4><blockquote>
<p>注意每段代码上面的是该方法的路径</p>
</blockquote>
<p>ctrl + 鼠标右键进入<strong>doPage()</strong></p>
<pre class=" language-lang-java"><code class="language-lang-java">//mybatis.pagehelper.PageHelper#doPage
public static <e> Page<e> doPage(int page, int size, Select select){
startPage(page,size);
return (Page<e>)select.doSelect();
}
</e></e></e></code></pre>
<p>再进入<strong>startPage()</strong>，一直点到为该线程设置Page属性。</p>
<pre class=" language-lang-java"><code class="language-lang-java">//mybatis.pagehelper.page.PageMethod#startPage
public static PageInfo startPage(int pageNum, int pageSize) {
return startPage(pageNum, pageSize, true);
}
//mybatis.pagehelper.page.PageMethod#startPage
public static PageInfo startPage(int pageNum, int pageSize, boolean count) {
PageInfo page = new PageInfo(pageNum, pageSize, count);
setLocalPage(page);
return page;
}
//mybatis.pagehelper.page.PageMethod#setLocalPage
protected static void setLocalPage(PageInfo info) {
LOCAL_PAGE.set(info);
}
//mybatis.pagehelper.page.PageMethod
protected static final ThreadLocal<pageinfo> LOCAL_PAGE = new ThreadLocal<pageinfo>();
</pageinfo></pageinfo></code></pre>
<p>这段代码大概意思就是用ThreadLocal把前端传来的page和pageSize存下来。点PageInfo类，我们可以看到：</p>
<pre class=" language-lang-java"><code class="language-lang-java">//mybatis.pagehelper.domain.PageInfo#PageInfo
public PageInfo(int page, int size, boolean count){
this.page = page;
this.size = size;
this.begin = page * size; //page
this.end = begin + size; //pageSize
this.count = count;
}
</code></pre>
<p>一不小心就看到了上面提到的limit page*pageSize,page+pageSize后面的公式，惊不惊喜？</p>
<h3 id="Second"><a href="#Second" class="headerlink" title="Second"></a>Second</h3><blockquote>
<p>既然存到当前线程里，它是不是得取出来然后做点事情，我们去看看。</p>
</blockquote>
<pre class=" language-lang-java"><code class="language-lang-java">//mybatis.pagehelper.page.PageMethod#getLocalPage
//取前端传过来得page和pageSize
public static PageInfo getLocalPage() {
return LOCAL_PAGE.get();
}
</code></pre>
<p>我们的目的也很简单，就是找谁拿这个对象然后取里面的page和pageSize，那下来我们该怎么做？你懂的</p>
<pre class=" language-lang-java"><code class="language-lang-java">//mybatis.pagehelper.dialect.AbstractHelperDialect#getLocalPage
public PageInfo getLocalPage() {
return PageHelper.getLocalPage();
}
</code></pre>
<p>这里虽然获取了PageInfo，但它没有做什么，当我们点击的时候，可以看到使用这个方法的都是本类中的。</p>
<p><img src="https://ws3.sinaimg.cn/large/005BYqpgly1g0s0qwl6obj30wk05h0tb.jpg" alt=""></p>
<p>看名字会知道它们是做某些操作的，所以我们随便选一个，我这里就点击第一个了。</p>
<pre class=" language-lang-java"><code class="language-lang-java">//mybatis.pagehelper.dialect.AbstractHelperDialect
public boolean beforeCount(MappedStatement ms, Object parameterObject, RowBounds rowBounds) {
PageInfo info = getLocalPage();
return info.isCount();
}
</code></pre>
<p>从返回值来看，它是判断是否需要查询返回List的size()的（当然，这只是猜测，后面我们会证实的），再点击<strong>beforeCount()</strong>，我们看到了我们的主角（拦截器）。</p>
<pre class=" language-lang-java"><code class="language-lang-java">@Intercepts(
{
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
}
)
public class PageInterceptor implements Interceptor {}
</code></pre>
<blockquote>
<p>这里的<strong>@Intercepts</strong>表明当前这个类为一个拦截器，而<strong>@Signature</strong>则表明要拦截的接口、方法以及对应的参数类型，如果想知道<strong>method</strong>，<strong>args</strong>该怎么写，只需要打开<strong>Executor</strong>这个类，填上参数的<a href="https://www.cnblogs.com/huhu1203/p/7225122.html" target="_blank" rel="noopener">类类型</a>即可。每一个参数都是一个sql执行的必要条件。</p>
</blockquote>
<pre class=" language-lang-java"><code class="language-lang-java">public interface Executor {
<e> List<e> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey cacheKey, BoundSql boundSql) throws SQLException;
<e> List<e> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException;
}
</e></e></e></e></code></pre>
<p>如果你点进去它的实现类中，你可以看到，参数多的这个 query 方法都是被少的这个 query 方法在内部进行调用的：</p>
<pre class=" language-lang-java"><code class="language-lang-java">//org.apache.ibatis.executor.BaseExecutor#query
@Override
public <e> List<e> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
//取参数
BoundSql boundSql = ms.getBoundSql(parameter);
CacheKey key = createCacheKey(ms, parameter, rowBounds, boundSql);
//此处调用了参数多的query
return query(ms, parameter, rowBounds, resultHandler, key, boundSql);
}
@SuppressWarnings("unchecked")
@Override
public <e> List<e> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey key, BoundSql boundSql) throws SQLException {
//......此处省略n行代码
List<e> list;
try {
queryStack++;
list = resultHandler == null ? (List<e>) localCache.getObject(key) : null;
if (list != null) {
handleLocallyCachedOutputParameters(ms, key, parameter, boundSql);
} else {
//返回一个list，是不是我们sql的查询结果呢？
list = queryFromDatabase(ms, parameter, rowBounds, resultHandler, key, boundSql);
}
//......此处省略n行代码
return list;
}
</e></e></e></e></e></e></code></pre>
<pre class=" language-lang-java"><code class="language-lang-java">//org.apache.ibatis.executor.BaseExecutor#query
private <e> List<e> queryFromDatabase(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey key, BoundSql boundSql) throws SQLException {
List<e> list;
localCache.putObject(key, EXECUTION_PLACEHOLDER);
try {
//是不是sql查询结果呢？
list = doQuery(ms, parameter, rowBounds, resultHandler, boundSql);
} finally {
localCache.removeObject(key);
}
//......此处省略n行代码
}
</e></e></e></code></pre>
<pre class=" language-lang-java"><code class="language-lang-java">@Override
public <e> List<e> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
Statement stmt = null;
try {
Configuration configuration = ms.getConfiguration();
StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, resultHandler, boundSql);
stmt = prepareStatement(handler, ms.getStatementLog());
return handler.<e>query(stmt, resultHandler);
} finally {
closeStatement(stmt);
}
}
</e></e></e></code></pre>
<p>看到了熟悉的<strong>prepareStatement</strong></p>
<pre class=" language-lang-java"><code class="language-lang-java">@Override
public <e> List<e> query(Statement statement, ResultHandler resultHandler) throws SQLException {
String sql = boundSql.getSql();
statement.execute(sql);
return resultSetHandler.<e>handleResultSets(statement);
}
</e></e></e></code></pre>
<p>它execute了sql，说明了什么？解释了pageHelper为什么只拦截Executor中的query方法。而不去拦截Executor中的其他方法。从上面的步骤我们可以知道拦截Executor的任意一个query都可以（参数少的可以通过自己取到参数多的那两个参数），但是为什么pageHelper都要拦截呢？别急，答案在后面。</p>
<blockquote>
<p>我们来大概看一看主角是怎么表演的？</p>
<p>注意逻辑都在<em>intercept</em>方法中，以下代码都在mybatis.pagehelper.PageInterceptor#intercept</p>
</blockquote>
<h5 id="a-在改造sql之前，是怎么得到当前sql的？"><a href="#a-在改造sql之前，是怎么得到当前sql的？" class="headerlink" title="a.在改造sql之前，是怎么得到当前sql的？"></a>a.在改造sql之前，是怎么得到当前sql的？</h5><pre class=" language-lang-java"><code class="language-lang-java">public Object intercept(Invocation invocation) throws Throwable {
try {
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
Object parameter = args[1];
RowBounds rowBounds = (RowBounds) args[2];
ResultHandler resultHandler = (ResultHandler) args[3];
Executor executor = (Executor) invocation.getTarget();
CacheKey cacheKey;
BoundSql boundSql;
//......此处真不知道省略了多少行代码
}
</code></pre>
<p>sql的信息全部存储在<strong>invocation</strong>这个对象中，这个过程就是获取信息而已。</p>
<pre class=" language-lang-java"><code class="language-lang-java">if(args.length == 4){
//4 个参数时
boundSql = ms.getBoundSql(parameter);
cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
} else {
//6 个参数时
cacheKey = (CacheKey) args[4];
boundSql = (BoundSql) args[5];
}
</code></pre>
<p>这里对上面提到的pageHelper选择两个<strong>query</strong>方法做了处理，==4说明是第一个query，else说明 !=4（感觉自己说了句废话，哈哈哈），最后查阅资料，pageHelper这样设计是为了避免发生多个拦截器加载顺序导致参数被中间那个拦截器修改而让这两个参数不在这里继续生效的情况。他的原话在这里：</p>
<blockquote>
<p>如果这个插件配置的靠后，是通过 4 个参数方法进来的，我们就获取这两个对象。如果这个插件配置的靠前，已经被别的拦截器处理成 6 个参数的方法了，那么我们直接从 args 中取出这两个参数直接使用即可。取出这两个参数就保证了当其他拦截器对这两个参数做过处理时，这两个参数在这里会继续生效。</p>
</blockquote>
<p>这样boundSql就是当前线程需要执行的sql，大家可以debug看到。</p>
<h5 id="b-改造sql（这里可要看好了，重点是它要拼sql，emm………-不小心剧透了）"><a href="#b-改造sql（这里可要看好了，重点是它要拼sql，emm………-不小心剧透了）" class="headerlink" title="b.改造sql（这里可要看好了，重点是它要拼sql，emm……….不小心剧透了）"></a>b.改造sql（这里可要看好了，重点是它要拼sql，emm……….不小心剧透了）</h5><pre class=" language-lang-java"><code class="language-lang-java">//判断是否需要进行分页查询
if (dialect.beforePage(ms, parameter, rowBounds)) {
//此处省略代码2行
String pageSql = dialect.getPageSql(ms, boundSql, parameter, rowBounds, pageKey);
BoundSql pageBoundSql = new BoundSql(ms.getConfiguration(), pageSql, boundSql.getParameterMappings(), parameter);
//设置动态参数
for (String key : additionalParameters.keySet()) {
pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
}
//执行分页查询
resultList = executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, pageKey, pageBoundSql);
}
</code></pre>
<p>我们看看<strong>getPageSql</strong>这个方法:</p>
<pre class=" language-lang-java"><code class="language-lang-java">public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
String sql = boundSql.getSql();
PageInfo info = getLocalPage();
//重点
return getPageSql(sql, info, pageKey);
}
</code></pre>
<p>再点进去<strong>getPageSql</strong>，选择<strong>MySqlDialect</strong>的实现类。</p>
<p><img src="https://ws3.sinaimg.cn/large/005BYqpgly1g0s91eqh33j311405c3yw.jpg" alt=""></p>
<p>我们可以看到：</p>
<pre class=" language-lang-java"><code class="language-lang-java">//mybatis.pagehelper.dialect.MySqlDialect#getPageSql
public String getPageSql(String sql, PageInfo info, CacheKey pageKey) {
StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
sqlBuilder.append(sql);
if (info.getBegin() == 0) {
sqlBuilder.append(" LIMIT ");
sqlBuilder.append(info.getSize());
} else {
sqlBuilder.append(" LIMIT ");
sqlBuilder.append(info.getBegin());
sqlBuilder.append(",");
sqlBuilder.append(info.getSize());
pageKey.update(info.getBegin());
}
pageKey.update(info.getBegin());
return sqlBuilder.toString();
}
</code></pre>
<p>终于验证了我们最初的猜测，拼接limit。</p>
<h4 id="第四部呢？"><a href="#第四部呢？" class="headerlink" title="第四部呢？"></a>第四部呢？</h4><h3 id="DML审计流程图"><a href="#DML审计流程图" class="headerlink" title="DML审计流程图"></a>DML审计流程图</h3><p><img src="https://ws3.sinaimg.cn/large/005BYqpgly1g0ry5zfghsj30h80t8q41.jpg" alt=""></p>
<h4 id="怎样才能拦截mybatis的dml请求呢？"><a href="#怎样才能拦截mybatis的dml请求呢？" class="headerlink" title="怎样才能拦截mybatis的dml请求呢？"></a>怎样才能拦截mybatis的dml请求呢？</h4><pre class=" language-lang-java"><code class="language-lang-java">@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})})
public class DmlLogInterceptor implements Interceptor {
}
</code></pre>
<blockquote>
<p>这里的<strong>@Intercepts</strong>表明当前这个类为一个拦截器，而<strong>@Signature</strong>则表明要拦截的接口、方法以及对应的参数类型</p>
</blockquote>
<p>我们可以看看Executor的这个类：</p>
<pre class=" language-lang-java"><code class="language-lang-java">public interface Executor {
int update(MappedStatement ms, Object parameter) throws SQLException;
<e> List<e> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey cacheKey, BoundSql boundSql) throws SQLException;
<e> List<e> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException;
}
</e></e></e></e></code></pre>
</div>
</div>
</div>
</div>
</div>
<script type="text/x-mathjax-config">
MathJax.Hub.Config({
tex2jax: {
inlineMath: [ ["$","$"], ["\\(","\\)"] ],
skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code'],
processEscapes: true
}
});
MathJax.Hub.Queue(function() {
var all = MathJax.Hub.getAllJax();
for (var i = 0; i < all.length; ++i)
all[i].SourceElement().parentNode.className += ' has-jax';
});
</script>
<script src="http://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script><!-- hexo-inject:begin --><!-- Begin: Injected MathJax -->
<script type="text/x-mathjax-config">
MathJax.Hub.Config("");
</script>
<script type="text/x-mathjax-config">
MathJax.Hub.Queue(function() {
var all = MathJax.Hub.getAllJax(), i;
for(i=0; i < all.length; i += 1) {
all[i].SourceElement().parentNode.className += ' has-jax';
}
});
</script>
<script type="text/javascript" src="custom_mathjax_source">
</script>
<!-- End: Injected MathJax -->
<!-- hexo-inject:end -->
</body>
<script src="//cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.min.js"></script>
<script src="//lib.baomitu.com/jquery/1.8.3/jquery.min.js"></script>
<script src="/js/plugin.js"></script>
<script src="/js/diaspora.js"></script>
<link rel="stylesheet" href="/photoswipe/photoswipe.css">
<link rel="stylesheet" href="/photoswipe/default-skin/default-skin.css">
<script src="/photoswipe/photoswipe.min.js"></script>
<script src="/photoswipe/photoswipe-ui-default.min.js"></script>
<!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">
<!-- Background of PhotoSwipe.
It's a separate element as animating opacity is faster than rgba(). -->
<div class="pswp__bg"></div>
<!-- Slides wrapper with overflow:hidden. -->
<div class="pswp__scroll-wrap">
<!-- Container that holds slides.
PhotoSwipe keeps only 3 of them in the DOM to save memory.
Don't modify these 3 pswp__item elements, data is added later on. -->
<div class="pswp__container">
<div class="pswp__item"></div>
<div class="pswp__item"></div>
<div class="pswp__item"></div>
</div>
<!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
<div class="pswp__ui pswp__ui--hidden">
<div class="pswp__top-bar">
<!-- Controls are self-explanatory. Order can be changed. -->
<div class="pswp__counter"></div>
<button class="pswp__button pswp__button--close" title="Close (Esc)"></button>
<button class="pswp__button pswp__button--share" title="Share"></button>
<button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>
<button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>
<!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
<!-- element will get class pswp__preloader--active when preloader is running -->
<div class="pswp__preloader">
<div class="pswp__preloader__icn">
<div class="pswp__preloader__cut">
<div class="pswp__preloader__donut"></div>
</div>
</div>
</div>
</div>
<div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
<div class="pswp__share-tooltip"></div>
</div>
<button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
</button>
<button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
</button>
<div class="pswp__caption">
<div class="pswp__caption__center"></div>
</div>
</div>
</div>
</div>
</html>