Skip to content

Latest commit

 

History

History
153 lines (103 loc) · 9.85 KB

mitigations.md

File metadata and controls

153 lines (103 loc) · 9.85 KB

Mitigation Strategies Analysis for jetbrains/exposed

Mitigation Strategy: Prioritize Exposed DSL over Raw SQL

Mitigation Strategy: Use Exposed's type-safe DSL for all database interactions whenever possible.

Description:

  1. Identify all existing database interactions: Review the codebase to find all instances where database queries are made.
  2. Convert raw SQL to DSL: For each instance of raw SQL, rewrite the query using Exposed's DSL functions (e.g., select, insert, update, delete, where, etc.).
  3. Test thoroughly: After each conversion, rigorously test the functionality to ensure it behaves identically to the original raw SQL query. Include unit and integration tests.
  4. Code Reviews: Mandate code reviews for all database interaction code, with a specific focus on ensuring the DSL is used correctly and no raw SQL is introduced without explicit justification and review.
  5. Establish Coding Standards: Create and enforce coding standards that require the use of the DSL and prohibit raw SQL except in exceptional, documented cases.
  6. Regular Audits: Periodically audit the codebase to identify any instances of raw SQL that may have slipped through.

Threats Mitigated:

  • SQL Injection (Severity: Critical): The primary threat. The DSL generates parameterized queries, preventing attackers from injecting malicious SQL code.
  • Data Type Mismatches (Severity: Medium): The DSL's type safety helps prevent errors caused by passing incorrect data types to the database.

Impact:

  • SQL Injection: Risk reduced from Critical to Very Low (assuming proper DSL usage and no raw SQL).
  • Data Type Mismatches: Risk reduced from Medium to Low.

Currently Implemented:

  • Implemented in the User and Product modules (e.g., src/main/kotlin/com/example/models/User.kt, src/main/kotlin/com/example/services/ProductService.kt). All database interactions in these modules use the Exposed DSL.

Missing Implementation:

  • The Reporting module (src/main/kotlin/com/example/reporting/ReportGenerator.kt) still uses some raw SQL for complex report generation. This needs to be refactored to use the DSL or, if absolutely necessary, carefully parameterized raw SQL with thorough validation.

Mitigation Strategy: Safe Handling of Raw SQL (When Unavoidable) with exec()

Mitigation Strategy: If raw SQL is absolutely necessary, use Exposed's exec() function with parameterized queries.

Description:

  1. Justification: Document why raw SQL is required. Explain why the DSL cannot be used.
  2. Parameterized Queries: Use Exposed's exec() function with placeholders (?) for all user-provided data. Pass the data as separate parameters to the exec() function within the lambda provided to exec. This is crucial for Exposed to handle the parameterization correctly.
  3. Code Review: Subject all raw SQL code to rigorous code review by a security expert.
  4. Testing: Create specific test cases to attempt SQL injection attacks against the raw SQL, even with parameterization, to ensure its robustness.

Threats Mitigated:

  • SQL Injection (Severity: Critical): Even with raw SQL, parameterization using exec() significantly reduces the risk.

Impact:

  • SQL Injection: Risk reduced from Critical to Low (assuming proper parameterization).

Currently Implemented:

  • Partially implemented in the Admin module (src/main/kotlin/com/example/admin/DatabaseUtils.kt), where a helper function for parameterized raw SQL execution using exec() exists.

Missing Implementation:

  • The Reporting module (mentioned above) uses raw SQL without consistent parameterization using exec(). This is a high-priority area for remediation.

Mitigation Strategy: Secure like(), regexp(), and Similar DSL Functions

Mitigation Strategy: Properly handle user input within Exposed's like() and regexp() DSL functions.

Description:

  1. like() Escaping: Create a utility function to escape special characters (% and _) in user input used with Exposed's like() function. Apply this function consistently before passing the input to like().
  2. Code Review: Pay close attention to the use of like() and regexp() in code reviews, ensuring that user input is properly handled.

Threats Mitigated:

  • SQL Injection (Severity: Medium): Improperly escaped like() patterns can lead to injection, even within the DSL.

