Bug #112724 Incorrect type conversions between VARCHAR and BOOLEAN types
Submitted: 14 Oct 2023 3:58 Modified: 16 Oct 2023 12:27
Reporter: Paul Smith Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[14 Oct 2023 3:58] Paul Smith
Description:
At present, implicit type conversions between VARCHAR and BOOLEAN types are implemented incorrectly. 

This is a potential security issue as it allows for some SQL injection vectors.

How to repeat:
CREATE TABLE types_table (
pk int primary key, s varchar(20), b bool
);

INSERT INTO types_table VALUES
(0, "abc", true),
(1, "xyz", false);
    
SELECT * FROM types_table WHERE b = "abc"
+----+------+------+
| pk | s    | b    |
+----+------+------+
|  1 | xyz  |    0 |
+----+------+------+

This query should return no results, type conversions are expected to happen from the narrowest type to the widest type. Which in this case means that the boolean/int value stored in the b column must be converted to a varchar before comparison. This leads to the following:

TRUE -> 1 -> "1"

"1" != "abc"

Suggested fix:
Type conversions should correctly be performed from narrowest to widest. In this particular case that means:

BOOLEAN -> INT -> VARCHAR
[16 Oct 2023 12:27] MySQL Verification Team
Hi Mr. Smith,

Thank you for your bug report.

However, it is not a bug.

SQL standard requires strict type checking, which MySQL (and many more) has not implemented as we, being human beings, would receive so many error from the RDBMS.

Hence, it was left to each implementation to make it's own decision. MySQL has gone, from the very beginnings in 1997, for finding a common denominator. 

Common denominator between Boolean and a string is a floating point. Since "abc" can not be converted to floating point, it returns 0. MySQL does not plan to implement strict type checking soon.

This is all explained in our Reference Manual, in a chapter on expression evaluation.

Not a bug.