Give it your database and it gives you an API with an endpoint for each column in each table (don't worry, you can exclude whatever table/column you like).
WARNING: there's absolutely no security whatsoever. Don't use this in production. This should only be used to explore a database or to play around. If you use it for anything else then that is on YOU.
I was working on a project and needed a way to easily spin up an API. The project has long since finished, but I had to scratch that itch.
NOTE: This is being developed against Python 3.10.8, so it'll work for that version. Anything else, you're on your own.
It's pretty simple to use. Create a file, import and instantiate a PyAutoAPI
object, then run uvicorn
. That's it! Then you can start querying the API.
Let's create a file called main.py
with the following contents
import pyautoapi as pyapi
api = pyapi.PyAutoAPI("mydatabase.db")
In your terminal run
$ uvicorn main:app --reload
INFO: Uvicorn running on http://127.0.0.1:8000 (Press CTRL+C to quit)
INFO: Started reloader process [28720]
INFO: Started server process [28722]
INFO: Waiting for application startup.
INFO: Application startup complete.
(If the above terminal looks familiar, it's because PyAutoAPI
uses FastAPI
under the hood.)
Now navigate to localhost:8000/docs to see the Swagger UI.
Everything is accessed through /?statement=<my-statement>
endpoint. For example,
if you want to get everything from the table called users
you'd use the following
query
import json
from urllib import request
from urllib.parse import urlencode
url = f"http://localhost:8000/?{urlencode({'statement': 'select * from users'})}"
response = request.urlopen(url)
data = json.loads(response.read())
The data
variable will hold the results of executing the givens statement and
will always be a list
of dict
s. If the execution of the statement was successful,
there are two possiblities for the response, the response will contain:
- the data that you selected if you sent a
SELECT
statement as alist
ofdict
s with eachdict
representing a row - a
list
with onedict
:[{"info": "Statement executed successfully"}]
which happens when aDELETE
,INSERT
, orUPDATE
statement is sent (nothing is returned from SQLAlchemy when these statements are executed).
If for some reason the execution of the statement fails, then the following will be returned:
{
"detail": "Invalid statement {exception}"
}
where {exception}
contains the exception that SQLAlchemy raised (which includes the
statement that you used) and an HTTPError
exception will be raised (if using Python
to make the request). For example, if you attempted to select
from a non-existent
table called table
then this would be what's returned:
{
"detail": "Invalid statement: (sqlite3.OperationalError) near \"table\": syntax error\n[SQL: select * from table]\n(Background on this error at: https://sqlalche.me/e/20/e3q8)"
}
SQLAlchemy is used to execute the statements that you send as raw text
statements
so go ahead and use much more complicated statements. Nothing is off limits! This
includes, but is not limited to, SQL injection. Just remember that there's no absolutely
security so if you open this API to the world then you will probably open yourself to
a world of hurt!
You can configure both the FastAPI object that PyAutoAPI uses by passing keyword arguments
when instantiating the PyAutoAPI object or the SQLAlchemy sessionmaker
function that
PyAutoAPI uses. Here's how:
api = PyAutoAPI() # include any keyword arguments that FastAPI uses
api.init_api(database=database) # include any keyword arguments that sessionmaker uses
Make sure that if you want to configure the session that PyAutoAPI uses you must use
the above two-step process and don't pass a URL or Engine
object to the
initializer because that is what triggers the session to be created. If you want to
configure the Engine
object then create your own with your own configurations and
then pass that to the PyAutoAPI during instantiation or to the init_api
method.
If you'd like to contribute (this would be very much welcome), then simply fork the repo, make your changes, and issue a pull request. To install this for development, you'll need to install the development dependencies using
make dev-setup
You can run the tests with
make test
and for the code coverage report
make coverage
Make sure to format and lint your code with
make lint
make format
respectively.
Certainly! Here's an updated version of the warning section that includes security vulnerabilities:
IMPORTANT: PyAutoAPI is intended for development and exploration purposes only. It is not suitable for production environments. The following points highlight security vulnerabilities that you should be aware of:
-
No Authentication or Authorization: PyAutoAPI does not provide any authentication or authorization mechanisms. This means that anyone with access to the API can execute arbitrary SQL statements on the connected database. It is strongly recommended to use PyAutoAPI only in isolated and trusted environments.
-
SQL Injection: PyAutoAPI executes raw SQL statements provided by users without any sanitization or parameterization. This opens the possibility of SQL injection attacks. Do not expose the PyAutoAPI endpoint to untrusted or public networks. Always validate and sanitize user input before constructing SQL statements.
-
Limited Error Handling: PyAutoAPI provides minimal error handling and exception reporting. When an error occurs during the execution of a statement, the API response may leak sensitive information, including the SQL statement and underlying database errors. Carefully review and handle exceptions to prevent exposing sensitive information to users.
-
No Input Validation: PyAutoAPI does not validate or enforce any constraints on the input statements. This means that malformed or invalid statements could potentially cause unexpected behavior, expose sensitive data, or even lead to database corruption. Always ensure that the input statements are valid and adhere to the expected syntax and semantics.
-
Potential Denial of Service (DoS) Attacks: Since PyAutoAPI allows users to execute arbitrary SQL statements, it is possible for malicious users to craft resource-intensive or inefficient queries that could consume excessive system resources and impact the performance of the server. Limit access to PyAutoAPI and monitor resource usage to mitigate potential DoS attacks.
It is crucial to emphasize that PyAutoAPI does not prioritize security features, and using it in an unsecured manner can expose your system to various risks. Always exercise caution and follow secure coding practices when using PyAutoAPI.
Remember, by using PyAutoAPI, you assume all risks and responsibilities. The project's creator and contributors are not liable for any misuse, security breaches, or consequences resulting from using PyAutoAPI in inappropriate or insecure contexts.
This project is licensed under the MIT License, so basically do whatever the hell you want to do with this, just don't hold me responsible for whatever terrible things may happen to you due to the complete lack of security that was mentioned (but not limited to) above.