如何使用JavaScript将变量作为SQLite的列名插入？

Absolutely, you can dynamically add columns using values from your questions array—but your current code has a syntax error in the ALTER TABLE statement, and there are important safety and design considerations to keep in mind.

What's wrong with your current code? #

First, the ALTER TABLE syntax for adding a column is ALTER TABLE table_name ADD COLUMN column_name data_type;—your code uses ADD VALUES(?) which isn't valid SQLite syntax. Second, parameter placeholders (?) work for values in queries, but not for identifiers like column names. SQLite won't interpret the placeholder as a column name here.

Corrected working code #

Here's how to adjust your code to add each q.title as a column, with proper sanitization to avoid SQL injection and syntax issues:

db.serialize(() => {
  db.run("CREATE TABLE assessmentTable (test TEXT)");
  
  for(const q of questions){
    // Sanitize column name: replace invalid characters with underscores
    const safeColumnName = q.title.replace(/[^a-zA-Z0-9_]/g, "_");
    // Wrap the sanitized name in backticks to handle edge cases (like spaces)
    db.run(`ALTER TABLE assessmentTable ADD COLUMN \`${safeColumnName}\` TEXT`);
  }
});

Key things to note: #

• SQL Injection Protection: Directly inserting dynamic values into SQL identifiers (like column names) is risky if title values come from untrusted sources. The sanitization step replaces non-alphanumeric/underscore characters with underscores to block malicious input.
• Column Name Rules: SQLite allows spaces or reserved words in column names if wrapped in backticks (or double quotes), but sanitizing first ensures you avoid unexpected syntax errors.
• Performance: Adding columns one by one in a loop can be slow if you have many questions. If possible, define all columns upfront when creating the table instead of altering it repeatedly.

Alternative better schema design #

If your goal is to store question titles and their corresponding responses, a normalized schema is usually more flexible than adding a column per question. Consider this structure instead:

CREATE TABLE assessmentResponses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  test TEXT,
  question_title TEXT,
  response TEXT
);

This avoids dynamic table alterations entirely and makes it easier to add/remove questions later without modifying your table structure.

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