使用MariaDB Connector/Node.js执行批量SQL时出现语法错误

问题场景 #

我用以下函数处理POST请求，目的是截断当前连接数据库的所有表：

exports.resetall = async (req, res, next) => {
    let conn;

    try {
        conn = await pool.getConnection({multipleStatements: true});

        await conn.query(`
            SET FOREIGN_KEY_CHECKS = 0;

            SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ';')
            FROM   information_schema.tables
            WHERE  table_type   = 'BASE TABLE'
            AND    table_schema = DATABASE();

            PREPARE stmt FROM @str;

            EXECUTE stmt;

            DEALLOCATE PREPARE stmt;

            SET FOREIGN_KEY_CHECKS = 1;
        `);

        res.status(200).json({
            status: "OK"
        });
    } catch(err) {
        return next(err);
    } finally {
        if(conn) conn.end();
    }
};

执行时触发SQL语法错误：

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name)\n ...' at line 3

这个脚本在其他SQL客户端能正常运行，仅在Node.js环境报错。我试过移除分号、用DELETE FROM替换TRUNCATE TABLE、移除换行、删除SET语句等操作，都没解决问题。

补充信息 #

连接池创建代码如下：

配置文件：

const config = {
    http: {
        host: "127.0.0.1",
        port: 9103
    },

    mariadb: {
        host: "127.0.0.1",
        port: 3306,
        user: "intelliq",
        password: "",
        database: "intelliq"
    }
};

module.exports = config;

连接池初始化：

const mariadb = require(`mariadb`);
const config = require(`../config`);

const pool = mariadb.createPool(config.mariadb);

module.exports = pool;

解决方案 #

问题出在multipleStatements的使用限制和原脚本的逻辑缺陷上。Node.js的mariadb驱动对多语句模式下的会话变量传递、预处理语句执行有特殊限制，而且原脚本中SELECT @str := ...会覆盖变量值，只能截断最后一个表，根本达不到清空所有表的目的。

换成分步执行的方式更可靠，代码如下：

exports.resetall = async (req, res, next) => {
    let conn;

    try {
        conn = await pool.getConnection();
        
        // 禁用外键检查，避免截断时触发关联报错
        await conn.query("SET FOREIGN_KEY_CHECKS = 0");
        
        // 查询当前库下所有基础表
        const tables = await conn.query(`
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_type = 'BASE TABLE' 
            AND table_schema = DATABASE()
        `);
        
        // 逐个执行截断操作
        for (const { table_name } of tables) {
            // 用??占位符处理表名，避免SQL注入
            await conn.query(`TRUNCATE TABLE ??`, [table_name]);
        }
        
        // 恢复外键检查
        await conn.query("SET FOREIGN_KEY_CHECKS = 1");
        
        res.status(200).json({ status: "OK" });
    } catch(err) {
        return next(err);
    } finally {
        if(conn) conn.end();
    }
};

原脚本失效原因 #

1. mariadb驱动的multipleStatements模式下，会话变量（如@str）的传递存在异常，后续语句无法读取到正确的变量值；
2. 即使变量传递正常，SELECT @str := ...查询多表时，变量会被最后一条结果覆盖，最终只截断一个表，完全不符合预期。

优化说明 #

• 不需要开启multipleStatements，降低SQL注入风险；
• 用参数占位符??处理表名，避免拼接字符串带来的安全问题；
• 分步执行逻辑清晰，便于调试和维护。

内容的提问来源于stack exchange，提问作者idressos