面试题：MySQL Schema设计中如何防止注入攻击

Question

在MySQL Schema设计场景下，简述防止SQL注入攻击的常见方法及在Schema层面可采取的措施。

Answer 1

常见方法

1. 使用参数化查询（预处理语句）：
   • 在大多数编程语言与MySQL交互的API中，支持参数化查询。例如在Java中使用JDBC，代码如下：

String sql = "SELECT * FROM users WHERE username =? AND password =?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();

• 这种方式下，SQL语句和参数值是分开传递的，数据库会对参数进行严格的类型检查和转义处理，有效防止SQL注入。

2. 输入验证：
   • 对用户输入的数据进行严格验证，确保其符合预期格式。比如，若某个字段预期是整数类型，在接收用户输入后，使用正则表达式或类型转换函数进行验证。在Python中可以这样验证整数输入：

import re
user_input = input("请输入一个整数: ")
if not re.match(r'^[0-9]+$', user_input):
    raise ValueError("输入必须是整数")

• 只允许特定范围内的字符或数据格式，减少恶意SQL语句构造的可能性。

3. 使用存储过程：
   • 存储过程是预编译的SQL代码块，存储在数据库中。例如在MySQL中创建并使用存储过程：

DELIMITER //
CREATE PROCEDURE GetUser(IN user_name VARCHAR(255))
BEGIN
    SELECT * FROM users WHERE username = user_name;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUser('test_user');

• 调用存储过程时，输入参数会被数据库安全处理，降低SQL注入风险。

Schema层面措施

1. 最小化权限原则：
   • 为数据库用户分配最小权限，只授予其执行所需操作的权限。比如，一个应用程序只需要读取特定表的数据，就只为该应用程序对应的数据库用户授予对这些表的SELECT权限，而不授予INSERT、UPDATE、DELETE等其他不必要的权限。
   • 避免使用具有过高权限（如root权限）的用户连接数据库执行应用程序相关操作。
2. 数据类型严格定义：
   • 在定义表结构时，明确指定每列的数据类型。例如，将用户年龄字段定义为INT类型，而不是VARCHAR类型。这样，当用户尝试输入非数字字符时，数据库会自动拒绝，防止恶意构造SQL语句。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT,
    username VARCHAR(255)
);

3. 避免使用动态SQL：
   • 在Schema设计和相关操作中，尽量避免使用动态生成的SQL语句。动态SQL容易因为拼接用户输入而引入SQL注入风险。如果确实需要动态SQL，也要谨慎处理参数，确保安全性。例如，尽量将动态部分作为参数传递，而不是直接拼接SQL语句。