[wp-trac] [WordPress Trac] #43578: Unexpected MYSQL data format

[WordPress Trac]

#43578: Unexpected MYSQL data format
-------------------------------------+-----------------------------
 Reporter:  loranrendel              |       Owner:
     Type:  defect (bug)             |      Status:  new
 Priority:  normal                   |   Milestone:  Future Release
Component:  Database                 |     Version:  4.9.4
 Severity:  normal                   |  Resolution:
 Keywords:  needs-patch 2nd-opinion  |     Focuses:
-------------------------------------+-----------------------------
Changes (by dd32):

 * keywords:   => needs-patch 2nd-opinion
 * milestone:  Awaiting Review => Future Release


Comment:

 Hi @loranrendel and welcome back to Trac.

 This happens because WordPress has mapped the `user_id` column internally
 as a numeric field and applies `%d` to the values through `insert()`,
 `replace()` and `update()` on `$wpdb`.

 For reference, the query generated by the `$wpdb->insert()` call is this:
 {{{
 INSERT INTO `wp_test` (`id`, `user_id`) VALUES (%s, %d)
 ->
 INSERT INTO `wp_test` (`id`, `user_id`) VALUES ('1', 0)
 }}}

 Possible options:
 a) Expected behaviour, don't do this, and if you do, specify the data-
 types
 b) Extend the `field_types` to have a table specifier, so that the fields
 have to be defined on a per-table basis.
 c) Treat custom tables as custom tables, and only ever use `%s` unless
 specified otherwise in the call. However `$wpdb->field_types` is public
 and plugins could theoretically update it.

 #b + #c has a back-compat issue with existing code which is receiving `%d`
 because it's writing to a custom table with similar column names would
 start writing with `%s` instead, which then uses MySQL's data-type
 conversion instead.
 I should say though, that I don't see that as a downside, just a side
 effect. The benefits would outweigh the downsides.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/43578#comment:1>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform