DB error using updateObject or Store after upgrading to Joomla 4

Question

I am a component developer and now I am migrating my component to Joomla 4.x.

In Joomla 3.x I could do code like:

$row= new stdClass();
$row->id = "2";
$row->user_id = "";
$row->anythind = "foo bar";

// Insert the object into the user profile table.
$result = JFactory::getDbo()->updateObject('#__test_table', $row, 'id');

Note that user_id is an empty string but in the DB is an integer type column. But once executed the DB value of user_id would be cero.

After upgrading to Joomla 4.4.3 the same code gives this error:

"1366 Incorrect integer value: '' for column database.#__test_table.user_id at row 1"

Does not accept an empty string for a column of integers.

I have also tried it with the JTable::store() method and I have the same result.

With dozens of administration forms it is literally impossible to force all integer fields to have a zero and not simply leave them blank.

I appreciate any help you can give me on this.

Answer 1

I'm not sure what's the point of this question since you already know the answer. You are using a string value for an integer column. This is clearly incorrect even if it worked before. The reason this did work in J3 is because the database driver explicitly used non-strict SQL mode. In J4 the driver uses STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION modes. And these are still much looser than MySQL's defaults (ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO and NO_ENGINE_SUBSTITUTION on MySQL 8). In the future, SQL modes in Joomla should only get stricter.

Furthermore, adding data filtering and validation, and using sane defaults, is not "literally impossible". It's necessary for a secure and reliable application and ideally should be done from the beginning.