intro
As you may know, databases store and organize data in structures that are then accessed by SQL queries. In databases, we store valuable and sensitive information such as personal data or financial records, which makes our databases and the data within them a prime target for attackers.
One of the top attacks on databases is an SQL injection attack. An SQL injection attack occurs when an attacker modifies an SQL query, allowing the attacker to access sensitive information stored in the database.
To prevent an SQL injection attack, you can use database security practices such as parameterizing SQL queries and not providing user input straight into a database. A parameterized query is a query in which placeholders are used for parameters, and the parameter values are supplied at execution time.
Apart from preventing SQL injection attacks, parameterized SQL queries can help you reuse SQL queries, especially when performing repetitive tasks. Reusing SQL queries saves time and effort spent coding, allowing you to focus on important things such as extracting meaningful insights from the data.
In this tutorial, you will learn how to parameterize SQL queries to prevent SQL injection attacks and to make your SQL queries reusable.
Prerequisites
To follow along with this tutorial, you will need the following:
Connecting Postgres to DbVisualizer
Step 1: Start DbVisualizer and click Create a Connection button as shown below.
Step 2: Search for Postgres and double-tap the Postgres driver from the popup menu on the left side of your screen, as shown below.
An object view tab for the Postgres connection is opened.
Step 3: Fill in the database connection name field with “ParameterizeQuery,” as shown below.
Fill in the Database Userid field with “postgres” and the Database Password field with your password (we use “test123”) as shown below.
Once done, click the Connect button at the bottom, and if there are any issues, they will be displayed under the Connection Message section.
If the connection is successful, you should see the newly created ParameterizeQuery connection on the left sidebar of your screen.
Parameterizing Queries In SQL To Prevent SQL Injection Attacks
Using parameters helps prevent SQL Injection attacks when the database is used in conjunction with a web application by making the query and user input separate.
For example, consider a web application that requires users to enter a Username and Password to login into their accounts. When a user enters a username and password on the login form, the query below is executed on the database.
1
SELECT * FROM users WHERE username = '<username>' AND password = '<password>';
If the user input from the login form is not validated correctly, an attacker can exploit this SQL query and gain unauthorized access to your web application. If user input is parameterized, the query and the data are sent to the database separately.
Now, we will show you how to prevent SQL injection by parameterizing a query using DbVisualizer. Parameterizing a query helps deny attackers the ability to gain unauthorized access by injecting additional conditions into an SQL statement.
Parameterizing Queries in DbVisualizer
Step 1: Navigate to this Google Drive Link and download a web application user’s login dataset.
Step 2: Open the database connection ParameterizeQuery on DbVisualizer. Right-click the Tables tab tree and select Import Table Data as shown below.
Step 3: Open the web application user’s login dataset file through the window that opens up.
Step 4: Keep clicking the Next button below the popup window until you reach the window below where you need to create a New Database Table.
Step 5: In the popup window, give the table that will hold the user’s login dataset a name. In this case, I have named my table users as shown below.
Step 6: Click the Next button and then import the dataset into your PostgreSQL database. If the data is imported successfully, you will get a Success message as shown below.
Step 7: Considering a simple login form where a user enters their username and password, the query below will compare the user’s input values with the values stored in the database. If they match, the user is granted access.
1
SELECT * FROM users WHERE username = '<username>' AND password = '<password>';
However, if an attacker enters **' ' OR '1'='1'** as the username and **0 or 1=1** as the password, they can gain access to your web application without using correct login credentials.
1
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 0 or 1=1;
To confirm if this is true, open an SQL editor in DbVisualizer using the (Ctrl+T) keys and execute the query above using the (Ctrl+Enter) keys.
As a result, the query returns all rows from the users' table, granting the attacker unauthorized access.
Step 8: Using DbVisualizer, we can add variables or parameter markers to the query validating users' inputs on the login form to express that certain parts of the query should be replaced with values when the SQL is executed.
In this case, you can insert variables for the username and password as shown below to ensure that user-supplied values are treated as data and not executable code.
1
SELECT * FROM users WHERE username = '${Username}$' AND password = ${Password}$;
For that reason, if you execute the query, you will be prompted to enter a username and password.
If you try injecting SQL in the values fields, you get an error message hence denying you access if you are an attacker.
If you enter values that already exist in the users' table, you are granted access.
Parameterizing Queries In SQL For Code Reusability
Assume you have been tasked by a company with thousands of employees to add the employees’ personal information into the company’s database. That means you will need to create an Employees table in the database first and then insert each employee’s information into the table.
To avoid the tedious process of repetitively adding each employee’s information, you can parameterize the INSERT INTO query and reuse it every time you add a different employee's information by following the steps below.
Step 1: Open the SQL editor in DbVisualizer using the (Ctrl+T) keys and create a table called Employees by executing the query below.
1
CREATE TABLE Employees (
2
ID int,
3
First_Name varchar(255),
4
Last_Name varchar(255),
5
Address varchar(255),
6
City varchar(255)
7
);
Once the query is executed, you should get a success message that the Employees table was created.
Step 2: Use DbVisualizer parameter markers to parameterize a query that inserts each employee's information into the Employees table.
1
INSERT INTO employees (ID, First_Name, Last_Name, Address, City)
2
values (null, &FirstName, &LastName, &Address, &City);
Step 3: To insert an employee’s information into the Employees table, execute the query above using the (Ctrl+Enter) keys and you will be prompted to insert the values.
Step 4: Let us say one of the company’s employee name is John Doe, whose address is 119 Bedford St. Lake Jackson, TX 77566. Below is how you would enter data for parameter markers. Then click the continue button to insert the employee's information into the Employees table.
Step 5: Open the Employees table and you should see that John Doe information was added.
Conclusion
In conclusion, learning to use parameterized SQL queries is crucial for keeping your data safe and efficiently managing databases. It guards against attacks like SQL injection and allows you to reuse queries.
By following this tutorial and utilizing tools like DbVisualizer, you'll enhance security and productivity, making your database work more effectively and worry-free.
We hope that you’ve enjoyed this tutorial and that you will stick around our blog for more tutorials to come.
FAQ
What is SQL query parameterization?
SQL query parameterization is a technique for separating user input from the SQL query itself. This helps to protect against SQL injection attacks by preventing malicious code from being injected into the query.
How does SQL query parameterization work?
Instead of embedding user input directly into the SQL query string, parameterized queries use placeholders to represent the user input. When the query is executed, the user input is passed to the database separately from the query itself. The database then substitutes the user input into the placeholders, creating a secure and validated query.
Why is SQL query parameterization important?
SQL injection attacks are a severe security risk that can lead to unauthorized access to data, data corruption, and even financial fraud. Using parameterized queries can significantly reduce your risk of being affected by a SQL injection attack.
Can parameterized queries be used with all database systems?
Yes, the concept of SQL query parameterization is applicable to most relational database systems, including but not limited to MySQL, PostgreSQL, SQL Server, and SQLite.
