determine where test-bigsearch barrage is coming from

Myriad downloads with name beginning "test-bigsearch[n]_" keep accumulating in the vn-downloads2 bucket. Determine where these are coming from and if they are legitimate. If there are not, take actions to avoid them.

Between 25 Oct 2016 and 4 Jan 2017, 529 files named test-bigsearch1... were created and another 529 named test-bigsearch2... were created.

Is there an email associated with these requests, per the request form so that we can send notification to a user?

On Wed, Jan 4, 2017 at 3:57 PM, John Wieczorek notifications@github.com wrote:

Myriad downloads with name beginning "test_bigsearch1" keep accumulating in the vn-downloads2 bucket. Determine where these are coming from and if they are legitimate. If there are not, take actions to avoid them.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/VertNet/webapp/issues/637, or mute the thread https://github.com/notifications/unsubscribe-auth/AA5hb-5uSqytp23YEoYyH_SFSP-bSVYrks5rPDH4gaJpZM4LbM0h .

Here is an example from the logs showing a test-bigsearch1 request:

email given is stuff@things.com from the middle of Amsterdam at lat, lon 52.370216, 4.895168.

This is not a legitimate use of our resources.

The test-bigsearch2 requests are coming from the same source. Here is an example of one of those:

13:47:29.136 Trying SQL: INSERT INTO query_log_master( api_version, client, count, download, downloader, error, lat, lon, matching_records, query, query_version, request_source, response_records, results_by_resource, type) VALUES ( 'api.py 2015-09-02T11:09:38+02:00', 'portal-prod', 1, '/vn-downloads2/test-bigsearch2-0c6bfded9ef24aed80ac0f4a701afac1.tsv', 'stuff@things.com', 'None', 52.370216, 4.895168, 1, 'specificepithet:collaris genus:ochotona year:1960', 'search.py 2015-08-29T21:04:44+02:00', 'DownloadAPI', 1, '{"84aefc7e-f762-11e1-a439-00145eb45e9a": 1}', 'download'); update query_log_master set the_geom = CDB_LatLng(lat,lon) URL: http://vertnet.cartodb.com/api/v2/sql?q=INSERT+INTO+query_log_master%28+++++++++++++api_version%2C+client%2C+count%2C+download%2C+downloader%2C+error%2C+lat%2C+lon%2C+++++++++++++matching_records%2C+query%2C+query_version%2C+request_source%2C+response_records%2C+++++++++++++results_by_resource%2C+type%29+VALUES+%28+++++++++++++%27api.py+2015-09-02T11%3A09%3A38%2B02%3A00%27%2C+%27portal-prod%27%2C+1%2C+%27%2Fvn-downloads2%2Ftest-bigsearch2-0c6bfded9ef24aed80ac0f4a701afac1.tsv%27%2C+%27stuff%40things.com%27%2C+%27None%27%2C+52.370216%2C+4.895168%2C+++++++++++++1%2C+%27specificepithet%3Acollaris+genus%3Aochotona+year%3A1960%27%2C+%27search.py+2015-08-29T21%3A04%3A44%2B02%3A00%27%2C+%27DownloadAPI%27%2C+1%2C+++++++++++++%27%7B%2284aefc7e-f762-11e1-a439-00145eb45e9a%22%3A+1%7D%27%2C+%27download%27%29%3B%0A++++++++++++update+query_log_master+set+the_geom+%3D+CDB_LatLng%28lat%2Clon%29&api_key=b3263ddb2c8afddfa4bddca43d44727f9d3220ce Version:tracker.py 2015-09-01T13:00:37+02:00

We can block all these?

On Wed, Jan 4, 2017 at 8:13 PM, John Wieczorek notifications@github.com wrote:

Here is an example from the logs showing a test-bigsearch1 request:

email given is stuff@things.com from the middle of Amsterdam at lat, lon 52.370216, 4.895168.

This is not a legitimate use of our resources.

