Getting undefined variables when inserting into mariadb

Question

I don't know what I'm doing wrong, but I get undefined variables in my query, while the variables are set. I'm trying to create a table and then put some data into it

const obj = {
    time: new Date(),
    taken: 0,
    given: 6.4
}

const pool = mariadb.createPool({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'P1data'
})

pool.getConnection().then(async conn => {
    let createLive = `create table if not exists live(
        time datetime primary key,
        taken float not null,
        given float not null
    )`
    
    conn.query(createLive, (err) => {
        if(err) console.error(err.message)
    })

    const res = await conn.query(`SHOW TABLES`)
    console.log(res)
    
    conn.end(err => {
        if(err) console.error(err.message)
    })
}).catch(err => {
    if(err) console.error(err)
})

pool.getConnection().then(conn => {
        conn.query(`INSERT INTO live(time, taken, given) VALUES (${obj.time}, ${obj.taken}, ${obj.given});`)
            .then(rows => {
                console.log(rows);
                conn.end();
            })
            .catch(err => {
                console.error(err)
            })
    }).catch(err => {
        if(err) console.error(err)
    })

The error I get looks like this: Error: (conn=354, no: 1054, SQLState: 42S22) Unknown column 'undefined' in 'field list' sql: INSERT INTO live(time, taken, given) VALUES (undefined, 0, undefined); - parameters:[]

derpirscher · Accepted Answer · 2021-06-03 17:16:01Z

1

INSERT INTO (...) VALUES (...) expects the string values to be enclosed in quotes. And with your current method, also the datetime column probably will be converted from a string on insert, but you don't have any quotes in your query.

Furthermore your obj.time and obj.given seem to be undefined. Thus, your string template for the query evaluates exactly to

INSERT INTO live(time, taken, given) VALUES (undefined, 0, undefined);

So, what the query processor sees in the VALUES is 2 times the identifier undefined (it must be an identifier, because it's not enclosed in quotes) and in the current situation an identifier can only be a column.

You should check your object data
You should not create your queries with string templates, because even if you had the correct quotes, your app is widely open to SQL injections. Use parameterized queries as described in the documentation. Then the mariadb library will care about all necessary quotes and escaping.
```
 conn.query("INSERT INTO live(time, taken, given) values (?, ?, ?)", [obj.time, obj.taken, obj.given])
  .then( ...)
```

edited Jun 3, 2021 at 17:16

answered Jun 3, 2021 at 17:05

derpirscher

17.7k3 gold badges25 silver badges54 bronze badges

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

2 Comments

Janessa Labeur Over a year ago

Thanks for the explanation! I just fixed it by putting the variables in a array and using that array for the values. When I tried the option you recommend, I also got the same error. I still don't know what is different now, but it works

derpirscher Over a year ago

I exactly recommended creating a query with parameters and a parameter array. The first query in the post is literally the output of your initial approach and directly copied from the error message ...

Collectives™ on Stack Overflow

Getting undefined variables when inserting into mariadb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related