Security

In the previous two sections, we hardcoded the hostname, username and password of our local MySQL instance in the connect_to_database function of the database.py file. As long as you're working locally on your laptop, this is okay. But as soon as your code ends up in a repository that others can watch, especially if it has public access, this is a big no-go.

In this section, we will demonstrate some basic security features to keep your sensitive information (like usernames and passwords) safe from prying eyes.

The .env file

As of now, we will make use of a .env file. A .env file, short for "environment", is a simple text file used to store configuration variables and sensitive information for your application environment. These variables typically include things like API keys, database connection strings, or any other environment-specific settings.

The purpose of using a .env file is to separate your configuration from your code and thus avoid hardcoding usernames, passwords, keys, etc. It also helps to maintain security by keeping sensitive data out of version control systems like Git.

Typically, the .env file consists of key-value pairs in the format KEY=VALUE, with each variable on a new line. Our .env file will look like this:

DB_USERNAME=root
DB_PASSWORD=1234
DB_HOSTNAME=localhost

Go ahead and create your .env file in your Python project. Please beware that the name of this file is literally .env, so with only the file extension .env, nothing in front of it.

Version control

Make sure to include the .env file in .gitignore. That way, it will never end up in a Git repository.

As a result, when working on a project in team, every team member should create his/her own .env file in the local repository.

When hosting your project in the cloud (see later), environment variables should be declared there, similar to how you declared them in the .env file.

Reading values in config.py

In our Python project, we will also create a new config.py file. The code in this file will read out the values of our .env file and make it available to our Python project.

The config.py file will contain the following code:

import os
from dotenv import load_dotenv

load_dotenv()

db_username = os.environ.get('DB_USERNAME')
db_password = os.environ.get('DB_PASSWORD')
db_hostname = os.environ.get('DB_HOSTNAME')

The load_dotenv() function used in this file is a function from the dotenv library that will read the content of our .env file and create environment variables. These environment variables are then accessible via os.environ.get('VARIABLE_NAME'), where 'VARIABLE_NAME' is the name of the environment variable you want to access.

In lines 6, 7 and 8, we read out the variables we created in our .env file and store them in Python variables. These Python variables can now be accessed by other files. Let's see how that works!

Accessing values from config.py in database.py

We actually don't need to change that much in database.py. In our basic structure, we should simply add an import of the config.py file at the top and change the line of code that is used to build up our MySQL connection.

In the connection, simply replace the hard coded hostname, username and password by the variables we created for them in config.py using the following notation: config.variable_name, where you replace variable_name by the variable you need.

The two highlighted lines of code below show the change.

import mysql.connector
import config

def connect_to_database():
    try:
        connection = mysql.connector.connect(host=config.db_hostname, user=config.db_username, password=config.db_password)
        return connection
    except mysql.connector.Error as error:
        print("Error connecting to database:", error)
        return error

def execute_sql_query(sql_query, query_parameters = None):
    connection = connect_to_database()
    result=''
    try:
        cursor = connection.cursor()
        cursor.execute(sql_query, query_parameters)
        if sql_query.upper().startswith("SELECT"):
            # executed for GET requests
            result = cursor.fetchall()
        else:
            # executed for POST requests
            connection.commit()
            result = True

        cursor.close()

    except mysql.connector.Error as exception:
        print("Error executing SQL query:", exception)
        result = exception


    finally:
        if connection.is_connected():
            connection.close()

        return result

Project structure

As of now, our project structure will look like this:

📂myproject
├── 📄.env
├── 📄config.py
├── 📄database.py
├── 📄main.py
└── 📂queries
    └── 📄festival_queries.py

Default values

In some cases we want to define default values to fall back on when the requested variable is not present in the .env file. If so, we add a second parameter to the os.environ.get() function in config.py as shown below. In the example below, localhost will become the value for db_hostname in case DB_HOSTNAME can't be found in the .env file. We might as well want to store the URL for our documentation in the .env file. By doing that, we can choose our own documentation endpoint when programming locally. Because of the default value None, no documentation endpoint will be available in case DOCS_URL can't be found in the .env file.

import os
from dotenv import load_dotenv

load_dotenv()

db_username = os.environ.get('DB_USERNAME')
db_password = os.environ.get('DB_PASSWORD')
db_hostname = os.environ.get('DB_HOSTNAME', "localhost")
documentation_url = os.environ.get("DOCS_URL", None)

To change the documentation endpoint of FastAPI, we need to change only one line of code in main.py, which is the line where we create our FastAPI instance, and add one import.

The change we need to make (applied to the example of the previous section of the course) is highlighted in the code below.

from fastapi import FastAPI
import database
from queries import festival as queries
import config

app = FastAPI(docs_url=config.documentation_url)

@app.get("/festivals")
def get_all_festivals():
    query = queries.festival_name_query
    festivals = database.execute_sql_query(query)
    if isinstance(festivals, Exception):
        return festivals, 500
    festivals_to_return = []
    for festival in festivals:
        festivals_to_return.append(festival[0])
    return({'festivals': festivals_to_return})

@app.get("/province")
def get_all_festivals_by_province(name: str):
    query = queries.festivals_by_province_query
    festivals = database.execute_sql_query(query, (
        name,
    ))
    if isinstance(festivals, Exception):
        return festivals, 500
    festivals_to_return = []
    for festival in festivals:
        festivals_to_return.append(festival[0])
    return({'festivals': festivals_to_return})

def get_all_festivals_by_name_and_month(name: str, month: int):
    query = queries.festivals_by_name_and_month_query
    festivals = database.execute_sql_query(query, (
        '%{}%'.format(name),
        month,
        month,
    ))
    if isinstance(festivals, Exception):
        return festivals, 500
    festivals_to_return = []
    for festival in festivals:
        location = festival[1] + ' (' + festival[4] + ')'
        festival_dictionary = {"name": festival[0],
                               "startDate": festival[2],
                               "endDate": festival[3],
                               "location": location }
        festivals_to_return.append(festival_dictionary)
    return({'festivals': festivals_to_return})

Of course, don't forget to add DOCS_URL to your .env file:

DB_USERNAME=root
DB_PASSWORD=1234
DB_HOSTNAME=localhost
DOCS_URL=/docs

Credentials as default value?

Shouldn't we also have default values for db_username and db_password in config.py? The answer is no. Default values are great to work with, but we should not use them for credentials like usernames and passwords. If we were to add this as a default value, we would defeat the purpose of keeping this data in a .env file.