A3:2021 | SQL Injection Mitigation (2) |

Stored Procedures

Stored procedures in SQL are a set of SQL statements stored in the database, executed as a single unit. They encapsulate and reuse code, enforce business logic, and enhance performance by reducing data exchange between the database and the application.

Safe Stored Procedure (Microsoft SQL Server)

CREATE PROCEDURE ListCustomers(@Country nvarchar(30))
AS
SELECT city, COUNT(*)
FROM customers
WHERE country LIKE @Country GROUP BY city


EXEC ListCustomers ‘USA’

Injectable Stored Procedure (Microsoft SQL Server)

CREATE PROCEDURE getUser(@lastName nvarchar(25))
AS
declare @sql nvarchar(255)
set @sql = 'SELECT * FROM users WHERE
            lastname = + @LastName + '
exec sp_executesql @sql

Stored Procedures in SQL

Stored Procedures in SQL

Stored procedures in SQL consist of one or more SQL statements stored in the database. Executable as a single unit, they encapsulate code, enforce business logic, and boost performance by minimizing data transfer between the database and the application.

Safe Stored Procedure (Microsoft SQL Server)

CREATE PROCEDURE ListCustomers(@Country nvarchar(30))
AS
SELECT city, COUNT(*)
FROM customers
WHERE country LIKE @Country GROUP BY city


EXEC ListCustomers ‘USA’

Injectable Stored Procedure (Microsoft SQL Server)

CREATE PROCEDURE getUser(@lastName nvarchar(25))
AS
declare @sql nvarchar(255)
set @sql = 'SELECT * FROM users WHERE
            lastname = + @LastName + '
exec sp_executesql @sql