By slayerment on

I am having issues with adding null values to my database via db_insert.

Here is my insert code:

db_set_active('global');
db_insert('contact')->fields(array(
	'domain' => global_get_domain(),
	'name' => $form_state['values']['name'],
	'email' => $form_state['values']['email'],
	'phone' => $form_state['values']['phone'],
	'state' => $form_state['values']['state'],
	'amount' => $form_state['values']['amount'],
	'message' => $form_state['values']['message'],
	'uri' => $_SERVER['REQUEST_URI'],
	'referer' => $_SERVER['HTTP_REFERER'],
	'hostname' => $_SERVER['REMOTE_ADDR'],
	'timestamp' => time(),
))->execute();
db_set_active();

I am pulling values from my form, but if one of those values was not entered by the user it will not be inserted into my database using db_insert. For instance if $form_state['values']['state'] is null I will receive the error:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'state' cannot be null: INSERT INTO {tools_contact} ...

If this variable has a value everything works correctly.

So my question would be. Do I have to perform a check on each variable that I am inserting into my database before inserting it first? This seems like a lot of unnecessary work. It seems like if a variable is null db_insert should automatically skip it. Or am I doing something completely wrong?

Thanks!

⋅ Categories: Drupal 7.x

Comments

jscoble’s picture

jscoble commented

As your error message states, the column does not allow null values. Since the database won't accept null values for that table.column, you will need to send it something other than null, i.e. an empty string '' or whatever you decide the appropriate non-null value is.

There is no getting around a column level constraint on a database unless you modify the table.column so that it allows nulls. That is not recommended, since such constraints are defined for a reason.

slayerment’s picture

slayerment commented

Thanks for the reply. The main reason I ask is because in Drupal 6 db_query() would automatically add a blank value or at least handle a null value. And now it is forcing me to add a blank value in D7. It just seems like a lot of extra work to be checking whether or not each value I enter into a database exists or not. I don't know.

jscoble’s picture

jscoble commented

Speaking generally of this stuff, and not Drupal specific, I would expect the db_query not implicitly converting NULLS to a non-null value to be the proper behavior.

NULLs are distinctly different than anything else and should be handled differently, which is why databases provide IS NULL and IS NOT NULL rather than using the standard comparison operators. Implicitely converting a NULL value to something else just so it fits into a table can lead to destabilizing the data and downstream issues. How to handle NULLs in input is something the coder should decide, not the function being called to insert data into the database.

Validating and sanitizing incoming values within your code is considered a best practice. It helps prevent security issues and unwanted side affects that can arise from input that is different than expectations. Malicious users actively seek to find holes in input validation that they can exploit. Drupal helps prevent some of the issues that can arise, but there is no replacement for checking input.

Sanatizing input cartoon:
http://xkcd.com/327/