Alright, I need to vent a bit and also see if anyone has cracked this. I was auditing some old lead-routing logic in our marketing database, and I found some raw SQL queries being built with string concatenation. Classic injection risk, right? I fed the problematic code to a leading Claw-based assistant and asked it to fix the vulnerabilities.
**My Prompt:**
> "Here's a Python function that takes a region parameter from a web form and uses it in a SQL query to pull leads. How do I fix the SQL injection vulnerability?"
```python
def get_leads_by_region(region):
query = f"SELECT email, name FROM leads WHERE region = '{region}' AND status = 'active';"
# ... execute query
return results
```
**The Assistant's Output:**
It correctly identified the f-string as the problem. But then it suggested using Python's string formatting with `%` and a manual escaping function, something like:
```python
def escape_sql(value):
# ... some custom regex to escape quotes
return escaped_value
def get_leads_by_region(region):
safe_region = escape_sql(region)
query = "SELECT email, name FROM leads WHERE region = '%s' AND status = 'active';" % safe_region
```
**The Actual Correct Answer:**
The assistant *completely missed* using parameterized queries (like with `cursor.execute(query, (region,))` for `psycopg2` or `sqlite3`). Manual escaping is error-prone, library-specific, and not the modern, accepted fix. The correct solution is to use the DB-API's built-in parameter substitution, which handles escaping safely at the driver level.
This is a perfect example of an assistant giving a *technically* "safer" but fundamentally *flawed* and outdated solution. It's like it memorized a 2005 blog post on SQL injection but didn't understand the principle of using the database adapter's own safety mechanisms.
Has anyone else run into this? It seems like on security-specific refactoring, these models often offer a "patch" rather than the architecturally correct principle. For marketing ops, where we're often stitching together data from forms, CRMs, and CDPs, this is a huge red flag. You can't just trust it to secure your pipelines.
What's worse, if a junior dev implemented its suggestion, they'd think they'd solved the problem, while still leaving a potential attack vector open.
It's telling that it tried to invent an `escape_sql` function. That's a red flag, suggesting it might be pulling from older or poorly generalized patterns. The correct answer for modern Python is almost always to use parameterized queries with your database driver's built-in placeholders, not string operations.
For your example, using something like psycopg2 or sqlite3, the fix is straightforward:
```python
def get_leads_by_region(region):
query = "SELECT email, name FROM leads WHERE region = %s AND status = 'active';"
cursor.execute(query, (region,))
```
The assistant's suggestion introduces new risks; manual escaping is fragile and database-specific. The real problem is that these models sometimes optimize for a "transformation" of the given code rather than recommending the proper architectural pattern, which is to use the DB-API's parameter substitution. Did you try a follow-up prompt asking it to use the database cursor's execute method directly?