Django ORM vs raw SQL - security

Question

My django project makes use of lots and lots of database queries, some are complex and some are basic SELECT queries with no conditions or logic involved.

So far I have been using the sqlite3 module to manage my database, instead of the django ORM which has worked very well. One problem or drawback I am aware of using raw SQL queries is their security flaws when compared to django's ORM, such as being viable to SQL injection attacks when passing in user input into my raw SQL queries.

My question is - Is it absolutely necessary to use django's ORM for queries involving user input or can I use a general function to remove any potentially malicious characters eg (,' -, *, ;)

def remove_characters(string:str):
    characters = ["'", ";", "-", "*"]
    for char in characters:
        if char in string:
            string = string.replace(char, "")
    return string

example of vunrable query in my project

username = "logan9997"
password = "x' or 'x' = 'x"

def check_login(self, username, password):
    sql = f"""
        SELECT *
        FROM App_user
        WHERE username = '{remove_character(username)}'
            AND password = '{{remove_character(password)}'
    """

Without the remove_characters function a hacker could gain access to someone else's account if the inputs were not sanitized

would this remove ALL threats of an SQL injection attack?

And would it just make more sense to use the ORM for queries involving user input?

Speaking as a person who wrote lots of PHP code which ended up being vulnerable to SQL injection, this strategy of "just remember to filter out/escape bad characters" never works across a big project. Inevitably you end up forgetting to do it somewhere, or doing it twice, or running into a person who uses `'` in their name. The much better approach is either [parameterized SQL](https://docs.djangoproject.com/en/4.1/topics/db/sql/#passing-parameters-into-raw), or using an ORM. — Nick ODell, Feb 19 '23 at 18:36
If you want to avoid SQL injection, *don't use f-strings to inject values into your statements*. Use parameter substitution, as described [here](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python). Under the hood, this is what Django's ORM is doing anyway. — snakecharmerb, Feb 19 '23 at 18:40
The documentation for the sqlite3 module has a section on using query parameters, and this is good defense against SQL injection. https://docs.python.org/3/library/sqlite3.html#how-to-use-placeholders-to-bind-values-in-sql-queries — Bill Karwin, Feb 20 '23 at 00:48
Your remove_character approach is totally unreliable. There is an established method of protecting from injections, *why don't you just use* it without much fuss? — Your Common Sense, Feb 20 '23 at 05:15

Django ORM vs raw SQL - security

0 Answers0