$db->bindVars question

[Dave224]

What is the difference between sanitization of a variable with 'string' and sanitization with 'stringIgnoreNull' in $db->bindVars when the variable value is NULL (not a string), and 'NULL' (a string). What is stored in the database? I can't seem to get a true NULL stored in the database.
Thank you!

zc1.5.7c, php8.0, mysql5.7.34 or 8.0.8 (production)

[dbltoe]

Perhaps the information in the docs at https://docs.zen-cart.com/dev/code/database_querying/ can help.

[mc12345678]

What is the difference between sanitization of a variable with 'string' and sanitization with 'stringIgnoreNull' in $db->bindVars when the variable value is NULL (not a string), and 'NULL' (a string). What is stored in the database? I can't seem to get a true NULL stored in the database.
Thank you!

zc1.5.7c, php8.0, mysql5.7.34 or 8.0.8 (production)

What does your chain of query statements look like? Depending on why the variable evaluated to null it either could cause a notice to be logged or result in an empty quote/string. If you are expecting the value to be null and to be able to store a null value then need to evaluate for that condition in advance.

Either within the use of bindVars, the value to be stored would be what has been sent along or a string of NULL as a string or bypass that particular replacement method of using stringIgnoreNull.

Your question though was if the variable to be stored ===null such that is_null($var) == true, then the response is the same for sanitization using either method. Although not stated in the link dbltoe provided, a text string that includes NULL will be assigned to the data value of null. So even if the word is: ANNULLED, when substituting as a string the entire text will be changed to just a null type field value. Or if there is a sentence being stored as a string and anywhere within the string is: NULL, then the value stored will be null.

So if you want to use bindVars to sanitize the value as a string and if you expect the value to actually be set but at add the value null, then in the third parameter, would suggest something like:

(is_null($var) ? 'NULL' : $var)

Or because it looks like you are using php 8:

($var ?? 'NULL')

If using stringIgnoreNull, then would want to address in some other way. In the php, somewhere evaluate the $var and then determine how to do the substitution. May need to use string of 'NULL' or some other action to ensure the variable is stored as a null value instead of empty text.

[Dave224]

I ran a test to find the answer to my question since I did not understand the documentation cited in post 2 ( I had read it before I posted the original question). I set up a test table allowing NULL values and wrote 7 variables to the table using $db->bindVars with 'string' and with 'stringIgnoreNull'. The variables were (1) a string enclosed with single quotes, (2) a string enclosed in double quotes, (3) an empty string enclosed in single quotes (''), (4) an empty string enclosed with double quotes (""), (5) the keyword NULL, (6) NULL enclosed in single quotes, and (7) NULL enclosed in double quotes. I then read the values from the table using SELECT.

The results showed no difference between using 'string' and 'stringIgnoreNull' for the first five variables. is_string was TRUE for all five, is_null was FALSE for all five, isset was TRUE for all five, and empty was FALSE for variables 1 and 2, and TRUE for variables 3, 4 and 5. But variables 6 and 7, NULL enclosed in quotes, did differ between 'string' and stringIgnoreNull'. NULL was treated as a string for 'stringIgnoreNull' and as a real NULL for 'string'. In other words, is_string and isset were TRUE and is_null and empty were FALSE when 'stringIgnoreNull' was used, and the reverse when 'string' was used.

So if you want to save a variable as NULL so that is_null and empty are TRUE, and is_string and isset are FALSE, you must set the variable to 'NULL' or "NULL" and use 'string' in $db->bindVars when you use zen cart's database functions. Interestingly NULL alone (without quotes) was treated as an empty string.

[torvista]

I ran a test to find the answer to my question since I did not understand the documentation

Can you improve the documentation with your now-expert knowledge?

[mc12345678]

I ran a test to find the answer to my question since I did not understand the documentation cited in post 2 ( I had read it before I posted the original question). I set up a test table allowing NULL values and wrote 7 variables to the table using $db->bindVars with 'string' and with 'stringIgnoreNull'. The variables were (1) a string enclosed with single quotes, (2) a string enclosed in double quotes, (3) an empty string enclosed in single quotes (''), (4) an empty string enclosed with double quotes (""), (5) the keyword NULL, (6) NULL enclosed in single quotes, and (7) NULL enclosed in double quotes. I then read the values from the table using SELECT.

The results showed no difference between using 'string' and 'stringIgnoreNull' for the first five variables. is_string was TRUE for all five, is_null was FALSE for all five, isset was TRUE for all five, and empty was FALSE for variables 1 and 2, and TRUE for variables 3, 4 and 5. But variables 6 and 7, NULL enclosed in quotes, did differ between 'string' and stringIgnoreNull'. NULL was treated as a string for 'stringIgnoreNull' and as a real NULL for 'string'. In other words, is_string and isset were TRUE and is_null and empty were FALSE when 'stringIgnoreNull' was used, and the reverse when 'string' was used.

So if you want to save a variable as NULL so that is_null and empty are TRUE, and is_string and isset are FALSE, you must set the variable to 'NULL' or "NULL" and use 'string' in $db->bindVars when you use zen cart's database functions. Interestingly NULL alone (without quotes) was treated as an empty string.

Can you improve the documentation with your now-expert knowledge?

May I suggest either an additional set or a modification to one set of test values if going to improve the instruction? The string in single or double quotes doesn't identify characteristics of the string where it might impact the further testing. E.g., is the string the string value 0 or contain a long string that contains trigger text? Instead of just 'NULL' and/or "NULL", try a value or any strong of text that uses all caps, where the word null is part of a word within that sentence. Such as:
'I JUST COULD NOT BELIEVE THEIR WEDDING WAS ANNULLED IN TWO HOURS!'
I believe you may further see the power and the need of the two different sanitizers.