TMA-MYSQL-4.8

Ensure 'sql_mode' Contains 'STRICT_ALL_TABLES'

LowLevel 2Automatedmysql

Description

When data changing statements are made (i.e., INSERT, UPDATE), MySQL can handle invalid or missing values differently depending on whether strict SQL mode is enabled. When strict SQL mode is enabled, data may not be truncated or otherwise "adjusted" to make the data changing statement work. Runs mysql -NBe with the client defaults: put an administrative account in ~/.my.cnf (mode 0600) or a mysql_config_editor login path, not MYSQL_PWD (see 1.4/1.6).

How it is checked

Audit method: SQL query

sql_query

out=$(mysql -NBe "SELECT IF(FIND_IN_SET('STRICT_ALL_TABLES', @@global.sql_mode) > 0, 'compliant', 'noncompliant')" 2>/dev/null) || exit 1; printf '%s\n' "$out"

Expected output: compliant

How it is fixed

Fix method: Guidance or infrastructure patch

Add STRICT_ALL_TABLES to sql_mode under [mysqld] (e.g. sql_mode=STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION) after testing applications, then restart or SET GLOBAL sql_mode.

Properties

Operating systems: Any
Tags: mysqldatabaseconfiguration
Reference: CIS Oracle MySQL Community Server 8.4 Benchmark v1.1.0 - 4.8 Ensure 'sql_mode' Contains 'STRICT_ALL_TABLES'

Check this host

Run this on a machine you are allowed to audit. The scan is read-only.

shell

tmaegis scan --target mysql --level 2