避免向Node.js中的存储过程注入SQL
避免向Node.js中的存储过程注入SQL
提问于 2019-02-02 10:40:12
如何在调用存储过程时避免从Node.js注入SQL
假设从UI的前端输入了一些特殊字符
例如：
- 如果输入
?，true将保存到数据库中 - 如果输入
??，`true`将保存到数据库中
一些特殊字符(如Backslashes(**\**)和撇号(**'**) )的
我将从控制台获取这些类型的错误。
From console: '
{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near
'''')' at line 1
at Query.Sequence._packetToError (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
at Query.ErrorPacket (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\sequences\Query.js:77:18)
at Protocol._parsePacket (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\Protocol.js:278:23)
at Parser.write (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\Parser.js:76:12)
at Protocol.write (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\Protocol.js:38:16)
at Socket.<anonymous> (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\Connection.js:91:28)
at Socket.<anonymous> (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\Connection.js:502:10)
at Socket.emit (events.js:182:13)
at addChunk (_stream_readable.js:283:12)
at readableAddChunk (_stream_readable.js:264:11)
--------------------
at Protocol._enqueue (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Connection.query (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\Connection.js:200:25)
at Object.saveFeeds (C:\xampp\htdocs\nodechat\middleware\db.js:96:15)
at C:\xampp\htdocs\nodechat\middleware\routes.js:187:12
at Layer.handle [as handle_request] (C:\xampp\htdocs\nodechat\node_modules\express\lib\router\layer.js:95:5)
at next (C:\xampp\htdocs\nodechat\node_modules\express\lib\router\route.js:137:13)
at Route.dispatch (C:\xampp\htdocs\nodechat\node_modules\express\lib\router\route.js:112:3)
at Layer.handle [as handle_request] (C:\xampp\htdocs\nodechat\node_modules\express\lib\router\layer.js:95:5)
at C:\xampp\htdocs\nodechat\node_modules\express\lib\router\index.js:281:22
at Function.process_params (C:\xampp\htdocs\nodechat\node_modules\express\lib\router\index.js:335:12)
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage:
'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'\'\'\')\' at line
1',
sqlState: '42000',
index: 0,
sql: 'CALL AddFeedItems(1,\'\'\')' }
undefined
C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\Parser.js:80
throw err; // Rethrow non-MySQL errors
^
TypeError [ERR_INVALID_ARG_TYPE]: The first argument must be one of type string or Buffer. Received type undefined
at write_ (_http_outgoing.js:595:11)
at ServerResponse.write (_http_outgoing.js:567:10)
at C:\xampp\htdocs\nodechat\middleware\routes.js:188:17
at Query.<anonymous> (C:\xampp\htdocs\nodechat\middleware\db.js:100:13)
at Query.<anonymous> (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\Connection.js:502:10)
at Query._callback (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\Connection.js:468:16)
at Query.Sequence.end (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\sequences\Sequence.js:83:24)
at Query.ErrorPacket (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\sequences\Query.js:90:8)
at Protocol._parsePacket (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\Protocol.js:278:23)
at Parser.write (C:\xampp\htdocs\nodechat\node_modules\mysql\lib\protocol\Parser.js:76:12)routes.js
app.post('/AddFeedItems', function(req, res) {
// console.log(req.body);
try{
console.log(JSON.parse(Object.keys(req.body)[0]));
req.body = JSON.parse(Object.keys(req.body)[0]);
} catch(err) {
console.log('Error');
req.body = req.body
}
db.saveFeeds(req.body, function(chats) {
res.write(JSON.stringify(chats));
res.end();
})
});db.js
function saveFeeds(data,cb) {
const conn = createConnection();
conn.connect();
console.log('From console'+data.keyword);
let sql ="CALL AddFeedItems("+data.senderid + ",'" + data.keyword + "')";
conn.query(sql, true,function(err,result) {
if(err) console.log(err);
conn.end();
console.log(result);
cb(result);
});
}MySQL存储过程
CREATE PROCEDURE `AddFeedItems`(IN `senderid` BIGINT(255), IN `keyword` VARCHAR(255)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER
BEGIN
DECLARE LastFeedId INT;
INSERT INTO `feed_item` (`userid`, `content`, `timestamp`, `likes`, `comments`, `user_flag`, `likes_data`) VALUES (senderid, keyword, CURRENT_TIMESTAMP(), 0, 0, 0, 'like');
SET LastFeedId = LAST_INSERT_ID();
INSERT INTO `feed_item_likes` (`feed_item_id`, `user_id`, `timestamp`, `is_like`) VALUES (LastFeedId, senderid, CURRENT_TIMESTAMP(), 0);
SELECT LastFeedId;
END一般情况下，为了防止这些特殊字符注入SQL。在守则中应采取哪些必要的步骤和预防措施？
编辑：
需要一个函数来去掉一组特殊字符，比如Backslashes(\)，Dollars($)，撇号、(')和Node.js中的问题Marks(?)
因此，我找到了替换上述所有特殊字符的解决方案，但问题Marks(?).除外。
--这些结果如下：
正则表达式中带有问号的
data.keyword.replace(/[\\$'"\?]/g, "\\$&")
+-----------+-----------+
|Entered |Saved into |
|Character |Database |
+-----------+-----------+
| ? | rue |
| ?? | rue? |
| ??? | ??? |
+-----------+-----------+正则表达式中无问号的
data.keyword.replace(/[\\$'"]/g, "\\$&")
+-----------+-----------+
|Entered |Saved into |
|Character |Database |
+-----------+-----------+
| ? | true |
| ?? | `true` |
| ??? | ??? |
+-----------+-----------+尝试了内置功能，
var key = mysql.escape(data.keyword);var key = conn.escape(data.keyword);
让sql =“调用AddFeedFriendItems”(“+data.senderid+”、“+ data.friendid +”、“+ data.friendusername +”、“+ key + ")"；
问题Marks(**?**)仍将以 true**.**的形式存储
我需要将这些问题Marks(?)替换为?值。相反，true或rue值将存储到数据库中。
如何编写正则表达式以匹配问号并只替换相同的字符？
在不使用saveFeeds()存储过程的情况下，替换db.js中的上述db.js
数据将以适当的方式存储
function saveFeeds(data,cb){
const conn = createConnection();
conn.connect();
conn.query(
"INSERT INTO feed_item (userid, content, timestamp, likes, comments, user_flag, likes_data) VALUES (?, ?, ?, ?, ?, ?, ?)", [data.senderid, data.keyword, data.timestamp, 0, 0, 0, 'like'],
function (err, rows) {
if(err) {
console.log(err);
} else {
var feedId = rows.insertId;
var feedId = rows.insertId;
conn.query(
"INSERT INTO feed_item_likes (feed_item_id, user_id, timestamp, is_like) VALUES (?, ?, ?, ?)", [feedId, data.senderid, data.timestamp, 0],
function (err, rows) {
if(err) {
console.log(err);
} else {
var feedId = rows.insertId;
}
}
);
}
conn.end();
cb(feedId);
}
);
}回答 2
Stack Overflow用户
发布于 2019-02-08 11:20:27
或者，我找到了使用Regex + Unicode +内置函数的解决方案。
var key = conn.escape(data.keyword);
var keyword = key.replace(/[?]/g, "❓");
let sql ="CALL AddFeedFriendItems("+data.senderid + "," + data.friendid + ",'" + data.friendusername + "'," + keyword + ")"; 默认情况下，排序规则将设置为latin1_swedish_ci in MySQL。这就混淆了是否保存哪种类型的字符。因此，自然地，它将保存为纯文本格式的?。尽管对于任何Unicode字符，?通常都会保存在DB中。因为排序规则设置为latin1_swedish_ci
注意:需要进行一些关于将Unicode字符保存到MySQL中的研究，这会影响性能或B树索引的检索记录，从而引发任何其他问题。因为当存储超过999K的消息时，.replace()将进一步降低性能。因为在默认情况下，排序规则将在latin1_swedish_ci中设置为MySQL
Stack Overflow用户
发布于 2019-02-02 16:50:35
问题不在存储过程中，而是在调用语句中：
sql: 'CALL AddFeedItems(1,\'\'\')' }这将导致SQL语句：
CALL AddFeedItems(1,''')一行中的三个'引号是无效的语法。它在CALL语句上抛出一个语法错误，它永远无法运行存储过程。
如果需要包含文字单引号的引用字符串，则SQL必须是下列形式之一：
CALL AddFeedItems(1,'''') -- two single-quotes become one literal single-quote
CALL AddFeedItems(1,'\'') -- escaped single-quote
CALL AddFeedItems(1,"'") -- delimit by alternative quotes, if sql_mode is not ANSI页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54492225
复制相关文章
相似问题
腾讯云开发者
