mysql - Avoiding SQL Injections to the Stored Procedure in Node.js

问题描述

How to avoid SQL injections from Node.js when the calls are made to the stored procedure

Suppose from the front End of the UI if I entered some special characters
Eg.:

If ? is entered true will be saved to the database
If ?? is entered `true` will be saved to the database

For some special characters such as Backslashes(\) and Apostrophes (')

I will be getting these types of errors from the console

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 Stored Procedures

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

In general, In order to prevent these special characters injections into the SQL. What are the necessary steps and precaution should be adopted in the code?

Edit:

Need a function to strip off a set of special character's like Backslashes(\), Dollars($), Apostrophes (') and Question Marks(?) from Node.js

So I found the solution to replace all the above special characters, except for the Question Marks(?).

These were the following results obtained:

With question mark in regex

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

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

Without question mark in regex

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

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

Tried out with built-in functions,

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

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

Still the result is not favoring, Question Marks(?) will be stored as true.

I need to replace those Question Marks(?) with only ? value. Instead, true or rue value will be stored into the database.

How am I supposed to write the regex to match the question mark and replace with the same characters only?

Without the MySQL Stored Procedures, replacing the above saveFeeds() in db.js

Data will be stored in a proper manner

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

标签： mysqlnode.jsstored-proceduresspecial-characterssql-injection

mysql - Avoiding SQL Injections to the Stored Procedure in Node.js

问题描述

Edit:

解决方案

推荐阅读