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

Question

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?

Aren't you using parameterized queries? I hope you're not using string-concatenation, that's how you open yourself up to SQL injection vulnerabilities. — Dai
– Dai, Commented Nov 24, 2020 at 19:40
hey there is no worry about SQL injection as I'm using puppeteer to scrape elements and store them locally in a MySQL database. — ikito999
– ikito999, Commented Nov 24, 2020 at 21:04
the codes really long like 500 lines and probably not very efficient (i'm a big noob) so i'm not sure its worth sharing. It gets the job done though, all thats missing is this final step. — ikito999
– ikito999, Commented Nov 24, 2020 at 21:07

Δ O · Accepted Answer · 2020-11-24 19:52:03Z

2

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) => {
    ...
  }
)

answered Nov 24, 2020 at 19:52

Δ O

3,7382 gold badges22 silver badges33 bronze badges

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

2 Comments

ikito999 Over a year ago

as far as I'm concerned injection attacks are not an issue. Im using puppeteer to scrape data and store it into a local MySQL database that only I have access to (I'm really new to this so I might be wrong). Does the solution you suggest mean that I have to store the variables as an array?

Δ O Over a year ago

Not necessarily, my solution suggests using built in escaping for providing values, if only for providing null value correctly. Like conn.query('SELECT ?', [null], (error, result) => { ... });

Gerd · Accepted Answer · 2022-12-16 11:52:19Z

0

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 ]

answered Dec 16, 2022 at 11:52

Gerd

2,6911 gold badge29 silver badges51 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related