Filter Queries
Found 14 queries.
- 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. At most one row is permitted in a table (based on check constraints)
INFORMATION_SCHEMA+system catalog base tablesFind base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.
#2. At most one row is permitted in a table (based on enumeration types)
INFORMATION_SCHEMA+system catalog base tablesFind base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.
#3. Base tables with plenty of data
system catalog base tables onlyFind base tables that have 1000 rows or more.
#4. Base tables with the biggest number of rows
system catalog base tables onlyFind the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables.
#5. Columns with only one value
INFORMATION_SCHEMA+system catalog base tablesFind base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing.
#6. Do not assume you must use files (based on user data)
INFORMATION_SCHEMA+system catalog base tablesFind cases where you store images and other media as files outside the database and store in the database only paths to the files.
#7. Do not format comma-separated lists (based on user data)
INFORMATION_SCHEMA+system catalog base tablesFind, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters.
#8. Empty columns
INFORMATION_SCHEMA+system catalog base tablesFind columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all.
#9. Empty tables
system catalog base tables onlyFind base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table.
#10. Foreign key column has a default value that is not present in the parent table
INFORMATION_SCHEMA+system catalog base tablesFind foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints.
#11. Number of rows in base tables
system catalog base tables onlyFind the number of rows in base tables.
#12. Password should not be open text
INFORMATION_SCHEMA+system catalog base tablesFind base table columns that name refers to the possibility that these are used to register passwords. Find the columns that have a CHECK constraint that seems to determine the minimal permitted length of the values in the column. Passwords in a database table must be hashed and salted. Checking the strength of the password by using a check constraint is in this case impossible and the check constraints that try to do it should be removed from the database.
#13. Plaintext password storage audit in base tables
INFORMATION_SCHEMA+system catalog base tablesThis query identifies base table columns whose names suggest they store user passwords (such as password, pwd, or parool) and retrieves a sample value from each flagged column for security inspection. Storing passwords in plaintext—or using weak, unsalted hashes like raw MD5—is a critical security vulnerability: if the database or its backups are compromised, attackers immediately gain unrestricted access to user accounts. Inspecting a sample value allows auditors to verify that stored credentials follow a strong, salted, slow cryptographic hash format (such as bcrypt or Argon2, recognizable by prefixes like $2a$, $2b$, or $argon2id$). Because this query returns actual stored values, it should be executed strictly within secure, access-controlled auditing environments.
#14. Weak bcrypt cost factor in hashed passwords
INFORMATION_SCHEMA+system catalog base tablesThis 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.
| # | Name(sorted ascending, activate to sort descending) | 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 | At most one row is permitted in a table (based on check constraints) | Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 2 | At most one row is permitted in a table (based on enumeration types) | Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 3 | Base tables with plenty of data | Find base tables that have 1000 rows or more. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 4 | Base tables with the biggest number of rows | Find the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 5 | Columns with only one value | Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 6 | Do not assume you must use files (based on user data) | Find cases where you store images and other media as files outside the database and store in the database only paths to the files. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 7 | Do not format comma-separated lists (based on user data) | Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 8 | Empty columns | Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 9 | Empty tables | Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. | Problem detection | system catalog base tables only | MIT (opens in new tab) | View | |
| 10 | Foreign key column has a default value that is not present in the parent table | Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 11 | Number of rows in base tables | Find the number of rows in base tables. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 12 | Password should not be open text | Find base table columns that name refers to the possibility that these are used to register passwords. Find the columns that have a CHECK constraint that seems to determine the minimal permitted length of the values in the column. Passwords in a database table must be hashed and salted. Checking the strength of the password by using a check constraint is in this case impossible and the check constraints that try to do it should be removed from the database. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 13 | Plaintext password storage audit in base tables | This query identifies base table columns whose names suggest they store user passwords (such as password, pwd, or parool) and retrieves a sample value from each flagged column for security inspection. Storing passwords in plaintext—or using weak, unsalted hashes like raw MD5—is a critical security vulnerability: if the database or its backups are compromised, attackers immediately gain unrestricted access to user accounts. Inspecting a sample value allows auditors to verify that stored credentials follow a strong, salted, slow cryptographic hash format (such as bcrypt or Argon2, recognizable by prefixes like $2a$, $2b$, or $argon2id$). Because this query returns actual stored values, it should be executed strictly within secure, access-controlled auditing environments. |
General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 14 | 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 |