Avoiding SQL Injections to the Stored Procedure in Node.js

Question

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,

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 + ")";

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

Answer 1

Alternatively, I found the solution with Regex + Unicode + Built-in function

var key = conn.escape(data.keyword);
var keyword = key.replace(/[?]/g, "❓");

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

List of Unicode Characters with Question Mark

By Default Collation will be set to latin1_swedish_ci in MySQL. This confuses whether which type of characters to be saved. So naturally, it will be saved in the plain text format?. Although In general for any Unicode character ? will be saved in the DB. Since Collation is set to latin1_swedish_ci

Note: Need some research about saving Unicode characters into MySQL will hinder performance or retrieving records by B-Tree index's raise any other issues. Since .replace() will degrade performance further when more than 999K messages are stored. Because By Default Collation will be set to latin1_swedish_ci in MySQL

Answer 2

The problem is not in your stored procedure, it's in your CALL statement:

sql: 'CALL AddFeedItems(1,\'\'\')' }

This results in an SQL statement:

CALL AddFeedItems(1,''')

Three ' quotes in a row is not valid syntax. It's throwing a syntax error on the CALL statement, it never gets past that to run the stored procedure.

If you want a quoted string containing a literal single-quote, the SQL must be one of the following forms:

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