On this very useful page there is this statement...
CREATE OR REPLACE FUNCTION user_access (p_uname TEXT)
RETURNS timestamp LANGUAGE sql AS
$func$
SELECT accessed_at FROM users WHERE username = $1
$func$;
...as an explanation of how to be safe from SQL injection. I am trying to understand why i would be safe this way. What does PostgreSQL do for me, that keeps me safe? Can someone please point me in the right direction?
PostgreSQL (and about any other DBMS) will first compile the statement with the $1 in place, and only then will use the actual value provided by the user. So whatever is in the value cannot be interpreted as a part of the statement. In other words, using the parameter separates code from data.
In most cases, you don't. What you really need is a mechanism that separates code from data, i.e. the parameter. In most languages and DB connectivity libraries nowadays there is a parameterized query support present, which allows you to write queries with placeholders (such as ?) and provide data through a separate parameter.