MySQL 防止 Node.js 中的 SQL 注入

Preventing SQL injection in Node.js

提问by funseiki

Is it possible to prevent SQL injections in Node.js (preferably with a module) in the same way that PHP had Prepared Statements that protected against them.

是否有可能以与 PHP 具有防止它们的预处理语句相同的方式防止 Node.js（最好使用模块）中的 SQL 注入。

If so, how? If not, what are some examplesthat might bypass the code I've provided (see below).

如果是这样，如何？如果没有，有哪些示例可以绕过我提供的代码（见下文）。

---

---

Some Context:

一些背景：

I'm making a web application with a back-end stack consisting of Node.js + MySql using the node-mysqlmodule. From a usability perspective, the module is great, but it has not yet implemented something akin to PHP's Prepared Statements(though I'm aware it is on the todo).

我正在使用node-mysql模块制作一个带有由 Node.js + MySql 组成的后端堆栈的 Web 应用程序。从可用性的角度来看，该模块很棒，但它还没有实现类似于 PHP 的Prepared Statements 的东西（尽管我知道它在todo 上）。

From my understanding, PHP's implementation of prepared statements, among other things, helped greatlyin the prevention of SQL injections. I'm worried, though, that my node.js app may be open to similar attacks, even with the string escaping provided by default(as in the code snippet below).

根据我的理解，PHP 对准备好的语句的实现，除其他外，在防止 SQL 注入方面有很大帮助。不过，我担心我的 node.js 应用程序可能会受到类似的攻击，即使默认提供了字符串转义（如下面的代码片段所示）。

node-mysql seems to be the most popular mysql connector for node.js, so I was wondering what other people might be doing (if anything) to account for this issue - or if it is even an issue with node.js to begin with (not sure how this wouldn't be, since user/client-side input is involved).

node-mysql 似乎是最流行的用于 node.js 的 mysql 连接器，所以我想知道其他人可能会做什么（如果有的话）来解决这个问题 - 或者它是否甚至是 node.js 的一个问题开始（不确定这会如何，因为涉及用户/客户端输入）。

Should I switch to node-mysql-nativefor the time being, since it does provide prepared statements?I'm hesitant to do this, because it does not seem to be as active as node-mysql (though that may just mean that it is complete).

我应该暂时切换到node-mysql-native，因为它确实提供了准备好的语句？我对这样做很犹豫，因为它似乎不像 node-mysql 那样活跃（尽管这可能只是意味着它是完整的）。

Here is a snippet of user registration code, which uses the sanitizermodule, along with node-mysql's prepared statement-like syntax (which, as I mentioned above, does character escaping), to prevent cross site scripting and sql injections, respectively:

下面是一段用户注册代码，它使用了sanitizer模块，以及 node-mysql 准备好的类似语句的语法（正如我上面提到的，它进行字符转义），以分别防止跨站点脚本和 sql 注入：

// Prevent xss
var clean_user = sanitizer.sanitize(username);

// assume password is hashed already
var post = {Username: clean_user, Password: hash};

// This just uses connection.escape() underneath
var query = connection.query('INSERT INTO users SET ?', post,
   function(err, results)
   {
       // Can a Sql injection happen here?
   });

采纳答案by Michael Pratt

The node-mysqllibrary automatically performs escaping when used as you are already doing. See https://github.com/felixge/node-mysql#escaping-query-values

该node-mysql库在使用时会自动执行转义，就像您已经在做的那样。见https://github.com/felixge/node-mysql#escaping-query-values

回答by hexacyanide

The library has a sectionin the readme about escaping. It's Javascript-native, so I do not suggest switching to node-mysql-native. The documentation states these guidelines for escaping:

图书馆有部分在有关转义自述。它是 Javascript-native，所以我不建议切换到node-mysql-native。文档说明了这些转义指南：

Edit:node-mysql-nativeis also a pure-Javascript solution.

编辑：node-mysql-native也是一个纯 Javascript 解决方案。

• Numbers are left untouched
• Booleans are converted to true/ falsestrings
• Date objects are converted to YYYY-mm-dd HH:ii:ssstrings
• Buffers are converted to hex strings, e.g. X'0fa5'
• Strings are safely escaped
• Arrays are turned into list, e.g. ['a', 'b']turns into 'a', 'b'
• Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']]turns into ('a', 'b'), ('c', 'd')
• Objects are turned into key = 'val'pairs. Nested objects are cast to strings.
• undefined/ nullare converted to NULL
• NaN/ Infinityare left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.

• 数字保持不变
• 布尔值转换为true/false字符串
• 日期对象转换为YYYY-mm-dd HH:ii:ss字符串
• 缓冲区被转换为十六进制字符串，例如 X'0fa5'
• 字符串被安全转义
• 数组变成列表，例如['a', 'b']变成'a', 'b'
• 嵌套数组变成分组列表（用于批量插入），例如[['a', 'b'], ['c', 'd']]变成('a', 'b'), ('c', 'd')
• 对象变成key = 'val'对。嵌套对象被转换为字符串。
• undefined/null转换为NULL
• NaN/Infinity保持原样。MySQL 不支持这些，并且尝试将它们作为值插入将触发 MySQL 错误，直到它们实现支持。

This allows for you to do things like so:

这允许您执行以下操作：

var userId = 5;
var query = connection.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {
  //query.sql returns SELECT * FROM users WHERE id = '5'
});

As well as this:

还有这个：

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
  //query.sql returns INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'
});

Aside from those functions, you can also use the escape functions:

除了这些函数之外，您还可以使用转义函数：

connection.escape(query);
mysql.escape(query);

To escape query identifiers:

转义查询标识符：

mysql.escapeId(identifier);

