0

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

writeToBhuwan
  • 3,233
  • 11
  • 39
  • 67

1 Answers1

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.

mscdex
  • 104,356
  • 15
  • 192
  • 153
  • +1 Thank you for your prompt reply but it is also sending "null" string value instead of DBNull object – writeToBhuwan Sep 27 '14 at 21:33
  • And thank you for your valuable suggestion. I've fixed my code. :) – writeToBhuwan Sep 27 '14 at 21:34
  • 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 Sep 27 '14 at 22:03
  • `null` for `err` means there was no error and typically there is no `result` for `INSERT`/`UPDATE`/`DELETE` because they don't return rows. – mscdex Sep 27 '14 at 22:20