Updating a value in SQL (better-sqlite3) with Node.JS

Question

I currently have a database of people with each individual person and they hold a status value. I am trying to change their status value.

  const id = parseInt(req.params.id , 10);
  const { valid, messageObj } = validateId(id);
  if (!valid) {
    res.status(400).send(messageObj);
  }

  let { status, priority } = req.body;
  let people = db.prepare('select * from people').all();
  const person = people.find(person => person.id === id);    

  if(status !== 'none' & status == 'ready' || status == 'done'){
    let updates = db.query(
        'UPDATE people SET ? WHERE ?', 
         [{ status: status }, { id: id }]
    );
  }

I keep getting an error of db.query is not a function but I get that for every function that I try.

Pretty new to SQL but just trying to figure this out or any documentation that will help me as the better-sqlite3 doesn't have any update functions in the official documentation.

GMB · Accepted Answer · 2019-03-02T23:56:52.263

9

I cannot find a function called query() in the better-sqlite3 API for the Database class. I think that you would need to prepare() a Statement object, then run() it.

Also, column names cannot be passed as bound parameters. Your query should look like:

UPDATE people SET status = ? WHERE name = ?

You would need to change this:

let updates = 
    db.query('UPDATE people SET ? WHERE ?', [{ status: status }, { id: id }]);

To:

const stmt = db.prepare('UPDATE people SET status = ? WHERE id = ?'); 
const updates = stmt.run(status, id);

edited Mar 02 '19 at 23:56

answered Mar 02 '19 at 23:16

GMB

216,147
25
84
135

2

Condensed `const info = db.prepare('update people set status = ? where id =?').run(status,id);` if you want – Gander7 May 05 '20 at 17:45
`Also, column names cannot be passed as bound parameters.` It does support bound parameters, see e.g. https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#binding-parameters and the example at https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#binding-parameters -- are you saying that, although they are supported from INSERT, they are not supported in UPDATE statements for some reason? – ChrisW Dec 10 '22 at 14:41
`bind-parameter` is part of the `expr` syntax on https://www.sqlite.org/lang_update.html – ChrisW Dec 10 '22 at 14:54
1

@ChrisW: yes it support bind parameters to pass *values*, but not other elements such as a column *name* or a table name for example. In the `insert` example that you linked, it is values that are bound. Keep in mind that the database needs to be able to *prepare* the statement using the query string only (not the bind parameters) ; it would not be possible to do if a structural information such as a column name was missing. – GMB Dec 10 '22 at 18:28

score 0 · Answer 2 · answered Aug 31 '22 at 09:06

0

According to templates you can use javascript syntax to replace variables to its value.

let updates = db.exec(`UPDATE people SET status='${status}' WHERE id='${id}'`);

answered Aug 31 '22 at 09:06

Sándor Krisztián

570
6
9

2

This is not safe. This allows SQL Injection. Exec doesn't perform ANY escaping. – Rawley Fowler Oct 04 '22 at 02:23
I see, prepare will drop error when code injection try to perform, but you have to handle it far before sql statement. You have to check the value of status variable before use template, and in case of attempt injection you have to refuse, and terminate the procedure before sql, so please separate the safe problems from replace method. – Sándor Krisztián Oct 05 '22 at 14:17

Updating a value in SQL (better-sqlite3) with Node.JS

2 Answers2