SQL syntax error on INSERT with mysql using array of escaped req.body values

Question

Thank you in advance for taking a look at this question! So, I am attempting to INSERT a row of data into a table named raw_base.

Here is the code:

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

As such, I am receiving the following error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''\'2018-07-26 13:34:33\'', '127548298', '0', '0', '\'Psychiatry Admin-Central\'', '' at line 1

If; however, I do not escape the values in the data array and add single quotes around the values in the sql INSERT query it works fine (like this):

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

I have also attempted to add single quotes around around each value in the data array with no luck. I assume this is a simple syntactical issue, but I can't seem to place my finger on exactly where I am going wrong. Thanks again for helping out!

Here is are the values from the data array (from req.body[0]):

[ '\'2018-07-26 13:34:33\'', '127548298', '0', '0', '\'Psychiatry Admin-Central\'', '\'RITM0023102\'', '\'HP USB Keyboard\'', '\'Accessories\'', '6', '14', '\'2018-08-22 12:51:40\'' ]

Your problem is messy data. You want something like [ '2018-07-26 13:34:33', '275829', '0', '0', 'Psychiatry Admin-Central', ... ]. But for some reason the data contains escaped strings, as in '\'2018-07-26 13:34:33\''. You need to fix that problem before using the data. — elixenide
– elixenide, Commented Nov 8, 2018 at 15:23
It is the connection.escape() method that is creating the escaped characters... not sure it would be good practice. Thoughts? — sivs
– sivs, Commented Nov 8, 2018 at 15:26
escape can only work on what is already there. The problem isn't that escape is escaping single quotes; it is that you have literal single quotes inside your strings. Why, I don't know. — elixenide
– elixenide, Commented Nov 8, 2018 at 15:29

KeeyPee · Accepted Answer · 2018-11-08 15:39:56Z

1

I think you missed the ( ) around the ? in your query.
Try this

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

Edit: extracted the connection.escape from the Strings.

edited Nov 8, 2018 at 15:39

answered Nov 8, 2018 at 15:34

KeeyPee

3181 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sivs Over a year ago

Thank you @KeeyPee, removing the interpolation from the items in the array was definitely good practice; however, it appears that the connection.escape() is adding the escaped single quotes that are causing my issue. Anyone have any idea on how to avoid this?

KeeyPee Over a year ago

@sivs Ah i forgot you dont need to do a connection.escape because it is doing it automatically in the prepared statement. Try the insert without your connection.escape

sivs · Accepted Answer · 2018-11-10 20:16:16Z

Removed the connection.escape()'s completely from the data array (in doing some more research it appears it is unnecessary to escape these values):

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

Collectives™ on Stack Overflow

SQL syntax error on INSERT with mysql using array of escaped req.body values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related