crate / crate/cratedb-guide

Security: SQL injection risk in pandas Jupyter tutorial

Open
#353 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
No language data
Stars
3
Forks
4
Avg merge
3d 18h
Merged PRs (30d)
1

Description

## Description

The `insert_values` function in the pandas Jupyter tutorial (`docs/integrate/pandas/tutorial-jupyter.md`, lines 260-369) builds SQL INSERT statements using string concatenation without proper parameterization. This creates potential SQL injection vulnerabilities when users adapt the code for other data sources.

## Problem

At line 289, ticker values are directly interpolated into the SQL string:
```python
values_array.append("({},\'{}\'',{})".format(
closing_date, row_values['ticker'], close_value))
```

If a ticker contained a single quote (e.g., `O'REILLY`), it would break the SQL statement and could allow injection attacks with untrusted data sources.

## Suggested Solution

1. Add a warning note about the security implications for production use
2. Demonstrate the safer approach using parameterized queries with `cursor.executemany()`

Example safe implementation:
```python
def insert_values(table_name, data):
create_table(table_name)

# Prepare data as list of tuples
values = []
for row in range(len(data)):
row_values = data.iloc[row, :]
close_value = row_values['close_value'] if not math.isnan(row_values['close_value']) else -1
closing_date = row_values['closing_date']
values.append((closing_date, row_values['ticker'], close_value))

# Use parameterized query
insert_stmt = f'INSERT INTO "{table_name}" (closing_date, ticker, close_value) VALUES (%s, %s, %s)'
cursor.executemany(insert_stmt, values)

print(f"Inserted {len(data)} rows in CrateDB")
```

## References

- Original discussion: https://github.com/crate/cratedb-guide/pull/297#discussion_r2392441430
- File: `docs/integrate/pandas/tutorial-jupyter.md`

## Requested by

@amotl

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.