astropy
provTAP and PEP 249
When converting the local access of a database (directly to postgres, mysql or similar) to TAP (ADQL or native language), one runs into the problem that the TAP service does not support PEP 249, the Python Database API Specification, but maintains the queries in its very own way.
Apart from not using the connection/cursor classes, there is also no way to safely use variables (as in cursor.execute() in PEP 249); instead one has to locally create the full query and also do the required escape sequences manually. This has the danger of SQL injection, especially when the query is built using external input.
IMO pyvo should follow the Python standard for a database API here.
On Thu, Apr 11, 2019 at 09:14:27AM +0000, Ole Streicher wrote:
Apart from not using the
connection/cursorclasses, there is also no way to safely use variables (as incursor.execute()in PEP 249); instead one has to locally create the full query and also do the required escape sequences manually. This has the danger of SQL injection, especially when the query is built using external input.
The question of SQL injection would become relevant if web services used TAP to communicate with protected resources and offered public interfaces. And used astropy in the process. This is a use case I don't quite forsee at the moment. As things are now, people can already send arbitrary SQL to the TAP services, so there's certainly no need to do SQL injection.
However:
IMO pyvo should follow the Python standard for a database API here.
While I believe TAP is sufficiently different from normal database APIs (e.g., uploads, metadata inspection) that we'll always have to have a custom API, I'd say pulling a dbapi layer on top of that has a certain charm (my vote: use %(abc)s escaping style), if only to educate scientific programmers to not construct queries using plain string interpolation in the general case. The connection/cursor paradigm won't come terribly natural, I guess, but then connection -> service is a fairly plain map. For a natural way to do cursor I'd have to think longer.
I don't think I'll tackle it any time soon, but if someone wrote a contribution, I'm sure it'd be gratefully received.
-- Markus
There is already an API in pyvo.dal.dbapi2, though it still needs some love.
