CodeIgniter Query Builder and MySQL strict mode interoperability

Answer

No. The CodeIgniter Query Builder does not guarantee consistent type handling for loose values under MySQL STRICT_TRANS_TABLES or STRICT_ALL_TABLES. Automatic escaping is sufficient for SQL injection prevention, it is not sufficient to avoid data-type mismatch errors. Explicit casting or correct PHP types at the application layer are required when strict mode is enabled. PHP escaping overhead is negligible compared to driver prepare/execute and MySQL execution; measure those separately with the framework profiler and driver timing.

Confirmed facts

• The Query Builder escapes values passed to where, insert and update unless escaping is disabled or raw SQL is used.
• Escaping/quoting prevents injection. It does not perform column-aware type casting.
• MySQL strict mode rejects or errors on implicit conversions that non-strict mode would silently coerce.
• Profiling consistently shows most query latency is in the database driver prepare/execute cycle and network round-trip, not in PHP escaping logic.

Likely explanation, version-dependent

CodeIgniter 3.x typically builds SQL strings and sends values as strings to MySQLi. Type fidelity is lost in PHP and MySQL performs implicit conversion. Under strict mode this conversion can raise warnings or errors for mismatched types.

CodeIgniter 4.x uses a more modern database abstraction and can use PDO. With PDO bound parameters, PHP native types can be preserved for the driver, but the Query Builder still does not introspect table schema to cast values to column types. Validation remains delegated to the database engine.

The decision to auto-cast based on column definitions is not implemented and is not documented as guaranteed behavior in either 3.x or 4.x.

Steps needed for this case

1. Assume no type guarantee. Ensure application code supplies values with correct PHP types for the target columns, or cast explicitly before passing to the Builder.
2. Do not rely on escaping to prevent strict-mode errors. Add validation/casting in the model layer for numeric, date and boolean fields.
3. Distinguish PHP escaping vs DB execution time:

• Enable CodeIgniter Profiler or Debug Toolbar and record Total Execution Time vs Query Time.
• Log the final SQL with $this->db->last_query() and enable MySQL General Query Log to confirm what is sent.
• Wrap builder calls with microtime around compile and around execute to isolate PHP string building from driver round-trip.

Uncertainty note: exact driver behavior varies by CI version, driver choice MySQLi vs PDO, and PDO emulated prepares setting. Current verification is required for your environment.

One missing diagnostic detail that changes the recommendation: which CodeIgniter major version and database driver you are using, and whether PDO emulated prepares are on. That determines whether bound parameter types are preserved to MySQL.