Filter Queries

Found 1092 queries.

Page 1 of 55
  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets, it shouldn't be a problem in case of the relatively small amount of data in the system catalog of a database.

#1. Weak bcrypt cost factor in hashed passwords

INFORMATION_SCHEMA+system catalog base tables

This query checks whether stored bcrypt password hashes use a cost factor (work factor) lower than 12. It inspects text-based columns in base tables whose names suggest password storage, parses the cost parameter from sample bcrypt hashes ($2a$, $2b$, $2x$, or $2y$), and flags entries where the numeric cost is less than 12. Because the bcrypt cost factor scales exponentially (2cost iterations), an insufficiently low work factor drastically reduces the computational effort required for attackers to perform offline brute-force cracking if the database or its backups are compromised.

Problem detection License: MIT (opens in new tab)

#2. Views with WHERE but without security barrier

INFORMATION_SCHEMA+system catalog base tables

Find views that do not have the security barrier option but restrict rows in some way.

Problem detection License: MIT (opens in new tab)

#3. Views with unnecessary security invoker

INFORMATION_SCHEMA+system catalog base tables

Find views with security invoker option that do not have any underlying base table with a security policy. Security invoker option of views is possible starting from PostgreSQL 15. "The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker."

Problem detection License: MIT (opens in new tab)

#4. Views with the WITH LOCAL CHECK OPTION constraint

INFORMATION_SCHEMA only

Find updatable views that have WITH LOCAL CHECK OPTION constraint. The predicate of a view is the conjunction of the predicates of its (directly and indirectly) underlying tables (both base tables and derived tables) as well as the predicate of the view itself. In case of using WITH LOCAL CHECK OPTION constraint "New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION)." (PostgreSQL manual) Thus, use instead WITH CASCADED CHECK option to instruct the system to check new rows against the entire predicate of the view.

Problem detection License: MIT (opens in new tab)

#5. Views with security invoker

system catalog base tables only

Find views that have the security invoker option. Such option is possible starting from PostgreSQL 15. In case of using such views one cannot create a system where data is accessed through views and the users (applications) do not have direct access to the base tables.

Problem detection License: MIT (opens in new tab)

#6. Views without security barrier

INFORMATION_SCHEMA+system catalog base tables

Find views that do not have the security barrier option.

Problem detection License: MIT (opens in new tab)

#7. Very similar table names

INFORMATION_SCHEMA+system catalog base tables

This query identifies potential redundancy or ambiguity in the schema by detecting pairs of table names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters (i.e., a distance of 0 or 1). This check applies across different types of tables (base tables, foreign tables, derived tables), helping to uncover typographical errors (e.g., users vs user), inconsistent pluralization, or confusingly named entities that violate the principle of distinct and descriptive identifiers.

Problem detection License: MIT (opens in new tab)

#8. Very similar domain names

INFORMATION_SCHEMA only

This query identifies potential redundancy or ambiguity in the schema by detecting pairs of user-defined domain names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters. Crucially, the query implements a filter to exclude pairs where the divergence is attributable solely to numerical digits. This heuristic prevents false positives for valid domain variations based on size or version (e.g., d_name_20 vs. d_name_50), focusing the analysis strictly on likely typographical errors or semantic duplicates.

Problem detection License: MIT (opens in new tab)

#9. Very similar column names

INFORMATION_SCHEMA+system catalog base tables

This query performs an intra-table analysis to detect potential schema ambiguities. It identifies pairs of columns within the same table that exhibit both high textual similarity and structural equivalence. Specifically, it flags pairs where the names have a Levenshtein edit distance of exactly one and the columns share the same data type or domain. This combination suggests a high probability of typographical errors (e.g., status vs statuss), inconsistent naming (singular vs. plural), or improper denormalization (e.g., item1 vs item2), all of which undermine schema clarity.

Problem detection License: MIT (opens in new tab)

#10. Very similar (but not equal) routine names

INFORMATION_SCHEMA+system catalog base tables

This query audits the schema for semantic ambiguity by identifying pairs of routine names (functions, procedures) that exhibit high textual similarity but are not identical. It filters for name pairs with a Levenshtein edit distance of exactly one (less than two, but excluding equality). This specific filter targets typographical errors (e.g., calc_tax vs. calc_tux) or inconsistent singular/plural naming (e.g., get_user vs. get_users), while correctly ignoring valid method overloading where names are identical.

