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](https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js) — sp00m, Aug 21 '20 at 09:27

score 2 · Accepted Answer · answered Aug 21 '20 at 09:30

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

score -1 · Answer 2 · answered Aug 21 '20 at 09:40

-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 '20 at 09:40

Karan Shedge

29
5

1

This is very prone to SQL injections, OP should rather follow https://github.com/mysqljs/mysql#escaping-query-values. – sp00m Aug 26 '20 at 15:32

SQl not accepting insert query in node js throws ER_BAD_FIELD_ERROR

2 Answers2