| 521 | 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 | |
| 522 | 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 | |
| 523 | 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 | |
| 524 | Perhaps too many input parameters | Too many parameters (in this case four or more) could be a sign of not separating concerns and having a routine that has more than one task. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 10:57 | MIT License | |
| 525 | Consistency of comments of routines | Find user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 10:56 | MIT License | |
| 526 | 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 | |
| 527 | Flag parameters | A Boolean parameter may be used to determine what task to fulfill. In this case the routine has multiple tasks and does not satisfy the separation of concerns principle. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 10:48 | MIT License | |
| 528 | 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 | |
| 529 | 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 | |
| 530 | 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 | |
| 531 | Are there enough routines that implement database operations? | Find user-defined routines that implement database operations (comment refers to an operation) but show these only if there are at least eight such routines. Contracts of database operations are specified in the system analysis documentation. The contracts apply the idea of design by contract in the field of databases. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-03 20:07 | MIT License | |
| 532 | User-defined non-trigger routines without parameters | Find user-defined non-trigger routines with no parameters. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-03 20:05 | MIT License | |
| 533 | Non-foreign key base table columns with the same name have a different set of CHECK constraints | Find non-foreign key base table columns that have the same name but a different set of check constraints. The use of constraints should be consistent and all the necessary constraints must be enforced. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-31 20:39 | MIT License | |
| 534 | Do not leave out referential constraints (based on composite keys) | Try to find missing foreign key constraints. Find columns of base tables that are not covered by any foreign key constraint but belong to a composite key, do not have an associated sequence generator, and have a name that refers to the possibility that these are used to record some kind of codes or id's. Moreover, there must be at least one other base table that has a column with the same name. Such strategy would find missing constraints in tables that implement many-to-many relationship types but which that are not complete "islands" in terms of missing foreign key constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-31 19:21 | MIT License | |
| 535 | Too generic names (user-defined types, domains) | Find names of user-defined types and domains that contain too generic (noise) words. | Problem detection | system catalog base tables only | 2021-10-31 17:41 | MIT License | |
| 536 | Too generic names (domain constraints) | Find domain CHECK constraints that have a too generic name - for instance, the name contains word "data" ) or the name is an abbreviation of the constraint type name (for instance, "chk" or "chk1"). | Problem detection | INFORMATION_SCHEMA only | 2021-10-31 17:39 | MIT License | |
| 537 | Routine body with ordering the query result based on positional references | Find routines where the query result is sorted based on the column number in the SELECT clause. Such query is sensitive towards changing the order of columns in the SELECT clause, i.e., if one changes the order of columns in the SELECT clause, then one must change the numbers in the ORDER BY clause as well, otherwise the query will produce undesired order of rows. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-31 15:23 | MIT License | |
| 538 | User-defined routines that use md5 hash for other purposes than generating test data | Find user-defined routines that use md5 hashes for the security purposes. Nowadays such hashes can be calculated too quickly and its use should be avoided at least for hashing passwords. Exclude routines that invoke both md5 function and generate_series function and are thus probably used to generate test data. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-31 15:23 | MIT License | |
| 539 | Too few rounds in case of calculating the hash | Find user-defined routines where gen_salt function is used with the number of rounds that is smaller than 12. The number should be adjusted based on the hardware where the system resides. Password hashing should take at least 250 ms. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 17:21 | MIT License | |
| 540 | PL/pgSQL routine with plain SELECT | Find PL/pgSQL that contain a SELECT statement that is not a SELECT … INTO statement. This is not permitted in PL/pgSQL routines. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 17:07 | MIT License | |