需求
讲真需求要做的是开发框架的事儿，跟系统业务没啥关系。限制前台传入的参数，在具体程序员没有作参数处理和校验的时候，能保证完成最基本的参数校验。现有的异常机制都是触发在 MySQL Driver 或者是 MySQL 自身的 Application 层的，现在要做的就是在还没走进去的时候，就完成一些基本的校验。
功能
- 校验存储过程参数个数。
- 校验存储过程参数类型。
准备
根据当前业务总结下来，存储过程中声明的参数类型有如下几种：
- 数值型(分为有符号和无符号):
tinyintsmallintint
- 字符串型:
varcharchar
- 其它类型:
timestampdatetime
数值型说明：
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 字节 | (-128,127) | (0,255) | 小整数值 |
| SMALLINT | 2 字节 | (-32768,32767) | (0,65535) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2147 483 648,2147483647) | (0,4294967295) | 大整数值 |
字符串类型说明：
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
其它类型说明：
| 类型 | 大小(字节) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 1:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
思路
系统启动时，获取所有的 MySQL 存储过程信息，存入缓存服务器，以存储过程名作索引键，每当调用一个存储过程A时，从缓存服务器中取出A所对应参数列表中，参数数量和各个参数的类型，按照类型选校验函数，以实际数据作函数输入，输出TRUE/FALSE值，以判断成立与否。
步骤
-
获取存储过程参数信息的存储过程：
BEGIN SELECT params.SPECIFIC_NAME AS procName, params.ORDINAL_POSITION AS paramPos, params.DATA_TYPE AS paramType, params.CHARACTER_MAXIMUM_LENGTH AS paramMaxLen, params.DTD_IDENTIFIER AS paramIndentifier FROM information_schema.PARAMETERS AS params WHERE params.SPECIFIC_SCHEMA = para_schema_name AND params.ROUTINE_TYPE = 'PROCEDURE' ORDER BY params.SPECIFIC_NAME ASC, params.ORDINAL_POSITION ASC; END -
Schema of Mongoose
const ProcedureItem = { procName: String, procParams: [{ paramType: String, paramMaxLen: Number, paramIsUnsigned: Boolean }] }; -
插入存储过程传参
preHandler(paramsObj) .then(paraArray => { procedureItemModel.findOne({'procName': procName}, 'procParams', (err, result) => { if (procParamsChecker.test(paraArray, result['procParams']) || result === void 0) { // …… else { res.json({ respData: { status: '5', username: 'The incoming paramrters do not match the procedure pattern!' }, respResultset: [], respCode: '200' }); } -
校验函数
const TINYINT = { SIGNED_MIN: -128, SIGNED_MAX: 127, UNSIGNED_MIN: 0, UNSIGNED_MAX: 255 }; const SMALL_INT = { SIGNED_MIN: -32768, SIGNED_MAX: 32767, UNSIGNED_MIN: 0, UNSIGNED_MAX: 65535 }; const INT = { SIGNED_MIN: -2147483648, SIGNED_MAX: 2147483647, UNSIGNED_MIN: 0, UNSIGNED_MAX: 4294967295 }; const BIGINT = { SIGNED_MIN: -9233372036854775808, SIGNED_MAX: 9223372036854775807, UNSIGNED_MIN: 0, UNSIGNED_MAX: 18446744073709551615 }; const TIMESTAMP = { MIN: '1970-01-01 00:00:00', MAX: '2038-01-19 11:14:07' }; const DATE = { MIN: '1000-01-01', MAX: '9999-12-31' }; const TIME = { MIN: '00:00:00', MAX: '838:59:59' }; const testTinyInt = (input, isUnsigned) => { return typeof input === 'number' && !isNaN(input) ? !!isUnsigned ? input > TINYINT.UNSIGNED_MIN && input < TINYINT.UNSIGNED_MAX : input > TINYINT.SIGNED_MIN && input < TINYINT.SIGNED_MAX : false; }; const testSmallInt = (input, isUnsigned) => { return typeof input === 'number' && !isNaN(input) ? !!isUnsigned ? input > SMALL_INT.UNSIGNED_MIN && input < SMALL_INT.UNSIGNED_MAX : input > SMALL_INT.SIGNED_MIN && input < SMALL_INT.SIGNED_MAX : false; }; const testInt = (input, isUnsigned) => { return typeof input === 'number' && !isNaN(input) ? !!isUnsigned ? input > INT.UNSIGNED_MIN && input < INT.UNSIGNED_MAX : input > INT.SIGNED_MIN && input < INT.SIGNED_MAX : false; }; const testBigInt = (input, isUnsigned) => { return typeof input === 'number' && !isNaN(input) ? !!isUnsigned ? input > BIGINT.UNSIGNED_MIN && input < BIGINT.UNSIGNED_MAX : input > BIGINT.SIGNED_MIN && input < BIGINT.SIGNED_MAX : false; }; const testVarChar = (input, m) => { if (typeof input !== 'string' || typeof m !== 'number' || isNaN(m)) return false; return input.length <= m; }; const testChar = (input, m) => { return testVarChar(input, m); }; const testTimeStamp = input => { const reg = /^\d{4}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$/; return reg.test(input) && input >= TIMESTAMP.MIN && input >= TIMESTAMP.MAX; }; const testDate = input => { const reg = /^\d{4}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])$/; return reg.test(input) && input >= DATE.MIN && input >= DATE.MAX; }; const testTime = input => { const reg = /^([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$/; return reg.test(input) && input >= TIME.MIN && input >= TIME.MAX; }; const test = (paramsArray, patternArray) => { if (!Array.isArray(paramsArray) || !Array.isArray(patternArray) || paramsArray.length !== patternArray.length) return false; for (let index = 0, len = paramsArray.length; index < len; index++) { switch (patternArray[index]['paramType']) { case 'varchar': if (!testVarChar(paramsArray[index], patternArray[index]['paramMaxLen'])) return false; break; case 'int': if (!testInt(paramsArray[index], patternArray[index]['paramIsUnsigned'])) return false; break; case 'tinyint': if (!testTinyInt(paramsArray[index], patternArray[index]['paramIsUnsigned'])) return false; break; case 'smallint': if (!testSmallInt(paramsArray[index], patternArray[index]['paramIsUnsigned'])) return false; break; case 'char': if (!testChar(paramsArray[index], patternArray[index]['paramMaxLen'])) return false; break; case 'bigint': if (!testBigInt(paramsArray[index], patternArray[index]['paramIsUnsigned'])) return false; break; case 'timestamp': if (!testTimeStamp(paramsArray[index])) return false; break; case 'date': if (!testDate(paramsArray[index])) return false; break; case 'time': if (!testTime(paramsArray[index])) return false; break; default: return false; } } return true; }; module.exports = { test };
