iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Stored Procedures

By · Reviewed & last updated · Part of SQL Injection Tutorial

Stored procedures used to be sold as an SQLi defence in themselves. They are not — a stored proc that builds dynamic SQL by concatenating its parameters is exactly as vulnerable as the equivalent inline query. The fix is the same: parameterise everything, including inside the proc. They DO help by centralising access, reducing surface area, and letting you grant minimal EXECUTE rights.

Vulnerable proc, fixed proc, and how to call them safely

EXAMPLE
-- ===== POSTGRES =====

-- VULNERABLE: dynamic SQL with concatenated input
CREATE OR REPLACE FUNCTION vuln_get_user(uname text)
RETURNS TABLE(id bigint, email text) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, email FROM users WHERE username = ''' || uname || '''';
END;
$$;
-- Attacker input:  alice'' OR ''1''=''1   leaks every row.

-- FIXED: parameterise even inside dynamic SQL
CREATE OR REPLACE FUNCTION get_user(uname text)
RETURNS TABLE(id bigint, email text) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, email FROM users WHERE username = $1'
    USING uname;
END;
$$;

-- EVEN BETTER: no dynamic SQL at all
CREATE OR REPLACE FUNCTION get_user_v2(uname text)
RETURNS TABLE(id bigint, email text) LANGUAGE sql AS $$
  SELECT id, email FROM users WHERE username = uname
$$;

-- Lock down access: only EXECUTE, no direct SELECT
REVOKE ALL ON users FROM app_user;
GRANT EXECUTE ON FUNCTION get_user(text), get_user_v2(text) TO app_user;

-- ===== SQL SERVER =====

-- VULNERABLE: sp_executesql with concatenation
CREATE PROCEDURE dbo.VulnGetUser @uname nvarchar(50) AS
BEGIN
  EXEC ('SELECT id, email FROM users WHERE username = ''' + @uname + '''');
END;

-- FIXED: parameterised sp_executesql
CREATE OR ALTER PROCEDURE dbo.GetUser @uname nvarchar(50) AS
BEGIN
  EXEC sp_executesql
    N'SELECT id, email FROM users WHERE username = @u',
    N'@u nvarchar(50)',
    @u = @uname;
END;

-- ===== Calling the FIXED proc from PHP (PDO) =====
$stmt = $pdo->prepare('SELECT id, email FROM get_user(:u)');
$stmt->execute(['u' => $input]);
foreach ($stmt as $row) { /* ... */ }

Why it matters

Treat \"we use stored procedures\" as orthogonal to \"we are safe from SQLi\". The questions to ask are: are the inputs bound as parameters, end-to-end? Does the app role have only EXECUTE? Is there any string concatenation building SQL anywhere — proc, ORM, or app code? Three yeses, then you are safe.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
-- Procs help if all parameters are bound. Don't EXECUTE dynamic SQL inside them.
CREATE PROCEDURE get_user (IN p_id BIGINT)
BEGIN
    SELECT id, email FROM users WHERE id = p_id;
END;
Try it Yourself »

Exercise

SQL keyword to begin a stored procedure.

CREATE get_user (IN p_id BIGINT)

Discussion

Loading…