MySQL SELECT doesn't checks if the value is exact

Question

I'm currently working with JS, DiscordJS and MySQL and trying to get some data from the table which usually always worked fine. But I'm struggling with a MySQL SELECT query because it doesn't checks if the value in the table is exectly the same as it is searching for. The end of the Discord ID is different then the one it's searching for.

Code:

let query = `SELECT * FROM accounts WHERE DiscordID = ${interaction.member.id}`;
console.log(`QUERY: ` + query);
MySQL.Connect()
    .then((connection) => {
        MySQL.Query(connection, query)
            .then((results) => {
                if (results == '') {
                    console.log('[RESULTS = 0]');
                    console.log(results);
                } else {
                    console.log('[RESULTS > 0]');
                    console.log(results);
                }
            })
            .catch((error) => {
                console.log(error);
            })
            .finally(() => {
                connection.end();
            });
    })
    .catch((error) => {
        console.log(error);
    });

Result:

QUERY: SELECT * FROM accounts WHERE DiscordID = 466934561738326016

RESULT: 
ID: 1,
Username: 'Test',
Password: 'Test',
DiscordID: '466934561738326022'

MySQL schema:

CREATE TABLE IF NOT EXISTS `accounts` (
`ID` INT NOT NULL AUTO_INCREMENT, 
`Username` VARCHAR(255) NOT NULL, 
`Password` VARCHAR(255) NOT NULL, 
`DiscordID` VARCHAR(100) NOT NULL, 
PRIMARY KEY (`ID`));

Try `SELECT * FROM accounts WHERE DiscordID = "${interaction.member.id}";` — , Feb 21 '22 at 23:04
Works fine, thanks a lot. I didn't thought it'll make such a big difference. — TheDivine, Feb 21 '22 at 23:08
It does, because number types are limited but strings can be really long. — , Feb 21 '22 at 23:09

score 0 · Accepted Answer · edited Feb 22 '22 at 12:43

You were bitten by MySQL's handling of numbers.

You said,

SELECT * FROM accounts WHERE DiscordID = 466934561738326016

MySQL took that long number and silently converted it to an IEEE 754 64-bit floating point number, losing precision. Doing SELECT 466934561738326016; gets you 466934561738326000. That matches the wrong DiscordID, sometimes. !!

If you put quotes around the ID, doing this instead, MySQL leaves the ID value intact as a text string. It works.

SELECT * FROM accounts WHERE DiscordID = '466934561738326016'

Check it out here. fiddle

MySQL SELECT doesn't checks if the value is exact

1 Answers1