Prev: Verifying PostgreSQL Certficates
Next: - Error handling
From: Chris on 9 Apr 2008 20:23
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/
 | 
Pages: 1
Prev: Verifying PostgreSQL Certficates
Next: - Error handling