0

when trying to run this statement in Node


app.post('/newpost', (req, res)=>{
    db.serialize(async()=>{
        await db.run(`
            insert into posts (title, desc, md) 
            values (${req.body.title}, ${req.body.desc}, ${req.body.body});
        `)
        await res.sendStatus(200).end()
    })
})

The following error appears



[Error: SQLITE_ERROR: near "<": syntax error
Emitted 'error' event on Statement instance at:
] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

I am trying to simply post data from a form into a db table, but it seems to think that the table doesn't exist or something

The table structure is

id    INTEGER PRIMARY KEY AUTOINCREMENT,
title VARCHAR(255) NOT NULL,
desc  TEXT NOT NULL,
md    TEXT NOT NULL
TheDNAHero
  • 11
  • 4
  • Could be worse. Imagine what would happen if `body` contained `''); DROP TABLE POSTS; --`. Never use string concatenation or interpolation to create SQL from raw input. That's how SQL injection attacks happen – Panagiotis Kanavos Aug 23 '22 at 14:52
  • @PanagiotisKanavos I would be the only person able to access the db. It's meant to be a blog for myself – TheDNAHero Aug 23 '22 at 14:58
  • You just hacked yourself then by using string interpolation. Obligatory [Exploits of a Mom](https://xkcd.com/327/). There's no excuse for this. It's actually a lot *easier* to pass the values as parameters – Panagiotis Kanavos Aug 23 '22 at 15:00
  • And before you consider another "but", what will happen when you try to insert a decimal or date value this way? Your values will be formatted as strings resulting in completely unexpected values. Eg `(1,234.57,2022-01-01,whatever)`. You can't even insert a single string this way. Quoting won't help either. That's the *best* case, where the code throws an error and doesn't allow bad values to be inserted. What happens if you enter a date string that gets interpreted using a different locale? Is `4/7` July 4th or April 7th? – Panagiotis Kanavos Aug 23 '22 at 15:04
  • Use parameterized queries instead, using `?` as the parameter placeholders. `db.run('INSERT ... VALUES (?,?,?)',[title,desc,body]);` The parameter values never become part of the query itself. The database engine will compile the query into an execution plan and pass the strongly typed values to it as ... parameters. Numbers remain numbers, dates remain dates. If you use *named* parameters you could even pass `req.body` as the parameters and `run` would match object properties with parameters by name – Panagiotis Kanavos Aug 23 '22 at 15:09
  • @PanagiotisKanavos I am not trying to add different datatypes in, just strings. My question is that the data is not entering the table when I get the form – TheDNAHero Aug 23 '22 at 15:17
  • Because the SQL injection cause an invalid SQL statement. The code you wrote can't insert anything other than integers without failing. What you have now is `INSERT INTO ... VALUES(,((&$#@^^potatoo,whatever)`. That's an invalid statement, because there's no string after `VALUES(`, there's a comparison operator. I already showed you how to fix this. `db.run('INSERT ... VALUES (?,?,?)',[title,desc,body]);` will work no matter what `title` contains – Panagiotis Kanavos Aug 23 '22 at 15:18
  • @PanagiotisKanavos I finally got what you were saying. Thanks! – TheDNAHero Aug 23 '22 at 15:42

0 Answers0