Bug #96366 GEOMETRY column type allows insert of nonsensical data
Submitted: 29 Jul 2019 18:54 Modified: 29 Jul 2019 20:32
Reporter: Karl Johansson Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: GIS Severity:S3 (Non-critical)
Version:8.0.16 OS:Ubuntu
Assigned to: CPU Architecture:x86

[29 Jul 2019 18:54] Karl Johansson
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

How to repeat:
Create table as follows:


CREATE TABLE `coordinate_test` (
  `coordinate` geometry DEFAULT NULL,
  PRIMARY KEY (`id`)

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
[29 Jul 2019 20:32] Karl Johansson
After reading some more about all of this, I'm closing this since it appears the misunderstanding is mine, and POINT can be used for scenarios also not referring to a spherical coordinate system.