php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #25845 Convert empty strings to NULL in DB::execute()
Submitted: 2003-10-12 12:06 UTC Modified: 2003-10-13 08:12 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:0 of 0 (0.0%)
From: temporary1 at understroem dot dk Assigned:
Status: Not a bug Package: PEAR related
PHP Version: 5.0.0b1 (beta1) OS:
Private report: No CVE-ID: None
 [2003-10-12 12:06 UTC] temporary1 at understroem dot dk
Description:
------------
It would be nice if empty strings were converted into the database NULL value when you use DB::execute(). Say you have a form in which the user writes a number which is later to be stored in a database column defined as SMALLINT. When the user pushes the submit button, the number will be accessible to the receiving script as, say, $_POST['age']. The script then tries to insert it into the database:

$preparation = $db->prepare('INSERT INTO users (
  age
)
VALUES (
  ?
)');

$db->execute($preparation, array(
  $_POST['age']
));

This works fine if the user actually entered his/her age. But say the age form field is optional. Now, the value of $_POST['age'] is an empty string, and when you run the above code, the database will complain that you're trying to insert a string into a SMALLINT column (at least PostgreSQL will behave that way - I don't know about other databases).

Most times when a programmer makes code that enters empty string into a database, he/she doesn't actually want the field to contain an empty string - he wants the field to be empty. Thus, it would be nice if PEAR DB converted empty strings into the database NULL value when you run execute(). The alternative is to run a lot of checks for each $_POST variable and then use '!' instead of '?' to insert:

$_POST['age'] = (!empty($_POST['age'])) ? $db->quote($_POST['age']) : 'NULL';

$preparation = $db->prepare('INSERT INTO users (
  age
)
VALUES (
  !
)');

$db->execute($preparation, array(
  $_POST['age']
));

...but that's a lot of work when you're dealing with a lot of form fields.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2003-10-13 05:24 UTC] temporary1 at understroem dot dk
I've altered DB/common.php to make it insert the database NULL value when the data in the second argument to DB::execute() is an empty string. See http://understroem.dk/lab/common.php.diff
 [2003-10-13 05:26 UTC] temporary1 at understroem dot dk
I should add that the version of common.php which I altered is 1.26.
 [2003-10-13 06:30 UTC] mansion@php.net
Hi,

I don't see the need for such a "feature". An empty string is not null and should not be treated as such. It's really up to you to filter your values before they get inserted. Your proposal will ad more problems than it will solve.

Furthermore, it's not a good habit to have NULL columns in a database. If a column can be NULL, then most of the time it's useless. I suggest you have your column defaut to '' or 0 or whatever. Well, that's my opinion...

 [2003-10-13 06:40 UTC] lsmith@php.net
Well NULL vs. empty strings is a mess.
oracle stores all empty strings as NULL for example.

However NULL's behave quite differently than empty strings due to the special character of NULL.

Anyways there really is no point in adding this feature imho as its usefulness is quite specific. I recommend that you write a little wrapper function instead.
 [2003-10-13 08:12 UTC] cox@php.net
You just need:

$_POST['age'] = (!empty($_POST['age'])) ? $_POST['age'] : null;

If you pass a php NULL (constant), it will be converted by sql NULL in execute().

Tomas V.V.Cox
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Tue Feb 18 01:01:26 2020 UTC