turingmind-code-review

### Automated Code Review Report for uncommitted changes in `acme-crm` repository

**Summary of Critical Issues (Require Immediate Attention):**
1. **SQL Injection Risk** in `src/db/queries.py` (Line 42): The `user_id` parameter in `get_user_orders()` is directly interpolated into the SQL query without parameterization. This could allow SQL injection if `user_id` comes from user input. **Fix:** Use `cursor.execute("SELECT * FROM orders WHERE user_id = %s", (user_id,))` instead.
2. **Memory Leak** in `src/services/cache_service.py` (Line 127): The `cache.set()` method is called without a TTL (Time-To-Live), causing cached data to grow indefinitely. **Fix:** Add `ttl=3600` (1 hour) to the `set()` call.
3. **Race Condition** in `src/workers/email_worker.py` (Line 89): The `send_email()` function assumes the SMTP connection is always available. If the connection drops, the function will fail silently. **Fix:** Implement retry logic with exponential backoff.

---

### Detailed Review by File:

#### `src/db/queries.py`
- **Issue (Line 42):** SQL injection vulnerability in `get_user_orders()`.
  ```python
  # Current (Vulnerable):
  query = f"SELECT * FROM orders WHERE user_id = {user_id}"
  
  # Recommended:
  query = "SELECT * FROM orders WHERE user_id = %s"
  cursor.execute(query, (user_id,))
  ```
- **Issue (Line 78):** Hardcoded database credentials in `DB_CONFIG`. Use environment variables instead.

#### `src/services/cache_service.py`
- **Issue (Line 127):** Unbounded cache growth.
  ```python
  # Current:
  cache.set(key, data)
  
  # Recommended:
  cache.set(key, data, ttl=3600)
  ```
- **Issue (Line 156):** Missing error handling for Redis connection failures.

#### `src/workers/email_worker.py`
- **Issue (Line 89):** No retry logic for SMTP failures.
  ```python
  # Current:
  def send_email(to, subject, body):
      smtp.sendmail(from_addr, to, subject, body)
  
  # Recommended:
  def send_email(to, subject, body, max_retries=3):
      for attempt in range(max_retries):
          try:
              smtp.sendmail(from_addr, to, subject, body)
              break
          except SMTPException as e:
              if attempt == max_retries - 1:
                  raise
              time.sleep(2 ** attempt)
  ```

---

### Suggestions for Improvement:
1. **Test Coverage:** Add unit tests for `queries.py` to verify SQL injection protections.
2. **Logging:** Add logging for cache misses and SMTP failures to aid debugging.
3. **Documentation:** Update docstrings in `cache_service.py` to clarify TTL usage.

**Next Steps:**
- Address critical issues (SQL injection, memory leak, race condition) first.
- Run `pytest` to ensure no regressions after fixes.
- Consider adding a pre-commit hook to automate this review process.

**Files Reviewed:** 12
**Critical Issues Found:** 3
**Warnings:** 5
**Suggestions:** 3