这是MySQL中的一个错误吗？

Question

I am using mysql and php (Laravel) and facing a very strange situation .

I am trying get an encrypted user type and user id and decrypt it . and then find the records for that user id .

my code is like this . i am using Laravel framework .

        $key = Config::get('app.key');
        $decodedUserIDwithType = base64_decode($encryptedUserIDwithType);
        $decryptedUserIDwithType = mcrypt_decrypt(XXXXX , $key, $decodedUserIDwithType, XXXXXX);
        $userIDwithType = $decryptedUserIDwithType;

I am expecting the decoded value to be something like this

id@100

so i will explode it by @ and find the user id , here it is 100 .

for testing i have changed the encrypted value by manually adding one or two characters . when i decrypt i got something like

id@100������������������������]u甀�+&�fj�W�ZЪS��d��]3�]"

and after i explode this i will get the id as 100���]u甀�+&�fj�W�ZЪS��d��]3�]"

now i select all raws with same id using .

SELECT * FROM table WHERE id=$id

it will select the recordes with id = 100 even the actual id is 100���]u甀�+&�fj�W�ZЪS��d��]3�]"

so weird . the type of the id column is INT may be that is why it is matched .

but from my point of view it is very bad , because my whole logic got incorrect because of this .

I checked this query both in Laravel and raw MySQL Query , the results are the same .

Any Ideas , Thanks in advance .

UPDATE

I understand the point mentioned by Shadow, but how to handle a scenario like this , from decryption i expect id like 100 , but if i got something like 1ASASAS, if you cast it to int it will be 1 (this is just for example) . now the problem is my database has a user id 1 also , so now you can see how much trouble i am in because i will get an incorrect user , sadly this is related to payment :P . some incorrect users wallet will be topped up . ha ha .how to handle this

Answer 1

This is not a bug, this is a feature in MySQL described in Type Conversion in Expression Evaluation section of the MySQL manual:

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

When MySQL converts a string to a number, it evaluates the characters starting from the left as long as the characters can be interpreted as part of a number and stops, if it encounters a character that cannot be considered as part of a number. In case of 100���]u甀�+&�fj�W�ZЪS��d��], the first � cannot be interpreted as a part of the number, so MySQL stops after 100. The characters after 100 seem to be some kind of garbage anyway and you should check your php code why it produces that garbage.