SQL injection attacks can be prevented with prepared statements by ensuring user inputs are handled as data instead of executable code. To enhance security, we can always use parameterized queries instead of dynamically constructing SQL strings. In this blog, let us explore how to prevent SQL injection attacks with prepared statements.
Table of Contents:
What is SQL injection?
SQL injection is a security vulnerability for web resources in which attackers inject their malicious SQL code into the SQL statements that user input would normally execute. Attackers can access private data that has been transmitted to and from an application. The private data may include usernames, passwords, financial records, etc. In some cases, attackers may also modify the data in database records.
Example:
Inserting or deleting certain information in databases may cause corruption of that information in a way that makes it inaccessible for recovery. Attackers can exploit login processes to give them unauthorized access, thus bypassing authentication entirely.
What are Prepared Statements?
By ensuring that user inputs are treated as data rather than executable SQL code, prepared statements act as a security measure to prevent SQL injection attacks. Prepared statements use parameterized queries instead of dynamically constructing queries through the concatenation of user input.
Master Advanced SQL Skills Today!
Enroll now and transform your future!
How Prepared Statements Can Prevent SQL Injection?
- Treating the user input as data rather than as executable code can help in preventing SQL injection.
- The database engine takes the input as literal, instead of as part of the SQL query.
- Since the SQL query is parsed and compiled once, it cannot be manipulated.
Methods to Prevent SQL Injection Using Prepared Statements
Before getting started with the methods of preventing SQL injection attacks using prepared statements, let us create a user table that can be used as an example for the following methods.
CREATE TABLE Users (
UserID INT IDENTITY(1,1) PRIMARY KEY,
Username NVARCHAR(100) NOT NULL UNIQUE,
PasswordHash NVARCHAR(255) NOT NULL,
Email NVARCHAR(255) NOT NULL UNIQUE,
Role NVARCHAR(50) CHECK (Role IN ('Admin', 'User', 'Guest')),
CreatedAt DATETIME DEFAULT GETDATE()
);
INSERT INTO Users (Username, PasswordHash, Email, Role)
VALUES
('admin_user', 'hashed_password_123', '[email protected]', 'Admin'),
('john_doe', 'hashed_password_456', '[email protected]', 'User'),
('jane_doe', 'hashed_password_789', '[email protected]', 'User'),
('guest_user', 'hashed_password_000', '[email protected]', 'Guest');
Select * from users;
This is how the table looks once it is created and inserted with the values.
Method 1: Using Parameterized Queries in SQL Server
In SQL Server, by using Placeholders (@param) instead of directly injecting user input into SQL Statements, parameterized queries can prevent SQL injection.
Syntax:
DECLARE @sql NVARCHAR(MAX);
DECLARE @username NVARCHAR(100);
SET @sql = N'SELECT * FROM Users WHERE Username = @UserName';
EXEC sp_executesql @sql, N'@UserName NVARCHAR(100)', @username;
Example:
DECLARE @sql NVARCHAR(MAX);
DECLARE @paramDefinition NVARCHAR(100);
DECLARE @Username NVARCHAR(100) = 'john_doe';
SET @sql = N'SELECT Username,Role FROM Users WHERE Username = @UserName';
SET @paramDefinition = N'@UserName NVARCHAR(100)';
EXEC sp_executesql @sql, @paramDefinition, @Username;
Output:
Explanation: Here, it guarantees that user information is safely retrieved by using sp_executesql with parameterized queries, so the WHERE Clause only retrieves John Doe’s record.
Method 2: Using Stored Procedure with Parameters in SQL Server
SQL injection can be avoided by using a Stored Procedure to run pre-defined SQL queries with parameters.
Syntax:
CREATE PROCEDURE GetUserByUsername
@Username NVARCHAR(100)
AS
BEGIN
SELECT * FROM Users WHERE Username = @Username;
END;
Example:
GO
CREATE PROCEDURE GetUserByUsername
@Username NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON; -- Improves performance by disabling row count messages
SELECT UserID, Username, Email, Role, CreatedAt
FROM Users
WHERE Username = @Username;
END;
GO
EXEC GetUserByUsername @Username = 'admin_user';
Output:
Explanation: Here, the stored procedure GetUserByUsername retrieves user details with username = ‘admin_user’ with the help of the @username input.
Get 100% Hike!
Master Most in Demand Skills Now!
Method 3: Using Restrict Database Privilege in SQL Server
The user has access only to the restricted privileges they need by limiting the database privileges to help prevent unauthorized changes to the data.
Syntax:
CREATE USER ReadOnlyUser FOR LOGIN ReadOnlyLogin;
GRANT SELECT ON Users TO ReadOnlyUser;
DENY INSERT, UPDATE, DELETE ON Users TO ReadOnlyUser;
Example:
GO
-- To Create a Read-Only View
CREATE VIEW ReadOnlyUsers AS
SELECT UserID, Username, Email, Role, CreatedAt FROM Users;
GO
-- To Query the Read-Only View (Simulating Read-Only Access)
SELECT * FROM ReadOnlyUsers;
GO
-- Try to attempt an Insert (This will work in an online SQL compiler but should fail in a real restricted setup)
INSERT INTO Users (Username, PasswordHash, Email, Role)
VALUES ('hacker', 'malicious_hash', '[email protected]', 'User');
GO
SELECT * FROM Users;
GO
Output:
Explanation: Here, the SELECT statement queries the view instead of the full table, which means that the user cannot modify data through this view.
Method 4: Using Secure Execution (sp_executesql) in SQL Server
Within parameterized queries, sp_executesql allows the safe execution of dynamic SQL by preventing injection.
Syntax:
EXEC sp_executesql
N'SELECT * FROM Users WHERE Username = @Username',
N'@Username NVARCHAR(100)',
@Username = 'Username';
Example:
DECLARE @UserInput NVARCHAR(100) = 'guest_user';
DECLARE @SQLQuery NVARCHAR(MAX);
DECLARE @ParamDefinition NVARCHAR(MAX);
SET @SQLQuery = N'SELECT UserID,Username FROM Users WHERE Username = @Username';
SET @ParamDefinition = N'@Username NVARCHAR(100)';
EXEC sp_executesql @SQLQuery, @ParamDefinition, @Username = @UserInput;
Output:
Explanation: Here, a parameterized SQL statement is executed by the sp_executesql to protect from SQL injection. The parameterization definition (@ParamDefinition) is used to safely pass the @UserInput value into @SQLQuery, preventing user input from being concatenated into the SQL string.
Alternative Methods of preventing SQL Injection attacks
Other ways to avoid SQL injection attacks include using prepared statements in MySQL and bind variables in PL/SQL.
Method 1: Using Bind Variables in PL/SQL
To prevent SQL injection, bind variables (:var) can be used in the EXECUTE IMMEDIATE Statement.
Syntax:
DECLARE
variable_name DATA_TYPE;
BEGIN
variable_name := 'some_value';
EXECUTE IMMEDIATE 'SQL_QUERY_WITH_PLACEHOLDER'
INTO target_variable
USING variable_name;
DBMS_OUTPUT.PUT_LINE('Result: ' || target_variable);
END;
/
Example:
-- Create a Table in Oracle
CREATE TABLE Users (
UserID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Username NVARCHAR2(100) NOT NULL UNIQUE,
PasswordHash NVARCHAR2(255) NOT NULL,
Email NVARCHAR2(255) NOT NULL UNIQUE,
Role NVARCHAR2(50) CHECK (Role IN ('Admin', 'User', 'Guest')),
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO Users (Username, PasswordHash, Email, Role)
VALUES
('admin_user', 'hashed_password_123', '[email protected]', 'Admin'),
('john_doe', 'hashed_password_456', '[email protected]', 'User'),
('jane_doe', 'hashed_password_789', '[email protected]', 'User'),
('guest_user', 'hashed_password_000', '[email protected]', 'Guest');
COMMIT;
SET SERVEROUTPUT ON
DECLARE
v_username VARCHAR2(100);
v_userid NUMBER;
BEGIN
v_username := 'john_doe';
EXECUTE IMMEDIATE 'SELECT UserID FROM Users WHERE Username = :1'
INTO v_userid
USING v_username;
DBMS_OUTPUT.PUT_LINE('UserID: ' || v_userid);
END;
/
Output:
Explanation: Here, the SELECT statement in EXECUTE IMMEDIATE retrieves the user ID with username = ‘john_doe.’
Method 2: Using Prepared Statements in MySQL
MySQL prepared statements enable SQL queries to run repeatedly with different parameters after only one compilation.
Syntax:
PREPARE stmt_name FROM 'SQL_QUERY_WITH_PLACEHOLDERS';
SET @variable_name = 'value';
EXECUTE stmt_name USING @variable_name;
DEALLOCATE PREPARE stmt_name;
Example:
CREATE TABLE Users (
UserID INT AUTO_INCREMENT PRIMARY KEY,
Username VARCHAR(100) NOT NULL UNIQUE,
PasswordHash VARCHAR(255) NOT NULL,
Email VARCHAR(255) NOT NULL UNIQUE,
Role ENUM('Admin', 'User', 'Guest') NOT NULL,
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO Users (Username, PasswordHash, Email, Role)
VALUES
('admin_user', 'hashed_password_123', '[email protected]', 'Admin'),
('john_doe', 'hashed_password_456', '[email protected]', 'User'),
('jane_doe', 'hashed_password_789', '[email protected]', 'User'),
('guest_user', 'hashed_password_000', '[email protected]', 'Guest');
PREPARE getUser FROM 'SELECT UserID FROM Users WHERE Username = ?';
SET @username = 'guest_user';
EXECUTE getUser USING @username;
DEALLOCATE PREPARE getUser;
Output:
Explanation: Here, the ‘PREPARE getUser’ with a placeholder (?) for Username, sets the input value (@username = ‘guest_user’), then executes the statement safely (EXECUTE getUser USING @username) so that SQL injection is prevented.
| Method |
Use Case |
Pros |
Cons |
| Using Parameterized Queries |
When user input has to be executed in dynamic queries |
Easy to implement |
Changes have to be made in the code in a few cases |
| Using a Stored Procedure with Parameters |
When using parameters in repetitive queries |
It prevents access to the table directly |
Extra maintenance is required |
| Using Restrict Database Privilege |
When restricting user access to private information |
Reduces the damage from SQL injection efficiently |
User role management has to be carefully handled |
| Using secure execution |
When safely running dynamic queries |
Dynamic SQL can be executed safely |
Proper parameterization is needed. |
Real-world Example
1. Banking System
Consider a banking system that allows its users to log in securely by preventing SQL injection in MySQL.
Example:
CREATE TABLE AccountHolders (
AccountID INT AUTO_INCREMENT PRIMARY KEY,
FullName VARCHAR(150) NOT NULL,
LoginID VARCHAR(100) NOT NULL UNIQUE,
PasswordHash VARCHAR(255) NOT NULL,
ContactEmail VARCHAR(255) NOT NULL UNIQUE,
UserType ENUM('Admin', 'Customer') NOT NULL,
RegisteredAt DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO AccountHolders (FullName, LoginID, PasswordHash, ContactEmail, UserType)
VALUES
('Admin One', 'admin_01', 'hashed_password_123', '[email protected]', 'Admin'),
('John Doe', 'john_d_92', 'hashed_password_456', '[email protected]', 'Customer'),
('Jane Smith', 'jane_s_88', 'hashed_password_789', '[email protected]', 'Customer');
PREPARE secureLogin FROM 'SELECT AccountID, UserType FROM AccountHolders WHERE LoginID = ? AND PasswordHash = ?';
SET @loginID = 'john_d_92';
SET @password = 'hashed_password_456';
EXECUTE secureLogin USING @loginID, @password;
DEALLOCATE PREPARE secureLogin;
Output:
Explanation: Here, the PREPARE statement uses placeholders (? ) to create a parameterized query that protects against SQL injection. This LoginID and Password are returned as output since John Doe is in the table.
Best Practices
- Restrict Database Privilege: Make sure outside people who use databases only have access to what they need.
- Use Stored Procedure: To stop ad-hoc SQL execution, store customer queries in the database.
- Cross-verify the input: Use whitelisting (allowed characters, formats), and reject suspicious input before passing it to SQL.
Start Your SQL Adventure!
Start Your SQL Journey for Free Today
Conclusion
Prepared statements are a powerful way to defend against SQL injection attacks. By using parameterized queries, secure stored procedures, and appropriate execution methods such as sp_executesql in SQL Server or PREPARE in MySQL, we can avoid malicious execution of SQL. In this blog, you have gained knowledge on different methods to avoid SQL injection attacks with prepared statements.
Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with our SQL interview questions, prepared by industry experts.
How to Prevent SQL Injection with Prepared Statements? – FAQs
Q1. How do Prepared Statements prevent SQL injection?
A prepared statement is a parameterized and reusable SQL query that forces the developer to write the SQL command and the user-provided data separately, which prevents SQL injection.
Q2. What has to be used to prevent an SQL injection attack?
Developers can prevent SQL injection by using parameterized queries and stored methods, and execute them securely using sp_executesql in SQL Server or PREPARE in MySQL to prevent the perpetration of malicious SQL code.
Q3. Does a Stored Procedure help in preventing SQL injection?
The stored Procedure is one of the efficient methods when used along with parameterized queries.
Q4. Which is the preferred method for preventing SQL injection?
Input validation and parameterized queries are the preferred methods for preventing SQL injection.
Q5. What is the use of prepared statements?
A prepared statement allows the efficient execution of the same (or similar) SQL statements multiple times.