Thursday, April 18, 2019

new release of plpgsql_check - possibility to check SQL injection issue

Yesterday I released next version of plpgsql_check.

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

Thursday, March 21, 2019

How to split string to array by individual characters?

Postgres has too many features, so sometimes is good to remind some.

Function string_to_array is well known. This function has two or three parameters. If second parameter (delimiter) is null, then input string is separated to array of characters.

postgres=# select string_to_array('Pavel Stěhule',null);
┌───────────────────────────────┐
│        string_to_array        │
╞═══════════════════════════════╡
│ {P,a,v,e,l," ",S,t,ě,h,u,l,e} │
└───────────────────────────────┘
(1 row)

Saturday, March 2, 2019

compiled dll of plpgsql 1.6 for PostgreSQL 10, 11

Adam Bartoszewicz prepared dll. Please, read a message.

Thank you, Adam

pspg is available in Fedora 29, Fedora 30 repository

If you use fresh Fedora distribution, you can install a pspg pager very simply:

dnf install pspg

after this:
export PSQL_PAGER=pspg #for Postgres 11 
export PAGER=pspg
psql dbname

Thursday, February 28, 2019

don't use SQL keywords as PLpgSQL variable names

Yesterday I had a possibility to see some strange runtime error

CREATE OR REPLACE FUNCTION public.fx()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE offset int DEFAULT 0;
BEGIN
  RETURN offset + 1;
END;
$function$

postgres=# SELECT fx();
ERROR:  query "SELECT offset + 1" returned 0 columns
CONTEXT:  PL/pgSQL function fx() line 4 at RETURN

What is problem? On first view, the RETURN returns 1 column, so error message is strange.

But any PLpgSQL expression is a SQL expression - more it is SQL SELECT statement. So SELECT OFFSET 1 really returns 0 columns.

The basic issue is on bad name of variable - it is same like SQL reserved keyword. OFFSET is unhappy word.

I wrote new check to plpgsql_check, that raises a warning in this situation.

Wednesday, January 30, 2019

plpgsql_check - new report for code coverage ratio calculation

Few months ago I integrated a profiler into plpgsql_check.

The result of prifiling is line oriented:

postgres=# select lineno, avg_time, source from plpgsql_profiler_function_tb('fx(int)');
┌────────┬──────────┬───────────────────────────────────────────────────────────────────┐
│ lineno │ avg_time │                              source                               │
╞════════╪══════════╪═══════════════════════════════════════════════════════════════════╡
│      1 │          │                                                                   │
│      2 │          │ declare result int = 0;                                           │
│      3 │    0.075 │ begin                                                             │
│      4 │    0.202 │   for i in 1..$1 loop                                             │
│      5 │    0.005 │     select result + i into result; select result + i into result; │
│      6 │          │   end loop;                                                       │
│      7 │        0 │   return result;                                                  │
│      8 │          │ end;                                                              │
└────────┴──────────┴───────────────────────────────────────────────────────────────────┘
(9 rows)

This format is well readable, but it is not practical for calculation of code coverage metrics. So this week I wrote new function, that produce
report based on commands:

        CREATE OR REPLACE FUNCTION public.fx1(a integer)
         RETURNS integer
         LANGUAGE plpgsql
1       AS $function$
2       begin
3         if a > 10 then
4           raise notice 'ahoj';
5           return -1;
6         else
7           raise notice 'nazdar';
8           return 1;
9         end if;
10      end;
11      $function$

postgres=# select stmtid, parent_stmtid, parent_note, lineno, exec_stmts, stmtname
             from plpgsql_profiler_function_statements_tb('fx1');
┌────────┬───────────────┬─────────────┬────────┬────────────┬─────────────────┐
│ stmtid │ parent_stmtid │ parent_note │ lineno │ exec_stmts │    stmtname     │
╞════════╪═══════════════╪═════════════╪════════╪════════════╪═════════════════╡
│      0 │             ∅ │ ∅           │      2 │          0 │ statement block │
│      1 │             0 │ body        │      3 │          0 │ IF              │
│      2 │             1 │ then body   │      4 │          0 │ RAISE           │
│      3 │             1 │ then body   │      5 │          0 │ RETURN          │
│      4 │             1 │ else body   │      7 │          0 │ RAISE           │
│      5 │             1 │ else body   │      8 │          0 │ RETURN          │
└────────┴───────────────┴─────────────┴────────┴────────────┴─────────────────┘
(6 rows)

Now, it should not be too difficult to calculate (by SQL) some coverage metrics.

Thursday, December 6, 2018

New release of Orafce extension

I released new mostly release of Orafce. Today it is massive package of emulation often used Oracle's API and the emulation is on maximum what is possible.

Now Orafce has good and very nice documentation written by Horikawa Tomohiro (big thanks for his work).

There are not too much news for people who use Oracle 3.6:
  • possibility to better emulate || operator for varchar2 and nvarchar2 types
  • few bugfixes
  • only PostgreSQL 9.4 and newer are supported
  • support for PostgreSQL 11, current master branch (future PostgreSQL 12) is supported too