Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
branch: master
Fetching contributors…

Cannot retrieve contributors at this time

92 lines (71 sloc) 3.075 kB

PostgreSQL

All of PostgreSQL's procedural languages, which allow you to write functions and procedures inside the database, allow you to execute arbitrary SQL statements.

PL/pgSQL

The safest way to execute SQL inside a PL/pgSQL statement is just to do so:

CREATE OR REPLACE FUNCTION user_access (p_uname TEXT)
  RETURNS timestamp LANGUAGE plpgsql AS
$func$
BEGIN
    RETURN accessed_at FROM users WHERE username = p_uname;
END
$func$;

For such a simple case, you're actually better off writing a pure SQL function:

CREATE OR REPLACE FUNCTION user_access (p_uname TEXT)
  RETURNS timestamp LANGUAGE sql AS
$func$
    SELECT accessed_at FROM users WHERE username = $1
$func$;

But sometimes you have to do more complicated things. Perhaps you dynamically add WHERE clause expressions based on input. In such a case, you'll end up using PL/pgSQL's EXECUTE syntax. Here's an example with an SQL injection vulnerability:

CREATE OR REPLACE FUNCTION get_users(p_column TEXT, p_value TEXT)
  RETURNS SETOF users LANGUAGE plpgsql AS
$func$
DECLARE
    query TEXT := 'SELECT * FROM users';
BEGIN
    IF p_column IS NOT NULL THEN
        query := query || ' WHERE ' || p_column
              || $_$ = '$_$ || p_value || $_$'$_$;
    END IF;
    RETURN QUERY EXECUTE query;
END
$func$;

Both the p_column and the p_value arguments are vulnerable. One way to avoid this problem is to use the quote_ident() function to quote SQL identifiers (p_column in this case) and quote_lteral() and quote literal values:

CREATE OR REPLACE FUNCTION get_users(p_column TEXT, p_value TEXT)
  RETURNS SETOF users LANGUAGE plpgsql AS
$func$
DECLARE
    query TEXT := 'SELECT * FROM users';
BEGIN
    IF p_column IS NOT NULL THEN
        query := query || ' WHERE ' || quote_ident(p_column)
              || ' = ' || quote_literal(p_value);
    END IF;
    RETURN QUERY EXECUTE query;
END;
$func$;

It's quite a bit easier to read, too! Better yet, employ the USING clause of the EXECUTE command for values (available since v8.4):

CREATE OR REPLACE FUNCTION get_users(p_column TEXT, p_value TEXT)
  RETURNS SETOF users LANGUAGE plpgsql AS
$func$
DECLARE
    query TEXT := 'SELECT * FROM users';
BEGIN
    IF p_column IS NOT NULL THEN
        query := query || ' WHERE ' || quote_ident(p_column) || ' = $1';
    END IF;
    RETURN QUERY EXECUTE query
    USING p_value;
END;
$func$;

This form avoids the run-time overhead of converting values to text and back in addition to protecting against SQLi.

PL/Perl

TODO.

PL/Python

TODO.

PL/Tcl

TODO.

Jump to Line
Something went wrong with that request. Please try again.