MySQL SELECT Query assumes 1623x=1623 is True

WHERE `code`= '1623x' 

I strongly suspect that code is a numeric column.

If so: to evaluate the condition, MySQL has to compare a number to a string. What it does is coerce the string to a number, and then perform numeric comparison. It turns out that string '1623x' translates to number 1623, so the condition is fullfilled.

If, for example, the string had been 'x1623', it would have been converted to 0 (because it does not start with a digit), and you wouldn't have received any result.

In a nutshell: don't compare values of different datatypes. Each database has it own understanding on what should happen in this case, which might, or might not feel intuitive to you.

What you are seeing is due to MySQL's particular casting rules. When you try to compare the string literal '1623x' to the integer 1623, MySQL has to do something to first bring the LHS and RHS to the same type. The only way to do that is to cast '1623x' to an integer. The casting rules are such that if the string starts with an integer, then MySQL will retain as many digits are available. In this case, '1623x' becomes the integer 1623, and so the LHS and RHS become true.

To be clear, here is the query which is evaluating to yield the unexpected record:

SELECT *
FROM otp
WHERE 1623 = 1623 AND userid = 4 AND status = 1;

MySQL uses the type of the right operand to cast the left operand. If you have an integer type column code and you say code = '123abc', it becomes equivalent to code = 123 (with a warning). If you want to prevent that (and also things like code = '00123' or code = '123.0') from finding 123, add an additional where condition to also do a string comparison:

and code='123abc' and concat(code)='123abc'

(or use cast instead of concat). I suggest an additional comparison rather than just replacing the existing one so that code can still be used for index lookups.