CODEWITHSUNDEEP
mysqlsqlnode.js
ikito999
ikito999

Reputation: 31

Pass a 'null' value to a MySQL database in node.js

I'm trying to pass data from a Webscraper to a MySQL database. I have a lot of variables that need to be entered at a time into the database and below is a snippet of the code I'm using. (where the etc. is there are a bunch more variables.

con.query(INSERT INTO Paper_2 (referenceCodeSubject,referenceCode,subject, etc.) values ('"+referenceCodeSubject+"','"+referenceCode+"','"+subject+"', etc.))

The columns in the database have types INT, VARCHAR and CHAR.

My issue is that when I scrape not all of the variables will be assigned values and will remain as 'null' and I cannot pass this null as NULL to MySQL. It would also be quite complicated to sort the different cases for when to pass what due to the large amount of variables.

I'm hoping theres a simple way of doing this as the only solutions I've seen so far are omit the value in the query (which is hard because I would then need to decide which values to omit) or pass a string of "NULL" or just a value of 0. Is there any other way of doing this?

Upvotes: 2

Views: 2925

Answers (3)

Vladimir Mamulov
Vladimir Mamulov

Reputation: 437

nameValue ?? null not "||" because if nameValue = 0, nameValue || will be null

Upvotes: 0

Gerd
Gerd

Reputation: 2603

If you have the case, that the bind values can sometime be a valid string and sometimes undefined, use an or operator in sqlValues to handle both cases with shorthand code:

let nameValue;

let sql="insert into user (name) values (?)"

let sqlValues[] = [nameValue || null ]

Upvotes: 0

Δ O
Δ O

Reputation: 3710

Better use the built in escaping feature to avoid sql injection attacks!

conn.query(
  'INSERT INTO Paper_2 (referenceCodeSubject,referenceCode,subject) VALUES ?'
  [
    ['refCodeSubject1', 'refCode1', 'subject1'],
    ['refCodeSubject2', 'refCode2', null]
  ],
  (error, results, fields) => {
    ...
  }
)

Upvotes: 1

Related Questions