使 javascript 字符串 sql 友好
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7744912/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Making a javascript string sql friendly
提问by Bobby
Is there away to make a javascript string being passed to NodeJS friendly for MySQL? I'm trying to pass an email address to my NodeJS server and query into MySQL database. When doing regular text such as a username works fine, but the email address doesn't. Using escape clearly is not the right answer as it is not meant for SQL insertion. I'm assuming I need something on the lines of the PHP function mysql_real_escape_string().
是否可以将 javascript 字符串传递给 NodeJS 对 MySQL 友好？我正在尝试将电子邮件地址传递给我的 NodeJS 服务器并查询 MySQL 数据库。在执行常规文本（例如用户名）时可以正常工作，但电子邮件地址不行。使用转义显然不是正确的答案，因为它不适用于 SQL 插入。我假设我需要一些关于 PHP 函数的内容mysql_real_escape_string()。
回答by Paul d'Aoust
It turns out that mysql_real_escape_string() is pretty trivial. According to the documentation:
事实证明 mysql_real_escape_string() 非常简单。根据文档：
mysql_real_escape_string()calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.
mysql_real_escape_string()调用 MySQL 的库函数 mysql_real_escape_string，它在以下字符前加上反斜杠：\x00、\n、\r、\、'、" 和 \x1a。
Sounds pretty simple, actually. You could do something like this:
听起来很简单，实际上。你可以这样做：
function mysql_real_escape_string (str) {
return str.replace(/[function mysql_real_escape_string (str) {
if (typeof str != 'string')
return str;
return str.replace(/[var _escapeString = function (val) {
val = val.replace(/[var mysql = require('mysql')
var connection = mysql.createConnection( // your connection string here
var query = "SELECT THING FROM THING WHERE FRED= " + connection.escape( your_string_here );
\n\r\b\t\'"\x1a]/g, function (s) {
switch (s) {
case "var regex = new RegExp(/[npm install sqlstring --save-dev
\x08\x09\x1a\n\r"'\\%]/g)
var escaper = function escaper(char){
var m = ['\0', '\x08', '\x09', '\x1a', '\n', '\r', "'", '"', "\", '\\', "%"];
var r = ['\\0', '\\b', '\\t', '\\z', '\\n', '\\r', "''", '""', '\\', '\\\\', '\%'];
return r[m.indexOf(char)];
};
//Implementation
"Some Crazy String that Needs Escaping".replace(regex, escaper);
":
return "\0";
case "\n":
return "\n";
case "\r":
return "\r";
case "\b":
return "\b";
case "\t":
return "\t";
case "\x1a":
return "\Z";
case "'":
return "''";
case '"':
return '""';
default:
return "\" + s;
}
});
return val;
};
\x08\x09\x1a\n\r"'\\%]/g, function (char) {
switch (char) {
case "npm install @types/sqlstring --save-dev
":
return "\0";
case "\x08":
return "\b";
case "\x09":
return "\t";
case "\x1a":
return "\z";
case "\n":
return "\n";
case "\r":
return "\r";
case "\"":
case "'":
case "\":
case "%":
return "\"+char; // prepends a backslash to backslash, percent,
// and double/single quotes
}
});
}
\x08\x09\x1a\n\r"'\\%]/g, function (char) {
switch (char) {
case "import { escape } from 'sqlstring';
const escapedString = escape(`it's going to be escaped!`);
":
return "\0";
case "\x08":
return "\b";
case "\x09":
return "\t";
case "\x1a":
return "\z";
case "\n":
return "\n";
case "\r":
return "\r";
case "\"":
case "'":
case "\":
case "%":
return "\"+char; // prepends a backslash to backslash, percent,
// and double/single quotes
default:
return char;
}
});
}
NOTE: I haven't run this through any sort of unit test or security test, but it does seem to work -- and, just as an added bonus, it escapes tabs, backspaces, and '%' so it can also be used in LIKE queries, as per OWASP's recommendations(unlike the PHP original).
注意：我没有通过任何类型的单元测试或安全测试运行它，但它似乎确实有效——而且，作为一个额外的好处，它转义了制表符、退格符和“%”，因此它也可以使用在 LIKE 查询中，根据OWASP 的建议（与 PHP 原始版本不同）。
I do know that mysql_real_escape_string()is character-set-aware, but I'm not sure what benefit that adds.
我确实知道这mysql_real_escape_string()是字符集感知的，但我不确定这会带来什么好处。
There's a good discussion of these issues over here.
回答by Simon H
Learnt the hard way that passing numbers to this function causes the whole process it is used in to die quietly. So I add a little test:
了解到将数字传递给此函数会导致它所使用的整个过程安静地死亡的艰难方法。所以我添加了一个小测试：
var escaped_str = escape(your_unescaped_string);
回答by Eye
In case someone is looking for, the escapeString() in CUBRID RDBMSworks as follows:
如果有人正在寻找，CUBRID RDBMS 中的 escapeString()工作如下：
[client]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
skip-character-set-client-handshake
This is an excerpt from CUBRID Node.js driver.
这是CUBRID Node.js driver的摘录。
回答by sien
For anyone who is coming to this answer from 2018 onwards it is also worth noting that a number of javascript database frameworks now contain a connection.escape method.
对于从 2018 年开始使用此答案的任何人，还值得注意的是，许多 javascript 数据库框架现在都包含 connection.escape 方法。
For instance:
例如：
##代码##回答by Justin
Using arrays instead of a case statement:
使用数组而不是 case 语句：
##代码##回答by Francesco Borzi
回答by wbartussek
Why not simply use the built function escapeLike this:
为什么不简单地使用内置函数escape像这样：
This works for me using MySQL on the back-end.
这对我在后端使用 MySQL 有效。
回答by Soyoes
If you are playing with CJK characters http://en.wikipedia.org/wiki/CJK_charactersor some special emotional icons of iOS/Android/Other mobiles ... such as "??‰??" or decodeURIComponent("\xF3\xBE\xAD\xA0").
如果你在玩 CJK 字符http://en.wikipedia.org/wiki/CJK_characters或者 iOS/Android/其他手机的一些特殊的情感图标......比如“??‰??” 或 decodeURIComponent("\xF3\xBE\xAD\xA0")。
You will need to set your my.cnf like this
你需要像这样设置你的 my.cnf
##代码##