| 581 |
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 |
View |
| 582 |
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 |
View |
| 583 |
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 |
View |
| 584 |
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 |
View |
| 585 |
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 |
View |
| 586 |
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 |
View |
| 587 |
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 |
View |
| 588 |
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 |
View |
| 589 |
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 |
View |
| 590 |
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 |
View |
| 591 |
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 |
View |
| 592 |
User-defined routines that implement UPSERT operation |
Find user-defioned routines that implement UPSERT operation. Make sure that it is consistent with the contracts of database operations. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 17:02 |
MIT License |
View |
| 593 |
Sorting rows based on random values in routines without limiting rows |
Find routines that contain a statement that sorts rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:57 |
MIT License |
View |
| 594 |
Sorting rows based on random values in routines |
Find routines that contain a statement that sorts rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:56 |
MIT License |
View |
| 595 |
Case insensitive search |
Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern). |
General |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:55 |
MIT License |
View |
| 596 |
Incorrect reference to a system-defined function in the routine body |
Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:48 |
MIT License |
View |
| 597 |
Perhaps Count(*) is wrongly used |
Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes Count aggregate function like this - Count(*), uses outer join, and grouping. In case of grouping you do not want to get an answer that an empty group contains one member. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:40 |
MIT License |
View |
| 598 |
User-defined routines that produce a temporary table |
Find user user-defined routines that produce a temporary table |
General |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:25 |
MIT License |
View |
| 599 |
Do not create user-defined routines that have the same name as some installed extension routine |
Avoid creating user-defined routines that have the same name as some extension routine because it may cause confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:14 |
MIT License |
View |
| 600 |
Overloading may cause runtime error |
Routines in the same schema that have the same name and that have parameters with different types or different number of parameters are not considered to be in conflict at the creation time. However, if defaults are provided in the definition of parameters, then these routines might be conflict during runtime. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:11 |
MIT License |
View |