With the advent of newer technologies and frameworks, Django took a really important place in the industry of web development frameworks. Django is a well-known and heavily tested open source Python framework which became public in 2005 with its first version. The framework follows a Model-Template-View (MTV) architectural pattern and, alongside with the variety of tools offered, it includes a stable Object Relational Mapping (ORM) framework.
In computer science, object-relational mappings is a programming technique that converts data from a relational database into objects that can be used programmatically during the development of an application. The primary objective of the methodology is to offer an abstract layer between the relational database and the application in a seamless way, without the necessity for the developer to actually write SQL queries.
For reference, a quick comparison between raw SQL queries and ORM-based queries.The code snippet below illustrates the traditional way to extract DBMS data into a scalar value that will be used in the workflow.
var sql = "SELECT id, first_name, last_name, phone, birth_date, sex, age FROM persons WHERE id = 10";
var result = context.Persons.FromSqlRaw(sql).ToList();
var name = result[0]["first_name"];
The following, on the other hand, is an example of the same query with an ORM layer.
var person = repository.GetPerson(10);
var firstName = person.GetFirstName();
While ORMs could technically understand how data has been represented in the DBMS by inspecting the database schema and generate an object-like structure automatically, it is considered standard practice for frameworks like Django to define a specific declarative model file which will then be used as a reference to define:
A simple model data structure could be represented as follows:
from datetime import date
from django.db import models
class Blog(models.Model):
name = models.CharField(max_length=100)
tagline = models.TextField()
def __str__(self):
return self.name
class Author(models.Model):
name = models.CharField(max_length=200)
email = models.EmailField()
def __str__(self):
return self.name
class Entry(models.Model):
blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
headline = models.CharField(max_length=255)
body_text = models.TextField()
pub_date = models.DateField()
mod_date = models.DateField(default=date.today)
authors = models.ManyToManyField(Author)
number_of_comments = models.IntegerField(default=0)
number_of_pingbacks = models.IntegerField(default=0)
rating = models.IntegerField(default=5)
def __str__(self):
return self.headline
The above example, when the migration is applied via the CLI tool, will be translated into the following data structure:
So during the execution of our application, the data can be queried:
# Creating a new blog
from blog.models import Blog
b = Blog(name="Beatles Blog", tagline="All the latest Beatles news.")
b.save()
# Querying a blog
from blog.models import Blog
b = Blog.objects.get(pk=1)
print(b.name) # Will output "Beatles blog"
# Updating a blog
from blog.models import Blog
b = Blog.objects.get(pk=1)
b.name = "Guns n roses blog"
b.save() # Blog title will be "Guns n roses"
SQL Injection (SQLi) is a type of attack where an attacker inserts malicious SQL code into a query, allowing them to manipulate databases and access sensitive information. The attack arises whenever a malicious actor is able to convert input supplied data like query strings, post parameters or cookie values into a SQL query. Consequences of this attack could vary from identity spoofing, data stealing/disclosure/loss, lateral privilege escalations or, in some cases, command execution.
The examples from the previous section showed how it is possible to interact with the Django framework and extract data from the DBMS, but is it possible to achieve a SQL injection in the following snippet?
from blog.models import Blog
input_name = request.POST.get("blog_name") # Takes the blog name from POST data
b = Blog.objects.filter(blog_name == input_name)
print(b.name)
Fortunately the above example is not vulnerable to SQL injection attacks, since everything that goes into the .filter() method will be parameterised automatically. But what happens whenever there is the necessity to build a custom and complex query?
from blog.models import Blog
input_name = request.POST.get("blog_name") # Takes the blog name from POST data
# vulnerable code, do not use
b = Entry.objects.raw("SELECT * FROM entries LEFT JOIN blog ON blog.id = entry.blog_id WHERE blog.name = '"+input_name+"'")
print(b.name)
As specified in the official Django documentation, no checking is done on the SQL statement passed on the raw function, making it possible to achieve a SQL injection in the traditional way by appending an OR condition to the classic query string OR 1=1.
In some circumstances, especially when the query is complex enough and using the declarative APIs of the Django ORM could be cumbersome, it is possible to achieve the same result by interacting with the Python Database API directly like the following:
from django.db import connection
cursor = connection.cursor()
input_name = request.POST.get("blog_name") # Takes the blog name from POST data
cursor.execute('SELECT * FROM entries LEFT JOIN blog ON blog.id = entry.blog_id WHERE blog.name = "%s"', (input_name,))
cursor.close()
In this case the WHERE condition parameter will be escaped by the library without further intervention and it could be considered as a best practice in order to avoid injection issues whenever there is the necessity to interact with the Python Database API directly.
Note: by using the Python Database API interface, the result set will be represented as a scalar, so it will not be mapped to the existing Entities defined in the model file. Additional code should be implemented in order to translate the scalar values into an entity.
While ORMs like the one included in the Django Framework can be particularly useful to avoid SQL injection issues, some of the APIs offered by the framework should be analysed carefully before the implementation.
The Python Database API, if used correctly, can be considered as a valid alternative when dealing with complex data structures or queries.
In general there are some security countermeasures to take in consideration when dealing with a Django ORM based application.
`.raw()` function of the Django ORM should be used with care, and only when the function does not expect input data that will be concatenated into a query string.