We are working on a cool forecasting project where my team lead has raised a pull request.

Github copilot had provided some feedback comment on different different code locations in the PR. One of the interesting comment I found serious….by looking at the keywords that includes SQL injection vulnerability and security risk.

I started looking into what copilot is trying to say…is copilot drunk 🥃 ?

Here is simplify version I am trying to show —

In the query string (Python f-strings), there were some dynamic variable were passed like user_id, forecast_date etc.,

sql = f"SELECT * FROM users WHERE id = {user_id}"

It looks simple string and does not look like any issue right ? but wait…

Here in Python f-strings blindly paste text into SQL like user_id.

Example:

If user_id contains SQL instead of a number, Python will still paste it.

If I pass below instead of user_id then…. ?

user = "'admin' OR 1=1 ;/*"

Now the query become —

SELECT * FROM users WHERE id = 'admin' OR 1=1 ;/* 

Result: Condition always true Security bypassed

Notice that the last chars (multiple line comment in sql → ;/*) will make all of the sql lines to be commented, got my point?

But few from you might ask — Snowflake supports parameterized queries. Doesn't that solve it?

Answer:

✔ Only for VALUES ❌ NOT for identifiers

This is valid:

cursor.execute(
"SELECT * FROM users WHERE date = %s"

What is the solution ?

1. Using parameterized queries-

sql = f"""
SELECT *
FROM my_db.data_science_stage.demand_forecast__{table_suffix}
WHERE start_forecast_dt = %s
  AND horizon <= %s
  AND region = %s
"""

cursor.execute(
    sql,
    (valid_date, horizon, region)
)

2. Validating/sanitizing these inputs before use (add some suffixes)-

ALLOWED_SUFFIXES = {"weekly", "monthly"}

if table_suffix not in ALLOWED_SUFFIXES:
    raise ValueError("Invalid table suffix")

sql = f"""
SELECT *
FROM my_db.data_stage.demand_forecast__{table_suffix}
"""

If this post did not inject bugs into your brain, press that clap button 😄. Follow for more tech stories without vulnerabilities.

Thanks for sticking till the end! 🙏