PostgreSQL
Alle prozeduralen Sprachen von PostgreSQL, mit denen man Funktionen und Prozeduren innerhalb der Datenbank schreiben kann, haben die Fähigkeit, beliebige SQL-Anweisungen auszuführen.
PL/pgSQL
Die sicherste Weise, SQL innerhalb einer PL/pgSQL-Anweisung auszuführen:
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$;
In so einem einfachen Fall schreibt man besser eine reine SQL-Funktion:
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
In Vorbereitung.
PL/Python
In Vorbereitung.
PL/Tcl
In Vorbereitung.
