This question is difficult to title but easy to show. I would like to add multiple sets of values to an SQL insert such as
var sqlInsertString = `INSERT INTO events (url) VALUES`
var sqlInsertValuesString = `(`('${event.url ? event.url : null}'), null, 1, 2, 3)`
pg_client.query(sqlInsertString + sqlInsertValuesString)
this happens in a loop and thus the separation of insert string and values string. Now, what I want to happen is that if event.url contains a url it should insert the url as 'https://www.example.com' (with quotation marks) but if event.url is empty, it should insert null.
My code above will insert ‘null’ (with quotation marks) instead of an actual null in the database. Removing the quotation marks will cause errors because of the ‘:’ in the url.
How do I, in sqlInsertValuesString either provide a valid url string OR a ‘real’ null (without quotation marks) with this method?
Advertisement
Answer
There is more than one reason to use parametrised queries, not just preventing injection attacks. They make it as simple as
pg_client.query( "INSERT INTO events (url, nothing, one, two, three) VALUES ($1, $2, $3, $4, $5)", [event.url || null, null, 1, 2, 3] ) .then(res => ...) .catch(err => ...)
No quoting necessary, the driver handles all the mechanics behind the scenes.
So even if you are not making your program safer, it is still good to use parametrised queries at least for legibility, and also for practice: if you get into a habit of using parametrised queries always, you will not need to think whether an injection is possible, and you will never make a mistake of misjudging it and leaving a security hole in your program.