| 561 | User-defined routines that use dynamic SQL to execute data manipulation statements | Find user-defined routines that use dynamic SQL to execute data manipulation statements (SELECT, INSERT, UPDATE, DELETE). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:39 | MIT License | |
| 562 | Routines that can be invoked with a variable number of arguments | Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:37 | MIT License | |
| 563 | SECURITY DEFINER procedures cannot end transactions | You cannot use COMMIT and ROLLBACK in a SECURITY DEFINER procedure. Procedures appeared in PostgreSQL 11. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:12 | MIT License | |
| 564 | Multiple selects from the same table in a routine | Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of selecting different fields of the same row with different SELECT INTO statements one should try to do it with one statement. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:10 | MIT License | |
| 565 | Explicit locking is probably not needed | You do not need explicit locking (LOCK TABLE or SELECT … FOR UPDATE) in case of routines that only search some data but do not modify any data and do not raise any exception. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:08 | MIT License | |
| 566 | No point to have in a procedure COMMIT without ROLLBACK or vice versa | If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:00 | MIT License | |
| 567 | User-defined routines with dynamic SQL that are potential targets of the SQL injection attack | Find routines that have at least one input parameter, use dynamic SQL but do not escape the input arguments at all. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:58 | MIT License | |
| 568 | The use of xmin hidden column in views and routines | Find the number of views and materialized views that have a column with the xid type and the number of routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:56 | MIT License | |
| 569 | PL/pgSQL routines that use a cursor | Working with sets of rows rather than processing each row separately is more effective. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:54 | MIT License | |
| 570 | Using conditionals to determine the returned value | Use SQL language instead of PL/pgSQL where possible. Instead of using an IF statement, you can check as to whether the data modification succeeded or not by using the RETURNING clause in the data modification statement. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:52 | MIT License | |
| 571 | Procedures cannot have START TRANSACTION and SAVEPOINT | You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:50 | MIT License | |
| 572 | The reference to a database operation is missing from a comment | Find user-defined routines that comment does not contain a reference to a database operation that the routine implements. In case of routines that have been created based on the contracts of database operations, one should refer to the short identifier of the operation in the comment of the routine. The operation identifier must be in this case in the form OP, but other forms could be used as well. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:39 | MIT License | |
| 573 | User-defined routines that use xmin hidden column | Find routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:30 | MIT License | |
| 574 | There is no reason to use PL/pgSQL to write table functions | Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs. You can create table functions by using SQL. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:28 | MIT License | |
| 575 | FOR UPDATE is not allowed with aggregate functions | Implement explicit locking correctly. Instead of writing, for instance, SELECT Count(*) AS cnt FROM person WHERE person_id=1 FOR UPDATE; one has to write SELECT person_id FROM person WHERE person_id=1 FOR UPDATE; | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:23 | MIT License | |
| 576 | User-defined routines with dynamic SQL | Find routines that use dynamic SQL. Make sure that dynamic SQL is indeed needed, i.e., the task cannot be solved with static SQL. Make sure that the routine is protected against attacks that use SQL injection method. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:09 | MIT License | |
| 577 | Functions that have transactional control | Find functions that contain transactional control statements (BEGIN, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT) in their body. PostgreSQL does not permit transaction control in functions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 10:50 | MIT License | |
| 578 | SECURITY INVOKER routines that access data | Find SECURITY INVOKER routines that read rows from a table, add rows to a table, update rows in a table, or delete rows from a table. Better to have for these purposes SECURITY DEFINER routines, which make it possible to give to the users privileges to only execute routines without having rights to access their underlying tables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 10:44 | MIT License | |
| 579 | A non-parameterized table function instead of a view | Find table functions that do not have any parameters. Prefer simpler and more portable solutions. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-03 21:16 | MIT License | |
| 580 | search_path should not be between quotation marks | Write security definer functions securely. Give to the DBMS correctly information about the sequence of schemas that constitute the search path. You shouldn't write search path value between quotation marks or apostrophes. Thus, instead of writing SET search_path = "public, pg_temp"; or SET search_path = 'public, pg_temp'; you should write SET search_path = public, pg_temp; | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-03 21:10 | MIT License | |