Impact:

  • SQL Injection: Risk reduced from Medium to Low.

Currently Implemented:

  • No specific implementation for escaping like() patterns.

Missing Implementation:

  • like() escaping needs to be implemented and consistently applied to all uses of the like() function within the Exposed DSL.

Mitigation Strategy: Prevent Dynamic Table/Column Names within the DSL

Mitigation Strategy: Never use user input to construct table or column names, even within Exposed's DSL.

Description:

  1. Hardcode Table/Column Names: Table and column names should be hardcoded as references to Exposed Table objects and their Column properties.
  2. Whitelist (If Necessary): If dynamic selection is absolutely unavoidable, create an enum or a strictly controlled list of allowed table/column objects (not strings). Validate user input against this whitelist and use the corresponding object in your Exposed query. Do not construct strings.
  3. Code Review: Flag any code that dynamically constructs table or column names based on user input, even if it appears to be within the DSL.

Threats Mitigated:

  • SQL Injection (Severity: Critical): Dynamic table/column names are a major injection vector.

Impact:

  • SQL Injection: Risk reduced from Critical to Very Low (if whitelisting is properly implemented using object references).

Currently Implemented:

  • Generally followed throughout the project. Table and column names are mostly hardcoded as object references.

Missing Implementation:

  • One instance identified in a legacy reporting function (src/main/kotlin/com/example/reporting/LegacyReport.kt) where a column name is partially constructed from user input. This needs to be refactored.

Mitigation Strategy: Explicit Column Selection with select

Mitigation Strategy: Always explicitly list the required columns using Exposed's select function.

Description:

  1. Avoid selectAll(): Use select(column1, column2, ...) instead of selectAll() or omitting the select call entirely. Explicitly name the Column objects you need.
  2. Code Review: Ensure that all select statements explicitly list the required columns.

Threats Mitigated:

  • Information Leakage (Severity: Medium): Prevents accidental exposure of sensitive data or internal database structure by only retrieving necessary columns.

Impact:

  • Information Leakage: Risk reduced from Medium to Low.

Currently Implemented:

  • Mostly implemented. Most queries use select with explicit column names.

Missing Implementation:

  • Some older queries might be missing explicit select calls, potentially retrieving all columns.

Mitigation Strategy: Eager Loading with with()

Mitigation Strategy: Use Exposed's with() function for eager loading of related entities.

Description:

  1. Identify N+1 Queries: Use profiling tools or database monitoring to identify instances of the N+1 query problem, where fetching a list of entities results in separate queries for each related entity.
  2. Use with(): When fetching entities with relationships, use the with(relatedTable) function within your select or other query building functions to eagerly load related entities in a single query.

Threats Mitigated:

  • Denial of Service (DoS) (Severity: Medium): The N+1 problem can make the application vulnerable to DoS attacks by overwhelming the database.

Impact:

  • Denial of Service (DoS): Risk reduced from Medium to Low.

Currently Implemented:

  • with() is used in some parts of the codebase, but not consistently.

Missing Implementation:

  • A comprehensive review of database interactions is needed to identify and fix all N+1 query problems by applying with() appropriately.

Mitigation Strategy: Correct Transaction Management with transaction Blocks

Mitigation Strategy: Use Exposed's transaction blocks correctly.

Description:

  1. transaction Blocks: Wrap all database operations within transaction { ... } blocks. This ensures that operations are atomic and that connections are managed correctly.
  2. Nested Transactions: If nested transactions are needed, use TransactionManager.manager.newTransaction() to create a new, independent transaction within the outer transaction block.

Threats Mitigated:

  • Data Inconsistency (Severity: High): Incorrect transaction management can lead to inconsistent data.
  • Resource Leaks (Severity: Medium): Uncommitted transactions can hold database resources indefinitely.

Impact:

  • Data Inconsistency: Risk reduced from High to Low.
  • Resource Leaks: Risk reduced from Medium to Low.

Currently Implemented:

  • transaction blocks are generally used correctly.

Missing Implementation: * Nested transactions are used in one module without TransactionManager.manager.newTransaction().