Discord.js bot写入MySQL报错：Unknown column 'narozeni' in 'field list'

错误详情 #

/home/container/node_modules/mysql/lib/protocol/Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^
Error: ER_BAD_FIELD_ERROR: Unknown column 'narozeni' in 'field list'
    at Query.Sequence._packetToError (/home/container/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/container/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/home/container/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/container/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/container/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/container/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/container/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/container/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:324:12)
    --------------------
    at Protocol._enqueue (/home/container/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/home/container/node_modules/mysql/lib/Connection.js:198:25)
    at Handshake.<anonymous> (/home/container/commands/postava.js:132:20)
    at Handshake.<anonymous> (/home/container/node_modules/mysql/lib/Connection.js:526:10)
    at Handshake._callback (/home/container/node_modules/mysql/lib/Connection.js:488:16)
    at Handshake.Sequence.end (/home/container/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
    at Handshake.Sequence.OkPacket (/home/container/node_modules/mysql/lib/protocol/sequences/Sequence.js:92:8)
    at Protocol._parsePacket (/home/container/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/container/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/container/node_modules/mysql/lib/protocol/Parser.js:43:10) {
  code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlMessage: "Unknown column 'narozeni' in 'field list'",
  sqlState: '42S22',
  index: 0,
  sql: 'INSERT INTO charaktery (jmeno, prijmeni, datumnarozeni, vek, cisloprukazu, discord, roblox) VALUES (`jmeno`, `prijmeni`, `narozeni`, `vek`, `randomp`, `discord.id`, `roblox`)'
}

对应代码 #

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "INSERT INTO charaktery (jmeno, prijmeni, datumnarozeni, vek, cisloprukazu, discord, roblox) VALUES (`jmeno`, `prijmeni`, `narozeni`, `vek`, `randomp`, `discord.id`, `roblox`)";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
});

问题根源 #

1. 反引号使用错误：MySQL中反引号()用于包裹列名/表名，你在VALUES里用反引号包裹narozeni等变量，导致MySQL把这些当成列名去查找，而你的表中并没有narozeni`这个列，因此报错。
2. 硬编码变量有风险：直接把变量拼接到SQL字符串里，不仅会引发语法错误，还存在严重的SQL注入风险。

修复方案 #

使用MySQL官方驱动支持的参数化查询，用?作为占位符，将变量作为第二个参数传入con.query，既避免语法错误，又能防止SQL注入：

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  // 用?作为值的占位符
  var sql = "INSERT INTO charaktery (jmeno, prijmeni, datumnarozeni, vek, cisloprukazu, discord, roblox) VALUES (?, ?, ?, ?, ?, ?, ?)";
  // 按列顺序传入变量值
  var values = [jmeno, prijmeni, narozeni, vek, randomp, discord.id, roblox];
  con.query(sql, values, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
});

额外注意事项 #

• 确保jmeno、prijmeni、narozeni等变量都已正确定义并赋值，否则可能出现新的错误。
• 数据库连接无需每次查询都调用con.connect，建议在程序启动时建立一次连接，后续直接复用。

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