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: stackoverflow.com/questions/17784390/… — Marin N.
– Marin N., Commented May 5, 2017 at 11:49
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
– Funk Forty Niner, Commented May 5, 2017 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
– Funk Forty Niner, Commented May 5, 2017 at 11:58

coding Bott · Accepted Answer · 2017-05-05 12:37:27Z

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 5, 2017 at 12:37

coding Bott

4,3872 gold badges30 silver badges44 bronze badges

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

1 Comment

kristapmon Over a year ago

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.

Madhivanan · Accepted Answer · 2017-05-05 11:51:02Z

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 5, 2017 at 11:51

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Comments

ScaisEdge · Accepted Answer · 2017-05-05 14:55:31Z

0

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

edited May 5, 2017 at 14:55

answered May 5, 2017 at 11:50

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Inserting PHP NULL variable value into MSSQL query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related