Skip to content

Procedures, Triggers & Security

Encapsulate logic with stored procedures, automate actions with triggers, and prevent SQL injection.

So far you have written queries that run once and return results. Stored procedures let you save reusable logic on the server. Triggers let you run code automatically when data changes. And SQL injection prevention is the most critical security skill every developer needs.

Definition

A stored procedure is a prepared SQL code saved and reused. A trigger is a special type of stored procedure that automatically executes when data in a table changes (INSERT, UPDATE, or DELETE).

Required step: If you do not have these tables yet, run this setup in the playground before continuing. It creates the tables used by every example in this stage.

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_name VARCHAR(100) NOT NULL,
  score DECIMAL(5,2),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students (name, email) VALUES
  ('Alice', 'alice@school.edu'),
  ('Bob', 'bob@school.edu'),
  ('Charlie', 'charlie@school.edu');

INSERT INTO enrollments (student_id, course_name, score) VALUES
  (1, 'Math', 90), (1, 'Science', 85), (2, 'Math', 78);

Stored Procedures

A stored procedure is a named set of SQL statements saved on the server. You call it by name instead of rewriting the same logic every time.

Create a simple procedure

CREATE PROCEDURE get_students()
BEGIN
  SELECT * FROM students;
END;

Creates a procedure named get_students. When called, it runs the SELECT statement inside. The BEGIN...END block contains the procedure body.

Call a procedure

CALL get_students();

Runs the stored procedure. The SELECT results are returned just like running the query directly.

Procedure with IN parameter

CREATE PROCEDURE get_students_by_course(IN course VARCHAR(100))
BEGIN
  SELECT s.name, e.score
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.course_name = course;
END;

-- Call it:
CALL get_students_by_course('Math');

IN parameter passes a value into the procedure. The caller provides the value. Here, 'Math' is passed and used in the WHERE clause.

Procedure with OUT parameter

CREATE PROCEDURE count_students(OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM students;
END;

-- Call it:
CALL count_students(@total);
SELECT @total AS student_count;

OUT parameter returns a value from the procedure. The INTO keyword stores the result in the OUT parameter. The caller reads it with @total.

Parameter types:IN vs OUT vs INOUT

IN: Passes a value into the procedure (default). The procedure can read but not modify it for the caller.

OUT: Returns a value from the procedure. The procedure writes to it, the caller reads it.

INOUT: Both directions. The caller passes a value, the procedure can modify it, and the caller sees the modified value.

Triggers

A trigger runs automatically when a specified event (INSERT, UPDATE, DELETE) occurs on a table. Use triggers for audit logging, data validation, or cascading custom logic.

Audit trigger

CREATE TABLE student_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  old_name VARCHAR(100),
  new_name VARCHAR(100),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER student_name_changed
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
  IF OLD.name != NEW.name THEN
    INSERT INTO student_audit (student_id, old_name, new_name)
    VALUES (OLD.id, OLD.name, NEW.name);
  END IF;
END;

Fires AFTER every UPDATE on students. OLD refers to the row before the update, NEW refers to the row after. If the name changed, it logs the old and new values.

BEFORE INSERT trigger

CREATE TRIGGER set_default_email
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
  IF NEW.email IS NULL THEN
    SET NEW.email = CONCAT(LOWER(NEW.name), '@school.edu');
  END IF;
END;

Fires BEFORE every INSERT. If no email is provided, it automatically generates one from the student's name. NEW refers to the row being inserted.

Drop a trigger

DROP TRIGGER IF EXISTS student_name_changed;

Removes the trigger. IF EXISTS prevents an error if the trigger does not exist.

SQL Injection

SQL injection is an attack where malicious input is inserted into a query to manipulate its behavior. It is the #1 web application vulnerability. Understanding it is essential.

Security critical:How SQL Injection Works

Imagine a login query built with string concatenation:

-- DANGEROUS: never do this
query = "SELECT * FROM users WHERE name = '" + input + "'";

If a user types admin' -- as input, the query becomes:

SELECT * FROM users WHERE name = 'admin' --';

The -- comments out the rest. The query returns the admin user without a password. The attacker is logged in as admin.

Prepared Statements

Prepared statements separate the SQL logic from the data. The database parses the query structure first, then fills in the values. Malicious input cannot change the query structure.

Prepared statement in MySQL

SET @sql = 'SELECT * FROM students WHERE name = ?';
PREPARE stmt FROM @sql;
SET @name = 'Alice';
EXECUTE stmt USING @name;
DEALLOCATE PREPARE stmt;

The ? is a placeholder. The query structure is fixed at PREPARE time. EXECUTE fills in the value safely. Even if @name contains SQL, it is treated as data, not code.

Parameterized query in application code

-- Python example (conceptual):
cursor.execute(
  "SELECT * FROM students WHERE name = %s",
  (user_input,)
)

-- The database receives:
-- Query: SELECT * FROM students WHERE name = ?
-- Params: ['Alice']

In application code, always use parameterized queries. The database driver handles escaping. Never concatenate user input into SQL strings.

Must follow:SQL Injection Prevention Rules

Never concatenate user input into SQL. Always use parameterized queries or prepared statements.

Use least-privilege database users. Even if injection occurs, the damage is limited by the user's privileges.

Validate input on the application side. Check types, lengths, and formats before sending to the database.

Keep database software updated. Newer versions patch known vulnerabilities.

What does CALL do in MySQL?

What does a BEFORE INSERT trigger do?

Why are prepared statements safer than string concatenation?

Key Takeaways

  • Stored procedures save reusable SQL logic on the server. Call them with CALL.
  • IN parameters pass values in, OUT parameters return values, INOUT does both.
  • Triggers fire automatically on INSERT/UPDATE/DELETE. Use OLD for pre-change values, NEW for post-change.
  • SQL injection attacks manipulate queries through malicious input.
  • Always use parameterized queries or prepared statements. Never concatenate user input into SQL.

Ready to test your knowledge?

Take a Quiz

Related Tutorials