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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux