优化数据库类删除函数：支持两组筛选条件提升安全性

Got it, let's walk through how to beef up your delete function's security by requiring two sets of field-value pairs—this will make it way harder for attackers to guess valid IDs and delete data they shouldn't have access to.

Core Implementation Ideas #

First, let's break down the key steps to modify your existing DB class:

1. Update the Method Signature
   Change your delete function to accept two separate condition parameters (instead of a single field-value pair). Each condition should be a key-value pair (like a dictionary with one entry) representing a field and its required value.

2. Validate Inputs
   Add checks to ensure both conditions are valid: non-empty, properly formatted (e.g., single-key dictionaries), and optionally verify the fields exist in the target table for extra safety.

3. Build a Secure SQL Query
   Construct a DELETE statement that uses AND to combine both conditions. Always use parameter binding to avoid SQL injection—never concatenate values directly into the query string.

4. Return Action Feedback
   Return the number of affected rows to the caller, so they can confirm if the deletion actually happened (a return value of 0 means no matching records were found, which could indicate a malicious guess).

Example Modified DB Class #

Here's a concrete example (using Python-style syntax, adjust to your language of choice):

class DB:
    def __init__(self, connection):
        self.connection = connection

    def delete(self, table_name, condition_1, condition_2):
        # Validate input conditions
        for idx, condition in enumerate([condition_1, condition_2], 1):
            if not isinstance(condition, dict) or len(condition) != 1:
                raise ValueError(f"Condition {idx} must be a dictionary with exactly one key-value pair")
            if not next(iter(condition.values())):
                raise ValueError(f"Condition {idx} value cannot be empty")

        # Extract field-value pairs from conditions
        field1, val1 = next(iter(condition_1.items()))
        field2, val2 = next(iter(condition_2.items()))

        # Build secure query with parameter placeholders
        query = f"DELETE FROM {table_name} WHERE {field1} = %s AND {field2} = %s"

        # Execute query safely
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query, (val1, val2))
                affected_rows = cursor.rowcount
                self.connection.commit()
            return affected_rows
        except Exception as e:
            self.connection.rollback()
            raise RuntimeError(f"Delete failed: {str(e)}") from e

Expected Usage #

Now your delete calls will require two conditions—for example, pairing a record ID with a user-specific identifier (like a tenant ID or owner UUID) to ensure only authorized users can delete records:

# Delete a user record only if ID matches AND tenant ID matches the requester's tenant
db.delete("users", {"id": 12345}, {"tenant_id": 67890})

# Delete an order only if order ID matches AND customer ID matches the logged-in user
db.delete("orders", {"order_id": "ORD-9876"}, {"customer_id": "CUS-1234"})

Extra Tips for Robustness #

• Enforce Unique Combinations: Make sure the two fields you use form a unique constraint in your database (e.g., id + tenant_id is unique) to prevent accidentally deleting multiple records.
• Add Audit Logging: Log the conditions used for each delete operation, along with the requester's identity, for security auditing purposes.
• Handle Edge Cases: Add checks for table existence (if your DB driver supports it) or restrict allowed fields to a predefined list to prevent misuse of the function.

内容的提问来源于stack exchange，提问作者BobSacamano