Problem detection License: MIT (opens in new tab)

#11. Validation triggers reading reference tables without FOR SHARE locking

INFORMATION_SCHEMA+system catalog base tables

This query identifies row-level BEFORE triggers that validate data by querying other tables (SELECT ... FROM ... WHERE) before raising an exception, but fail to acquire a shared lock (FOR SHARE) on the referenced rows. Under PostgreSQL's default READ COMMITTED isolation level, reading a reference or classifier row (such as checking whether a status, category, or type is active) without FOR SHARE allows concurrent transactions to deactivate or modify that reference row before the triggering transaction commits, leading to write skew and data integrity violations.

Problem detection License: MIT (opens in new tab)

#12. Validation method does not confirm

INFORMATION_SCHEMA+system catalog base tables

Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "validate" or "check").

Problem detection License: MIT (opens in new tab)

#13. Vacuum and analyze status of base tables

system catalog base tables only

Find for each base table the last time when the table has been vacuumed or analyzed (either manually or automatically).

General License: MIT (opens in new tab)

#14. Using system-defined names of constraints (constraints that involve one column)

system catalog base tables only

Find the constraint types in case of which there exists system-defined names.

Problem detection License: MIT (opens in new tab)

#15. Using system-defined names of constraints (constraints that involve more than one column)

system catalog base tables only

Find the constraint types in case of which there exists system-defined names.

Problem detection License: MIT (opens in new tab)

#16. Using routine name in front of a parameter name in a routine body to refer to the parameter of the routine

INFORMATION_SCHEMA+system catalog base tables

Write code that is easy to understand and not unnecessarily long. A routine cannot have two or more parameters with the same name. In this case using longer identifier in the form routine_name.parameter name is unnecessary.

Problem detection License: MIT (opens in new tab)

#17. Using conditionals to determine the returned value

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#18. Using BEFORE triggers to log data changes

INFORMATION_SCHEMA+system catalog base tables

Do not let the system to do extra work. Logging changes with a BEFORE trigger means extra work for rolling back the changes in case the logged data modification fails.

Problem detection License: MIT (opens in new tab)

#19. Using an internal data type - name

INFORMATION_SCHEMA only

Find base table columns that use type name that is used in system catalog tables. It is not a problem if the column is meant for recording identifiers of database objects.

General License: MIT (opens in new tab)

#20. Using AFTER triggers to enforce constraints

INFORMATION_SCHEMA+system catalog base tables

Do not let the system to do extra work. Checking a constraint with an AFTER trigger means that the trigger procedure will be executed after the data modification and if the check fails, then the system has to do extra work to roll back the changes.

