Flutter中SQLite INSERT语句VALUES附近语法错误求助

错误原因 #

你的代码里存在两处SQL语法错误，直接导致了near "VALUES": syntax error：

1. INSERT语句格式错误 #

在plantTableQuery方法中，列名列表最后一项story_audio后面多了一个逗号，而且列名列表的右括号被遗漏，直接接上了VALUES，破坏了SQL的语法结构。

2. CREATE_TABLE语句格式错误 #

CREATE_TABLE常量中，最后一列story_audio TEXT后面多了一个多余的逗号，SQLite不允许列定义末尾存在此类无效逗号。

修复后的代码 #

修正plantTableQuery方法 #

static String plantTableQuery(WordTable words) {
  return ''' INSERT INTO words (
  word_id, 
  tina,
  letter,
  naba,
  itavi,
  lomanibai,
  dewa,
  ivakamacala,
  coke,
  idusidusi,
  ivosavosa,
  itakataka,
  vosa_vakalatina,
  word_main_image,
  word_main_audio,
  story_audio
  ) VALUES (
  '${words.WORD_ID}',
  '${words.TINA}',
  '${words.LETTER}',
  '${words.NABA}',
  '${words.ITAVI}',
  '${words.LOMANIBAI}',
  '${words.DEWA}',
  '${words.IVAKAMACALA}',
  '${words.COKE}',
  '${words.IDUSIDUSI}',
  '${words.IVOSAVOSA}',
  '${words.ITAKATAKA}',
  '${words.VOSA_VAKALATINA}',
  '${words.WORDIMG}',
  '${words.MAIN_AUDIO}',
  '${words.STORY_AUDIO}'
  )''';
}

修正CREATE_TABLE常量 #

static const String CREATE_TABLE = '''CREATE TABLE IF NOT EXISTS words ( 
  word_id INTEGER PRIMARY KEY,
  tina TEXT,
  letter TEXT,
  naba TEXT,
  itavi TEXT,
  lomanibai TEXT,
  dewa TEXT,
  ivakamacala TEXT,
  coke TEXT,
  idusidusi TEXT,
  ivosavosa TEXT,
  itakataka TEXT,
  vosa_vakalatina TEXT,
  word_main_image TEXT,
  word_main_audio TEXT,
  story_audio TEXT
)''';

额外优化建议 #

直接拼接字符串生成SQL语句存在SQL注入风险，建议使用sqflite提供的参数化插入方法，示例如下：

Future<int> insertWord(WordTable word) async {
  final db = await _getDatabase(); // 替换为你的数据库实例获取逻辑
  return await db.insert(
    WordTable.table,
    {
      'word_id': word.WORD_ID,
      'tina': word.TINA,
      'letter': word.LETTER,
      'naba': word.NABA,
      'itavi': word.ITAVI,
      'lomanibai': word.LOMANIBAI,
      'dewa': word.DEWA,
      'ivakamacala': word.IVAKAMACALA,
      'coke': word.COKE,
      'idusidusi': word.IDUSIDUSI,
      'ivosavosa': word.IVOSAVOSA,
      'itakataka': word.ITAKATAKA,
      'vosa_vakalatina': word.VOSA_VAKALATINA,
      'word_main_image': word.WORDIMG,
      'word_main_audio': word.MAIN_AUDIO,
      'story_audio': word.STORY_AUDIO,
    },
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
}

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