MySQL mysteriously adding VALUES

Question

I have this code in my project

app.post('/history/form/confirm', isLoggedIn, (req,res)=>{
        let code = req.body.pcode,
        quanti = req.body.qty, 
        price = req.body.price, 
        cust = req.body.orderedBy, 
        oDate = req.body.orderDate; 
        [code].forEach((product, index, arr) =>{
            const q = quanti[index];
            let sql = `INSERT INTO inventory.orders (productCode, productName, unitPrice, quantity, totalPrice, customer, date)
                       VALUES (`+con.escape(product)+`, (SELECT productName FROM inventory.receive WHERE productCode = `+con.escape(product)+`), (SELECT unitPrice FROM inventory.receive WHERE productCode = `+con.escape(product)+`), `+con.escape(q)+`,`+con.escape(price)+`,`+con.escape(cust)+`,`+con.escape(oDate)+`)`
            con.query(sql, (err,result)=>{
                if (!err){
                    req.flash('historyMessage', 'Order Created')
                    res.redirect('/admin/history')
                }
                else{
                    res.status(404).send(err);
                }
            })
        })
    });


let sql = `INSERT INTO inventory.orders (productCode, productName, unitPrice, quantity, totalPrice, customer, date) VALUES (`+con.escape(product)+`, (SELECT productName FROM inventory.receive WHERE productCode = `+con.escape(product)+`), (SELECT unitPrice FROM inventory.receive WHERE productCode = `+con.escape(product)+`), `+con.escape(q)+`,`+con.escape(price)+`,`+con.escape(cust)+`,`+con.escape(oDate)+`)`

then i get this error ERROR

it adds two more columns to VALUES even if I only have 8 columns on my table and the id is on auto increment

db inventory.orders

What could be the culprit?

I tried other ways of coding like not having a subquery and it still adds those 3 extra values for some reason

HERE is the code from my other project but I didn't use Select because I didnt get the other values from other tables

.post("/send-data", (req,res)=>{
        let order = req.body.OrderNo;
        let quantity = req.body.quantity;
        let first = req.body.fname, 
        last = req.body.lname,
        contact = req.body.Contact,
        email = req.body.emailAdd,
        fb = req.body.facebook,
        date = req.body.date,
        delivery = req.body.delivery,
        payment = req.body.payment,
        time = req.body.time,
        address = req.body.address;
        [order].forEach((product, index, arr)=>{
                const q = quantity[index];
                let sql = "INSERT INTO foodorder.orders (" +
                    "food_id," +
                    " qty,"+ 
                    " customer_FName," + 
                    " customer_LName," +
                    " customer_address," +
                    " customer_number," +
                    " customer_email," +
                    " customer_facebook," +
                    " order_date," +
                    " delivery_option," +
                    " mode_of_payment," +
                    " delivery_time" +
                 ") VALUES (" + 
                     con.escape(product) + `,` +
                     con.escape(q) + `,` +
                     con.escape(first) + `,` +
                     con.escape(last) + `,` +
                     con.escape(address) + `,` +
                     con.escape(contact) + `,` +
                     con.escape(""+email) + `,` +
                     con.escape(fb) + `,` +
                     con.escape(date) + `,` +
                     con.escape(delivery) + `,` +
                     con.escape(payment) + `,` +
                     con.escape(time) +
                     `)`; 
                con.query(sql, (err,result) => {
                    if(!err){
                        res.redirect('thankyou.html');
                    }
                    else{
                        res.status(404).send('ERROR. Please Go back and Order Again');
                    }
                })
        })
    });

For Barmar's Answer Error after using Barmar's answer

