Inserting PHP NULL variable value into MSSQL query

Question

I have this PHP IF statement that is working 'fine':

if(EMPTY($_POST['review_date'])){
    $review_date = NULL;
}
else {
    $review_date = $_POST['review_date'];
}

The problem starts when I pass on the variable to the sqlsrv_query:

sqlsrv_query($connect,"UPDATE goals SET review_date='$review_date' WHERE id='$department'") or die(sqlsrv_errors());

In the case where IF statement is TRUE, the value in the review_date cell shows up as 1900-01-01, instead of NULL.

If I try to put quotes around the NULL value in the IF statement, I get the following error:

Notice: Array to string conversion in C:\xampp\htdocs\project\edit.php on line 30

If NULL is inserted into query directly, it executes as it should.

The Column Properties in the goals Table have Allow Nulls enabled and Default Value is set to (NULL).

Swifted through other questions but didn't really get anywhere.

What am I doing wrong here?

UPDATE 1:

As requested, here is the HTML code:

<form method="POST" action="edit.php">

Review date <i>(optional)</i>: <input type="date" name="review_date" value="" placeholder="dd/mm/yyyy" > 

<p><button type="submit">Save</button></p>

</form>

With regards to var_dumps:

var_dump($review_date);

Returned: NULL

var_dump($department);

Returned: string(2) "36"

var_dump(sqlsrv_query($connect,"UPDATE goals SET review_date='$review_date' WHERE id='$department'"));

Returned: resource(7) of type (SQL Server Statement)

There is already a similar thread with the answer for this: http://stackoverflow.com/questions/17784390/mysql-how-to-insert-null-dates — Marin N., May 05 '17 at 11:49
Possible duplicate of [MySQL, how to insert null dates](http://stackoverflow.com/questions/17784390/mysql-how-to-insert-null-dates) — René Hoffmann, May 05 '17 at 11:53
Does that column have a default value such as current date, or can accept NULL values? check also if there's a trigger event. — Funk Forty Niner, May 05 '17 at 11:54
@RenéHoffmann Possibly a duplicate, but if by my comment to them just above is what is going on, then that duplicate won't apply. If the OP accepts one of the answers, then I will close it with the duplicate you flagged it as. They need to post their db schema. — Funk Forty Niner, May 05 '17 at 11:58
@Fred-ii- yes the default value has been set to NULL. No trigger events. — kristapmon, May 05 '17 at 11:59
@kristapmon Try it with quotes `$review_date = 'NULL';` - That should work. — Funk Forty Niner, May 05 '17 at 12:00
@kristapmon I just read this *"If I try to put quotes around the NULL value in the IF statement"* - so my above comment doesn't apply; an oversight on my part. Did you check the possible duplicate shown further up in comments? Did you try any of the answers below also? — Funk Forty Niner, May 05 '17 at 12:04
@Fred-ii- getting the **Notice: Array to string conversion in C:\xampp\htdocs\project\edit.php on line 30** message. — kristapmon, May 05 '17 at 12:05
@Fred-ii- will go through the comments below and in the links mentioned. — kristapmon, May 05 '17 at 12:06
can you `var_dump($review_date);` and `var_dump($department);` see what those return? Something strange is happening somewhere. You may need to add a bit more code, including the html form. @kristapmon also echo / var_dump the query. — Funk Forty Niner, May 05 '17 at 12:08
@kristapmon another thing; make sure the input for the POST array doesn't have a default value like `value="xxx"`. You may need to update your question to contain the html form and also the db schema. There isn't anything else that I can think of. — Funk Forty Niner, May 05 '17 at 12:13
@kristapmon I revisited your question and did not see an update, nor any comments placed under any of the answers. However, if one of those answers did solve the question, consider accepting one. In not commenting or accepting an answer, leaves everyone wondering if their answer solved it or not, so the question is unclear at this point in time. — Funk Forty Niner, May 05 '17 at 12:47
@Fred-ii- apologies I have now updated the description initial comment — kristapmon, May 05 '17 at 13:17

score 1 · Accepted Answer · answered May 05 '17 at 12:37

1

You code is dangerous because it contains SQL-injections!

You should do this:

if(EMPTY($_POST['review_date'])){
    $review_date = NULL;
}
else {
    $review_date = $_POST['review_date'];
}

$sql = "UPDATE goals SET review_date=? WHERE id=?";
$params = array($review_date, $department);

$stmt = sqlsrv_query( $conn, $sql, $params);

By using parameters in queries you can transfer this null value without problems.

ALWAYS USE PARAMETERS!

answered May 05 '17 at 12:37

coding Bott

4,287
1
27
44

Changed up the code to include parameters and the NULL value is now being transferred. Thanks very much @Bernd-Ott I am aware of the SQL-injections but wanted to get the functionality first. Thanks for pointing it out though. – kristapmon May 05 '17 at 14:10

ScaisEdge · Answer 2 · 2017-05-05T14:55:31.093

assign the NULL to var implies an empty date and not a null date so

you could use different condition for generate dinamically the code you need

if(EMPTY($_POST['review_date'])){
    $code = ' SET review_date = NULL ' ;
}
else {
    $code = ' SET review_date = ' $_POST['review_date'];
}


sqlsrv_query($connect,"UPDATE goals ". $code . " WHERE id='$department'") or die(sqlsrv_errors());

be careful using php var in sql code you could be victim of sqlinjection

score 0 · Answer 3 · answered May 05 '17 at 11:51

0

It is because whenever there is a missing value, send it as NULL and not as empty string. Empty string will be default to 1900-01-01

ex

declare @d datetime
set @d=''
select @d as date

result

1900-01-01 00:00:00

answered May 05 '17 at 11:51

Madhivanan

13,470
1
24
29

Inserting PHP NULL variable value into MSSQL query

3 Answers3