Next, let's see how the background system is connected to the database so that the application can save, modify, search, and delete data.
Old, dangerous way
In the past, it was typical for web applications to talk to databases by building raw SQL queries, often combining user input with the query. If we think of a simple todo list application, the code that adds a new task could look like this:
@app.route('/todos', methods=['POST'])
def create_todo():
title = request.form['title']
result = connection.execute(f"INSERT INTO todos (title) VALUES ('{title}')")
todo_id = result.lastrowid
return '', 204
If you remember from the module on HTML templates that this old-fashioned template in HTML construction led to XSS vulnerabilities, you might already guess what kind of vulnerability this becomes. The correct answer is SQL injection. If the user input a quotation mark in the title variable, then the user could change the structure of the SQL query as they please.
You can learn more about SQL injections at Hakatemian SQL injection course, but let's now look at the correct, modern way to use a database from a web application backend.
Modern, proper way: ORM
Nowadays modern web applications communicate with databases through an abstract layer called ORM (Object Relational Mapper). This makes writing database code not only easier and more enjoyable but also significantly safer.
Let's start by defining a data class that defines a table called "Todo" with id, task, and done columns.
class Todo(db.Model):
id = db.Column(db.Integer, primary_key=True)
task = db.Column(db.String(200))
done = db.Column(db.Boolean)We can now use a class for data processing. For example, retrieving all tasks from the database is this easy:
todos = Todo.query.all()A new task can be created as follows:
new_todo = Todo(task='Buy ketchup', done=False)
db.session.add(new_todo)
db.session.commit()And so on. Next, let's look at a complete example.
Simple example
This application is a simple to-do list created using Python's Flask and SQLAlchemy libraries. With this application, you can add tasks to the list, mark them as completed, or delete them.
When you open the application, you will see all the tasks in the database. You can add new tasks by entering them into the text field and clicking the "Add" button. Next to each task, there are two links: one to mark the task as complete and another to delete the task.
When you mark a task as completed, its name will be crossed out. If you want to remove a task, you can click the "x" button next to it.
This application is built on an SQLite database that stores all the tasks you add to a database named todo.db locally on your disk.
main.py
from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///todo.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class Todo(db.Model):
id = db.Column(db.Integer, primary_key=True)
task = db.Column(db.String(200))
done = db.Column(db.Boolean)
@app.route('/')
def index():
todos = Todo.query.all()
return render_template('index.html', todos=todos)
@app.route('/add', methods=['POST'])
def add():
task = request.form['task']
new_todo = Todo(task=task, done=False)
db.session.add(new_todo)
db.session.commit()
return redirect(url_for('index'))
@app.route('/update/<int:id>')
def update(id):
todo = Todo.query.filter_by(id=id).first()
todo.done = not todo.done
db.session.commit()
return redirect(url_for('index'))
@app.route('/delete/<int:id>')
def delete(id):
todo = Todo.query.filter_by(id=id).first()
db.session.delete(todo)
db.session.commit()
return redirect(url_for('index'))
if __name__ == '__main__':
with app.app_context():
db.create_all()
app.run(host='0.0.0.0', port=81)
templates/index.html
<!DOCTYPE html><html><head><title>To-do list</title></head><body><h1> To-do list </h1><form method="POST" action="{{ url_for('add') }}"><input type="text" name="task" placeholder="Lisää tehtävä"> <button type="submit">More</button></form><br><ul> {% for todo in todos %}<li {% if todo.done %}style="text-decoration: line-through"{% endif %}> <a href="{{ url_for('update', id=todo.id) }}">✓</a> {{ todo.task }} <a href="{{ url_for('delete', id=todo.id) }}">x</a></li> {% endfor %}</ul></body></html>Practice
Valitettavasti Replit-palvelu on muuttnut lennosta eikä enää anna suorittaa näitä koodeja suoraan selaimessa. Voit klikata alla olevaa "Open in Replit" linkkiä ja avata koodin Replit-palvelussa. Meillä on työn alla etsiä Replitille korvaaja.
Ready to become an ethical hacker?
Start today.
As a member of Hakatemia you get unlimited access to Hakatemia modules, exercises and tools, and you get access to the Hakatemia Discord channel where you can ask for help from both instructors and other Hakatemia members.