pymysql如何解决sql注入问题深入讲解
<p><span><strong>1. SQL 注入</strong></span></p>
<p>
SQL 注入是非常常见的一种网络攻击方式，主要是通过参数来让 mysql 执行 sql 语句时进行预期之外的操作。</p>
<p>
即：因为传入的参数改变SQL的语义，变成了其他命令，从而操作了数据库。</p>
<p>
产生原因：SQL语句使用了动态拼接的方式。</p>
<p>
例如，下面这段代码通过获取用户信息来校验用户权限：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_778685">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py keyword">import</code> <code class="py plain">pymysql</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="py plain">sql </code><code class="py keyword">=</code> <code class="py string">'SELECT count(*) as count FROM user WHERE id = '</code> <code class="py keyword">+</code> <code class="py functions">str</code><code class="py plain">(</code><code class="py functions">input</code><code class="py plain">[</code><code class="py string">'id'</code><code class="py plain">]) </code><code class="py keyword">+</code> <code class="py string">' AND password = "'</code> <code class="py keyword">+</code> <code class="py functions">input</code><code class="py plain">[</code><code class="py string">'password'</code><code class="py plain">] </code><code class="py keyword">+</code> <code class="py string">'"'</code>
</div>
<div class="line number4 index3 alt1">
<code class="py plain">cursor </code><code class="py keyword">=</code> <code class="py plain">dbclient.cursor(pymysql.cursors.DictCursor)</code>
</div>
<div class="line number5 index4 alt2">
<code class="py plain">cursor.execute(sql)</code>
</div>
<div class="line number6 index5 alt1">
<code class="py plain">count </code><code class="py keyword">=</code> <code class="py plain">cursor.fetchone()</code>
</div>
<div class="line number7 index6 alt2">
<code class="py keyword">if</code> <code class="py plain">count </code><code class="py keyword">is</code> <code class="py keyword">not</code> <code class="py color1">None</code> <code class="py keyword">and</code> <code class="py plain">count[</code><code class="py string">'count'</code><code class="py plain">] > </code><code class="py value">0</code><code class="py plain">:</code>
</div>
<div class="line number8 index7 alt1">
<code class="py spaces"> </code><code class="py functions">print</code><code class="py plain">(</code><code class="py string">'登陆成功'</code><code class="py plain">)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
但是，如果传入参数是：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_382556">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py functions">input</code><code class="py plain">[</code><code class="py string">'id'</code><code class="py plain">] </code><code class="py keyword">=</code> <code class="py string">'2 or 1=1'</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
你会发现，用户能够直接登录到系统中，因为原本 sql 语句的判断条件被 or 短路成为了永远正确的语句。<br>
这里仅仅是举一个例子，事实上，sql 注入的方式还有很多种，这里不深入介绍了。</p>
<p>
总之，只要是通过用户输入数据来拼接 sql 语句，就必须在第一时间考虑如何避免 SQL 注入问题。</p>
<p>
那么，如何防止 SQL 注入呢？</p>
<p>
<span><strong>2. 预防 SQL 注入 – pymysql 参数化语句</strong></span></p>
<p>
pymysql 的 execute 支持参数化 sql，通过占位符 %s 配合参数就可以实现 sql 注入问题的避免。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_739569">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py keyword">import</code> <code class="py plain">pymysql</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="py plain">sql </code><code class="py keyword">=</code> <code class="py string">'SELECT count(*) as count FROM user WHERE id = %s AND password = %s'</code>
</div>
<div class="line number4 index3 alt1">
<code class="py plain">valus </code><code class="py keyword">=</code> <code class="py plain">[</code><code class="py functions">input</code><code class="py plain">[</code><code class="py string">'id'</code><code class="py plain">], </code><code class="py functions">input</code><code class="py plain">[</code><code class="py string">'password'</code><code class="py plain">]]</code>
</div>
<div class="line number5 index4 alt2">
<code class="py plain">cursor </code><code class="py keyword">=</code> <code class="py plain">dbclient.cursor(pymysql.cursors.DictCursor)</code>
</div>
<div class="line number6 index5 alt1">
<code class="py plain">cursor.execute(sql, values)</code>
</div>
<div class="line number7 index6 alt2">
<code class="py plain">count </code><code class="py keyword">=</code> <code class="py plain">cursor.fetchone()</code>
</div>
<div class="line number8 index7 alt1">
<code class="py keyword">if</code> <code class="py plain">count </code><code class="py keyword">is</code> <code class="py keyword">not</code> <code class="py color1">None</code> <code class="py keyword">and</code> <code class="py plain">count[</code><code class="py string">'count'</code><code class="py plain">] > </code><code class="py value">0</code><code class="py plain">:</code>
</div>
<div class="line number9 index8 alt2">
<code class="py spaces"> </code><code class="py functions">print</code><code class="py plain">(</code><code class="py string">'登陆成功'</code><code class="py plain">)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这样参数化的方式，让 mysql 通过预处理的方式避免了 sql 注入的存在。</p>
<p>
需要注意的是，不要因为参数是其他类型而换掉 %s，pymysql 的占位符并不是 python 的通用占位符。</p>
<p>
同时，也不要因为参数是 string 就在 %s 两边加引号，mysql 会自动去处理。</p>
<p>
<span><strong>3. 预防 SQL 注入 – mysql 存储过程</strong></span></p>
<p>
数据库存储过程是 mysql 的一种高级用法，但是一般来说，并不建议使用数据库的存储过程。</p>
<p>
<strong>主要原因是：</strong></p>
<ul>
<li>
存储过程的语法与普通 SQL 语句语法相差太大，增加维护成本</li>
<li>
存储过程在各数据库间不通用且差别较大，给数据库的移植和扩展带来困难</li>
<li>
编写困难，数据库脚本语言使用起来还是很不方便的，包括很多数据结构的缺失，让很多事情做起来很困难</li>
<li>
调试困难，虽然有一些功能强大的 IDE 提供了数据库存储过程的调试功能，但是通常你需要同时在数据库层面上和业务中同时进行调试，两处调试极为不便</li>
<li>
业务耦合，编写存储过程通常是需要在其中放入部分业务逻辑，这使得业务分散在数据层，业务层与数据层的耦合对于项目维护和扩展都会带来极大地不便。</li>
</ul>
<p>
但是，虽然不建议使用存储过程，但是毕竟可以依赖他实现各种跨语言的 sql 注入预防，在复杂的场景下还是有其使用价值的。（以后需要用再去详细学，这里只作简单介绍）</p>
<p>
<strong>3.1. 存储过程编写</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_346801">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py plain">delimiter \DROP PROCEDURE IF EXISTS proc_sql \CREATE PROCEDURE proc_sql (</code>
</div>
<div class="line number2 index1 alt1">
<code class="py spaces"> </code><code class="py keyword">in</code> <code class="py plain">nid1 </code><code class="py functions">INT</code><code class="py plain">,</code>
</div>
<div class="line number3 index2 alt2">
<code class="py spaces"> </code><code class="py keyword">in</code> <code class="py plain">nid2 </code><code class="py functions">INT</code><code class="py plain">,</code>
</div>
<div class="line number4 index3 alt1">
<code class="py spaces"> </code><code class="py keyword">in</code> <code class="py plain">callsql VARCHAR(</code><code class="py value">255</code><code class="py plain">)</code>
</div>
<div class="line number5 index4 alt2">
<code class="py plain">)</code>
</div>
<div class="line number6 index5 alt1">
<code class="py plain">BEGIN</code>
</div>
<div class="line number7 index6 alt2">
<code class="py spaces"> </code><code class="py functions">set</code> <code class="py plain">@nid1 </code><code class="py keyword">=</code> <code class="py plain">nid1;</code>
</div>
<div class="line number8 index7 alt1">
<code class="py spaces"> </code><code class="py functions">set</code> <code class="py plain">@nid2 </code><code class="py keyword">=</code> <code class="py plain">nid2;</code>
</div>
<div class="line number9 index8 alt2">
<code class="py spaces"> </code><code class="py functions">set</code> <code class="py plain">@callsql </code><code class="py keyword">=</code> <code class="py plain">callsql;</code>
</div>
<div class="line number10 index9 alt1">
<code class="py spaces"> </code><code class="py plain">PREPARE myprod FROM @callsql;</code>
</div>
<div class="line number11 index10 alt2">
<code class="py spaces"> </code><code class="py keyword">-</code><code class="py keyword">-</code> <code class="py plain">PREPARE prod FROM </code><code class="py string">'select * from tb2 where nid>? and nid<?'</code><code class="py plain">; 传入的值为字符串，？为占位符</code>
</div>
<div class="line number12 index11 alt1">
<code class="py spaces"> </code><code class="py keyword">-</code><code class="py keyword">-</code> <code class="py plain">用@p1，和@p2填充占位符</code>
</div>
<div class="line number13 index12 alt2">
<code class="py spaces"> </code><code class="py plain">EXECUTE myprod USING @nid1,@nid2;</code>
</div>
<div class="line number14 index13 alt1">
<code class="py spaces"> </code><code class="py plain">DEALLOCATE prepare myprod;</code>
</div>
<div class="line number15 index14 alt2">
</div>
<div class="line number16 index15 alt1">
<code class="py plain">END\delimiter ;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
3.2. pymsql 中调用</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterpy" id="highlighter_298918">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="py keyword">import</code> <code class="py plain">pymysql</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="py plain">cursor </code><code class="py keyword">=</code> <code class="py plain">conn.cursor()</code>
</div>
<div class="line number4 index3 alt1">
<code class="py plain">mysql</code><code class="py keyword">=</code><code class="py string">"SELECT * FROM user where nid > ? and nid < ?"</code>
</div>
<div class="line number5 index4 alt2">
<code class="py plain">cursor.callproc(</code><code class="py string">'proc_sql'</code><code class="py plain">, args</code><code class="py keyword">=</code><code class="py plain">(</code><code class="py value">11</code><code class="py plain">, </code><code class="py value">15</code><code class="py plain">, mysql))</code>
</div>
<div class="line number6 index5 alt1">
<code class="py plain">rows </code><code class="py keyword">=</code> <code class="py plain">cursor.fetchall()</code>
</div>
<div class="line number7 index6 alt2">
<code class="py plain">conn.commit()</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<span><strong>总结</strong></span></p>
<p>
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。</p>
<p>
原文链接：https://www.cnblogs.com/liangmingshen/p/10323535.html</p>
頁:
[1]