15:40:39.057 Trying SQL: INSERT INTO query_log_master( api_version, client, count, download, downloader, error, lat, lon, matching_records, query, query_version, request_source, response_records, results_by_resource, type) VALUES ( 'api.py 2015-09-02T11:09:38+02:00', 'portal-prod', 8110, '/vn-downloads2/test-bigsearch1-fe1095ef50fa476fb77e44f8c01887e1.tsv', ' stuff@things.com', 'None', 52.370216, 4.895168, 8110, 'specificepithet:princeps genus:ochotona', 'search.py 2015-08-29T21:04:44+02:00', 'DownloadAPI', 110, '{"04169fce-7d4f-41f7-9dff-245960f5b59e": 5, "6ce7290f-47f6-4046-8356-371f5b6749df": 39, "0daed095-478a-4af6-abf5-18acb790fbb2": 923, "81e4afd9-0b61-483d-b7fa-0690f06c8e14": 1, "7f6dd0f7-9ed4-49c0-bb71-b2a9c7fed9f1": 10, "88d8974c-f762-11e1-a439-00145eb45e9a": 146, "8eddc200-f535-4c65-9b4d-f723eafe607e": 64, "854f70cc-55e3-4af2-9417-0f47d6c7902d": 10, "temp-uconn-datasetid": 2, "6720aee6-2aad-446d-bb97-ba009d1b5666": 1, "832a5f06-f762-11e1-a439-00145eb45e9a": 11, "830eb5d0-f762-11e1-a439-00145eb45e9a": 152, "609217ee-054d-441d-a850-356cbc2fb385": 2, "6346de55-4767-4bc3-a855-1c9e819f3fe9": 903, "84aefc7e-f762-11e1-a439-00145eb45e9a": 6, "acede617-a095-4caa-a93e-4a77f5f77935": 2, "854f602e-f762-11e1-a439-00145eb45e9a": 36, "7a25f7aa-03fb-4322-aaeb-66719e1a9527": 69, "5df38344-b821-49c2-8174-cf0f29f4df0d-Mammals": 874, "96275bf2-8999-4869-95d5-4903a84391b0": 5, "ea9f5b0b-ad97-45ea-935a-ba2784c80cbb": 3, "6d2cfc0a-9903-40b8-802b-403398218e4a": 202, "8631295a-f762-11e1-a439-00145eb45e9a": 95, "f11db245-3f9f-4fc6-a0cc-12b4124d081b": 2, "07e9980a-a9f6-4a35-9df6-26371459e570": 21, "84e6e5f8-f762-11e1-a439-00145eb45e9a": 21, "5f3463d2-51b6-4a8a-b252-9bab4388934e": 208, "8855595b-a836-4557-b17b-794aadda747a": 3, "96ca66b4-f762-11e1-a439-00145eb45e9a": 178, "3ad882bb-cd21-4201-8b83-3684bfc6d830": 187, "835ff8be-ea5c-45e4-b3f3-126852ac56fe": 1, "1d04e739-98a9-4e16-9970-8f8f3bf9e9e3": 323, "f36b26ac-97d1-40f6-a9a0-484defff98e7": 22, "8a5459ee-3d3a-4e0a-9fb0-0cbbb9e5a6f9": 1, "41fc5c40-5e81-496f-9733-6b5681b3b7a5": 68, "847e2306-f762-11e1-a439-00145eb45e9a": 13, "4e50e3a6-8d31-44bc-ad3d-de6311ad0943": 948, "08dc18e5-e3c0-4f74-b49a-b8274aabe020": 18, "22a66350-7947-4a49-84a3-39c7c1b0881f": 4, "c5c4a23e-2035-4416-ab64-032d6df52ddb": 77, "3a339645-d3b4-40aa-b891-f9c92dc554d8": 96, "4837d6b0-19fe-4fe4-9ddf-b62dd17a060e": 21, "75018539-6328-41de-b875-7c2e61dc1635": 18, "b15d4952-7d20-46f1-8a3e-556a512b04c5": 1635, "41a910f3-db13-4520-a3b2-199edb4a32ea": 2, "58d0f326-2e85-4d0a-a744-571461220f00": 4, "78dcfcbd-03c4-4d47-8618-830aac6f2ee5": 6, "35720b3e-aded-4b83-b4f1-967f1d457d6a": 430, "377be098-626f-4cc2-b4b5-35700050669a": 23, "f9772d3a-db34-4a33-baa0-7a016a970358": 4, "b6015b60-6f96-43a9-88e5-2f41854e8f07": 44, "bd2feca8-ec39-4480-9dad-e353ab6a506d": 2, "4bfac3ea-8763-4f4b-a71a-76a6f5f243d3": 90, "226b536c-25f9-4f6b-b144-edcbdffa3566": 1, "88d5d94e-f762-11e1-a439-00145eb45e9a": 77, "e6acc36b-4149-4458-a26f-17fbeb0df90b": 1}', 'download'); update query_log_master set the_geom = CDB_LatLng(lat,lon) URL: http://vertnet.cartodb.com/api/v2/sql?q=INSERT+INTO+ query_log_master%28+++++++++++++api_version%2C+client%2C+ count%2C+download%2C+downloader%2C+error%2C+lat%2C+ lon%2C+++++++++++++matchingrecords%2C+query%2C+query version%2C+request_source%2C+response_records%2C+++++++++++ ++results_by_resource%2C+type%29+VALUES+%28+++++++++++++% 27api.py+2015-09-02T11%3A09%3A38%2B02%3A00%27%2C+% 27portal-prod%27%2C+8110%2C+%27%2Fvn-downloads2%2Ftest-bigsearch1- fe1095ef50fa476fb77e44f8c01887e1.tsv%27%2C+%27stuff% 40things.com%27%2C+%27None%27%2C+52.370216%2C+4.895168%2C+++ ++++++++++8110%2C+%27specificepithet%3Aprinceps+genus%3Aochotona%27%2C+% 27search.py+2015-08-29T21%3A04%3A44%2B02%3A00%27%2C+% 27DownloadAPI%27%2C+110%2C+++++++++++++%27%7B%2204169fce- 7d4f-41f7-9dff-245960f5b59e%22%3A+5%2C+%226ce7290f-47f6- 4046-8356-371f5b6749df%22%3A+39%2C+%220daed095-478a-4af6- abf5-18acb790fbb2%22%3A+923%2C+%2281e4afd9-0b61-483d-b7fa- 0690f06c8e14%22%3A+1%2C+%227f6dd0f7-9ed4-49c0-bb71- b2a9c7fed9f1%22%3A+10%2C+%2288d8974c-f762-11e1-a439- 00145eb45e9a%22%3A+146%2C+%228eddc200-f535-4c65-9b4d- f723eafe607e%22%3A+64%2C+%22854f70cc-55e3-4af2-9417- 0f47d6c7902d%22%3A+10%2C+%22temp-uconn-datasetid%22%3A+ 2%2C+%226720aee6-2aad-446d-bb97-ba009d1b5666%22%3A+1%2C+% 22832a5f06-f762-11e1-a439-00145eb45e9a%22%3A+11%2C+% 22830eb5d0-f762-11e1-a439-00145eb45e9a%22%3A+152%2C+% 22609217ee-054d-441d-a850-356cbc2fb385%22%3A+2%2C+% 226346de55-4767-4bc3-a855-1c9e819f3fe9%22%3A+903%2C+% 2284aefc7e-f762-11e1-a439-00145eb45e9a%22%3A+6%2C+% 22acede617-a095-4caa-a93e-4a77f5f77935%22%3A+2%2C+% 22854f602e-f762-11e1-a439-00145eb45e9a%22%3A+36%2C+% 227a25f7aa-03fb-4322-aaeb-66719e1a9527%22%3A+69%2C+% 225df38344-b821-49c2-8174-cf0f29f4df0d-Mammals%22%3A+ 874%2C+%2296275bf2-8999-4869-95d5-4903a84391b0%22%3A+5%2C+% 22ea9f5b0b-ad97-45ea-935a-ba2784c80cbb%22%3A+3%2C+% 226d2cfc0a-9903-40b8-802b-403398218e4a%22%3A+202%2C+% 228631295a-f762-11e1-a439-00145eb45e9a%22%3A+95%2C+% 22f11db245-3f9f-4fc6-a0cc-12b4124d081b%22%3A+2%2C+% 2207e9980a-a9f6-4a35-9df6-26371459e570%22%3A+21%2C+% 2284e6e5f8-f762-11e1-a439-00145eb45e9a%22%3A+21%2C+% 225f3463d2-51b6-4a8a-b252-9bab4388934e%22%3A+208%2C+% 228855595b-a836-4557-b17b-794aadda747a%22%3A+3%2C+% 2296ca66b4-f762-11e1-a439-00145eb45e9a%22%3A+178%2C+% 223ad882bb-cd21-4201-8b83-3684bfc6d830%22%3A+187%2C+% 22835ff8be-ea5c-45e4-b3f3-126852ac56fe%22%3A+1%2C+% 221d04e739-98a9-4e16-9970-8f8f3bf9e9e3%22%3A+323%2C+% 22f36b26ac-97d1-40f6-a9a0-484defff98e7%22%3A+22%2C+% 228a5459ee-3d3a-4e0a-9fb0-0cbbb9e5a6f9%22%3A+1%2C+% 2241fc5c40-5e81-496f-9733-6b5681b3b7a5%22%3A+68%2C+% 22847e2306-f762-11e1-a439-00145eb45e9a%22%3A+13%2C+% 224e50e3a6-8d31-44bc-ad3d-de6311ad0943%22%3A+948%2C+% 2208dc18e5-e3c0-4f74-b49a-b8274aabe020%22%3A+18%2C+% 2222a66350-7947-4a49-84a3-39c7c1b0881f%22%3A+4%2C+% 22c5c4a23e-2035-4416-ab64-032d6df52ddb%22%3A+77%2C+% 223a339645-d3b4-40aa-b891-f9c92dc554d8%22%3A+96%2C+% 224837d6b0-19fe-4fe4-9ddf-b62dd17a060e%22%3A+21%2C+% 2275018539-6328-41de-b875-7c2e61dc1635%22%3A+18%2C+% 22b15d4952-7d20-46f1-8a3e-556a512b04c5%22%3A+1635%2C+% 2241a910f3-db13-4520-a3b2-199edb4a32ea%22%3A+2%2C+% 2258d0f326-2e85-4d0a-a744-571461220f00%22%3A+4%2C+% 2278dcfcbd-03c4-4d47-8618-830aac6f2ee5%22%3A+6%2C+% 2235720b3e-aded-4b83-b4f1-967f1d457d6a%22%3A+430%2C+% 22377be098-626f-4cc2-b4b5-35700050669a%22%3A+23%2C+% 22f9772d3a-db34-4a33-baa0-7a016a970358%22%3A+4%2C+% 22b6015b60-6f96-43a9-88e5-2f41854e8f07%22%3A+44%2C+% 22bd2feca8-ec39-4480-9dad-e353ab6a506d%22%3A+2%2C+% 224bfac3ea-8763-4f4b-a71a-76a6f5f243d3%22%3A+90%2C+% 22226b536c-25f9-4f6b-b144-edcbdffa3566%22%3A+1%2C+% 2288d5d94e-f762-11e1-a439-00145eb45e9a%22%3A+77%2C+% 22e6acc36b-4149-4458-a26f-17fbeb0df90b%22%3A+1%7D%27%2C+ %27download%27%29%3B%0A++++++++++++update+query_log_master+ set+the_geom+%3D+CDB_LatLng%28lat%2Clon%29&api_key= b3263ddb2c8afddfa4bddca43d44727f9d3220ce Version:tracker.py 2015-09-01T13:00:37+02:00

