“`html

SQL injection assaults can be averted through prepared statements by ensuring that user entries are treated as data rather than executable code. To bolster security, utilizing parameterized queries in place of dynamically creating SQL strings is always advisable. In this article, we will delve into how to avert SQL injection threats using prepared statements.

Contents Overview:

• What is SQL injection?
• What are Prepared Statements?
• In what way can Prepared Statements Prevent SQL Injection?
• Strategies for Averting SQL Injection Through Prepared Statements
  • Strategy 1: Implementing Parameterized Queries in SQL Server
  • Strategy 2: Utilizing Stored Procedures with Parameters in SQL Server
  • Strategy 3: Applying Restrictive Database Permissions in SQL Server
  • Strategy 4: Employing Secure Execution (sp_executesql) in SQL Server
• Other Methods to Mitigate SQL Injection Threats
  • Strategy 1: Utilizing Bind Variables in PL/SQL
  • Strategy 2: Implementing Prepared Statements in MySQL
• Performance Analysis of Various Methods
• Practical Examples
• Top Recommendations
• Summary

What is SQL injection?

SQL injection is a cybersecurity flaw present in web applications, where malicious actors inject harmful SQL commands into SQL statements that would traditionally run based on user input. This vulnerability enables attackers to access confidential information relayed to and from an application, which may consist of usernames, passwords, financial data, etc. In certain scenarios, attackers might also change data within the database records.

Illustration:

Introducing or deleting specific data in databases may lead to corruption of that data in a manner that renders it unrecoverable. Malicious users can exploit authentication systems to gain unauthorized access, effectively bypassing security measures.

What are Prepared Statements?

Prepared statements function as a defensive tactic against SQL injection by ensuring user inputs are recognized as data rather than executable SQL commands. These statements implement parameterized queries, avoiding dynamic construction of inquiries through the concatenation of user input.

In what way can Prepared Statements Prevent SQL Injection?

• Considering user input as data rather than executable code is helpful in thwarting SQL injection.
• The database engine interprets the input as a literal instead of being part of the SQL query.
• As the SQL query is parsed and compiled one time, it cannot be altered.

Strategies for Averting SQL Injection Through Prepared Statements

Prior to discussing the strategies for preventing SQL injection attacks through prepared statements, let’s create a user table that will serve as an example for the upcoming 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 the appearance of the table once created and populated with data.

Strategy 1: Implementing Parameterized Queries in SQL Server

In SQL Server, leveraging Placeholders (@param) rather than directly incorporating user input into SQL statements enables parameterized queries to block 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;

Illustration:

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;

Result:

“`class=”wp-block-image size-full is-resized”>

Clarification: This ensures that user data is securely fetched by utilizing sp_executesql along with parameterized queries, thus the WHERE Clause solely obtains John Doe’s record.

Technique 2: Utilizing Stored Procedure with Parameters in SQL Server

SQL injection threats can be mitigated through the implementation of a Stored Procedure to execute predefined SQL queries equipped with parameters.

Format:

CREATE PROCEDURE GetUserByUsername  
    @Username NVARCHAR(100)  
AS  
BEGIN  
    SELECT * FROM Users WHERE Username = @Username;  
END;

Instance:

GO
CREATE PROCEDURE GetUserByUsername  
    @Username NVARCHAR(100)  
AS  
BEGIN  
    SET NOCOUNT ON;  -- Enhances performance by turning off row count notifications
    SELECT UserID, Username, Email, Role, CreatedAt  
    FROM Users  
    WHERE Username = @Username;  
END;
GO
EXEC GetUserByUsername @Username = 'admin_user';

Result:

Clarification: The stored procedure GetUserByUsername fetches user details associated with username = ‘admin_user’ using the @username parameter input.

Technique 3: Applying Restricted Database Privileges in SQL Server

Users possess access strictly to the limited privileges necessary by constraining the database permissions to safeguard against unauthorized alterations to the data.

Format:

CREATE USER ReadOnlyUser FOR LOGIN ReadOnlyLogin;
GRANT SELECT ON Users TO ReadOnlyUser;
DENY INSERT, UPDATE, DELETE ON Users TO ReadOnlyUser;

Instance:

