避免SQL注入到Node.js的存储过程

如何避免Node.js的SQL注入时，调用存储过程制成

从UI的前端假设，如果我进入一些特殊字符 例如。：

• 如果输入? true将被保存到数据库中
• 如果输入?? `true`将被保存到数据库中

对于一些特殊的字符，如反斜杠（\）和撇号（'）

我会从控制台获得这些类型的错误

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。什么是应该在代码中采取必要的措施和防范？

编辑：

需要一个函数来剥去的一组特殊字符，如反斜杠（\），元（qazxsw POI），撇号（qazxsw POI）和问号（$）从Node.js的

于是，我找到了解决办法，以取代上述所有特殊字符，除了问号（'）。

这些人得到以下结果：

随着正则表达式问号

?

?

如果没有正则表达式的问号

data.keyword.replace(/[\\$'"\?]/g, "\\$&")

+-----------+-----------+
|Entered    |Saved into |
|Character  |Database   |
+-----------+-----------+
| ?         | rue       |
| ??        | rue?      |
| ???       | ???       |
+-----------+-----------+

具有内置功能试用，

1. data.keyword.replace(/[\\$'"]/g, "\\$&")
2. +-----------+-----------+ |Entered |Saved into | |Character |Database | +-----------+-----------+ | ? | true | | ?? | `true` | | ??? | ??? | +-----------+-----------+

var key = mysql.escape(data.keyword);

不过结果不是偏袒，问号（var key = conn.escape(data.keyword);）将被存储为let sql ="CALL AddFeedFriendItems("+data.senderid + "," + data.friendid + ",'" + data.friendusername + "'," + key + ")"; 。

我需要更换这些问号（?）只有true值。相反，?或?值将被存储到数据库中。

我怎么写的正则表达式匹配问号，只有相同的字符替换？

如果没有MySQL的存储过程，替换true上述rue

数据将被存储在一个适当的方式

saveFeeds()

这个问题是不是在你的存储过程，它在你的CALL语句：

db.js

这导致SQL语句：

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);
            }
          );
}

三连胜sql: 'CALL AddFeedItems(1,\'\'\')' } 报价无效语法。它扔在CALL语句语法错误，它从来没有得到过去，要运行存储过程。

如果你想包含一个文本单引号引用字符串，则SQL必须是下列形式之一：

CALL AddFeedItems(1,''')