Skip to content

Ensure 'sql_mode' Contains 'STRICT_ALL_TABLES'

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.

Rationale

Without strict mode the server tries to proceed with an action when an error might have been a more secure choice. When strict SQL mode is enabled, data may not be truncated or otherwise "adjusted" to make the data changing statement work.

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.

Applies To

  • Databases

Tags

This rule is applied when the following tags are present:

Tag With Value
secureclouddb/provider aws
secureclouddb/service rds
secureclouddb/engine mysql

Default Rule

const { getServerSetting, isEmpty } = module

/**
 * @param {Object} databaseSettings - database settings object
 * @returns {boolean} true if STRICT_ALL_TABLES is present in sql_mode list
 */
function validate(databaseSettings) {
  const settingName = "sql_mode"
  const expectedValue = "strict_all_tables"
  const currentValue = getServerSetting(databaseSettings, settingName)

  var success = false;
  if (!isEmpty(currentValue)) {
    const currentValueArray = currentValue.toLowerCase().split(",")
    success = currentValueArray.includes(expectedValue)
  }

  return {
      success,
  }
}

// invoke
validate(databaseSettings);