The test-bigsearch2 requests are coming from the same source. Here is an example of one of those:

13:47:29.136 Trying SQL: INSERT INTO query_log_master( api_version, client, count, download, downloader, error, lat, lon, matching_records, query, query_version, request_source, response_records, results_by_resource, type) VALUES ( 'api.py 2015-09-02T11:09:38+02:00', 'portal-prod', 1, '/vn-downloads2/test-bigsearch2-0c6bfded9ef24aed80ac0f4a701afac1.tsv', ' stuff@things.com', 'None', 52.370216, 4.895168, 1, 'specificepithet:collaris genus:ochotona year:1960', 'search.py 2015-08-29T21:04:44+02:00', 'DownloadAPI', 1, '{"84aefc7e-f762-11e1-a439-00145eb45e9a": 1}', 'download'); update query_log_master set the_geom = CDB_LatLng(lat,lon) URL: http://vertnet.cartodb.com/api/v2/sql?q=INSERT+INTO+ query_log_master%28+++++++++++++api_version%2C+client%2C+ count%2C+download%2C+downloader%2C+error%2C+lat%2C+ lon%2C+++++++++++++matchingrecords%2C+query%2C+query version%2C+request_source%2C+response_records%2C+++++++++++ ++results_by_resource%2C+type%29+VALUES+%28+++++++++++++% 27api.py+2015-09-02T11%3A09%3A38%2B02%3A00%27%2C+% 27portal-prod%27%2C+1%2C+%27%2Fvn-downloads2%2Ftest-bigsearch2- 0c6bfded9ef24aed80ac0f4a701afac1.tsv%27%2C+%27stuff% 40things.com%27%2C+%27None%27%2C+52.370216%2C+4.895168%2C+++ ++++++++++1%2C+%27specificepithet%3Acollaris+genus%3Aochotona+year%3A1960% 27%2C+%27search.py+2015-08-29T21%3A04%3A44%2B02%3A00%27% 2C+%27DownloadAPI%27%2C+1%2C+++++++++++++%27%7B%2284aefc7e- f762-11e1-a439-00145eb45e9a%22%3A+1%7D%27%2C+%27download% 27%29%3B%0A++++++++++++update+query_log_master+set+the_geom+ %3D+CDB_LatLng%28lat%2Clon%29&api_key=b3263ddb2c8afddfa4bddca43d4472 7f9d3220ce Version:tracker.py 2015-09-01T13:00:37+02:00

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/VertNet/webapp/issues/637#issuecomment-270535421, or mute the thread https://github.com/notifications/unsubscribe-auth/AAcc7Knz0ZwKfo0FE-pMPm0AAm7cF_VKks5rPEOmgaJpZM4LbM0h .

