node-mysql中防止SQL注入的方法总结
<p><span><strong>SQL注入简介</strong></span></p>
<p>
SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编程时的疏忽，通过SQL语句，实现无帐号登录，甚至篡改数据库。</p>
<p>
<span><strong>node-mysql中防止SQL注入</strong></span></p>
<p>
为了防止SQL注入，可以将SQL中传入参数进行编码，而不是直接进行字符串拼接。在node-mysql中，防止SQL注入的常用方法有以下四种：</p>
<p>
<span><strong>方法一：使用escape()对传入参数进行编码：</strong></span></p>
<p>
<strong>参数编码方法有如下三个：</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterxhtml" id="highlighter_443095">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="xhtml plain">mysql.escape(param)</code>
</div>
<div class="line number2 index1 alt1">
<code class="xhtml plain">connection.escape(param)</code>
</div>
<div class="line number3 index2 alt2">
<code class="xhtml plain">pool.escape(param)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>例如：</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightercpp" id="highlighter_195074">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="cpp plain">var userId = 1, name = </code><code class="cpp string">'test'</code><code class="cpp plain">;</code>
</div>
<div class="line number2 index1 alt1">
<code class="cpp plain">var query = connection.query(</code><code class="cpp string">'SELECT * FROM users WHERE id = '</code> <code class="cpp plain">+ connection.escape(userId) + </code><code class="cpp string">', name = '</code> <code class="cpp plain">+ connection.escape(name), function(err, results) {</code>
</div>
<div class="line number3 index2 alt2">
<code class="cpp spaces"> </code><code class="cpp comments">// ...</code>
</div>
<div class="line number4 index3 alt1">
<code class="cpp plain">});</code>
</div>
<div class="line number5 index4 alt2">
<code class="cpp plain">console.</code><code class="cpp functions bold">log</code><code class="cpp plain">(query.sql); </code><code class="cpp comments">// SELECT * FROM users WHERE id = 1, name = 'test'</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>escape()方法编码规则如下：</strong></p>
<p>
Numbers不进行转换；</p>
<p>
Booleans转换为true/false；</p>
<p>
Date对象转换为'YYYY-mm-dd HH:ii:ss'字符串；</p>
<p>
Buffers转换为hex字符串，如X'0fa5'；</p>
<p>
Strings进行安全转义；</p>
<p>
Arrays转换为列表，如[‘a', ‘b']会转换为'a', ‘b'；</p>
<p>
多维数组转换为组列表，如[[‘a', ‘b'], [‘c', ‘d']]会转换为'a', ‘b'), (‘c', ‘d')；</p>
<p>
Objects会转换为key=value键值对的形式。嵌套的对象转换为字符串；</p>
<p>
undefined/null会转换为NULL；</p>
<p>
MySQL不支持NaN/Infinity，并且会触发MySQL错误。</p>
<p>
<span><strong>方法二：使用connection.query()的查询参数占位符：</strong></span></p>
<p>
可使用 ? 做为查询参数占位符。在使用查询参数占位符时，在其内部自动调用<code> connection.escape() </code>方法对传入参数进行编码。</p>
<p>
如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightercpp" id="highlighter_427322">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="cpp plain">var userId = 1, name = </code><code class="cpp string">'test'</code><code class="cpp plain">;</code>
</div>
<div class="line number2 index1 alt1">
<code class="cpp plain">var query = connection.query(</code><code class="cpp string">'SELECT * FROM users WHERE id = ?, name = ?'</code><code class="cpp plain">, , function(err, results) {</code>
</div>
<div class="line number3 index2 alt2">
<code class="cpp spaces"> </code><code class="cpp comments">// ...</code>
</div>
<div class="line number4 index3 alt1">
<code class="cpp plain">});</code>
</div>
<div class="line number5 index4 alt2">
<code class="cpp plain">console.</code><code class="cpp functions bold">log</code><code class="cpp plain">(query.sql); </code><code class="cpp comments">// SELECT * FROM users WHERE id = 1, name = 'test'</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="syntaxhighlightercpp" id="highlighter_312524">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="cpp plain">var post = {userId: 1, name: </code><code class="cpp string">'test'</code><code class="cpp plain">};</code>
</div>
<div class="line number2 index1 alt1">
<code class="cpp plain">var query = connection.query(</code><code class="cpp string">'SELECT * FROM users WHERE ?'</code><code class="cpp plain">, post, function(err, results) {</code>
</div>
<div class="line number3 index2 alt2">
<code class="cpp spaces"> </code><code class="cpp comments">// ...</code>
</div>
<div class="line number4 index3 alt1">
<code class="cpp plain">});</code>
</div>
<div class="line number5 index4 alt2">
<code class="cpp plain">console.</code><code class="cpp functions bold">log</code><code class="cpp plain">(query.sql); </code><code class="cpp comments">// SELECT * FROM users WHERE id = 1, name = 'test'</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>方法三： 使用escapeId()编码SQL查询标识符：</strong></span></p>
<p>
如果你不信任用户传入的SQL标识符（数据库、表、字符名），可以使用escapeId()方法进行编码。最常用于排序等。</p>
<p>
<code>escapeId()</code>有如下三个功能相似的方法：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightercpp" id="highlighter_784354">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="cpp plain">mysql.escapeId(identifier)</code>
</div>
<div class="line number2 index1 alt1">
<code class="cpp plain">connection.escapeId(identifier)</code>
</div>
<div class="line number3 index2 alt2">
<code class="cpp plain">pool.escapeId(identifier)</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="syntaxhighlightercpp" id="highlighter_552951">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="cpp plain">var sorter = </code><code class="cpp string">'date'</code><code class="cpp plain">;</code>
</div>
<div class="line number2 index1 alt1">
<code class="cpp plain">var sql = </code><code class="cpp string">'SELECT * FROM posts ORDER BY '</code> <code class="cpp plain">+ connection.escapeId(sorter);</code>
</div>
<div class="line number3 index2 alt2">
<code class="cpp plain">connection.query(sql, function(err, results) {</code>
</div>
<div class="line number4 index3 alt1">
<code class="cpp spaces"> </code><code class="cpp comments">// ...</code>
</div>
<div class="line number5 index4 alt2">
<code class="cpp 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>
<span><strong>方法四： 使用mysql.format()转义参数：</strong></span></p>
<p>
准备查询，该函数会选择合适的转义方法转义参数 <code>mysql.format()</code>用于准备查询语句，该函数会自动的选择合适的方法转义参数。</p>
<p>
例如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightercpp" id="highlighter_940078">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="cpp plain">var userId = 1;</code>
</div>
<div class="line number2 index1 alt1">
<code class="cpp plain">var sql = </code><code class="cpp string">"SELECT * FROM ?? WHERE ?? = ?"</code><code class="cpp plain">;</code>
</div>
<div class="line number3 index2 alt2">
<code class="cpp plain">var inserts = [</code><code class="cpp string">'users'</code><code class="cpp plain">, </code><code class="cpp string">'id'</code><code class="cpp plain">, userId];</code>
</div>
<div class="line number4 index3 alt1">
<code class="cpp plain">sql = mysql.format(sql, inserts); </code><code class="cpp comments">// SELECT * FROM users WHERE id = 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>
<span><strong>总结</strong></span></p>
<p>
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作带来一定的帮助，如果有疑问大家可以留言交流。</p>
頁:
[1]