使用转义的req.body值数组插入mysql时出现sql语法错误

hwazgwia  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(300)

提前感谢大家看了这个问题!因此,我试图将一行数据插入名为raw\u 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\u parse\u错误:您的sql语法有错误;请查看与您的mysql服务器版本对应的手册,以了解第1行“'2018-07-26 13:34:33'”、“127548298”、“0”、“0”、“精神病学管理中心”附近使用的正确语法
如果;但是,我不会对数据数组中的值进行转义,也不会在sql insert查询中的值周围添加单引号,这样可以正常工作(如下所示):

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','psychiatry admin central'','ritm0023102'','hp usb键盘'','accessories'','6','14','2018-08-22 12:51:40']

ukxgm1gy

ukxgm1gy1#

完全从数据数组中删除了connection.escape()(在做更多研究时,似乎没有必要转义这些值):

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);
            });
avkwfej4

avkwfej42#

我想你错过了周末的()了?在您的查询中。
试试这个

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。

相关问题