使用转义req.body值数组对mysql执行INSERT时出现SQL语法错误

Question

提前感谢您关注这个问题！因此，我试图将一行数据插入到一个名为raw_base的表中。

这是代码：

const express = require('express');
const router = express.Router();
const mysql = require('mysql');
// Import MySQL Options
const options = require('../db_options');
const connection = mysql.createConnection(options);
router.post('/raw', (req, res) => {
let data = [
`${connection.escape(req.body[0].opened)}`,
`${connection.escape(req.body[0].funding_source)}`,
`${connection.escape(req.body[0].replace_existing_device)}`,
`${connection.escape(req.body[0].project)}`,
`${connection.escape(req.body[0].department)}`,
`${connection.escape(req.body[0].ritm_number)}`,
`${connection.escape(req.body[0].item)}`,
`${connection.escape(req.body[0].category)}`,
`${connection.escape(req.body[0].quantity)}`,
`${connection.escape(req.body[0].price)}`,
`${connection.escape(req.body[0].closed)}`
];
connection.query('INSERT INTO `raw_base` (`opened`, `funding_source`, `replace_existing_device`, `project`, `department`, `ritm_number`, `item`, `category`, `quantity`, `price`, `closed`) VALUES ?', [data], (error, results, fields) => {
if (error) throw error;
console.log(results);
});

因此，我收到以下错误：

错误：ER_PARSE_Error:您的SQL语法有错误；在第1行的"\'2018-07-26 13:34:33\'"、"127548298"、"0"、"0\"、"精神管理中心"、"附近，查看与MySQL服务器版本相对应的手册，以获取要使用的正确语法

If；但是，我不转义数据数组中的值，也不在sqlINSERT查询中的值周围添加单引号，它运行良好(如下所示(：

connection.query('INSERT INTO `raw_base` (`opened`, `funding_source`, `replace_existing_device`, `project`, `department`, `ritm_number`, `item`, `category`, `quantity`, `price`, `closed`) VALUES ('
+ ''' + req.body[0].opened + '', '
+ ''' + req.body[0].funding_source + '', '
+ ''' + req.body[0].replace_existing_device + '', '
+ ''' + req.body[0].project + '', '
+ ''' + req.body[0].department + '', '
+ ''' + req.body[0].ritm_number + '', '
+ ''' + req.body[0].item + '', '
+ ''' + req.body[0].category + '', '
+ ''' + req.body[0].quantity + '', '
+ ''' + req.body[0].price + '', '
+ ''' + req.body[0].closed + '')'
, (error, results, fields) => {
if (error) throw error;
console.log(results);
});

我还尝试在数据数组中的每个值周围添加单引号，但没有成功。我认为这是一个简单的语法问题，但我似乎无法准确地指出我做错了什么。再次感谢您的帮助！

以下是数据数组中的值(来自req.body[0](：

['\'2018-07-26 13:34:33\'，'127548298'，"0"，"0"，'\'精神病管理中心\'，'\'RITM0023102\''，'\'HP USB键盘\'，"\"附件"，’6’，’14’，'\'2018-08-22 12:51:40\']

Answer 1

我想你错过了周围的((？在您的查询中
试试这个

const express = require('express');
const router = express.Router();
const mysql = require('mysql');
// Import MySQL Options
const options = require('../db_options');
const connection = mysql.createConnection(options);
router.post('/raw', (req, res) => {
let data = [
connection.escape(req.body[0].opened),
connection.escape(req.body[0].funding_source),
connection.escape(req.body[0].replace_existing_device),
connection.escape(req.body[0].project),
connection.escape(req.body[0].department),
connection.escape(req.body[0].ritm_number),
connection.escape(req.body[0].item),
connection.escape(req.body[0].category),
connection.escape(req.body[0].quantity),
connection.escape(req.body[0].price),
connection.escape(req.body[0].closed)
];
connection.query('INSERT INTO `raw_base` (`opened`, `funding_source`, `replace_existing_device`, `project`, `department`, `ritm_number`, `item`, `category`, `quantity`, `price`, `closed`) VALUES (?)', [data], (error, results, fields) => {
if (error) throw error;
console.log(results);
});

编辑：从字符串中提取connection.escape。

Answer 2

从数据数组中完全删除了connection.eescape(((在进行更多研究时，似乎没有必要转义这些值(：

const express = require('express');
const router = express.Router();
const mysql = require('mysql');
// Import MySQL Options
const options = require('../db_options');
const connection = mysql.createConnection(options);
router.post('/raw', (req, res) => {
let data = [
req.body[0].opened,
req.body[0].funding_source,
req.body[0].replace_existing_device,
req.body[0].project,
req.body[0].department,
req.body[0].ritm_number,
req.body[0].item,
req.body[0].category,
req.body[0].quantity,
req.body[0].price,
req.body[0].closed
];
connection.query('INSERT INTO `raw_base` (`opened`, `funding_source`, `replace_existing_device`, `project`, `department`, `ritm_number`, `item`, `category`, `quantity`, `price`, `closed`) VALUES (?)', [data], (error, results, fields) => {
if (error) throw error;
console.log(results);
});