We can write code to look for that email address and not pass along the request any further.

Who would be doing queries 16 times a day for the same ochotona filters from Amsterdam?

Who indeed?

On Wed, Jan 4, 2017 at 8:22 PM, John Wieczorek notifications@github.com wrote:

Who would be doing queries 16 times a day for the same ochotona filters from Amsterdam?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/VertNet/webapp/issues/637#issuecomment-270537447, or mute the thread https://github.com/notifications/unsubscribe-auth/AAcc7FgrLpCf3dB_KSoW4z0IBiiiNCvYks5rPEXhgaJpZM4LbM0h .

A mole from GBIF trying to undermine our portal? Russian hackers believing we hold the US economy in It hands?

On Jan 4, 2017 5:22 PM, "John Wieczorek" notifications@github.com wrote:

Who would be doing queries 16 times a day for the same ochotona filters from Amsterdam?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/VertNet/webapp/issues/637#issuecomment-270537447, or mute the thread https://github.com/notifications/unsubscribe-auth/AA5hb0kS8AKSkPMsmy5DM1bP9MH0A5USks5rPEXhgaJpZM4LbM0h .

Those moles are in Copenhagen, sir...

On Wed, Jan 4, 2017 at 8:28 PM, David Bloom notifications@github.com wrote:

A mole from GBIF trying to undermine our portal? Russian hackers believing we hold the US economy in It hands?

