SQl not accepting insert query in node js throws ER_BAD_FIELD_ERROR

Question

const axios = require('axios');
const mysql = require('mysql');

var con = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'YEX8QHOG@a',
    database: 'test',
});

con.connect((err) => {
    if (err) {
        throw err;
    }
    console.log('Connected...');
});

let createTable = 'CREATE TABLE employee(id VARCHAR(5),empname VARCHAR(30),empsal VARCHAR(30),empage VARCHAR(30))';
con.query(createTable, function (err, result, feilds) {
    if (err) {
        throw err;
    }
    console.log('Db Created');
    console.log(result);
});

function setData(id, empname, empsal, empage) {
    let query = `INSERT INTO employee (id,empname,empsal,empage) VALUES (${id},${empname},${empsal},${empage})`;
    con.query(query, function (err, result) {
        if (err) {
            throw err;
        } else {
            console.log('Data insert', id);
            console.log(result);
        }
    });
}

async function putData() {
    let url = 'http://dummy.restapiexample.com/api/v1/employees';
    let data = await axios.get(url);
    let dataarr = await data.data.data;
    console.log(dataarr);

    setData('1', 'Karan', '36000', '20');
    dataarr.map((emp) => {
    setData(emp.id, emp.employee_name, emp.employee_salary, emp.employee_age);
        console.log(typeof emp.id, typeof emp.employee_name, typeof emp.employee_salary, typeof emp.employee_age);
    });
}

putData();

code: 'ER_BAD_FIELD_ERROR', errno: 1054, sqlMessage: "Unknown column 'Karan' in 'field list'", sqlState: '42S22', index: 0, sql: 'INSERT INTO employee (id,empname,empsal,empage) VALUES (1,Karan,36000,20)'

when I pass values they throw this error for employee name

Does this answer your question? Preventing SQL injection in Node.js — sp00m
– sp00m, Commented Aug 21, 2020 at 9:27

Terry Lennox · Accepted Answer · 2020-08-21 09:30:23Z

2

I'd suggest using parameters, this has other benefits too (especially the prevention of SQL injection),

This is documented at: mysql#escaping-query

You can change the function setData easily to do this:

function setData(id, empname, empsal, empage) {
    let query = `INSERT INTO employee (id,empname,empsal,empage) VALUES (?,?,?,?)`;
    let parameters = [id, empname, empsal, empage];
    con.query(query, parameters, function (err, result) {
        if (err) {
            throw err;
        } else {
            console.log('Data insert', id);
            console.log(result);
        }
    });
}

answered Aug 21, 2020 at 9:30

Terry Lennox

30.9k5 gold badges36 silver badges45 bronze badges

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

Comments

Karan Shedge · Accepted Answer · 2020-08-21 09:40:31Z

-1

let query = `INSERT INTO employee (id,empname,empsal,empage) VALUES ('${id}','${empname}','${empsal}','${empage}')`;

use quotes for any kind of data '${id}'

answered Aug 21, 2020 at 9:40

Karan Shedge

295 bronze badges

1 Comment

sp00m Over a year ago

This is very prone to SQL injections, OP should rather follow github.com/mysqljs/mysql#escaping-query-values.

Collectives™ on Stack Overflow

SQl not accepting insert query in node js throws ER_BAD_FIELD_ERROR

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related