CODEWITHSUNDEEP
javascriptmysqlnode.jsnode-mysql
writeToBhuwan
writeToBhuwan

Reputation: 3281

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

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???

Upvotes: 0

Views: 2998

Answers (1)

mscdex
mscdex

Reputation: 106726

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.

Upvotes: 3

Related Questions