如何构建用户指定列的SQL Select语句并规避SQL注入

API 探路者

2026-5-26

Great question! Let's break this down clearly—your initial format() approach is definitely risky for SQL injection, but the parameterized query attempt you made won't work for column names either, and here's why, plus the correct way to handle this:

Why your parameterized attempt doesn't work

Parameter placeholders like ? are designed for values (e.g., WHERE id = ?), not SQL identifiers (column names, table names). When you run select ? from table; with ('id',), the database treats 'id' as a string literal, not a column name. So instead of returning values from the id column, you'll get a result set full of the string 'id' repeated for every row—not what you want!

The safe way to handle dynamic column names

Since column names are part of SQL syntax (not values), we need to validate user input against a whitelist of allowed columns before constructing the query. This ensures only valid, pre-approved column names are used, eliminating injection risks entirely.

Step 1: Define a whitelist of allowed columns

First, list all columns in your table that users are allowed to query. This prevents arbitrary input from messing with your SQL:

# Replace with your actual table's columns
allowed_columns = {"id", "username", "email", "join_date", "last_login"}

Step 2: Validate user input against the whitelist

Take the user's input, clean it up, and only keep columns that are in your allowed list:

# Get user input
column_one = input("Column one: ").strip()
column_two = input("Column two: ").strip()
column_three = input("Column three: ").strip()

# Filter out invalid/empty columns
selected_columns = [col for col in [column_one, column_two, column_three] if col in allowed_columns]

# Fallback to a default column if no valid inputs were provided
if not selected_columns:
    selected_columns = ["id"]

Step 3: Construct and execute the safe query

Now that we have only valid column names, we can safely join them into the SQL query. Since we've already validated the inputs, there's no injection risk here:

import sqlite3

con = sqlite3.connect("example.sqlite3")
cur = con.cursor()

# Build the query string
sql_query = f"SELECT {', '.join(selected_columns)} FROM table;"

# Execute and fetch results
cur.execute(sql_query)
rows = cur.fetchall()

# Process results as needed
for row in rows:
    print(row)

con.close()

Bonus: Dynamically get allowed columns from the database

If you don't want to hardcode the allowed columns (e.g., if your table schema might change), you can fetch valid column names directly from the database metadata. For SQLite, use PRAGMA table_info():

cur.execute("PRAGMA table_info(table);")
# Extract column names from the result (second element in each row)
table_columns = {row[1] for row in cur.fetchall()}

# Now use table_columns as your whitelist instead of hardcoding
selected_columns = [col for col in [column_one, column_two, column_three] if col in table_columns]

Key takeaway

Never use format() or string concatenation with unvalidated user input for SQL identifiers (columns, tables).
Parameterized queries are for values, not syntax elements like column names.
Always validate user input against a whitelist (either hardcoded or fetched from database metadata) before building queries with dynamic identifiers.

内容的提问来源于stack exchange，提问作者Sebastian S