PREVENT YOUR BACKEND FROM SQL INJECTION

Graphite Community — Mon, 26 Aug 2024 13:29:37 +0000

This is a series of short articles by Graphite Community on software engineering and security.

WHAT IS SQL INJECTION

SQL injection is an injection attack (usually from a malicious source) that uses input from the client side to perform operations on an application's database (backend/server side).

This attack is common with PHP and ASP applications but needs to be looked out for and prevented when building the backend of an application.

RISKS POSED BY SQL INJECTION

Privacy: This attack can be used to view users' data in the database hence, breaching privacy.
Authentication: This attack can be used to view users' login details hence, unauthorized access can be gotten to a user's account.
Authorization: This attack can be used to view users with higher-level permission granting the hacker unauthorized access to the application. This kind of access will put the hacker in a position to perform operations which can cause more damage.
Integrity: This attack can be used to tamper (write or delete) the data in the database.

SAMPLE CODE

This is an SQL injection caused by a dynamic query (concatenating a variable to an SQL query string)

import sqlite3

def connect_to_db():
    connection = sqlite3.connect('user_data.db')

    return connection

def get_user_input():
    customer = input("Enter a name")
    return customer

def print_result(results):

    try:
        for row in results:
            print(row)
    except TypeError as error:
        print(error)

def create_table():
    connection = connect_to_db().cursor()

    try:
        query = "create table user_data (firstname, lastname)"

        connection.execute(query)

        connection.close()

        print("Table crated successfully")

    except sqlite3.OperationalError as error:

        print(error)
        delete_data()
        create_table()

def insert_data():
    connection = connect_to_db()
    cursor = connection.cursor()

    try:
        query = "INSERT INTO user_data VALUES ('Qowiyyu', 'Adelaja'), ('Qudus', 'Babalola'), ('Tom', 'Blue')"
        cursor.execute(query)
        connection.commit()
        connection.close()
        print("Data Added to table successfully")

    except sqlite3.OperationalError as error:
        print(error)

def delete_data():
    connection = connect_to_db()
    cursor = connection.cursor()

    try:
        query = "DROP TABLE user_data"
        cursor.execute(query)
        connection.commit()
        connection.close()
        print("All data Deleted")
    except sqlite3.OperationalError as error:
        print(error)

def vulnerable_code(name):

    try:
        print(f"The entered name is {name}")
        query = f"SELECT firstname, lastname FROM user_data WHERE firstname = '{name}'"

        cursor = connect_to_db().cursor()
        cursor.execute(query)
        return cursor.fetchall()

    except sqlite3.OperationalError as error:
        print(error)


def non_vulnerable_code(name):

    try:
        print(f"The entered name is {name}")
        query = f"SELECT firstname, lastname FROM user_data WHERE firstname = ?"

        connection = connect_to_db()
        cursor = connection.cursor()
        cursor.execute(query, (name,))
        return cursor.fetchall()

    except sqlite3.OperationalError as error:
        print(error)

if __name__ == "__main__":
    create_table()
    insert_data()
    name = get_user_input()
    print("VULNERABLE CODE RESULT TO FIND Qowiyyu IN THE DATABASE")
    result = vulnerable_code(name)
    print_result(result)

    print("NON-VULNERABLE CODE RESULT TO FIND Qowiyyu IN THE DATABASE")
    result = non_vulnerable_code(name)
    print_result(result)

Results

When a valid non-attack intended input is submitted

It can be seen that without an attack input, both the code vulnerable and non-vulnerable to an SQL injection gave the expected output.

When an attack input is submitted

It can be seen that with an attack input submitted, the vulnerable code was tricked by the input to return all the users' info in the database while the non-vulnerable code did not return anything.

Source code here: Graphite Academy SQL Injection Lab

ORMs (Object-Relational Mapping)

ORMs like SQLAlchemy, SQLModel, Django ORM also help in preventing SQL injection by providing an object-oriented interface which automatically generates the SQL query.

Like and share this article to help others discover it. Don't forget to follow us so you're notified whenever we release new content.