如何使用sockets.io/node.js/javascript转义mysql特殊字符 [英] How to escape mysql special characters with sockets.io/node.js/javascript
问题描述
我正在使用sockets.io将用户生成的消息插入MySQL，但是我遇到了插入带撇号的记录的问题。我一直在尝试在客户端脚本上使用replace（）方法，但输入文本是使用撇号传递的。
socket.on（'refresh feed'，function（msg）{
str = msg.replace（' ，\'）;
alert（str）;
$（＃chtxt）。append（str +'< br />'）;
}） ;
上述尝试将在没有特殊字符的情况下提醒任何字符串，但在存在时不会发出警报。我相信，在消息发送到套接字之后，我实际上正在警告。
所以，我尝试调整这段代码，观察输入键按下以观察撇号，也没有运气。
$（'＃omnibox'）。keypress（function（e）{
var key = e.which;
if（key == 13）//输入密钥代码
{
$（'input [name =clicky]'）。click（）;
return false ;
}
if（key == 222）//撇号密码
{
alert（'撇号！'）
返回false;
}
}）;
我研究了如何使用javascript替换MySQL的特殊字符的问题，但发现过时的堆栈解释了为什么客户端对此的验证并不是一个好主意。
没关系。如果我不应该这样做客户端，那么我需要知道如何在我的server.js节点脚本中执行此操作。它仍然是javascript，因此只要它的安全性很好就可以解决方案。
服务器端代码：
var add_status = function（status，callback）{
pool.getConnection（function（err，connection）{
if（err）{
callback（false）;
return;
}
connection.query（INSERT INTO`mideosub`（`url`）VALUES（'status +'），function（err） ，行）{
connection.release（）;
if（！err）{
callback（true）;
}
}）;
connection .on（'error'，function（err）{
callback（false）;
return;
}）;
}）;
}
谢谢！
不要这样做
你问的是问题的解决方法是错误的。
要用反斜杠撇号替换撇号，您可以使用：
str = msg.replace（/ '/G， '\\\''）;
但你不应该这样做。我只是提供这些信息，因为这是你的问题，但在下面阅读。
为什么这是一个坏主意
您不应该在客户端执行此操作，也不应该在服务器端执行此操作。如果避免SQL注入漏洞是一个简单的问题，用反斜杠撇号替换撇号，那么这不会是一个问题。不幸的是它更复杂。
拥有你提供的信息甚至无法判断反斜杠撇号是否会在没有看到你的代码的情况下做到你想要的那样实际上是进行数据库查询。但这并不重要，因为你永远不应该这样做。决不。查看这些答案以了解原因 - 这些问题与SQL注入无关，但代码示例包含SQL注入漏洞，答案解释如下：
-
你应该做什么
有人说过，你没有告诉你用什么模块来查询数据库，但无论你是使用
mysql模块还是Sequelize或任何有价值的东西，都应该有一种以安全的方式插入变量的机制，而无需手动转义和连接字符串。
示例
您甚至没有显示与此相关的单行代码所以我不能告诉你如何修复它但考虑这个例子：
不安全：
connection.query（
SELECT * FROM player WHERE nick ='
+ data.login +'AND pass ='+ data.pass +'，
function（err，rows）{
// ...
}
）;
仍然不安全，复杂，难以理解，不可维护且不可靠：
connection.query（
SELECT * FROM player WHERE nick ='
+ data.login.replace（/'/ g，'\\ \\\\''）+'AND pass ='+ data.pass.replace（/'/ g，'\\\''）+'，
函数（错误） ，行）{
// ... ...
}
）;
安全和简单：
connection.query（
SELECT * FROM player WHERE nick =？AND pass =？，[data.login，data.pass]，
function（err，rows） {
// ... ...
}
）;
更多信息
有关详细信息，请参阅文档：
I am using sockets.io to insert user-generated messages into MySQL, but I'm running into issues inserting records with an apostrophe. I've been trying to use the replace() method on the client side script, but the input text is passing with the apostrophe.
socket.on('refresh feed',function(msg){ str=msg.replace("'", "\'"); alert(str); $("#chtxt").append(str + '<br />'); });The above attempt will alert any string without the special character, but does not alert when it exist. I believe that I'm actually alerting after the message has been sent to sockets.
So, I tried adapting this code which watches for the enter key press to also watch for the apostrophe, with no luck there either.
$('#omnibox').keypress(function (e) { var key = e.which; if(key == 13) // the enter key code { $('input[name = "clicky"]').click(); return false; } if(key == 222) // the apostrophe key code { alert('Apostrophe!') return false; } });I researched the question of how to replace special characters for MySQL using javascript but found outdated stacks explaining why client-side validation for this is not a good idea.
That's fine. If I shouldn't do this client side, I need to know then how to do it in my server.js node script, then. It is still javascript, so a solution on either side, provided it's secure would be great.
Server-side code:
var add_status = function (status,callback) { pool.getConnection(function(err,connection){ if (err) { callback(false); return; } connection.query("INSERT INTO `videosub` (`url`) VALUES ('"+status+"')",function(err,rows){ connection.release(); if(!err) { callback(true); } }); connection.on('error', function(err) { callback(false); return; }); }); }Thanks!
解决方案Don't do it
You are asking about the wrong solution to the problem.
To replace the apostrophes with backslash-apostrophes you might use:
str = msg.replace(/'/g, '\\\'');but you should not do that. I am only providing that info because that's what your question asks about but read below.
Why it's a bad idea
You shouldn't do it on the client side and you shouldn't do in on the server side either. If avoiding SQL injection vulnerabilities was a simple matter of replacing apostrophes with backslash-apostrophes then it wouldn't be an issue. Unfortunately it's more complicated.
Having the info that you provided it's even impossible to tell whether backslash-apostrophe would even do what you expect in the first place without seeing your code that actually makes the database queries. But it doesn't matter because you should never ever do that. Never. See those answers to see why - those questions are not about SQL injections but the code examples included SQL injection vulnerabilities and the answers explain it:
- cannot use backtick when using nodejs 7.3.0
- Node js - Promise Rejection Warning when process a lot of data
- Is it possible to listen for object instantiation in Node.js?
Obligatory comic strip
What you should do instead
That having been said, you didn't tell what module you're using to query the database but no matter if you're using the
mysqlmodule or Sequelize or anything worth its salt, there should always be a mechanism of interpolating variables in a safe manner without manually escaping and concatenating the strings.Examples
You didn't show even a single line of code that is relevant here so I cannot tell you how to fix it but consider this example:
Unsafe:
connection.query( "SELECT * FROM player WHERE nick = '" + data.login + "' AND pass = '" + data.pass + "'", function (err, rows) { //... } );Still unsafe, complex, unreadable, unmaintainable and unreliable:
connection.query( "SELECT * FROM player WHERE nick = '" + data.login.replace(/'/g, '\\\'') + "' AND pass = '" + data.pass.replace(/'/g, '\\\'') + "'", function (err, rows) { //... } );Safe and simple:
connection.query( "SELECT * FROM player WHERE nick = ? AND pass = ?", [data.login, data.pass], function (err, rows) { // ... } );More info
For more info see the docs:
这篇关于如何使用sockets.io/node.js/javascript转义mysql特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
