> Stored procedures don't offer any protection against malicious input.

Not necessarily true. Parameterized queries generally do and most stored procedure queries should be parameterized. Add good check constraints....

Now, the protection isn't perfect but it is well beyond no protection at all and can be a good level.

> An SQL statement built up of concatenation including user input is just as bad in a stored procedure as it is directly in a program.

Yeah, and sometimes that's the only way to do things in PostgreSQL. So if you find yourself having to do this (I find it most common in CREATE statements), then heavily commend and make sure (via frequent audit) that all values from the input are properly quoted by quote_ident and quote_literal functions.

> Not necessarily true. Parameterized queries generally do and most stored procedure queries should be parameterized.

But that's not anything special about stored procs. Queries called from application code should generally be parameterized, which provides the same protection.

It is different because in SQL you can't do non-parameterized queries, and in PLPGSQL you have to specify you are executing dynamic sql. This means that since it is in the db, it is going to be parameterized by default.

So what is different is the fact that unless you use a non-SQL-derivative stored proc language, you pretty much have to put up big warning signs any time you put in place a possibly exploitable query.

Examples:

1. This is not possibly exploitable:

    CREATE OR REPLACE FUNCTION foo(in_bar int) RETURNS setof foo
    LANGUAGE PLPGSQL AS
    $$
    BEGIN
        RETURN QUERY
            SELECT * FROM foo WHERE bar_id = in_bar;
    END;
    $$;

2. This one is exploitable.

    CREATE OR REPLACE FUNCTION new_user(username text) 
    RETURNS BOOL LANGUAGE PLPGSQL AS
    $$
    BEGIN
       EXECUTE $E$CREATE USER $E$ || username;
       RETURN TRUE;
    END;
    $$;

3. This one is not exploitable.

    CREATE OR REPLACE FUNCTION new_user(username text) 
    RETURNS BOOL LANGUAGE PLPGSQL AS
    $$
    BEGIN
       EXECUTE $E$CREATE USER $E$ || quote_ident(username);
       RETURN TRUE;
    END;
    $$;

The point is that it is crystal clear in these cases whether a query is parameterized internally or not. If you don't see the combination of EXECUTE and ||, there is nothing to worry about.

Stored procedures can do some more input validation, tough, that can be useful. You can protect (from the integrity standpoint) some of the content with traditional tools like foreign keys, but procedures are more flexible.

Yes, that is why yo never do that, regardless if its in a SP or in the calling code.