How to use "?" placeholders with bigint values?

[cakoose]

Some of our columns are bigints and we're currently using BigNumber.js to work with those values.

My first attempt:

const n = new BigNumber('26000000000000000')
connection.query(`SELECT ?;`, [n], ...)
// SELECT `s` = 1, `e` = 16, `c` = 260, `_isBigNumber` = true

DoingBigNumber.toString() fixes that:

connection.query(`SELECT ?;`, [n.toString()], ...)
// SELECT '26000000000000000'

But the problem is that quoting numbers causes MySQL to treat the value differently, and lose precision when doing arithmetic (bug):

SELECT 26000000000012345;            // 26000000000012345
SELECT '26000000000012345';          // 26000000000012345
SELECT 12345 + 26000000000000000;    // 26000000000012345
SELECT 12345 + '26000000000000000';  // 2.6000000000012344e16

Is there a way to use a "?" placeholder to produce an unquoted bigint value?

[dougwilson]

Something like the following:

console.log(SqlString.format('SELECT ?', [SqlString.raw(n.toString())]))

[cakoose]

Ah, thanks! That does work, but one concern is that we'd lose some protection against SQL injection.

Does the "mysql" package allow replacing "sqlstring" with a different quoting function? I could write a custom function that handles all the standard types and also handles BigNumber.js.

[dougwilson]

https://github.com/mysqljs/mysql/blob/master/Readme.md#custom-format

[cakoose]

Thanks, again!

[dougwilson]

No problem at all! Node.js updated the JavaScript and supports native BigInts now. I don't think this lib works with them, but I should be able to add support, which would mean if you're able to use the native BitInts, then you can use this without modification. It would be like console.log(SqlString.format('SELECT ?', [26000000000000000n]))

[cakoose]

Yeah, BigInts currently come through quoted as well. Would be nice to fix that.

(It'll take us a while to migrate from BigNumber.js to native BigInt, so we'll need to use one of the other workarounds for now...)