const dbconfig = require('../config/database');
const mysql = require('mysql2');
const con = mysql.createConnection(dbconfig.connection);
con.query('USE ' + dbconfig.database);
module.exports = function(app, passport) {

    app.use((req, res, next)=>{
        res.locals.filterdata;
        next();
    })
    // LOGIN =========================
    // ===============================
    app.get('/', (req,res) =>{
        res.redirect('/login');
    });
    app.get('/login', function(req, res) {
        res.render(process.cwd() + '/pages/login', { message: req.flash('loginMessage') });
    });
    app.post('/login', passport.authenticate('local-login', {
            successRedirect : '/profile', 
            failureRedirect : '/login', 
            failureFlash : true 
        }),
        function(req, res) {
            console.log("someone logged in");

            if (req.body.remember) {
              req.session.cookie.maxAge = 1000 * 60 * 3;
            } else {
              req.session.cookie.expires = false;
            }
        res.redirect('/');
    });

    // FORGOT PW =======================
    // =================================
    app.get('/forgot', function(req, res) {
        res.render(process.cwd() + '/pages/forgot');
    });

    // PAGE ROUTES =====================
    // =================================
    app.get('/profile', isLoggedIn, (req, res)=> {
        if (req.isAuthenticated() && (req.user.isAdmin === 1)) {
                res.redirect('/admin');
        }
        else{
            res.redirect('/cashier');
        }
    });

    // ADMIN ROUTES =====================
    // ==================================
    app.get('/admin', isLoggedIn, (req,res)=>{
        let sql = "SELECT * FROM orders"
        con.query(sql, (err,result)=>{
            if(!err){
                res.render(process.cwd() + '/pages/admin/history', {
                    data:result,
                    user: req.user,
                    message: req.flash('historyMessage')
                });
            }
            else{
                res.status(404).send(err);
            }
        }); 
    });
    app.get('/admin/history', isLoggedIn, (req,res)=>{
        let sql = "SELECT * FROM orders"
        con.query(sql, (err,result)=>{
            if(!err){
                res.render(process.cwd() + '/pages/admin/history', {
                    data:result, 
                    user: req.user,
                    message: req.flash('historyMessage')
                });
            }
            else{
                res.status(404).send(err);
            }
        }); 
    });
    app.get('/history/form', isLoggedIn,(req,res)=>{
        let sql = "SELECT * FROM receive"
        let sql2 = "SELECT * FROM orders"
        con.query(sql, (err,result)=>{
            con.query(sql2, (err2,result2)=>{
                if(!err){
                    res.render(process.cwd() + '/pages/admin/form', {data2:result2, data:result, user: req.user});
    
                }
                else{
                    res.status(404).send(err, err2);
                }
            })
        }); 
    });
    app.post('/history/form/confirm', isLoggedIn, (req,res)=>{
        let code = req.body.pcode,
        quanti = req.body.qty, 
        price = req.body.price, 
        cust = req.body.orderedBy, 
        oDate = req.body.orderDate; 
        [code].forEach((product, index, arr) =>{
            const q = quanti[index];
            let sql = `INSERT INTO inventory.orders (productCode, productName, unitPrice, quantity, totalPrice, customer, date)
            SELECT ?, productName, unitPrice, ?, ?, ?, ?
            FROM inventory.receive
            WHERE productCode = ?`;
            console.log(sql);
            con.query(sql,[product, q, price, cust, oDate], (err,result)=>{
                if (!err){
                    req.flash('historyMessage', 'Order Created')
                    res.redirect('/admin/history')
                }
                else{
                    console.log(sql);
                    res.status(404).send(err);
                }
            })
        })
    });
    app.post('/history/form/confirmPrint', isLoggedIn, (req,res)=>{
        let code = req.body.pcode, name = req.body.pname, unit = req.body.punit,
        qty = req.body.qty, price = req.body.price, cust = req.body.orderedBy, oDate = req.body.orderDate; 
        [code].forEach((product, index, arr) =>{
            const q = qty[index];
            let sql = "INSERT INTO inventory.orders (productCode, productName, unitPrice, quantity, totalPrice, customer, date) VALUES (?,?,?,?,?,?,?)"
            con.query(sql,[product, name, unit, q, price, cust, oDate], (err,result)=>{
                if (!err){
                    req.flash('historyMessage', 'Order Created')
                    res.redirect('/admin/history')
                }
                else{
                    res.status(404).send(err);
                }
            });
        })
    });
    
    app.get('/admin/stocks', isLoggedIn, (req,res)=>{
        let sql = "SELECT * FROM receive"
        con.query(sql, (err,result)=>{
            if(!err){
                res.render(process.cwd() + '/pages/admin/stocks', {data: result, user: req.user});
            }
            else{
                res.status(404).send(err);
            }
        }); 
    });
    app.get('/admin/receive', isLoggedIn, (req,res)=>{
        let date = ""+ new Date().getFullYear() + "-" + (new Date().getMonth()+1) + "-" + new Date().getDate() ;
        let sql = "SELECT * FROM receive WHERE date = ?";
        con.query(sql,[date], (err,result)=>{
            if (!err){
                req.flash('dateMessage', date)
                    res.render(process.cwd() + '/pages/admin/receive', {
                        data: result, 
                        user: req.user,
                        fltrdate: req.flash('dateMessage'),
                        message: req.flash('receiveMessage')
                    });
            }
            else{
                res.status(404).send(err);
            }
        });
    });
    app.get('/receive/edit', isLoggedIn, (req,res)=>{
        let date = ""+ new Date().getFullYear() + "-" + (new Date().getMonth()+1) + "-" + new Date().getDate() ;
        let sql = "SELECT * FROM receive WHERE date = ?";
        con.query(sql,[date], (err,result)=>{
            if (!err){
                req.flash('dateMessage', "" + date)
                    res.render(process.cwd() + '/pages/admin/editReceive', {
                        data: result, 
                        user: req.user,
                        fltrdate: req.flash('dateMessage')
                    });
            }
            else{
                res.status(404).send(err);
            }
        });
    });
    app.post('/receive/edit/delete', isLoggedIn, (req,res)=>{
        let date = ""+ new Date().getFullYear() + "-" + (new Date().getMonth()+1) + "-" + new Date().getDate() ;
        let sql = "DELETE FROM receive WHERE (date,productCode) = (?,?)";
        con.query(sql,[date, req.body.deleteProd], (err,result)=>{
            if (!err){
                req.flash('receiveMessage', 'Successfully deleted')
                res.redirect('/admin/receive')
            }
            else{
                res.status(404).send(err);
            }
        });
    });
    app.post('/receive/edit/save', isLoggedIn, (req,res)=>{
        let date = ""+ new Date().getFullYear() + "-" + (new Date().getMonth()+1) + "-" + new Date().getDate() ;
        let code = req.body.code;  let product = req.body.product;
        let unit = req.body.unit; let quantity = req.body.quantity;
        [code].forEach((p, index, arr)=>{
            const q = quantity[index];
            let sql = "INSERT INTO inventory.receive (productName, unitPrice, quantity, date) VALUES (?,?,?,?)";
            con.query(sql,[product, unit, q, date], (err,result)=>{
                if (!err){
                    req.flash('receiveMessage', 'Successfully saved')
                    res.redirect('/admin/receive')
                }
                else{
                    res.status(404).send(err);
                }
            });
        });
    });


    // FILTER ADMIN ROUTES =====================
    // =========================================
    app.post('/receive/filter', isLoggedIn, (req,res)=>{
        let date2 = req.body.date;
        filterdata = date2;
        let sql = "SELECT * FROM receive WHERE date = ?";
        con.query(sql,[date2], (err,result)=>{
            if (!err){
                req.flash('dateMessage', date2)
                res.render(process.cwd() + '/pages/admin/receiveFltr', {
                    data: result, 
                    user: req.user,
                    message: req.flash('receiveMessage'),
                    fltrdate: req.flash('dateMessage')
                });
            }
            else{
                res.status(404).send(err);
            }
        });
    });
    app.post('/filter/edit', isLoggedIn, (req,res)=>{
        let date3 = filterdata;
        let sql = "SELECT * FROM receive WHERE date = ?";
        con.query(sql,[date3], (err,result)=>{
            if (!err){
                req.flash('dateMessage', date3)
                res.render(process.cwd() + '/pages/admin/editReceiveFltr', {
                    data: result, 
                    user: req.user,
                    fltrdate: req.flash('dateMessage')
                });
            }
            else{
                res.status(404).send(err);
            }
        });
    });
    app.post('/filter/edit/delete', isLoggedIn, (req,res)=>{
        let date = filterdata;
        let sql = "DELETE FROM receive WHERE (date,productCode) = (?,?)";
        con.query(sql,[date, req.body.deleteProd], (err,result)=>{
            if (!err){
                req.flash('receiveMessage', 'Successfully deleted')
                res.redirect('/admin/receive')
            }
            else{
                res.status(404).send(err);
            }
        });
    });
    app.post('/filter/edit/save', isLoggedIn, (req,res)=>{
        let date = filterdata;
        let code = req.body.code;  let product = req.body.product;
        let unit = req.body.unit; let quantity = req.body.quantity;
        [code].forEach((p, index, arr)=>{
            const q = quantity[index];
            let sql = "INSERT INTO inventory.receive (productName, unitPrice, quantity, date) VALUES (?,?,?,?)";
            con.query(sql,[product, unit, q, date], (err,result)=>{
                if (!err){
                    req.flash('receiveMessage', 'Successfully saved')
                    res.redirect('/admin/receive')
                }
                else{
                    res.status(404).send(err);
                }
            });
        });
    });

    


    


    // CASHIER ROUTES =====================
    // =================================


    // LOGOUT =========================
    // ================================
    app.get('/logout', (req, res)=> {
        req.logout();
        res.redirect('/login');
    });


    function isLoggedIn(req, res, next) {
    if (req.isAuthenticated())
        return next();
        res.redirect('/');
    }
}

