4.9 Ensure 'sql_mode' Contains 'STRICT_ALL_TABLES'

Information

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.

Rationale:

Without strict mode the server tries to proceed with the action when an error might have been a more secure choice. For example, by default MySQL will truncate data if it does not fit in a field, which can lead to unknown behavior, or be leveraged by an attacker to circumvent data validation.

Impact:

Applications relying on the MySQL database should be aware that STRICT_ALL_TABLES is in use, such that error conditions are handled appropriately.

Solution

Set STRICT_ALL_TABLES to the sql_mode in the server's global configuration, for example:

SET GLOBAL 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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Default Value:

NO_ENGINE_SUBSTITUTION

See Also

https://workbench.cisecurity.org/files/3859

Item Details

Category: PLANNING, SYSTEM AND SERVICES ACQUISITION

References: 800-53|PL-8, 800-53|SA-8

Plugin: MySQLDB

Control ID: 60609804316a012364f117ffb3a235b793306894df4cf71bbb4228b51f1aff9e