Problem detection License: MIT (opens in new tab)
# Name(sorted descending, activate to sort ascending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(activate to sort ascending) Last update(activate to sort ascending) License Actions
1 Weak bcrypt cost factor in hashed passwords This query checks whether stored bcrypt password hashes use a cost factor (work factor) lower than 12. It inspects text-based columns in base tables whose names suggest password storage, parses the cost parameter from sample bcrypt hashes ($2a$, $2b$, $2x$, or $2y$), and flags entries where the numeric cost is less than 12. Because the bcrypt cost factor scales exponentially (2cost iterations), an insufficiently low work factor drastically reduces the computational effort required for attackers to perform offline brute-force cracking if the database or its backups are compromised. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
2 Views with WHERE but without security barrier Find views that do not have the security barrier option but restrict rows in some way. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
3 Views with unnecessary security invoker Find views with security invoker option that do not have any underlying base table with a security policy. Security invoker option of views is possible starting from PostgreSQL 15. "The main use case (and the one that inspired the feature) is to be able to use views and still check row-level security policies on the underlying tables as the invoker." Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
4 Views with the WITH LOCAL CHECK OPTION constraint Find updatable views that have WITH LOCAL CHECK OPTION constraint. The predicate of a view is the conjunction of the predicates of its (directly and indirectly) underlying tables (both base tables and derived tables) as well as the predicate of the view itself. In case of using WITH LOCAL CHECK OPTION constraint "New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION)." (PostgreSQL manual) Thus, use instead WITH CASCADED CHECK option to instruct the system to check new rows against the entire predicate of the view. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
5 Views with security invoker Find views that have the security invoker option. Such option is possible starting from PostgreSQL 15. In case of using such views one cannot create a system where data is accessed through views and the users (applications) do not have direct access to the base tables. Problem detection system catalog base tables only MIT (opens in new tab) View
6 Views without security barrier Find views that do not have the security barrier option. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
7 Very similar table names This query identifies potential redundancy or ambiguity in the schema by detecting pairs of table names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters (i.e., a distance of 0 or 1). This check applies across different types of tables (base tables, foreign tables, derived tables), helping to uncover typographical errors (e.g., users vs user), inconsistent pluralization, or confusingly named entities that violate the principle of distinct and descriptive identifiers. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
8 Very similar domain names This query identifies potential redundancy or ambiguity in the schema by detecting pairs of user-defined domain names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters. Crucially, the query implements a filter to exclude pairs where the divergence is attributable solely to numerical digits. This heuristic prevents false positives for valid domain variations based on size or version (e.g., d_name_20 vs. d_name_50), focusing the analysis strictly on likely typographical errors or semantic duplicates. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
9 Very similar column names This query performs an intra-table analysis to detect potential schema ambiguities. It identifies pairs of columns within the same table that exhibit both high textual similarity and structural equivalence. Specifically, it flags pairs where the names have a Levenshtein edit distance of exactly one and the columns share the same data type or domain. This combination suggests a high probability of typographical errors (e.g., status vs statuss), inconsistent naming (singular vs. plural), or improper denormalization (e.g., item1 vs item2), all of which undermine schema clarity. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
10 Very similar (but not equal) routine names This query audits the schema for semantic ambiguity by identifying pairs of routine names (functions, procedures) that exhibit high textual similarity but are not identical. It filters for name pairs with a Levenshtein edit distance of exactly one (less than two, but excluding equality). This specific filter targets typographical errors (e.g., calc_tax vs. calc_tux) or inconsistent singular/plural naming (e.g., get_user vs. get_users), while correctly ignoring valid method overloading where names are identical. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
11 Validation triggers reading reference tables without FOR SHARE locking This query identifies row-level BEFORE triggers that validate data by querying other tables (SELECT ... FROM ... WHERE) before raising an exception, but fail to acquire a shared lock (FOR SHARE) on the referenced rows. Under PostgreSQL's default READ COMMITTED isolation level, reading a reference or classifier row (such as checking whether a status, category, or type is active) without FOR SHARE allows concurrent transactions to deactivate or modify that reference row before the triggering transaction commits, leading to write skew and data integrity violations. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
12 Validation method does not confirm Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "validate" or "check"). Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
13 Vacuum and analyze status of base tables Find for each base table the last time when the table has been vacuumed or analyzed (either manually or automatically). General system catalog base tables only MIT (opens in new tab) View
14 Using system-defined names of constraints (constraints that involve one column) Find the constraint types in case of which there exists system-defined names. Problem detection system catalog base tables only MIT (opens in new tab) View
15 Using system-defined names of constraints (constraints that involve more than one column) Find the constraint types in case of which there exists system-defined names. Problem detection system catalog base tables only MIT (opens in new tab) View
16 Using routine name in front of a parameter name in a routine body to refer to the parameter of the routine Write code that is easy to understand and not unnecessarily long. A routine cannot have two or more parameters with the same name. In this case using longer identifier in the form routine_name.parameter name is unnecessary. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
17 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 MIT (opens in new tab) View
18 Using BEFORE triggers to log data changes Do not let the system to do extra work. Logging changes with a BEFORE trigger means extra work for rolling back the changes in case the logged data modification fails. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
19 Using an internal data type - name Find base table columns that use type name that is used in system catalog tables. It is not a problem if the column is meant for recording identifiers of database objects. General INFORMATION_SCHEMA only MIT (opens in new tab) View
20 Using AFTER triggers to enforce constraints Do not let the system to do extra work. Checking a constraint with an AFTER trigger means that the trigger procedure will be executed after the data modification and if the check fails, then the system has to do extra work to roll back the changes. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View