Use a prepared statement with placeholders rather than concatenating strings. — Barmar, Mar 31 '22 at 19:04
What's the point of using `[code].forEach()`? If you only have one `code`, you don't need a loop. — Barmar, Mar 31 '22 at 19:07
I need a loop because in my html code i have multiple values that get accepted as pcode. Think of an OLTP — Carl-Valencia, Mar 31 '22 at 19:11
Tip: if you have trouble matching values to columns, you try the alternative INSERT syntax: `INSERT INTO inventory.orders SET productCode=?, productName=?, unitPrice=?, quantity=?, ...`. This syntax only supports inserting one row at a time, but it makes it easier to avoid getting confused about matching columns to values. And do use query parameters. — Bill Karwin, Mar 31 '22 at 19:12
`[code]` doesn't split `code` into an array. Maybe you mean `code.split(',').forEach()` if it's a comma-separated list? And if `req.body.pcode` is an array, you don't need to wrap it in `[]`. — Barmar, Mar 31 '22 at 19:12
oh I didnt know that other syntax I'll try that. As for BarmarI have done this code before (Check my other question) and solved it by iterating the first variable. I'll try the other syntax now — Carl-Valencia, Mar 31 '22 at 19:16
I don't see any iteration in your other question https://stackoverflow.com/questions/70134269/how-to-insert-an-array-with-some-repeating-values-into-mysql-using-node-js — Barmar, Mar 31 '22 at 19:17
I also don't see how the code you show above could cause the error, which makes me suspect the error is actually cause by some different code. Either another part of the app, or a trigger, or perhaps you have not deployed the code you thought you deployed. I would try `console.log(sql)` _after_ formatting it (or some equivalent method of diagnostic output), so you are sure it is running the SQL syntax you think it is when you get the error. — Bill Karwin, Mar 31 '22 at 19:36
Tried your syntax and a adding an output to check. The console log output shows the exact sql code after formatting but in the error it still adds 3 more columns. Been trying to debug it for hours. tried everything and still same result on err — Carl-Valencia, Mar 31 '22 at 19:47

