NULL fields are exported as empty strings

[Albvadi]

Version Plugin: 3.1.2

Hi,
after search and replace in my database, the import script show a format error.

The problem is that the NULL values in datetime fields in database are exported as empty strings ''.

Example:

• Screenshot from the database with the NULL fields (I removed a few columns):
  

• SQL script exported:

CREATE TABLE `wp_gf_entry` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `form_id` mediumint(8) unsigned NOT NULL,
  `post_id` bigint(20) unsigned DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `date_updated` datetime DEFAULT NULL,
  `is_starred` tinyint(1) NOT NULL DEFAULT '0',
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  `ip` varchar(39) COLLATE utf8mb4_unicode_ci NOT NULL,
  `source_url` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_agent` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `currency` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_status` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_date` datetime DEFAULT NULL,
  `payment_amount` decimal(19,2) DEFAULT NULL,
  `payment_method` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `transaction_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_fulfilled` tinyint(1) DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `transaction_type` tinyint(1) DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `form_id` (`form_id`),
  KEY `form_id_status` (`form_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

#
# Data contents of table `wp_gf_entry`
#
 
INSERT INTO `wp_gf_entry` VALUES (1, 2, NULL, '2019-01-09 08:26:33', '', 0, 1, '0.0.0.0', 'https://www.domain.com/?page_id=142', 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36', 'USD', '', '', '', '', '', NULL, NULL, NULL, 'active'); 
INSERT INTO `wp_gf_entry` VALUES (2, 2, NULL, '2019-01-09 10:18:07', '2019-01-09 10:18:07', 0, 0, '0.0.0.0', 'https://www.domain.com/?page_id=142', 'Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0', 'USD', '', '', '', '', '', NULL, 1, NULL, 'active');
#
# End of data contents of table `wp_gf_entry

When import, the error says it´s incorrect format in the payment_date field

Thanks!

[bueltge]

Hi @Albvadi
please can you retest this topic with the last stable release 3.2.0 ? thx.

[bvdv]

I've checked, current S&R version while Create SQL file always return '' for DB table field value NULL because of this statement (it doesn't check for NULL)

foreach ( $table_structure as $struct ) {
	if ( 0 === strpos( $struct->Type, 'tinyint' )
	     || 0 === stripos( $struct->Type, 'smallint' )
	     || 0 === stripos( $struct->Type, 'mediumint' )
	     || 0 === stripos( $struct->Type, 'int' )
	     || 0 === stripos( $struct->Type, 'bigint' )
	) {
		$defs[ strtolower( $struct->Field ) ] = ( null === $struct->Default ) ? 'NULL' : $struct->Default;
		$ints[ strtolower( $struct->Field ) ] = '1';

	}

I think it may be can be solved by adding to if statement checking for NULL - || 0 === stripos( $struct->Null, 'YES' )

[bueltge]

So maybe you @bvdv will send a Pull Request and help us?

[bvdv]

@bueltge I will check it more carefully and then will sent.

[widoz]

Instead of having all of those conditions, could make sense to switch to a switch statement?

[bvdv]

I was wrong, my suggestion doesn't help and make other problem.

So, I can suggest only just add one more elseif for to avoid turning NULL into ' ' while backup DB.
May be it is not best solution.
But add elseif (null === $value) { $values[] = 'NULL'; } in Exporter.php at line 529, look like solve it.

if ( isset( $ints[ strtolower( $column ) ] ) ) {
	// make sure there are no blank spots in the insert syntax,
	// yet try to avoid quotation marks around integers
	$value    = ( null === $value || '' === $value ) ? $defs[ strtolower( $column ) ] : $value;
	$values[] = ( '' === $value ) ? "''" : $value;
} elseif ( isset( $binaries[ strtolower( $column ) ] ) ) {
	$hex      = unpack( 'H*', $value );
	$values[] = "0x$hex[1]";
} elseif (null === $value) {
	$values[] = 'NULL';
} else {
	$values[] = "'" . str_replace(
			$hex_search,
			$hex_replace,
			$this->sql_addslashes( $value )
		) . "'";
}