On Jan 4, 2017 5:22 PM, "John Wieczorek" notifications@github.com wrote:

Who would be doing queries 16 times a day for the same ochotona filters from Amsterdam?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/VertNet/webapp/issues/637#issuecomment-270537447, or mute the thread https://github.com/notifications/unsubscribe-auth/ AA5hb0kS8AKSkPMsmy5DM1bP9MH0A5USks5rPEXhgaJpZM4LbM0h .

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/VertNet/webapp/issues/637#issuecomment-270538473, or mute the thread https://github.com/notifications/unsubscribe-auth/AAcc7H8HezrBY1p95yh82M23PM1WGakZks5rPEczgaJpZM4LbM0h .

I understand that. First rule of being a malicious mole: don't do it from your hideout. Amsterdam provides the perfect cover, plus you can get great split pea soup whom you're done with your subterfuge.

On Jan 4, 2017 5:29 PM, "Rob" notifications@github.com wrote:

Those moles are in Copenhagen, sir...

On Wed, Jan 4, 2017 at 8:28 PM, David Bloom notifications@github.com wrote:

A mole from GBIF trying to undermine our portal? Russian hackers believing we hold the US economy in It hands?

On Jan 4, 2017 5:22 PM, "John Wieczorek" notifications@github.com wrote:

Who would be doing queries 16 times a day for the same ochotona filters from Amsterdam?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/VertNet/webapp/issues/637#issuecomment-270537447, or mute the thread https://github.com/notifications/unsubscribe-auth/ AA5hb0kS8AKSkPMsmy5DM1bP9MH0A5USks5rPEXhgaJpZM4LbM0h .

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/VertNet/webapp/issues/637#issuecomment-270538473, or mute the thread https://github.com/notifications/unsubscribe-auth/ AAcc7H8HezrBY1p95yh82M23PM1WGakZks5rPEczgaJpZM4LbM0h .

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/VertNet/webapp/issues/637#issuecomment-270538631, or mute the thread https://github.com/notifications/unsubscribe-auth/AA5hb0WB6MbdnmfL55902PnllbCr5z1Nks5rPEdhgaJpZM4LbM0h .

Fixed in commit 60bb5837c5ee0e82ced2786427b509acdb9a033c

VertNet / webapp

determine where test-bigsearch barrage is coming from #637