如何在MySQL中使用Ecto.Adapters.SQL.query？表名参数未被替换怎么办？

Hey there! I’ve run into this exact snag before, so let’s break down what’s going on and how to fix it.

Why the Table Name Isn’t Being Replaced #

First, let’s clear up a key point: Ecto’s parameter binding (using ? placeholders) is built only for values (like the 123 in WHERE id = ?). It doesn’t work for identifiers such as table names, column names, or database names—this isn’t an Ecto limitation, it’s how SQL databases handle parameterized queries. If you try passing a table name as a parameter, MySQL will treat it as a string literal (e.g., 'users' instead of users), which throws syntax errors.

Solution 1: Safe Table Name Concatenation (Controlled Input) #

If your table name comes from a trusted, internal source (like an enum or hardcoded value), you can safely concatenate it into your SQL string after properly escaping it with Ecto’s built-in quoting function. This ensures the table name follows MySQL’s identifier rules (like wrapping in backticks if needed) and avoids syntax issues or injection risks.

Here’s a working example:

# Replace MyApp.Repo with your actual repo module
repo = MyApp.Repo
table_name = "users"

# Quote the table name to match MySQL's formatting requirements
quoted_table = Ecto.Adapters.SQL.quote(repo, table_name, :table)

# Build your SQL string with the safely quoted table name
sql = "SELECT * FROM #{quoted_table}"

# Execute the query
result = Ecto.Adapters.SQL.query!(repo, sql, [])

The Ecto.Adapters.SQL.quote/3 function handles database-specific escaping automatically—so if your table name has special characters or is a reserved keyword (like order), it’ll wrap it in backticks to keep the query valid.

Solution 2: Handling User-Provided Table Names (Uncontrolled Input) #

If the table name comes from user input (a form, API parameter, etc.), you must add a strict whitelist check before quoting and concatenating. Never trust raw user input directly—this is non-negotiable to prevent SQL injection attacks.

Example implementation:

repo = MyApp.Repo
user_table_input = params["table_name"] # From untrusted user input
allowed_tables = ["users", "posts", "comments"] # Your pre-approved list of tables

if user_table_input in allowed_tables do
  quoted_table = Ecto.Adapters.SQL.quote(repo, user_table_input, :table)
  sql = "SELECT * FROM #{quoted_table}"
  result = Ecto.Adapters.SQL.query!(repo, sql, [])
else
  raise ArgumentError, "Invalid or unauthorized table name"
end

What to Avoid #

Never do either of these—they’ll cause errors or expose you to security risks:

# ❌ DON'T DO THIS! No escaping or validation = SQL injection risk
sql = "SELECT * FROM #{user_table_input}"
result = Ecto.Adapters.SQL.query!(repo, sql, [])

# ❌ ALSO DON'T DO THIS! Parameter binding doesn't work for identifiers
Ecto.Adapters.SQL.query!(repo, "SELECT * FROM ?", [user_table_input])

The second example will send SELECT * FROM 'users' to MySQL, which treats 'users' as a string literal instead of a table name—leading to a Table 'mydb.'users'' doesn't exist error.

内容的提问来源于stack exchange，提问作者Cesar Gutierrez