bignum didn't take care in where statment

[linbo]

Here in my table, defined uid column type is bigint

| uid                     | bigint(20) unsigned | NO 

I query table filter by uid

var sql = "SELECT id from table_name where uid=?";
        connection.query(sql, [uid], function(err, results){
            connection.release();

But if I pass wrong uid, it also works.

if uid == 12345 works
then uid == 12345ff  works fine

Is it because bignum didn't do restrict conversion?

> bignum("123")
<BigNum 123>
> bignum("123f")
<BigNum 123>

[sidorares]

yes, for bignum "123" and "123f" are same numbers (I guess you are using https://www.npmjs.org/package/bignum )

> a = bignum("123")
<BigNum 123>
> b = bignum("123f")
<BigNum 123>
> a == b
false
> a.eq(b)
true

why don't you just compare original strings for equality?

[dougwilson]

@linbo we don't convert to bignum on the sending side; what is uid in your original example? Is it your own bignum instance, or a native JavaScript Number?

You'll notice that if you go to a MySQL console and run

SELECT id from table_name where uid='123f';

MySQL itself will select where uid is 123, so there is nothing this library can do.

mysql> CREATE TABLE test (`id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `uid` BIGINT UNSIGNED);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test (`uid`) VALUES(123);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id FROM test WHERE uid = 123;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)

mysql> SELECT id FROM test WHERE uid = '123f';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

[dougwilson]

If you need to, you'll need to manually do a typeof uid === 'number' check before you query the database.

[linbo]

@dougwilson thanks. uid is passed by HTTP GET request params. Looks I need check GET request params by myself before query MySQL.