Re: [PHP] Writing MySQL Update Query with NULL value

Hi Bill,

Please keep the replies on the list so others can provide their input 
and also learn.


>> 2) You do need quotes around your data, plus you should use 
>> mysql_real_escape_string to stop sql injection attacks:
> 
> Again, I'm using mysql_real_escape_string() in my code, just trying to 
> focus on the NULL question and not complicate the code I posted.

Fair enough :)

>> // set a default of NULL
>> $suffix = "NULL";
>> if (!empty($_POST['suffix'])) {
>>   // note - you need to add the quotes around the data here
>>   $suffix = "'" . mysql_real_escape_string($_POST['suffix']) . "'";
>> }
> 
> Is this materially different than: $suffix = empty($suffix) ? NULL : 
> $suffix;
> having already sanitized $suffix?


It may end up different in your final query.

Notice the quotes around $suffix where mysql_real_escape_string is used.

You cannot have quotes around the "NULL" keyword otherwise the database 
(mysql, postgres, oracle, mssql will all work the same in this regard) 
treats that as a text value.

NULL !== "NULL".

If you have a query like:

update table set field='NULL' where id='X';

Then field will have a value of 'NULL' - which is a text string (a value 
the db knows about).


If you have a query like:

update table set field=NULL where id='X';

Then field will have a NULL value which means "unknown".


You can test that out with these queries:

// this will find "unknown" values for "field".
select id, field from table where field is null;

// this will find values for field where it contains the string 'NULL'.
select id, field from table where field='NULL';


or even better:

select id, field, isnull(field) from table;

-- 
Postgresql & php tutorials
http://www.designmagick.com/