In this tutorial, you will learn how to encrypt sensitive data in a MySQL database table using FastAPI and SQLAlchemy. Encryption is essential for protecting the confidentiality of data stored in a database and ensuring its security. In this example, we will use SQLAlchemy to model the database and the Fernet function from the cryptography library to encrypt and decrypt data.
Steps to Encrypt Data in a MySQL Table with FastAPI and SQLAlchemy:
Step 1: Set up the environment and create the database
First, make sure FastAPI and SQLAlchemy are installed in your development environment. Then, create a MySQL database and configure the connection in your FastAPI application.
from fastapi import FastAPI
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# Configure the connection to the MySQL database
DATABASE_URL = "mysql+mysqlconnector://user:password@localhost/database_name"
engine = create_engine(DATABASE_URL)
# Create an instance of the FastAPI application
app = FastAPI()
# Define a model class for the users table
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
name = Column(String)
password = Column(String) # The password will be stored in plain text before encryption
Step 2: Encrypt and store data in the database
We will use the Fernet function from the cryptography library to encrypt the password before storing it in the database. Make sure to replace ‘secret_key’ with a securely generated secret key.
from sqlalchemy.orm import Session
from cryptography.fernet import Fernet
# Generate a secret key for data encryption
secret_key = Fernet.generate_key()
fernet = Fernet(secret_key)
# Endpoint to create a new user with an encrypted password
@app.post("/create_user/")
async def create_user(user: User):
user.password = fernet.encrypt(user.password.encode())
db = Session(engine)
db.add(user)
db.commit()
db.close()
return {"message": "User created successfully"}
Step 3: Decrypt the password when necessary
To retrieve the decrypted password when needed, you can use the secret key to decrypt it.
# Endpoint to retrieve a user and decrypt the password
@app.get("/get_user/{user_id}")
async def get_user(user_id: int):
db = Session(engine)
user = db.query(User).filter(User.id == user_id).first()
if user:
decrypted_password = fernet.decrypt(user.password.encode()).decode()
db.close()
return {"name": user.name, "password": decrypted_password}
db.close()
return {"message": "User not found"}
Conclusion:
Encrypting data in a MySQL database table is an important practice to protect the security and confidentiality of sensitive data. In this tutorial, we have learned how to configure a MySQL database in FastAPI and use SQLAlchemy to interact with it. We have also used the Fernet function from the cryptography library to encrypt and decrypt passwords.
Remember to keep the secret key used for encryption secure, as it is essential for data protection. Additionally, implement additional security measures to ensure the integrity and confidentiality of your data in the database.
