1

I am using this library for connecting my node js application to an sqlite database. I saw its documentation for how to use prepared statements here but it is very brief and I cannot understand how to use it for my use case.

Lets say I have an sql string like this:

const sql = "INSERT INTO tbl(name) VALUES (?)"

So as per the documentation guess I should first create a statement and then use bind to populate it:

const stmt = await db.prepare(sql)
stmt.bind({? : "John"})

Is this the right way to do this? Also once I have created the Prepared statement how am I supposed to run this. All the examples mentioned in the docs are select statements, but if it is an insert statement I suppose stmt.get() or stmt.all() method are not correct as there is no result set to return here. How then am I supposed to do this?

Chandrachur Mukherjee
  • 154
  • 8
  • Documentation there is pretty poor (I assume `?5` is a typo and the position of the placeholder can be used in binding but with no write up it is unclear). Looks like you can use other functions to parameterize as well though.. `db.run('INSERT INTO tbl(name) VALUES (?)', 'John')` – user3783243 Jul 31 '22 at 15:40
  • Right, I guess I was focusing on prepared statements too much. I have worked with databases on Java and I heard that using PreparedStatements in jdbc removes the risk of sql injection attacks. Does doing it this way have sql injection attack vulnerabilities? – Chandrachur Mukherjee Jul 31 '22 at 15:43
  • 1
    Yes, they are all parameterized queries so the driver does the quoting and escaping as needed. – user3783243 Jul 31 '22 at 15:45

1 Answers1

1

I don't know the sqlite library too well, but the following example from the documentation performs a parameterised INSERT statement:

const result = await db.run('INSERT INTO tbl(col) VALUES (:col)', {
  ':col': 'something'
})

I think you're focusing on the phrase 'prepared statement' a little too much. Instead of looking only for this exact phrase, look for any use of parameters whose values are passed separately to the main SQL string. The example above fits this: there is a parameter :col which appears in the SQL string and the value for it is provided in an object passed to db.run alongside the SQL string.

Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
  • Right, I guess I was focusing on prepared statements too much. I worked with databases on Java and I heard that using PreparedStatements in jdbc removes the risk of sql injection attacks. Does doing it this way have sql injection attack vulnerabilities? – Chandrachur Mukherjee Jul 31 '22 at 15:44