score 1 · Answer 1 · answered Mar 31 '22 at 19:15

1

I'm not sure where the extra values are coming from, but you can simplify this by using a prepared statement with parameters. And the query can use INSERT INTO ... SELECT ... rather than putting subqueries into the VALUES list.

app.post('/history/form/confirm', isLoggedIn, (req,res)=>{
    let code = req.body.pcode,
        quanti = req.body.qty, 
        price = req.body.price, 
        cust = req.body.orderedBy, 
        oDate = req.body.orderDate; 
    [code].forEach(product => {
        let sql = `INSERT INTO inventory.orders (productCode, productName, unitPrice, quantity, totalPrice, customer, date)
                   SELECT ?, productName, unitPrice, ?, ?, ?, ?
                   FROM inventory.receive
                   WHERE productCode = ?`;
        con.query(sql, [product, q, price, cust, oDate, product], (err,result)=>{
            if (!err){
                req.flash('historyMessage', 'Order Created')
                res.redirect('/admin/history')
            }
            else{
                res.status(404).send(err);
            }
        });
    });
});

answered Mar 31 '22 at 19:15

Barmar

741,623
53
500
612

Tried this stated above that without subquery it still adds those extra Values – Carl-Valencia Mar 31 '22 at 19:17
Are you sure the error is coming from this query? I don't see any way that it can happen. – Barmar Mar 31 '22 at 19:18
Should i add my ejs here? Maybe there? – Carl-Valencia Mar 31 '22 at 19:20
I don't see how the EJS matters. This is happening entirely in the node.js server. – Barmar Mar 31 '22 at 19:21
Are you sure you reloaded the server with the updated code? – Barmar Mar 31 '22 at 19:21
Yes always reloaded the server. Wait ill try the other syntax from bill and comment again – Carl-Valencia Mar 31 '22 at 19:21
Does the error message still say `VALUES (...)`? – Barmar Mar 31 '22 at 19:22
Used this. Still adds 3 more empty VALUES after price – Carl-Valencia Mar 31 '22 at 19:53
There shouldn't be any `VALUES` in the query, I removed that entirely in my code. If you're seeing that in the error message you're not running the code I wrote. – Barmar Mar 31 '22 at 19:54
Sorry I mean Select. Got used to using values. i can add the screenshot to the question – Carl-Valencia Mar 31 '22 at 20:00
I don't see any way that this could be coming from either of the codes. Do you have any triggers in the database? – Barmar Mar 31 '22 at 20:05
No I don't. I can add the photo from Triggers on workbench if you need it – Carl-Valencia Mar 31 '22 at 20:07

score 0 · Answer 2 · answered Apr 05 '22 at 15:12

I have solved the issue and it's in my EJS file. I have an iteration to view the results from my table and have inputs for the price. The price didn't have a disabled attribute that's why it keeps accepting the other prices even if the checkbox is false.

Just add disabled and create a JS file that removes the disabled attribute if the checkbox is checked == true.

MySQL mysteriously adding VALUES

2 Answers2