Security Auditing for Stored Procedures

In this lesson, you will learn how to identify and remediate SQL injection vulnerabilities within PostgreSQL stored procedures.

When we write stored procedures, it is tempting to use dynamic SQL to build queries on the fly. However, if that logic incorporates unsanitized user input, you open the door to SQL injection. Because procedures often run with elevated privileges (like SECURITY DEFINER), an attacker can manipulate your code to bypass standard access controls or exfiltrate data from tables they shouldn't see.

Identifying the Vulnerability

Consider this insecure example where a procedure takes a username as an argument:

CREATE OR REPLACE PROCEDURE delete_user(user_name TEXT)
LANGUAGE plpgsql AS $$
BEGIN
    -- VULNERABLE: Direct string concatenation
    EXECUTE 'DELETE FROM users WHERE username = ''' || user_name || '''';
END;
$$;

If a user passes ' OR '1'='1 as the user_name, the executed query becomes DELETE FROM users WHERE username = '' OR '1'='1'. This results in the entire users table being wiped out.

Auditing and Remediation

To audit your code, search for any EXECUTE statements that concatenate variables directly into a command string. To fix this, you must use parameterized queries. PostgreSQL provides the USING clause to safely inject values into dynamic strings without treating them as executable code.

Here is the secure version of the same procedure:

CREATE OR REPLACE PROCEDURE delete_user(user_name TEXT)
LANGUAGE plpgsql AS $$
BEGIN
    -- SECURE: Using the USING clause to parameterize inputs
    EXECUTE 'DELETE FROM users WHERE username = $1' USING user_name;
END;
$$;

When you use the USING clause, the database driver ensures the content of the user_name variable is treated strictly as a literal value, not as part of the SQL command structure. By auditing your EXECUTE statements and replacing string concatenation with parameter binding, you neutralize the risk of injection, even when your procedures run with higher security contexts.

Key Takeaway: Always use the USING clause in PostgreSQL dynamic SQL to pass variables, as direct string concatenation in stored procedures is a primary vector for SQL injection attacks.