30.06.2023

Protecting a Linux database from SQL injection

Get acquainted with the basics of web application security, and how to prevent attacks on your databases. As part of the instruction, master the skills of finding vulnerabilities and learn how to apply various methods of protection against this kind of attack to ensure the security of their applications.

SQL

SQL (Structured Query Language) is a language that has strict syntax rules in query construction and performs the function of managing relational databases. SQL is a declarative language, but its main direction is databases, you can access it directly, or you can use a programming language with pre–installed libraries.

SQL injections

The first and foremost action of security in any system is quite simple, we need to update the software of the machine. On Debian, this command will be:

sudo apt update && sudo apt upgrade -y

Screenshot №1 — Update machine packets

Then we use the Python library to request our database. Download the necessary package:

pip install psycopg2

Screenshot №2 — Install psycopg2

If you're seeing the error message "pip command not found" in a Debian system>, enter this command:

sudo apt-get install python3-pip

If you're seeing the error message "Could not find a version that satisfies the requirement psycopg2", enter this command:

pip install psycopg2-binary

To make sure you can type in CLI:

pip --version

Screenshot №3 — Pip installation

Parameterized Queries

Let's say you have a web application that allows users to search for products by name. Instead of using a query like:

SELECT * FROM products WHERE name = 'searchterm';

You need to use a parameterized query that can predict input non–validation data like in this config:

SELECT * FROM products WHERE name = ?;

And pass the search term as a parameter, for example, present below:

import psycopg2

conn = psycopg2.connect(database="mydb", user="myuser", password="mypassword", host="localhost", port="5432")

cur = conn.cursor()

search_term = "apple"

cur.execute("SELECT * FROM products WHERE name = %s;", (search_term,))

rows = cur.fetchall()

for row in rows:
print(row)

conn.close()

Screenshot №4 — Firts method

Input Validation

Let's say you have a form where users can submit comments. Before inserting the comment into the database, validate the input to ensure it only contains valid characters. For example, in PHP, you can use the preg_match function to check for alphanumeric characters and spaces.

$comment = $_POST['comment'];

if (!preg_match('/^[a-zA-Z0-9 ]+$/', $comment)) {
// Invalid input, do something
} else {
// Insert comment into database
}

Screenshot №5 — Second method

Least Privilege Access

You have a database with multiple tables, and you want to create a user that can only read data from one table. Create a new user and grant them only SELECT privileges on that table. Example display below:

CREATE USER readonly_user;
GRANT SELECT ON products TO readonly_user;

Error Handling

Let's say you have a login form where users can enter their username and password. If there is an error, such as an incorrect username or password, display a generic error message instead of a detailed one. For example, in Java, you can catch the SQLException and display a generic error message.

try {
// Execute query to check if the username and password match
} catch (SQLException e) {
System.out.println("Error: Could not log in.");
}

Screenshot №6 — Third method

Protecting against SQL injection

In conclusion, web application security is a crucial aspect of software development. SQL injection attacks are one of the most common types of attacks on databases that can result in significant damage to an organization. To prevent such attacks, it's important to learn the basic principles of the SQL language and its vulnerabilities, study SQL injection techniques, and analyze code examples containing SQL injection vulnerabilities.

Additionally, developers should apply various methods of protection against SQL injections, such as using parameterized queries, input validation, least privilege access, and error handling. By implementing these techniques, developers can ensure the security of their applications and protect sensitive data from unauthorized access. It's essential to stay updated with the latest security measures and keep software up–to–date to avoid any potential vulnerabilities.