避免对 Node.js 中的存储过程进行 SQL 注入

问：

如何避免在调用存储过程时从 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)

路由.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中。守则中应采取哪些必要步骤和预防措施？

编辑：

需要一个函数从 Node.js 中删除一组特殊字符，如反斜杠 （\）、美元 （$）、撇号 （'） 和问号 （？）

因此，我找到了替换上述所有特殊字符的解决方案，但问号（？）除外。

这些是获得的以下结果：

正则表达式中带有问号

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

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

正则表达式中没有问号

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

+-----------+-----------+
|Entered    |Saved into |
|Character  |Database   |
+-----------+-----------+
| ?         | true      |
| ??        | `true`    |
| ???       | ???       |
+-----------+-----------+

尝试了内置功能，

1. var key = mysql.escape(data.keyword);
2. var key = conn.escape(data.keyword);

let sql ="CALL AddFeedFriendItems("+data.senderid + "," + data.friendid + ",'" + data.friendusername + "'," + key + ")";

结果仍然不利，问号（？）将被存储为true。

我只需要用 ？ 值替换这些问号（？）。相反，true 或 rue 值将存储到数据库中。

我应该如何编写正则表达式以匹配问号并仅替换为相同的字符？

如果没有 MySQL 存储过程，将上面的 saveFeeds（） 替换为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);
            }
          );
}