Protection from SQL Injections and Other Attacks: PREPARE, EXECUTE, parameterized queries

Alright, first let's figure out what we're protecting, or more specifically — from whom. At the start of this level, we already mentioned SQL injections, which are some of the most popular and destructive attacks on databases. The attack works like this: a bad actor sends malicious SQL code into your query to "trick" it and get access to data they shouldn't see. Now let's break it down in more detail.

SQL Injection Example

Let's say you have a web app with a simple username and password input. The backend runs an SQL query to check if the user exists in the database:

SELECT * FROM users WHERE username = 'admin' AND password = 'password123';

This query looks fine... as long as the user enters valid data. But what happens if someone enters:

• Username: admin' --
• Password: (let's leave this field empty)

In the end, the query turns into something like this:

SELECT * FROM users WHERE username = 'admin' -- AND password = 'password123';

Notice the -- symbols, which in SQL mean the start of a comment. Everything after them gets ignored. As a result, the password check just gets skipped, and the attacker gets in as admin!

Potential Consequences of SQL Injections

SQL injection can lead to some nasty consequences:

1. Getting unauthorized access to data. For example, a bad actor could get their hands on sensitive stuff, like user passwords.
2. Deleting or messing up data. Someone could wipe out a whole table or just make a mess of it.
3. Running arbitrary SQL code. Imagine an attacker running DROP DATABASE... Yeah, that's a nightmare.

But hey, we're not scared! PostgreSQL gives us a bunch of tools to help us defend against these attacks.

How to Defend? Methods to Prevent SQL Injections

1. Using prepared statements (PREPARE and EXECUTE)

Prepared statements are like a tried-and-true recipe for your SQL code. Here's how they work: you "prepare" the SQL query once, and then pass the data into it separately. This makes it impossible to sneak in malicious code.

Here's an example of the right way to do it:

Prepare the query using PREPARE.

PREPARE user_login (text, text) AS
SELECT * 
FROM users 
WHERE username = $1 AND password = $2;

The query with two parameters $1 and $2 expects the real values to be passed in later.

Use EXECUTE to run the query.

EXECUTE user_login('admin', 'password123');

PostgreSQL will automatically escape all user data, stopping any malicious SQL code from getting in.

Why this rocks:

• SQL injection just can't happen, since parameters are treated as plain data, not as part of the SQL code.
• Queries are safer and run faster thanks to execution plan caching.

2. Parameterized Queries

This method is especially popular in apps written in languages like Python or Java. Instead of manually using PREPARE and EXECUTE, you can use libraries or ORMs that handle parameters for you.

Example with Python and the psycopg2 library:

import psycopg2

connection = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()

# Using a parameterized query
username = "admin"
password = "password123"
query = "SELECT * FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))

# Your data is totally safe!
result = cursor.fetchall()
print(result)

Check out the %s in the SQL query — that's where the parameter goes. The psycopg2 library takes care of passing the data safely.

3. Input Validation

If you're passing in user-entered data, make sure it matches what you expect. For example:

• For text data, use regular expressions to make sure there are no forbidden characters.
• For numbers, make sure they're actually numbers.

Example in Python:

import re

username = input("Enter username: ")

# Only allow letters, numbers, and underscores
if re.match(r"^\w+$", username):
    print("Username is valid")
else:
    print("Dangerous username!")

4. Use Minimal Privileges

Make sure the roles you use to run queries have only the privileges they really need. For example, don't give a role access to DROP TABLE or ALTER TABLE commands if it's not necessary.

5. Log Suspicious Activity

You can use PostgreSQL settings to track user activity:

• log_statement = 'all' — log all queries.
• log_connections = on — log all connections to the database.

These settings help you spot potentially shady actions.

Implementation Examples

Example 1: Using a prepared statement via SQL

-- Let's create a prepared statement
PREPARE check_credentials (text, text) AS
SELECT * FROM users WHERE username = $1 AND password = $2;

-- Run the query with safe parameters
EXECUTE check_credentials('admin', 'password123');

Example 2: Parameterized query in Python

query = "UPDATE users SET last_login = NOW() WHERE username = %s"
username = "admin"
cursor.execute(query, (username,))

Security Recommendations

Always validate input data. Never trust data that comes from the user.

Only use prepared statements or parameterized queries. These methods are your main shield against SQL injections.

Assign roles with the minimum privileges needed. This minimizes the damage if an attacker gets in.

Set up logging and check your logs regularly. Stay on top of what's happening in your database.

SQL injections are definitely a scary beast, but if you use prepared statements, parameterized queries, and stick to good practices, you'll keep your database safe and sleep easy at night, without worrying that someone will "accidentally" wipe all your tables. PostgreSQL gives you all the tools for this, so go for it!