Description:
GEOGRAPHY columns don't seem to implement any data validation when entering coordinates.
I know it might be easy to answer with "check it on the client, dummy" but it's easy to forget and in strict mode at least you would hope for some help in this area by the server.
select @@sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
How to repeat:
Create table as follows:
-----------
CREATE TABLE `coordinate_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`coordinate` geometry DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`coordinate_test`(`id`, `coordinate`) VALUES (1, ST_GeomFromText('POINT(2.167744 41.390162)'));
-----------
Run some queries for a few months. All is well. For instance:
SELECT * FROM `coordinate_test`
where ST_Distance_Sphere(POINT(2.1677, 41.3901), coordinate)
< 100
-----------
Later, a user inputs some nice data after a night on the town:
INSERT INTO `test`.`coordinate_test`(`id`, `coordinate`) VALUES (2, ST_GeomFromText('POINT(234532845638 41.33)'));
-----------
Run the select again:
SELECT * FROM `coordinate_test`
where ST_Distance_Sphere(POINT(2.1677, 41.3901), coordinate)
< 100
> Longitude 234532845638.000000 is out of range in function st_distance_sphere. It must be within (-180.000000, 180.000000].
> Time: 0,001s
-> The query breaks unexpectedly because of user input
Suggested fix:
Implement input validation on data to the GIS functions
Description: GEOGRAPHY columns don't seem to implement any data validation when entering coordinates. I know it might be easy to answer with "check it on the client, dummy" but it's easy to forget and in strict mode at least you would hope for some help in this area by the server. select @@sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION How to repeat: Create table as follows: ----------- CREATE TABLE `coordinate_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `coordinate` geometry DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `test`.`coordinate_test`(`id`, `coordinate`) VALUES (1, ST_GeomFromText('POINT(2.167744 41.390162)')); ----------- Run some queries for a few months. All is well. For instance: SELECT * FROM `coordinate_test` where ST_Distance_Sphere(POINT(2.1677, 41.3901), coordinate) < 100 ----------- Later, a user inputs some nice data after a night on the town: INSERT INTO `test`.`coordinate_test`(`id`, `coordinate`) VALUES (2, ST_GeomFromText('POINT(234532845638 41.33)')); ----------- Run the select again: SELECT * FROM `coordinate_test` where ST_Distance_Sphere(POINT(2.1677, 41.3901), coordinate) < 100 > Longitude 234532845638.000000 is out of range in function st_distance_sphere. It must be within (-180.000000, 180.000000]. > Time: 0,001s -> The query breaks unexpectedly because of user input Suggested fix: Implement input validation on data to the GIS functions