Send DBNull.Value instead of 'null' in nodeJS MySql

Question

HOw can i send null value to database in node.js? I am using MySql Package for node JS

and I have some null values to insert which I want to be stored in database as DBNull.Value( as in asp.net) and not as "null" or " ".

Also, I cannot skip any value to insert.

My code below:

var query=connection.query("insert into user_details(user_id,username,screenname,firstname,middlename,lastname,about_me,color_code,Gender,hobbies,occupation,member_of_group,profile_pic,address1,address2,city,pincode,phonenumber,EmailId1,EmailId2,createdate,updatedate) values('"+json.user_id+"','"+ json.username+"','"+json.screenname+"','"+json.firstname+"','"+json.middlename+"','"+json.lastname+"','"+json.about_me+"','"+json.color_code+"','"+json.Gender+"','"+json.hobbies+"','"+json.occupation+"','"+json.member_of_group+"','"+json.profile_pic+"','"+json.address1+"','"+json.address2+"','"+json.city+"',"+json.pincode+",'"+json.phonenumber+"','"+json.EmailId1+"','"+json.EmailId2+"','"+json.createdate+"','"+json.updatedate+"');",function(err,result){
       if(!err){
        connection.destroy();
        callback(result);
       }
       else
       {
          console.log(err);
          callback('error');
       }
});

How can I do that???

mscdex · Accepted Answer · 2014-09-27 21:13:11Z

3

First of all, you shouldn't directly concatenate (especially user submitted) values for security reasons and it's just plain tedious when you have a lot of concatenation going on.

Try this instead which makes it easier to work with and properly escapes your values:

connection.query('INSERT INTO user_details SET ?', json, function(err, result) {
  connection.destroy();
  if (err)
    console.log(err);
  callback(err, result);
});

or manually specify the values if the keys in the data source do not match up with column names:

var values = {
  user_id: json.id,
  username: json.user,
  // ...
};
connection.query('INSERT INTO user_details SET ?', values, function(err, result) {
  connection.destroy();
  if (err)
    console.log(err);
  callback(err, result);
});

Secondly, typically callbacks use "error-first", so passing a real error object as the first argument is better.

answered Sep 27, 2014 at 21:13

mscdex

107k15 gold badges201 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

writeToBhuwan Over a year ago

+1 Thank you for your prompt reply but it is also sending "null" string value instead of DBNull object

writeToBhuwan Over a year ago

And thank you for your valuable suggestion. I've fixed my code. :)

mscdex Over a year ago

I'm not sure I understand. What does console.dir(json.somenullproperty) output when you pass in a value with a null value? Does it show null or 'null' or something else?

mscdex Over a year ago

null for err means there was no error and typically there is no result for INSERT/UPDATE/DELETE because they don't return rows.

Collectives™ on Stack Overflow

Send DBNull.Value instead of 'null' in nodeJS MySql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related