Security: SQL injection risk in pandas Jupyter tutorial
- 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.