mysql存储过程注入

CREATE PROCEDURE SafeGetUser(IN username VARCHAR(50))
BEGIN
    -- 使用预处理语句防止注入
    SET @username = username;
    PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
    EXECUTE stmt USING @username;
    DEALLOCATE PREPARE stmt;
END

CREATE PROCEDURE AddUser(IN uname VARCHAR(50), IN pwd VARCHAR(50))
BEGIN
    -- 验证用户名只包含字母数字和下划线
    IF uname REGEXP '^[a-zA-Z0-9_]+$' = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid username format';
    END IF;
    
    -- 验证密码长度
    IF LENGTH(pwd) < 8 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password too short';
    END IF;
    
    INSERT INTO users (username, password) VALUES (uname, pwd);
END

CREATE PROCEDURE SafeProcedure()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 记录错误但不暴露详细信息
        ROLLBACK;
        RESIGNAL;
    END;
    
    -- 业务逻辑
END

-- 更安全的动态SQL方式
CREATE PROCEDURE SafeDynamicQuery(IN table_name VARCHAR(50))
BEGIN
    -- 只允许特定表名
    IF table_name NOT IN ('users', 'orders', 'products') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid table name';
    END IF;
    
    SET @sql = CONCAT('SELECT * FROM ', table_name);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END