search

AmphibiaWeb / amphibian-disease-tracker

Amphibian Disease Tracker Portal
GNU General Public License v3.0
6 stars 0 forks source link

Uploading following dataset gives unauthorized query #302

Closed jdeck88 closed 5 years ago

jdeck88 commented 5 years ago

Using the test user notification project, Select "Replace Data" Select blue cloud upload button next to the "Drop your files here to upload" Select the attached file.

It processes and eventually returns the following message: Amphibian_Disease_Panama_dataSmall.xlsx

{"status":false,"error":"UNAUTHORIZED_QUERY_TYPE","query_type":"ifexists(\nselect1\nfrominformation_schema.tables\nwhere\n)drop","args_provided":{"action":"upload","sql_query":"SUYgRVhJU1RTICgKICAgIFNFTEVDVCAxCiAgICBGUk9NIGluZm9ybWF0aW9uX3NjaGVtYS50YWJsZXMKICAgIFdIRVJFIHRhYmxlX25hbWUgPSAndDRkNGFhZjU5M2ZlNDI5NGJlZTA1NGMwN2MzNmU2Yzk1X2JhNDNkZWE0YzE3MDE0MjVlZmI2NzUyN2Q3NmIwYzdmOTEyMmEzMWInCikgRFJPUCBUQUJMRSB0NGQ0YWFmNTkzZmU0Mjk0YmVlMDU0YzA3YzM2ZTZjOTVfYmE0M2RlYTRjMTcwMTQyNWVmYjY3NTI3ZDc2YjBjN2Y5MTIyYTMxYjtDUkVBVEUgVEFCTEUgdDRkNGFhZjU5M2ZlNDI5NGJlZTA1NGMwN2MzNmU2Yzk1X2JhNDNkZWE0YzE3MDE0MjVlZmI2NzUyN2Q3NmIwYzdmOTEyMmEzMWIgIChpZCBpbnQsQ29sbGVjdG9yIHZhcmNoYXIsY29vcmRpbmF0ZVVuY2VydGFpbnR5SW5NZXRlcnMgZGVjaW1hbCxkZWNpbWFsTGF0aXR1ZGUgZGVjaW1hbCxkZWNpbWFsTG9uZ2l0dWRlIGRlY2ltYWwsZGF0ZUlkZW50aWZpZWQgZGF0ZSxzcGVjaWZpY0VwaXRoZXQgdmFyY2hhcixnZW51cyB2YXJjaGFyLGZhdGFsIGJvb2xlYW4sZGlzZWFzZURldGVjdGVkIHZhcmNoYXIsc2FtcGxlTWV0aG9kIHZhcmNoYXIsZGlzZWFzZVRlc3RlZCB2YXJjaGFyLHNhbXBsZUlEIHRleHQsZGlzZWFzZUxpbmVhZ2UgdGV4dCxnZW5vdHlwZU1ldGhvZCB0ZXh0LHNlcXVlbmNlVVJJIHRleHQsbGlmZVN0YWdlIHZhcmNoYXIsc2V4IHZhcmNoYXIsYWx0IGRlY2ltYWwsZGlhZ25vc3RpY0xhYiB0ZXh0LGZpZWxkTnVtYmVyIHRleHQsWkVzY29yZSB0ZXh0LG1vbnRoIHRleHQsZGF5IHRleHQseWVhciB0ZXh0LGNvdW50cnkgdGV4dCxzdGF0ZV9wcm92aW5jZSB0ZXh0LGZpbXNFeHRyYSBqc29uLGluZnJhc3BlY2lmaWNFcGl0aGV0IHZhcmNoYXIsb3JpZ2luYWxUYXhhIHZhcmNoYXIsdGhlX2dlb20gZ2VvbWV0cnkpOyBJTlNFUlQgSU5UTyB0NGQ0YWFmNTkzZmU0Mjk0YmVlMDU0YzA3YzM2ZTZjOTVfYmE0M2RlYTRjMTcwMTQyNWVmYjY3NTI3ZDc2YjBjN2Y5MTIyYTMxYiBWQUxVRVMgKDEsJ0phbWllIFZveWxlcycsMzAsOC41MTM3NjMyLC04MS4xMjE4ODI0LCcyMDEyLTExLTEyJywnYWxib21hY3VsYXRhJywnU2FjaGF0YW1pYScsZmFsc2UsZmFsc2UsZmFsc2UsJ0JkJywnMTIxMTEyXzA0JyxudWxsLG51bGwsbnVsbCxudWxsLG51bGwsbnVsbCxudWxsLG51bGwsMCwnMTEnLCcxMicsMjAxMiwnUGFuYW1hJyxudWxsLCd7IkNvbnRhY3ROYW1lIjoiQWxsaWUgQnlybmUiLCJiYXNpc09mUmVjb3JkIjoiTGl2aW5nU3BlY2ltZW4iLCJsYWJOdW1iZXIiOiIxMjExMTJfMDQiLCJDb2xsZWN0b3IyIjpudWxsLCJDb2xsZWN0b3IzIjpudWxsLCJ2ZXJiYXRpbUxvY2FsaXR5IjoiQWx0b3MgZGUgUGllZHJhIiwiSGFiaXRhdCI6bnVsbCwiVGVzdF9NZXRob2QiOiJxdWFudGl0YXRpdmUgUENSIiwiZXZlbnRSZW1hcmtzIjpudWxsLCJxdWFudGl0eURldGVjdGVkIjpudWxsLCJkaWx1dGlvbkZhY3RvciI6bnVsbCwiY3ljbGVUaW1lRmlyc3REZXRlY3Rpb24iOm51bGx9JywnJywnU2FjaGF0YW1pYSBhbGJvbWFjdWxhdGEnLFNUX1NldFNSSUQoU1RfUG9pbnQoLTgxLjEyMTg4MjQsOC41MTM3NjMyKSw0MzI2KSksICgyLCdKYW1pZSBWb3lsZXMnLDMwLDguNTEzNzYzMiwtODEuMTIxODgyNCwnMjAxMi0xMS0xMicsJ2FsYm9tYWN1bGF0YScsJ1NhY2hhdGFtaWEnLGZhbHNlLGZhbHNlLGZhbHNlLCdCZCcsJzEyMTExMl8wNicsbnVsbCxudWxsLG51bGwsbnVsbCxudWxsLG51bGwsbnVsbCxudWxsLDAsJzExJywnMTInLDIwMTIsJ1BhbmFtYScsbnVsbCwneyJDb250YWN0TmFtZSI6IkFsbGllIEJ5cm5lIiwiYmFzaXNPZlJlY29yZCI6IkxpdmluZ1NwZWNpbWVuIiwibGFiTnVtYmVyIjoiMTIxMTEyXzA2IiwiQ29sbGVjdG9yMiI6bnVsbCwiQ29sbGVjdG9yMyI6bnVsbCwidmVyYmF0aW1Mb2NhbGl0eSI6IkFsdG9zIGRlIFBpZWRyYSIsIkhhYml0YXQiOm51bGwsIlRlc3RfTWV0aG9kIjoicXVhbnRpdGF0aXZlIFBDUiIsImV2ZW50UmVtYXJrcyI6bnVsbCwicXVhbnRpdHlEZXRlY3RlZCI6bnVsbCwiZGlsdXRpb25GYWN0b3IiOm51bGwsImN5Y2xlVGltZUZpcnN0RGV0ZWN0aW9uIjpudWxsfScsJycsJ1NhY2hhdGFtaWEgYWxib21hY3VsYXRhJyxTVF9TZXRTUklEKFNUX1BvaW50KC04MS4xMjE4ODI0LDguNTEzNzYzMiksNDMyNikpLCAoMywnSmFtaWUgVm95bGVzJywzMCw4LjUxMzc2MzIsLTgxLjEyMTg4MjQsJzIwMTItMTEtMTInLCdhbGJvbWFjdWxhdGEnLCdTYWNoYXRhbWlhJyxmYWxzZSxmYWxzZSxmYWxzZSwnQmQnLCcxMjExMTJfMDcnLG51bGwsbnVsbCxudWxsLG51bGwsbnVsbCxudWxsLG51bGwsbnVsbCwwLCcxMScsJzEyJywyMDEyLCdQYW5hbWEnLG51bGwsJ3siQ29udGFjdE5hbWUiOiJBbGxpZSBCeXJuZSIsImJhc2lzT2ZSZWNvcmQiOiJMaXZpbmdTcGVjaW1lbiIsImxhYk51bWJlciI6IjEyMTExMl8wNyIsIkNvbGxlY3RvcjIiOm51bGwsIkNvbGxlY3RvcjMiOm51bGwsInZlcmJhdGltTG9jYWxpdHkiOiJBbHRvcyBkZSBQaWVkcmEiLCJIYWJpdGF0IjpudWxsLCJUZXN0X01ldGhvZCI6InF1YW50aXRhdGl2ZSBQQ1IiLCJldmVudFJlbWFya3MiOm51bGwsInF1YW50aXR5RGV0ZWN0ZWQiOm51bGwsImRpbHV0aW9uRmFjdG9yIjpudWxsLCJjeWNsZVRpbWVGaXJzdERldGVjdGlvbiI6bnVsbH0nLCcnLCdTYWNoYXRhbWlhIGFsYm9tYWN1bGF0YScsU1RfU2V0U1JJRChTVF9Qb2ludCgtODEuMTIxODgyNCw4LjUxMzc2MzIpLDQzMjYpKSwgKDQsJ0phbWllIFZveWxlcycsMzAsOC41MTM3NjMyLC04MS4xMjE4ODI0LCcyMDEyLTExLTEyJywnYWxib21hY3VsYXRhJywnU2FjaGF0YW1pYScsZmFsc2UsZmFsc2UsZmFsc2UsJ0JkJywnMTIxMTEyXzA4JyxudWxsLG51bGwsbnVsbCxudWxsLG51bGwsbnVsbCxudWxsLG51bGwsMCwnMTEnLCcxMicsMjAxMiwnUGFuYW1hJyxudWxsLCd7IkNvbnRhY3ROYW1lIjoiQWxsaWUgQnlybmUiLCJiYXNpc09mUmVjb3JkIjoiTGl2aW5nU3BlY2ltZW4iLCJsYWJOdW1iZXIiOiIxMjExMTJfMDgiLCJDb2xsZWN0b3IyIjpudWxsLCJDb2xsZWN0b3IzIjpudWxsLCJ2ZXJiYXRpbUxvY2FsaXR5IjoiQWx0b3MgZGUgUGllZHJhIiwiSGFiaXRhdCI6bnVsbCwiVGVzdF9NZXRob2QiOiJxdWFudGl0YXRpdmUgUENSIiwiZXZlbnRSZW1hcmtzIjpudWxsLCJxdWFudGl0eURldGVjdGVkIjpudWxsLCJkaWx1dGlvbkZhY3RvciI6bnVsbCwiY3ljbGVUaW1lRmlyc3REZXRlY3Rpb24iOm51bGx9JywnJywnU2FjaGF0YW1pYSBhbGJvbWFjdWxhdGEnLFNUX1NldFNSSUQoU1RfUG9pbnQoLTgxLjEyMTg4MjQsOC41MTM3NjMyKSw0MzI2KSk7U0VMRUNUIGNkYl9jYXJ0b2RiZnl0YWJsZSgndDRkNGFhZjU5M2ZlNDI5NGJlZTA1NGMwN2MzNmU2Yzk1X2JhNDNkZWE0YzE3MDE0MjVlZmI2NzUyN2Q3NmIwYzdmOTEyMmEzMWInKTs="},"statement_context":{"statements_count":4,"statement_parsed":"IF EXISTS (\n SELECT 1\n FROM information_schema.tables\n WHERE table_name = 't4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b'\n) DROP TABLE t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b;CREATE TABLE t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b (id int,Collector varchar,coordinateUncertaintyInMeters decimal,decimalLatitude decimal,decimalLongitude decimal,dateIdentified date,specificEpithet varchar,genus varchar,fatal boolean,diseaseDetected varchar,sampleMethod varchar,diseaseTested varchar,sampleID text,diseaseLineage text,genotypeMethod text,sequenceURI text,lifeStage varchar,sex varchar,alt decimal,diagnosticLab text,fieldNumber text,ZEscore text,month text,day text,year text,country text,state_province text,fimsExtra json,infraspecificEpithet varchar,originalTaxa varchar,the_geom geometry","effective_key":1,"action_exists":false,"allowed_actions":{"0":"select","1":"delete","2":"insert","3":"insertinto","4":"update","5":"create"},"statement_number":0,"statements":{"0":"IF EXISTS (\n SELECT 1\n FROM information_schema.tables\n WHERE table_name = 't4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b'\n) DROP TABLE t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b;CREATE TABLE t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b (id int,Collector varchar,coordinateUncertaintyInMeters decimal,decimalLatitude decimal,decimalLongitude decimal,dateIdentified date,specificEpithet varchar,genus varchar,fatal boolean,diseaseDetected varchar,sampleMethod varchar,diseaseTested varchar,sampleID text,diseaseLineage text,genotypeMethod text,sequenceURI text,lifeStage varchar,sex varchar,alt decimal,diagnosticLab text,fieldNumber text,ZEscore text,month text,day text,year text,country text,state_province text,fimsExtra json,infraspecificEpithet varchar,originalTaxa varchar,the_geom geometry","1":" INSERT INTO t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b VALUES (1,'Jamie Voyles',30,8.5137632,-81.1218824,'2012-11-12','albomaculata','Sachatamia',false,false,false,'Bd','121112_04',null,null,null,null,null,null,null,null,0,'11','12',2012,'Panama',null,'{\"ContactName\":\"Allie Byrne\",\"basisOfRecord\":\"LivingSpecimen\",\"labNumber\":\"121112_04\",\"Collector2\":null,\"Collector3\":null,\"verbatimLocality\":\"Altos de Piedra\",\"Habitat\":null,\"Test_Method\":\"quantitative PCR\",\"eventRemarks\":null,\"quantityDetected\":null,\"dilutionFactor\":null,\"cycleTimeFirstDetection\":null}','','Sachatamia albomaculata',ST_SetSRID(ST_Point(-81.1218824,8.5137632),4326)), (2,'Jamie Voyles',30,8.5137632,-81.1218824,'2012-11-12','albomaculata','Sachatamia',false,false,false,'Bd','121112_06',null,null,null,null,null,null,null,null,0,'11','12',2012,'Panama',null,'{\"ContactName\":\"Allie Byrne\",\"basisOfRecord\":\"LivingSpecimen\",\"labNumber\":\"121112_06\",\"Collector2\":null,\"Collector3\":null,\"verbatimLocality\":\"Altos de Piedra\",\"Habitat\":null,\"Test_Method\":\"quantitative PCR\",\"eventRemarks\":null,\"quantityDetected\":null,\"dilutionFactor\":null,\"cycleTimeFirstDetection\":null}','','Sachatamia albomaculata',ST_SetSRID(ST_Point(-81.1218824,8.5137632),4326)), (3,'Jamie Voyles',30,8.5137632,-81.1218824,'2012-11-12','albomaculata','Sachatamia',false,false,false,'Bd','121112_07',null,null,null,null,null,null,null,null,0,'11','12',2012,'Panama',null,'{\"ContactName\":\"Allie Byrne\",\"basisOfRecord\":\"LivingSpecimen\",\"labNumber\":\"121112_07\",\"Collector2\":null,\"Collector3\":null,\"verbatimLocality\":\"Altos de Piedra\",\"Habitat\":null,\"Test_Method\":\"quantitative PCR\",\"eventRemarks\":null,\"quantityDetected\":null,\"dilutionFactor\":null,\"cycleTimeFirstDetection\":null}','','Sachatamia albomaculata',ST_SetSRID(ST_Point(-81.1218824,8.5137632),4326)), (4,'Jamie Voyles',30,8.5137632,-81.1218824,'2012-11-12','albomaculata','Sachatamia',false,false,false,'Bd','121112_08',null,null,null,null,null,null,null,null,0,'11','12',2012,'Panama',null,'{\"ContactName\":\"Allie Byrne\",\"basisOfRecord\":\"LivingSpecimen\",\"labNumber\":\"121112_08\",\"Collector2\":null,\"Collector3\":null,\"verbatimLocality\":\"Altos de Piedra\",\"Habitat\":null,\"Test_Method\":\"quantitative PCR\",\"eventRemarks\":null,\"quantityDetected\":null,\"dilutionFactor\":null,\"cycleTimeFirstDetection\":null}','','Sachatamia albomaculata',ST_SetSRID(ST_Point(-81.1218824,8.5137632),4326)","2":"SELECT cdb_cartodbfytable('t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b'","3":""}},"read_query":"IF EXISTS (\n SELECT 1\n FROM information_schema.tables\n WHERE table_name = 't4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b'\n) DROP TABLE t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b;CREATE TABLE t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b (id int,Collector varchar,coordinateUncertaintyInMeters decimal,decimalLatitude decimal,decimalLongitude decimal,dateIdentified date,specificEpithet varchar,genus varchar,fatal boolean,diseaseDetected varchar,sampleMethod varchar,diseaseTested varchar,sampleID text,diseaseLineage text,genotypeMethod text,sequenceURI text,lifeStage varchar,sex varchar,alt decimal,diagnosticLab text,fieldNumber text,ZEscore text,month text,day text,year text,country text,state_province text,fimsExtra json,infraspecificEpithet varchar,originalTaxa varchar,the_geom geometry); INSERT INTO t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b VALUES (1,'Jamie Voyles',30,8.5137632,-81.1218824,'2012-11-12','albomaculata','Sachatamia',false,false,false,'Bd','121112_04',null,null,null,null,null,null,null,null,0,'11','12',2012,'Panama',null,'{\"ContactName\":\"Allie Byrne\",\"basisOfRecord\":\"LivingSpecimen\",\"labNumber\":\"121112_04\",\"Collector2\":null,\"Collector3\":null,\"verbatimLocality\":\"Altos de Piedra\",\"Habitat\":null,\"Test_Method\":\"quantitative PCR\",\"eventRemarks\":null,\"quantityDetected\":null,\"dilutionFactor\":null,\"cycleTimeFirstDetection\":null}','','Sachatamia albomaculata',ST_SetSRID(ST_Point(-81.1218824,8.5137632),4326)), (2,'Jamie Voyles',30,8.5137632,-81.1218824,'2012-11-12','albomaculata','Sachatamia',false,false,false,'Bd','121112_06',null,null,null,null,null,null,null,null,0,'11','12',2012,'Panama',null,'{\"ContactName\":\"Allie Byrne\",\"basisOfRecord\":\"LivingSpecimen\",\"labNumber\":\"121112_06\",\"Collector2\":null,\"Collector3\":null,\"verbatimLocality\":\"Altos de Piedra\",\"Habitat\":null,\"Test_Method\":\"quantitative PCR\",\"eventRemarks\":null,\"quantityDetected\":null,\"dilutionFactor\":null,\"cycleTimeFirstDetection\":null}','','Sachatamia albomaculata',ST_SetSRID(ST_Point(-81.1218824,8.5137632),4326)), (3,'Jamie Voyles',30,8.5137632,-81.1218824,'2012-11-12','albomaculata','Sachatamia',false,false,false,'Bd','121112_07',null,null,null,null,null,null,null,null,0,'11','12',2012,'Panama',null,'{\"ContactName\":\"Allie Byrne\",\"basisOfRecord\":\"LivingSpecimen\",\"labNumber\":\"121112_07\",\"Collector2\":null,\"Collector3\":null,\"verbatimLocality\":\"Altos de Piedra\",\"Habitat\":null,\"Test_Method\":\"quantitative PCR\",\"eventRemarks\":null,\"quantityDetected\":null,\"dilutionFactor\":null,\"cycleTimeFirstDetection\":null}','','Sachatamia albomaculata',ST_SetSRID(ST_Point(-81.1218824,8.5137632),4326)), (4,'Jamie Voyles',30,8.5137632,-81.1218824,'2012-11-12','albomaculata','Sachatamia',false,false,false,'Bd','121112_08',null,null,null,null,null,null,null,null,0,'11','12',2012,'Panama',null,'{\"ContactName\":\"Allie Byrne\",\"basisOfRecord\":\"LivingSpecimen\",\"labNumber\":\"121112_08\",\"Collector2\":null,\"Collector3\":null,\"verbatimLocality\":\"Altos de Piedra\",\"Habitat\":null,\"Test_Method\":\"quantitative PCR\",\"eventRemarks\":null,\"quantityDetected\":null,\"dilutionFactor\":null,\"cycleTimeFirstDetection\":null}','','Sachatamia albomaculata',ST_SetSRID(ST_Point(-81.1218824,8.5137632),4326));SELECT cdb_cartodbfytable('t4d4aaf593fe4294bee054c07c36e6c95_ba43dea4c1701425efb67527d76b0c7f9122a31b');","execution_time":0.35190582275391}

tigerhawkvok commented 5 years ago

Right. This hit the security layer to prevent malicious queries.

PR #304 includes possible fix https://github.com/AmphibiaWeb/amphibian-disease-tracker/commit/3ea2bdd79f31df6716fc6e898f71f4ba46c437b4

tigerhawkvok commented 5 years ago

Fixed by PR #305