GO
-- To Establish a Read-Only View
CREATE VIEW ReadOnlyUsers AS  
SELECT UserID, Username, Email, Role, CreatedAt FROM Users;  
GO

-- To Retrieve Data from the Read-Only View (Simulating Read-Only Access)
SELECT * FROM ReadOnlyUsers;
GO

-- Attempt an Insert (This may succeed in an online SQL compiler but should fail in an authentically restricted environment)
INSERT INTO Users (Username, PasswordHash, Email, Role)  
VALUES ('hacker', 'malicious_hash', '[email protected]', 'User');  
GO
SELECT * FROM Users;
GO

Result:

Clarification: In this case, the SELECT command queries the view instead of the entire table, indicating that the user does not have the ability to alter data through this view.

Technique 4: Implementing Secure Execution (sp_executesql) in SQL Server

Within parameterized queries, sp_executesql provides a means for the safe execution of dynamic SQL, effectively preventing injection attacks.

Format:

EXEC sp_executesql  
    N'SELECT * FROM Users WHERE Username = @Username',  
    N'@Username NVARCHAR(100)',  
    @Username = 'Username';

Instance:

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;

Result:

Clarification: In this example, a parameterized SQL statement is executed via sp_executesql to shield against SQL injection. The parameterization definition (@ParamDefinition) ensures the safe conveyance of the @UserInput value into @SQLQuery, preventing user input from being concatenated within the SQL string.

Alternative Approaches for Preventing SQL Injection Attacks

Additional methods to avert SQL injection attacks encompass the utilization of prepared statements in MySQL and bind variables in PL/SQL.

Technique 1: Utilizing Bind Variables in PL/SQL 

To thwart SQL injection, bind variables (:var) should be employed in the EXECUTE IMMEDIATE Statement.

Format:

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;
/

Instance:

-- 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;
/

Result:

Clarification: The SELECT command in EXECUTE IMMEDIATE acquires the user ID for the specified username = ‘john_doe.’

Method 2: Utilizing Prepared Statements in MySQL

MySQL prepared statements allow SQL queries to be executed multiple times with varying parameters after just a single 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: In this instance, the ‘PREPARE getUser’ statement utilizes a parameter (?) for Username, sets the input value (@username = ‘guest_user’), and then executes the statement securely (EXECUTE getUser USING @username) to avert SQL injection.

Performance Evaluation of Each Approach

Method	Use Case	Advantages	Disadvantages
Utilizing Parameterized Queries	When user input needs to be executed in dynamic queries	Simple to implement	Modifications may be needed in the code in certain situations
Utilizing a Stored Procedure with Parameters	When employing parameters in repetitive queries	Restricts direct access to the table	Requires additional maintenance
Applying Database Privilege Restrictions	When limiting user access to confidential information	Effectively mitigates damage from SQL injection	Management of user roles must be handled meticulously
Implementing Secure Execution	When safely executing dynamic queries	Dynamic SQL can be executed in a secure manner	Proper parameterization is essential.

Practical Example

1. Banking System

Imagine a banking system that allows its users to log in securely while 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: In this example, the PREPARE statement uses placeholders (? ) to create a parameterized query that defends against SQL injection. The LoginID and Password are returned as output as John Doe exists in the table.

Best Practices

• Limit Database Privilege: Ensure that external users of databases only possess access to what is necessary.
• Utilize Stored Procedure: To prevent spontaneous SQL execution, save user queries within the database.
• Validate Input: Implement whitelisting (allowed characters, formats), and decline suspicious input prior to submitting it to SQL.

Conclusion

Prepared statements represent a robust way to guard against SQL injection threats. Through the use of parameterized queries, secure stored procedures, and fitting execution methods such as sp_executesql in SQL Server or PREPARE in MySQL, we can avert the malicious execution of SQL. This blog has equipped you with insights on various approaches to mitigate SQL injection risks using prepared statements.

Elevate your expertise by enrolling in the SQL Training Course today to acquire practical experience. Additionally, prepare for job interviews with our SQL interview questions, crafted by industry specialists.

How to Avoid SQL Injection with Prepared Statements? – FAQs

The article How to Prevent SQL Injection with Prepared Statements first appeared on Intellipaat Blog.