Node.js mysql2与预处理语句

在Node.js中连接MySQL数据库时，mysql2库比基础的mysql库更受欢迎，主要是因为它对预处理语句（Prepared Statements）的原生支持。这直接解决了SQL注入攻击的风险，并提升了查询性能。如果你现在还在使用字符串拼接来构造SQL语句，那么切换到mysql2的预处理语句是必须的。

为什么预处理语句是Node.js数据库操作的安全基石？

SQL注入的原理是攻击者通过输入恶意字符串，改变原始SQL的逻辑。例如，一个登录查询原本是SELECT * FROM users WHERE username = '输入的用户名' AND password = '输入的密码'。如果用户输入' OR '1'='1作为用户名，拼接后的查询就会变成SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...'，这将绕过密码验证。使用字符串拼接或模板字面量来构造SQL，就是在敞开大门迎接这种攻击。

预处理语句将SQL逻辑与数据完全分离。你首先定义一个带占位符（通常是?）的SQL模板，然后将用户输入的数据作为参数单独传入。数据库驱动会确保参数被安全地转义和处理，即使参数中包含恶意SQL片段，也只会被当作普通字符串数据，而不会被执行。这从根本上消除了注入的可能性。

mysql2库的安装与基本连接

首先，在你的Node.js项目中安装mysql2：npm install mysql2。连接池（Connection Pool）是生产环境的推荐方式，因为它管理一组可重用的连接，提升了效率。

const mysql = require('mysql2/promise'); // 使用Promise API

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'yourpassword',
  database: 'test_db',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

使用预处理语句执行查询：安全与性能兼备

mysql2的预处理语句使用非常简单。对于SELECT查询，使用execute()方法；对于INSERT, UPDATE, DELETE，可以使用execute()，但更推荐使用query()方法配合占位符，因为某些情况下execute()会触发服务器端的预处理语句协议，可能带来额外开销，而query()会在客户端模拟预处理，同样安全。

示例一：安全的用户查询

async function getUser(email) {
  const [rows] = await pool.execute(
    'SELECT id, name FROM users WHERE email = ?',
    [email]
  );
  return rows[0];
}

示例二：插入数据

async function createUser(userData) {
  const sql = 'INSERT INTO users (name, email) VALUES (?, ?)';
  const [result] = await pool.query(sql, [userData.name, userData.email]);
  return result.insertId;
}

这里的?就是占位符。mysql2会按顺序将数组中的参数安全地替换到SQL中。即使userData.name是"O'Brien"这样的字符串，单引号也会被正确转义。

深入理解：预处理语句的性能优势

除了安全，预处理语句对性能也有显著帮助。当你使用execute()方法时，如果数据库服务器支持（如MySQL），SQL语句模板会被预先编译和缓存。当你多次执行同一条语句（例如批量插入或频繁查询），只是参数不同时，数据库服务器无需重复进行语法解析、语义检查和优化查询计划，直接使用缓存的执行计划即可，这能大幅降低CPU开销，尤其是在高并发场景下。

批量插入操作是展示其性能优势的绝佳场景：

async function batchInsertUsers(usersArray) {
  const sql = 'INSERT INTO users (name, email) VALUES ?';
  // 注意这里是 VALUES ?，一个占位符对应一个多维数组
  const values = usersArray.map(user => [user.name, user.email]);
  const [result] = await pool.query(sql, [values]);
  return result.affectedRows;
}

处理复杂查询与命名占位符

当SQL语句非常复杂、参数众多时，使用?占位符容易导致参数顺序混乱。mysql2的query()方法支持一种模拟的命名占位符（对象形式），这极大地提高了代码的可读性和可维护性。

async function complexQuery(params) {
  const sql = `
    SELECT * FROM orders 
    WHERE user_id = :userId 
      AND status = :status 
      AND created_at BETWEEN :startDate AND :endDate
    LIMIT :limit OFFSET :offset
  `;
  const [rows] = await pool.query(sql, params); // params是一个对象
  return rows;
}

// 调用
const results = await complexQuery({
  userId: 123,
  status: 'completed',
  startDate: '2023-01-01',
  endDate: '2023-12-31',
  limit: 10,
  offset: 0
});

需要注意的是，这种命名占位符是mysql2在客户端层面实现的语法糖，最终还是会转换成?占位符和参数数组发送给MySQL服务器。但这不影响其安全性，因为参数替换过程仍在客户端安全地完成。

事务处理中的预处理语句

在数据库事务中，一致性至关重要。使用预处理语句能确保事务内的所有操作都是安全的。mysql2的Promise API让事务代码变得非常清晰。

async function transferMoney(fromId, toId, amount) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    // 使用预处理语句扣款
    await connection.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?',
      [amount, fromId, amount]
    );

    // 使用预处理语句加款
    await connection.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [amount, toId]
    );

    await connection.commit();
    return { success: true };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release(); // 务必释放连接到连接池
  }
}

常见陷阱与最佳实践

尽管预处理语句很强大，但使用时也需注意以下几点：第一，不要将表名或列名作为参数传入占位符。占位符仅用于数据值（字符串、数字、日期）。动态表名或列名应通过白名单校验后直接拼接。

// 错误做法：占位符不能用于表名
const badSql = `SELECT * FROM ? WHERE id = ?`; // 第一个?不会被正确解释

// 正确做法：通过白名单校验
const validTables = { users: true, products: true };
function queryTable(tableName, id) {
  if (!validTables[tableName]) throw new Error('Invalid table name');
  const sql = `SELECT * FROM ${tableName} WHERE id = ?`;
  return pool.execute(sql, [id]);
}

第二，对于IN子句查询，不能直接使用一个带数组的占位符如WHERE id IN (?)。你需要根据数组长度动态生成占位符。

async function getUsersByIds(ids) {
  const placeholders = ids.map(() => '?').join(',');
  const sql = `SELECT * FROM users WHERE id IN (${placeholders})`;
  const [rows] = await pool.execute(sql, ids);
  return rows;
}

第三，始终使用连接池，并合理配置connectionLimit。第四，对于超大规模批量插入，考虑使用LOAD DATA INFILE或分批次执行，避免单个SQL语句过大。

总结：将预处理语句作为默认选择

在Node.js的MySQL开发中，mysql2库搭配预处理语句应成为你的标准配置。它不是一个可选的“高级特性”，而是保障应用安全底线、提升运行效率的必需品。从今天起，彻底告别字符串拼接SQL，将所有用户输入和外部数据都通过预处理语句的占位符来传递。这不仅是代码质量的体现，更是对数据和业务安全的基本责任。记住，在数据库操作中，安全性和性能往往不是选择题，而mysql2的预处理语句为你提供了两者兼得的优秀解决方案。

Node.js mysql2与预处理语句

服务指南

帮助中心