Unknown column in 'field list' error when running mysql query in node.js

Question

I am writing an sql query in node.js that always gives error: unkown column in field list, whenever i try to use a string as a value, but not another data type

CREATE TABLE tableform (
id int auto_increment primary key,
billID int,
dinnerID int,
url text,
FOREIGN KEY (billID) REFERENCES bill(billID),
FOREIGN KEY (dinnerID) REFERENCES dinner(dinnerID)
);

    updatedatabase: function (billID, dinnerID, tablename, restaurant) {
        conn.query('insert into tableform(billID,dinnerID,url) values (' +billID+ ',' +dinnerID+ ',' + number + ');',
            function (err, results, fields) {
                if (err) throw err;
                else console.log('update');
            });
    }

In the code shown, if I change variable number to 4 (i.e. var number = 4; on the 3rd line, the query works fine. If it's a string (as shown in the code), the error message appears.

There shouldn't be any error messages and the code should run fine if number is a string. However, the aforementioned error message still shows.

Errors:

Error: Unknown column 'sfsdfsd' in 'field list'
    at Packet.asError (c:\Users\Documents\GitHub\node_modules\mysql2\lib\packets\packet.js:684:17)
    at Query.execute (c:\Users\Documents\GitHub\node_modules\mysql2\lib\commands\command.js:28:26)
    at Connection.handlePacket (c:\Users\Documents\GitHub\node_modules\mysql2\lib\connection.js:455:32)
    at PacketParser.onPacket (c:\Users\Documents\GitHub\node_modules\mysql2\lib\connection.js:73:18)
    at PacketParser.executeStart (c:\Users\Documents\GitHub\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket.<anonymous> (c:\Users\Documents\GitHub\node_modules\mysql2\lib\connection.js:80:31)
    at Socket.emit (events.js:189:13)
    at Socket.EventEmitter.emit (domain.js:441:20)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)

What is your mysql schema for directoravailabilitiesinputform table, add it to the question — Aritra Chakraborty
– Aritra Chakraborty, Commented Jun 21, 2019 at 22:37
I've attached an image and the code in the question - is it clearer now? — user11508332
– user11508332, Commented Jun 21, 2019 at 22:43
Also can you post the full error log? That way other's might help you too. — Aritra Chakraborty
– Aritra Chakraborty, Commented Jun 21, 2019 at 22:48

Aritra Chakraborty · Accepted Answer · 2019-06-21 23:08:30Z

2

The problem is in your query you are not surrounding your values with quotes ' or ".

the query should be,

const query = 'insert into directoravailabilitiesinputform(sID,eID,url) values ("' +sID+ '","' +eID+ '","' + number + '")'

A little better to use template literals,

const query = `insert into directoravailabilitiesinputform(sID,eID,url) values ("${sID}","${eID}","${number}")`;

But you should really sanitize the input values and Use parameterized queries. From the documentation of node-mysql:

const query = 'insert into directoravailabilitiesinputform(sID,eID,url) values (?, ?, ?)';
conn.query(query, [sID, eID, number], function (error, results, fields){ .. })

answered Jun 21, 2019 at 23:08

Aritra Chakraborty

12.6k3 gold badges30 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Unknown column in 'field list' error when running mysql query in node.js

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related