Better-sqlite3: .run() modifies integer values to floats

[eslachance]

So I've run into this issue where I'm attempting to insert a number inside the database, and it's converting that number (integer) into a floating point.

The code I'm running:

const SQLite = require("better-sqlite3");
const sql = new SQLite('test.sqlite');

sql.prepare(`DELETE FROM testing;`).run();
sql.prepare(`INSERT INTO testing(key, value) VALUES(1, "test")`).run();
sql.prepare(`INSERT INTO testing(key, value) VALUES(?, ?)`).run(2, "test");
sql.prepare(`INSERT INTO testing(key, value) VALUES(?, ?)`).run("three", 3);

(this of course assumes testing exists).

Expected results:
keys should be 1, 2, three
values should be "test", "test", 3

Actual results:
keys are 1, 2.0, three
values are "test", "test", 3.0

So, you can see that it's the escaping done by run() that actually modifies the value into a float, as inserting directly as values works as expected.

Is there any way around this without exposing myself to SQL injection? Would this be considered a bug that can be fixed?

[eslachance]

(Note: as a workaround I did .toString() on my inserted key in my actual code, and this does insert the correct values).

[JoshuaWise]

This is not a bug, it's a symptom of JavaScript's built-in number format, which is the IEEE-754 standard float format.

There are three solutions:

1. If the sqlite column was defined with a type affinity of INTEGER (or any string containing INT), then sqlite will automatically convert floats to integers when inserted into that column (as long as the float has no fractional component).
2. You can use better-sqlite3's bundled Integer class to explicitly insert 64-bit integers into the database.
3. You can use an SQL casting expression to explicitly convert the value.

Example of solution 1

sql.prepare(`CREATE TABLE testing(key INTEGER, value INTEGER)`).run();

// You should get the expected results with these queries.
sql.prepare(`INSERT INTO testing(key, value) VALUES(1, "test")`).run();
sql.prepare(`INSERT INTO testing(key, value) VALUES(?, ?)`).run(2, "test");
sql.prepare(`INSERT INTO testing(key, value) VALUES(?, ?)`).run("three", 3);

// This solution has the side-effect that if a string looks like an integer, sqlite will
// convert it too. In the example below, both the key and value will become integers.
sql.prepare(`INSERT INTO testing(key, value) VALUES(?, ?)`).run(4, "4");

Example of solution 2

const { Integer } = require('better-sqlite3');

sql.prepare(`INSERT INTO testing(key, value) VALUES(1, "test")`).run();
sql.prepare(`INSERT INTO testing(key, value) VALUES(?, ?)`).run(Integer(2), "test");
sql.prepare(`INSERT INTO testing(key, value) VALUES(?, ?)`).run("three", Integer(3));

Example of solution 3

sql.prepare(`INSERT INTO testing(key, value) VALUES(1, "test")`).run();
sql.prepare(`INSERT INTO testing(key, value) VALUES(CAST(? AS INTEGER), ?)`).run(2, "test");
sql.prepare(`INSERT INTO testing(key, value) VALUES(?, CAST(? AS INTEGER))`).run("three", 3);

[eslachance]

I see. That's unfortunate, since my inserts can be both text or integers. I guess I'll have to use that workaround, then.

Thanks!