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
Then we use the Python library to request our database. Download the necessary package:
pip 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-pipIf you're seeing the error message "Could not find a version that satisfies the requirement psycopg2", enter this command:
pip install psycopg2-binaryTo make sure you can type in CLI:
pip --version
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()
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
}
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.");
}
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.
