SQL_MODE为严格模式下的数据安全专题报告

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

专题描述	详细描述SQL_MODE为严格模式下可能出现的数据错误风险。
问题提出	1	日期默认'0000-00-00 00:00:00'的，查询时会直接报错。 Caused by: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp URL需添加如下参数：zeroDateTimeBehavior=convertToNull。
	2	OMS数据库因UAT环境和性能测试环境的参数配置不同，导致性能测试环境下应用SQL执行报错，相关参数为STRICT_TRANS_TABLES与explicit_defaults_for_timestamp。
数据风险	1	整型类型数据值截断风险	重点关注 TINYINT类型，其他整数类型上线初期溢出概率低。以“`payment_display_mode` tinyint(4) DEFAULT NULL COMMENT '付款到期日显示方式: 11-最晚汇款日 12-到期日',” 为例。示例：在非严格模式下，应用程序为字段 payment_display_mode 设置一个值小于-128 或大于127 ，则超过部分会被截断（超过-128，则存储为-128，例如 -200 会默认变成 -128；超过127，则存储为127），例如 300 会默认变成127。
	2	浮点类型截断	以“`longitude` DECIMAL(19,5) NULL DEFAULT '0.00000' COMMENT '经度坐标'” 为例。示例：在非严格模式下，应用程序为字段 longitude 设置一个值超过 5位小数或者整数超过14位，则超过部分会被截断。
	3	字符串类型风险	以“`MODIFIER_USER` VARCHAR(30) NUL L DEFAULT NULL COMMENT '更新者用户账号',” 为例。示例：在非严格模式下，应用程序为字段 MODIFIER_USER 设置一个值超过 30个字符，则超过部分会被截断。
	4	时间类型风险	由于JAVA代码认为0000-00-00 00:00:00时间是非法的时间，JDBC中提供参数控制对它的处理，故可通过添加zeroDateTimeBehavior=convertToNull使其转化为null。连接字符串示例： jdbc:mysql://10.202.198.201:3319/oms6?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull
	5	NOT NULL类型风险	非严格模式下，不给字段定义约束 NOT NULL字段赋值或给其一个NULL值，在无定义约束无默认值的情况下：数据值类型的字段值默认为0，字符串类型的字段值默认为空字符串''。从SQL语句和存储过程处理分析，多数情况下都兼容处理了，但依然请研发团队谨慎评估。
问题原因	数据库SQL_MODE设置为严格模式。
解决方案	1	建议DBA团队设置SQL_MODE为严格模式，运行系统进行测试，将根据报错定位存在的数据隐患，修改程序。
	2	设置服务器SQL_MODE为严格模式。
知识点	1	数据库SQL_MODE设置为严格模式将出现问题分为以下几类：1、日期默认'0000-00-00 00:00:00'的；查询时会直接报错， 2、字段类型设置为NOT NULL时，插入NULL值将报错； 3、当插入超出数值字段类型范围的值时，直接报错；
	2	STRICT_TRANS_TABLES释义: 官方文档链接： http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict 官方文档解释：For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and rolled back. 使用该参数的主要目的还是使得数据库面对错误的数据和操作时倾向于报错，而不是给出警告。便于提早发现问题，与规范对数据库的操作。
	3	数据库SQL_MODE设置为非严格模式时，系统上述情况倾向于警告。插入超过数值类型范围字段值时系统取最大值插入，插入字符类型字段值超过长度范围时会截断。