[wp-trac] [WordPress Trac] #52723: Admin options.php default value to NULL for option_value may lead to MySQL Integrity constraint violation error, potential other bugs

[WordPress Trac]

#52723: Admin options.php default value to NULL for option_value may lead to MySQL
Integrity constraint violation error, potential other bugs
----------------------------+-----------------------------
 Reporter:  pinoceniccola   |      Owner:  (none)
     Type:  defect (bug)    |     Status:  new
 Priority:  normal          |  Milestone:  Awaiting Review
Component:  General         |    Version:
 Severity:  normal          |   Keywords:
  Focuses:  administration  |
----------------------------+-----------------------------
 It looks like `wp-admin/options.php` set a `null` value by default for any
 unchecked option:

 https://core.trac.wordpress.org/browser/trunk/src/wp-
 admin/options.php#L306

 Now, this leads to execute queries like this by `update_option`:

     UPDATE `wp_options` SET `option_value` = NULL WHERE `option_name` =
 'default_pingback_flag'

 Which is wrong, given the schema explicitly set `option_value` to `NOT
 NULL`:

 https://core.trac.wordpress.org/browser/trunk/src/wp-
 admin/includes/schema.php#L144

 This would trigger an integrity constraint violation error by MySQL when
 in (default) strict mode:

     Error! SQLSTATE[23000]: Integrity constraint violation: 1048 Column
 'option_value' cannot be null

 To get around this (and for other reasons too, I presume), WordPress
 currently tries to disable any MySQL strict mode in the `$wpdb` class,
 with the effect that MySQL silently "fix" the error itself:

 https://core.trac.wordpress.org/browser/trunk/src/wp-includes/wp-
 db.php#L567

 https://core.trac.wordpress.org/browser/trunk/src/wp-includes/wp-
 db.php#L826

 But **not every environment support this**, so there are people out there
 who cannot save options and they are confused about the reason why, for
 example:

 https://www.reddit.com/r/Wordpress/comments/l61rvs/cannot_disable_avatars/
 https://wordpress.org/support/topic/discussion-comment-settings-saved-
 changes-are-not-taking-effect-at-all/
 https://wordpress.org/support/topic/wordpress-database-error-column-
 option_value-cannot-be-null/

 A simple solution would be to set a different default value (`0` or even
 an empty string) in `wp-admin/options.php` and, ''better yet'', **cast any
 `NULL` value to the same different default value in both `update_option`
 and `add_option`**.

 Please note that, without a fix, **this bug may also lead to other nasty
 side effects**.

 As a quick fix/test, I successful got around this with this simple filter:

 {{{#!php
 <?php
 add_action( 'init', 'p_options_fix' );

 function p_options_fix() {

         add_filter( 'pre_update_option', 'p_options_fix_not_null', 10, 3
 );

         function p_options_fix_not_null( $value, $option, $old_value ) {

                 // The fix: cast NULL values to 0
                 $value = ( is_null($value) ) ? 0 : $value;

                 return $value;
         }
 }

 }}}

 But I think this is something that should be really fixed in the core.

 Thank you.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/52723>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform