Stored Procedures
A Stored Procedure is a prepared SQL code that you can save, so the code can be reused over and over again. It is a subroutine available to applications that access a relational database management system (RDBMS).
1. The Core Concept
Instead of writing complex SQL queries inside your application code (e.g., Java, Python, Node.js) and sending them to the database every time, you define the logic inside the database itself.
- Precompiled: The database parses, compiles, and optimizes the query plan once and stores it. Subsequent calls are faster.
- Parameters: Procedures can accept input parameters and return output parameters.
2. Example (SQL)
Creating a procedure to get all customers from a specific city:
CREATE PROCEDURE GetCustomersByCity (IN cityName VARCHAR(50))
BEGIN
SELECT *
FROM Customers
WHERE City = cityName;
END;Calling the procedure:
CALL GetCustomersByCity('London');3. Benefits
- Performance: Since the SQL is precompiled, the execution is often faster than sending raw SQL strings.
- Reduced Network Traffic: Instead of sending a 50-line SQL query over the network, you send a short command (
CALL MyProc). - Security: You can grant users permission to execute a procedure without granting them permission to access the underlying tables directly. This prevents SQL Injection if implemented correctly (using parameters).
- Centralized Logic: Business logic involving data is kept in one place (the DB). If the logic changes, you update the procedure, not 10 different microservices.
4. Drawbacks
- Vendor Lock-in: Stored procedure syntax (PL/SQL for Oracle, T-SQL for SQL Server, PL/pgSQL for Postgres) varies significantly between databases. Moving from Oracle to Postgres is painful.
- Debugging: Debugging stored procedures is generally harder than debugging application code.
- Version Control: It is harder to track changes to stored procedures in Git compared to application code files.
- Resource Usage: Puts more CPU/Memory load on the database server (which is harder to scale) rather than the application server (which is easier to scale).
5. SQL Injection and Stored Procedures
Stored procedures are often cited as a defense against SQL Injection, but they are not a silver bullet.
- Safe: When using parameters (as in the example above), the database treats the input strictly as data, not executable code. This effectively neutralizes injection attacks.
- Unsafe (Dynamic SQL): If you concatenate strings inside the stored procedure to build a query dynamically, you are still vulnerable.
Vulnerable Example (Dynamic SQL)
CREATE PROCEDURE UnsafeSearch (IN nameInput VARCHAR(50))
BEGIN
-- Concatenating input directly into the query string
SET @s = CONCAT('SELECT * FROM Users WHERE name = "', nameInput, '"');
PREPARE stmt FROM @s;
EXECUTE stmt;
END;If nameInput is "; DROP TABLE Users; --, the procedure executes the drop command. Always use parameterized queries, even inside stored procedures.
programming/database-basics programming/database-views programming/database-transactions