How to insert NULL value from nodeJS/javascript to MySQL using parameterized inputs?

Question

How do I ensure the value being inserted into the database is actually the NULL value instead of a 'NULL' or empty string using parameterized inputs?

Note: This was modified from a question to be more of a tutorial.

Answer 1

Using 'mariadb/callback' as the connector to the database:

const db = require('mariadb/callback');

mariaDB is one option for a MySQL database.

Let's use the following as our example variables:

var customName = '    ';
var someAddress = 'this is an address';
var mainID = 'some unique ID';
var secondID = 'maybe not a unique ID';

Queries are parameterized as such:

var sql = `UPDATE myTable SET myName=?, myAddress=? WHERE _id=? AND otherID=?;`;

By using the '?' character instead of the variable name you not only simplify the SQL statement, this also, and more importantly, prevents SQL injection. Specifically, if one of the variables was storing this:

var id = '42 OR 1=1;'

The result of not using parameterized inputs in this case might cause the entire table to be updated with the same name and address. Using parameterized inputs will simply cause this operation to fail because none of the id's in the database will be '42 OR 1=1'.

Now, if customName is an empty string or whitespace, or even a 'NULL' string, you can do this:

customName = customName.trim();
if ( (customName === '') || (customName.toUpperCase() === 'NULL') ) {
  customName = null;
}

Which is the same as:

if ( (customName.trim() === '') || (customName.trim().toUpperCase() === 'NULL') ) {
  customName = null;
}

(the first version simply reassigns customName without any leading/trailing whitespace - make sure you don't need to preserve the value of the string when choosing this method)

.trim() will remove all whitespace from both ends of the string (it will not remove any whitespace between any non-whitespace characters).

.toUpperCase() will change the string to all uppercase characters, that way if the string is 'null' or 'Null' or any other variation that includes any lowercase characters it will still evaluate to 'true' when evaluating the 'NULL' equality. Note, it's important to use .trim() here as well, that way

'  nuLl  '

becomes

'NULL'

and still evaluates to 'true'.

Next, we need to create our array of values:

var values = [customName, someAddress, mainID, secondID];

Finally, the db query:

db.query(sql, values, (err, result) => {
  // callback stuff to do here
}

db refers to the connector created at the beginning of this post. I did not include the actual connection process because that is out of scope for this post. For reference, the documentation for the API is here.

AFTER the db is connected, db.query is used to execute the query. The first argument 'sql' is the parameterized SQL statement. The second argument 'values' is the array of values that will be used in the query. This array MUST be in the same order as they will be used.

As an example of what not to do:

var sql = `UPDATE myTable SET myName=${customName}, myAddress=${someAddress} WHERE _id=${mainID} AND otherID=${secondID};`;

The reason this is bad is because of SQL injection, which works like this: You start by doing this:

var customName = 'same name';
var mainID = '" OR ""="';
var secondID = '" OR ""="';

The resulting sql code will be:

var sql = `UPDATE myTable SET myName="same name", myAddress="this is an address" WHERE _id="" OR ""="" AND otherID="" OR ""="";`;

And the result is now every single entry in the database now has the same name and same address.

This SQL injection example is modified from W3 schools.

Answer 2

How about if you try INSERT INTO mytable VALUES ('', NULLIF('$customName',''))

Here are some other resources as well they seem to have similar issue, Set value to NULL in MySQL, How to update column with null value, Set value to NULL in MySQL

also now that I think of it you just may need to change customName = "NULL"

Answer 3

You have to create second table and fill the table only if myname!==Null

and

select mytable t1 left join mytablewithvalidnames t2 on t1.id=t2.id

In this way t2.myname will be Null

Answer 4

Another solution, which works with mysql2 npm library would be to do something like this:

const IS_NULL = some_param ? `'${some_param}'` : 'NULL'
const saveRecordsQuery = `INSERT INTO some_table (some_column) VALUES (${some_param});`

With this, your not inserting the string NULL, but sql NULL value in the query, since it won't be inside quotes, if the value you want to insert is or undefined or null, just like this:

INSERT INTO some_table (some_column) VALUES (NULL);

Answer 5

Also struggled with that building an SQL query string from JSON data having null values. Most likely not the best solution, but I ended up in iterating through the input data and simply do not include fields that haveing null values:

{ key1 : "value1", key2 : "value2". key3 : null }
=> INSERT INTO tabler (`key1`,`key2`) VALUES ('value1', 'value2')

Answer 6

? had the same problem, the solution is really simple, use null instead of NULL, so in your case

var sql = UPDATE myTable SET myName=?, myAddress=? WHERE _id=? AND otherID=?;;

where customName= null;