SQL 动态 SQL 执行技巧与风险

concat比+更安全，因mysql中+会触发隐式类型转换导致sql逻辑错误，而concat强制字符串拼接且行为可预测；动态sql需严格校验表名、使用用户变量传参，并避免在函数/视图中使用。

动态拼接 SQL 字符串时，为什么 CONCAT 比 + 更安全？

MySQL 5.7+ 默认开启 sql_mode=STRICT_TRANS_tableS，用 + 拼接字符串会触发隐式类型转换：如果任一操作数是数字（比如 id 字段值为 123），整个表达式变成数值加法，'select <em> FROM user WHERE id = ' + 123</em> 直接算成 123，最终执行的是 SELECT FROM user WHERE id = 123 —— 看似正常，实则丢失了原始意图，且无法注入调试痕迹。

CONCAT 强制所有参数转为字符串，哪怕传入 NULL 也会让整结果为 NULL（可配合 ifNULL 处理），行为可预测。

• 永远用 CONCAT('SELECT <em> FROM ', @table_name, ' WHERE id = ', @id)</em>，不用 'SELECT FROM ' + @table_name + ' WHERE id = ' + @id
• 如果 @table<em>name</em> 来自用户输入，必须先校验是否匹配 ^[a-zA-Z][a-zA-Z0-9_]*$ 正则，否则 CONCAT 也救不了你
• postgresql 要用 ||，SQL Server 用 + 但需显式 CAST，别跨数据库硬套

存储过程中用 PREPARE + EXECUTE，变量作用域怎么不丢？

PREPARE 只认用户变量（@var），不认存储过程的局部变量（DECLARE var int）。写成 SET @sql = CONCAT('SELECT * FROM t WHERE id = ', my_id); 是错的 —— my_id 在 PREPARE 执行时已出作用域，实际拼进去的是 0 或 NULL。

正确做法是全部转成用户变量，且确保赋值在 PREPARE 前完成：

• 存储过程内，先把局部变量赋给用户变量：SET @id = my_id;，再拼 @sql
• 多参数时别手抖写错名：SET @name = in_name; SET @status = in_status;，然后 CONCAT(... ' AND name = ? AND status = ?')
• EXECUTE stmt using @id, @status; —— 这里的 @id 必须和前面 SET 的一致，大小写敏感（MySQL 默认不区分，但配置了 lower_case_table_names=0 时可能翻车）

mysql_real_escape_string 已废弃，PHP 中该用什么防注入？

mysql_real_escape_string 在 PHP 7.0+ 彻底移除，且它只处理字符串，对表名、列名、排序方向（ASC/DESC）完全无效。现在唯一靠谱路径是：参数化仅用于值，结构部分靠白名单校验。

• 值一律走 pdo::prepare() + bindValue()，例如：$stmt = $pdo->prepare("SELECT * FROM users WHERE status = ?"); $stmt->bindValue(1, $status, PDO::PARAM_STR);
• 表名/字段名/排序字段不能进占位符，必须提前定义允许范围：$allowed_tables = ['users', 'orders']; if (!in_array($table, $allowed_tables)) die('invalid table');
• 排序方向只能接受 ASC 或 DESC，用 in_array($dir, ['ASC', 'DESC']) 判定，别试图“转义”
• 不要用 addslashes() 或 htmlspecialchars() 替代，它们对 SQL 解析层无效

动态 SQL 在视图或函数里为什么总报错 this function has none of DETERMINISTIC...？

MySQL 要求函数/存储过程声明确定性（DETERMINISTIC）、读写权限（READS SQL DATA 等），而动态 SQL（PREPARE/EXECUTE）被认定为“可能非确定”，即使你只是查固定表。

根本原因是：MySQL 无法静态分析动态语句的行为，所以拒绝将其放入函数或视图逻辑中。

• 函数里禁止出现 PREPARE、EXECUTE、DEALLOCATE PREPARE，连 SELECT ... INTO @var 都可能触发限制
• 视图定义里不能含任何动态逻辑，视图本质是预存的 SELECT 文本，不支持变量替换
• 唯一能用动态 SQL 的地方是存储过程（PROCEDURE），且必须显式声明：CREATE PROCEDURE sp_xxx() READS SQL DATA BEGIN ... END
• 如果真需要“动态视图效果”，用存储过程返回结果集，或应用层拼好 SQL 再发给 MySQL

动态 SQL 的核心矛盾就在这儿：越想灵活，越得亲手把住每一道校验关口；漏掉一个表名校验，或者少写一个 SET @var = ...，后面查半天才发现是变量没传进去。