With this release a developer can check some well known patterns of SQL injection vulnerabilities. The code of stored procedures of native languages like PL/SQL, T-SQL or PL/pgSQL is secure, and there is not a risk of SQL injection until dynamic SQL is used (the
EXECUTE
command in PL/pgSQL). The safe programming requires sanitization of all string variables. Anybody can use functions: quote_literal
, quote_ident
or format
. This check can be slow, so it should be enabled by setting security_warnings
parameter:CREATE OR REPLACE FUNCTION public.foo1(a text) RETURNS text LANGUAGE plpgsql AS $function$ DECLARE result text; BEGIN -- secure EXECUTE 'SELECT $1' INTO result USING a; -- secure EXECUTE 'SELECT ' || quote_literal(a) INTO result; -- secure EXECUTE format('SELECT %L', a) INTO result; -- unsecure EXECUTE 'SELECT ''' || a || '''' INTO result; -- unsecure EXECUTE format(e'SELECT \'%s\'', a) INTO result; RETURN result; END; $function$ postgres=# select * from plpgsql_check_function('foo1'); ┌────────────────────────┐ │ plpgsql_check_function │ ╞════════════════════════╡ └────────────────────────┘ (0 rows) postgres=# select * from plpgsql_check_function('foo1', security_warnings => true); ┌─────────────────────────────────────────────────────────────────────────────┐ │ plpgsql_check_function │ ╞═════════════════════════════════════════════════════════════════════════════╡ │ security:00000:11:EXECUTE:text type variable is not sanitized │ │ Query: SELECT 'SELECT ''' || a || '''' │ │ -- ^ │ │ Detail: The EXECUTE expression is SQL injection vulnerable. │ │ Hint: Use quote_ident, quote_literal or format function to secure variable. │ │ security:00000:13:EXECUTE:text type variable is not sanitized │ │ Query: SELECT format(e'SELECT \'%s\'', a) │ │ -- ^ │ │ Detail: The EXECUTE expression is SQL injection vulnerable. │ │ Hint: Use quote_ident, quote_literal or format function to secure variable. │ └─────────────────────────────────────────────────────────────────────────────┘ (10 rows)
Thanks to Adam Bartoszewicz's work, this extension compiled for MS Windows is available https://groups.google.com/d/msg/postgresql-extensions-hacking/U_ZJACaDz60/BYagJi9iDAAJ
No comments:
Post a Comment