Nowadays I generally write raw SQL with the Tiramisu library (shameless plug: https://github.com/stucchio/Tiramisu ) I cooked up. It's basically writing straight SQL, but with the ability to safely do (almost) string concatenation to build queries.
http://thedailywtf.com/Articles/Database-Changes-Done-Right....
(Or if you're really lucky, even the current load on the database could make it fail... i.e. transaction serialization failure due to the random row order you attempted to lock)
So it should have ended with 'you're doomed'.
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.
But that's not anything special about stored procs. Queries called from application code should generally be parameterized, which provides the same protection.
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;
$$;
CREATE OR REPLACE FUNCTION new_user(username text)
RETURNS BOOL LANGUAGE PLPGSQL AS
$$
BEGIN
EXECUTE $E$CREATE USER $E$ || username;
RETURN TRUE;
END;
$$;
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;
$$;
Let's take an example: separation of duties in accounting. You want someone to enter transactions and you want someone else to approve them, and you want to enforce this through db permissions.
You might give insert permissions to user 1 but update permissions to user 2. Except that you don't want to allow updates. You only want to allow some updates, to a few columns.
So now you grant update permissions only on some columns. Except that you don't want to allow all updates on those columns. You only want to allow updating from, say, a null value to a definite value (say, indicating who and when).
You can't do this with standard permissions, so you now create another role with permission to update these columns, and require that mere mortals go through a stored procedure to update them, which will only allow those rows to be updated which had nulls in these values.
Now, I hear you say, you could do this with a join table called 'approval' with these columns so you don't have to worry about NULLs. Great, except that NOT EXISTS() queries tend to suck and you can't use partial indexes to ensure you can always quickly look up those transactions pending approval. So in the end NULLs, stored procedures, and an intermediate role end up winning out in terms of security.