And as a response to your comment on prepared statements:

作为对您对准备好的声明的评论的回应：

From a usability perspective, the module is great, but it has not yet implemented something akin to PHP's Prepared Statements.

从可用性的角度来看，该模块很棒，但它还没有实现类似于 PHP 的 Prepared Statements 的功能。

The prepared statements are on the todolist for this connector, but this module at least allows you to specify custom formats that can be very similar to prepared statements. Here's an example from the readme:

准备好的语句在此连接器的待办事项列表中，但该模块至少允许您指定与准备好的语句非常相似的自定义格式。这是自述文件中的一个示例：

connection.config.queryFormat = function (query, values) {
  if (!values) return query;
  return query.replace(/\:(\w+)/g, function (txt, key) {
    if (values.hasOwnProperty(key)) {
      return this.escape(values[key]);
    }
    return txt;
  }.bind(this));
};

This changes the query format of the connection so you can use queries like this:

这会更改连接的查询格式，因此您可以使用如下查询：

connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });
//equivalent to
connection.query("UPDATE posts SET title = " + mysql.escape("Hello MySQL");

回答by jas-

I realize this is an older post but it seems that an answer was never marked so I will throw this out there.

我意识到这是一篇较旧的帖子，但似乎从未标记过答案，因此我将其扔在那里。

In regards to testing if an module you are utilizing is secure or not there are several routes you can take. I will touch on the pros/cons of each so you can make a more informed decision.

关于测试您使用的模块是否安全，您可以采用多种方法。我将介绍每种方法的优缺点，以便您做出更明智的决定。

Currently there are not any vulnerabilities for the module you are utilizing, however, this can often lead to a false sense of security as there very well could be a vulnerability currently exploiting the module/software package you are using and you wouldn't be alerted to a problem until the vendor applies a fix/patch.

目前您正在使用的模块没有任何漏洞，但是，这通常会导致错误的安全感，因为当前很可能存在正在利用您正在使用的模块/软件包的漏洞并且您不会收到警报直到供应商应用修复程序/补丁程序为止。

1. To keep abreast of vulnerabilities you will need to follow mailing lists, forums, IRC & other hacking related discussions. PRO: You can often times you will become aware of potential problems within a library before a vendor has been alerted or has issued a fix/patch to remedy the potential avenue of attack on their software. CON: This can be very time consuming and resource intensive. If you do go this route a bot using RSS feeds, log parsing (IRC chat logs) and or a web scrapper using key phrases (in this case node-mysql-native) and notifications can help reduce time spent trolling these resources.

2. Create a fuzzer, use a fuzzeror other vulnerability framework such as metasploit, sqlMapetc. to help test for problems that the vendor may not have looked for. PRO: This can prove to be a sure fire method of ensuring to an acceptable level whether or not the module/software you are implementing is safe for public access. CON: This also becomes time consuming and costly. The other problem will stem from false positives as well as uneducated review of the results where a problem resides but is not noticed.

1. 为了及时了解漏洞，您需要关注邮件列表、论坛、IRC 和其他与黑客相关的讨论。优点：在供应商收到警报或发布修复/补丁以补救对其软件的潜在攻击途径之前，您经常会意识到库中的潜在问题。缺点：这可能非常耗时且资源密集。如果您确实使用 RSS 提要、日志解析（IRC 聊天日志）和/或使用关键短语（在本例中为 node-mysql-native）和通知的网络抓取工具，那么机器人可以帮助减少花在拖钓这些资源上的时间。

2. 创建一个模糊器，使用模糊器或其他漏洞框架（例如metasploit、sqlMap等）来帮助测试供应商可能没有寻找的问题。PRO：这可以证明是确保您正在实施的模块/软件是否可以安全供公众访问的可接受水平的可靠方法。缺点：这也变得耗时且昂贵。另一个问题将源于误报以及对存在问题但未被注意到的结果的未受教育的。

Really security, and application security in general can be very time consuming and resource intensive. One thing managers will always use is a formula to determine the cost effectiveness (manpower, resources, time, pay etc) of performing the above two options.

真正的安全性和一般的应用程序安全性可能非常耗时且资源密集。管理者将始终使用的一件事是确定执行上述两个选项的成本效益（人力、资源、时间、薪酬等）的公式。

Anyways, I realize this is not a 'yes' or 'no' answer that may have been hoping for but I don't think anyone can give that to you until they perform an analysis of the software in question.

无论如何，我意识到这不是一个可能一直希望的“是”或“否”的答案，但我认为在他们对相关软件进行分析之前，没有人可以给你这个答案。

回答by Boy pro

I know that this question is old but for anyone interested, Mysql-native has been outdated so it became MySQL2that is a new module created with the help of the original MySQL module's team. This module has more features and I think it has what you want as it has prepared statements(by using.execute()) like in PHP for more security.

我知道这个问题很老，但对于任何感兴趣的人来说，Mysql-native 已经过时了，所以它变成了MySQL2，这是一个在原始 MySQL 模块团队的帮助下创建的新模块。该模块具有更多功能，我认为它具有您想要的功能，因为它已准备好语句（通过使用.execute()），就像在 PHP 中一样，以提高安全性。

It's also very active(the last change was from 2-1 days) I didn't try it before but I think it's what you want and more.

它也非常活跃（最后一次更改是从 2-1 天开始）我之前没有尝试过，但我认为这是您想要的等等。

回答by Bird Dad

The easiest way is to handle all of your database interactions in its own module that you export to your routes. If your route has no context of the database then SQL can't touch it anyway.

最简单的方法是在导出到路由的自己的模块中处理所有数据库交互。如果您的路线没有数据库的上下文，那么 SQL 无论如何都无法触及它。