Parsing string in nodejs

Question

I am uploading images using nodejs

My query is like:

var user = req.body;
var imgurl=projectDir +"/uploads/"+req.files.displayImage.name;
var sql= "INSERT INTO users values('','"+user.name+"','"+user.email+"','"+user.user+"','"+user.pass+"','"+imgurl+"',now())";

Everything goes right, except when it inserts imgurl it does not parse it,

My project directory is D:\node also I get it in projectDir =D:\node

But it will insert in database like:

D:
ode/uploads/canvas.png

I understand that it converts \n to new line,

So, my question is how to prevent this and what should I do for both single and double quotes insertion?

Thanks.

Out of all the answers below: connection.escape(value) is the best one. — Rehmat
– Rehmat, Commented Nov 1, 2019 at 14:25

Community · Accepted Answer · 2017-05-23 11:59:52Z

Escape them using \ as such \\n or \" \' etc.

Here's a related question that answers your query. The method:

function mysql_real_escape_string (str) {
    return str.replace(/[\0\x08\x09\x1a\n\r"'\\\%]/g, function (char) {
        switch (char) {
            case "\0":
                return "\\0";
            case "\x08":
                return "\\b";
            case "\x09":
                return "\\t";
            case "\x1a":
                return "\\z";
            case "\n":
                return "\\n";
            case "\r":
                return "\\r";
            case "\"":
            case "'":
            case "\\":
            case "%":
                return "\\"+char; // prepends a backslash to backslash, percent,
                                  // and double/single quotes
        }
    });
}

You can store the mysql_real_escape_string() function as a separate module and require it before usage or directly insert it into the .js file that will be using it. You could use it as shown below:

var sql= "INSERT INTO users values('','"+user.name+"','"+user.email+"','"+user.user+"','"+user.pass+"','"+mysql_real_escape_string(imgurl)+"',now())";

J. Rambo · Accepted Answer · 2013-10-20 05:04:15Z

5

You should consider preparing your queries using the '?' syntax (https://github.com/felixge/node-mysql#preparing-queries).

var sql= "INSERT INTO users SET ?";
// Connection attained as listed above.
connection.query( sql, { name:user.name, email:user.email, user:user.user, pass:user.pass, image:imgurl, timestamp:now()}, function(err, result){
   // check result if err is undefined.
});

Using this pattern, node-mysql will do all the escaping and safety checks for you.

Hope this helps...

answered Oct 20, 2013 at 5:04

J. Rambo

1251 gold badge2 silver badges8 bronze badges

Comments

user568109 · Accepted Answer · 2013-03-15 11:18:38Z

0

Uggh, escape all the backslash and quotes in your query string. Always use connection.escape

var mysql      = require('mysql');
var connection = mysql.createConnection(...);
var userId = 'some user provided value';
var sql    = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function(err, results) {
  // ...
});

answered Mar 15, 2013 at 11:18

user568109

48.1k18 gold badges101 silver badges124 bronze badges

1 Comment

Rohan Kumar Over a year ago

Gives error like:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

Pramod Jain · Accepted Answer · 2019-07-23 12:19:48Z

0

var mysql      = require('mysql');
var connection = mysql.createConnection({
 host     : 'localhost',
 user     : 'root',
 password : '',
 database : '*****'
});

connection.connect(function(err){
 if(!err) {
  console.log("Database is connected");
 } else {
  console.log("Error while connecting with database");
 }
});

var message  = req.body.message;
var user_id  = req.body.user_id;
var sql = "INSERT INTO users(user_id, message) VALUES ('"+user_id+"', "+connection.escape(message)+")";
connection.query(sql, function(err, result) { });

edited Jul 23, 2019 at 12:19

answered Jul 23, 2019 at 12:13

Pramod Jain

4724 silver badges5 bronze badges

Collectives™ on Stack Overflow

Parsing string in nodejs

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related