Flutter习惯追踪应用SQLite INSERT语句语法错误求助

问题背景 #

开发Flutter习惯追踪应用，已实现习惯列表展示、标记完成、删除功能，但创建习惯时SQLite INSERT语句执行失败，报语法错误。

错误SQL语句 #

HabitCreate中的错误语句：

INSERT INTO habits (title; done) VALUES (`${title}`; 0);

终端报错日志 #

PS C:\Users\renar\Documents\achieve_potential_s_q_l_test (6)\achieve_potential_s_q_l_test> flutter run
Resolving dependencies... (6.6s)
+ async 2.11.0
+ auto_size_text 3.0.0
+ boolean_selector 2.1.1
+ cached_network_image 3.3.1
+ cached_network_image_platform_interface 4.0.0
+ cached_network_image_web 1.1.1
+ characters 1.3.0
+ clock 1.1.1
+ collection 1.18.0
+ crypto 3.0.3
+ cupertino_icons 1.0.6
+ fake_async 1.3.1
+ ffi 2.1.2
+ file 7.0.0
+ fixnum 1.1.0
+ flutter 0.0.0 from sdk flutter
+ flutter_animate 4.1.1+1 (4.5.0 available)
+ flutter_cache_manager 3.3.1
+ flutter_lints 3.0.0 (3.0.1 available)
+ flutter_localizations 0.0.0 from sdk flutter
+ flutter_test 0.0.0 from sdk flutter
+ flutter_web_plugins 0.0.0 from sdk flutter
+ font_awesome_flutter 10.6.0 (10.7.0 available)
+ from_css_color 2.0.0
+ go_router 7.1.1 (13.2.1 available)
+ google_fonts 6.1.0 (6.2.1 available)
! http 1.2.0 (overridden) (1.2.1 available)
+ http_parser 4.0.2
+ intl 0.18.1 (0.19.0 available)
+ iregexp 0.1.1 (0.1.2 available)
+ json_path 0.6.2 (0.7.1 available)
+ leak_tracker 10.0.0 (10.0.4 available)
+ leak_tracker_flutter_testing 2.0.1 (3.0.3 available)
+ leak_tracker_testing 2.0.1 (3.0.1 available)
+ lints 3.0.0
+ logging 1.2.0
+ matcher 0.12.16+1
+ material_color_utilities 0.8.0 (0.11.1 available)
+ maybe_just_nothing 0.5.3
+ meta 1.11.0 (1.12.0 available)
+ nested 1.0.0
+ octo_image 2.0.0
+ page_transition 2.1.0
+ path 1.9.0
+ path_provider 2.0.14 (2.1.2 available)
+ path_provider_android 2.0.25 (2.2.2 available)
+ path_provider_foundation 2.2.2 (2.3.2 available)
+ path_provider_linux 2.1.11 (2.2.1 available)
+ path_provider_platform_interface 2.0.6 (2.1.2 available)
+ path_provider_windows 2.1.7 (2.2.1 available)
+ petitparser 5.4.0 (6.0.2 available)
+ platform 3.1.4
+ plugin_platform_interface 2.1.8
+ provider 6.0.5 (6.1.2 available)
+ rfc_6901 0.1.1 (0.2.0 available)
+ rxdart 0.27.7
+ shared_preferences 2.2.2
+ shared_preferences_android 2.2.1
+ shared_preferences_foundation 2.3.4 (2.3.5 available)
+ shared_preferences_linux 2.3.2
+ shared_preferences_platform_interface 2.3.1 (2.3.2 available)
+ shared_preferences_web 2.2.1 (2.3.0 available)
+ shared_preferences_windows 2.3.2
+ sky_engine 0.0.99 from sdk flutter
+ source_span 1.10.0
+ sprintf 7.0.0
+ sqflite 2.2.6 (2.3.2 available)
+ sqflite_common 2.5.4
+ stack_trace 1.11.1
+ stream_channel 2.1.2
+ string_scanner 1.2.0
+ synchronized 3.1.0+1
+ term_glyph 1.2.1
+ test_api 0.6.1 (0.7.0 available)
+ timeago 3.2.2 (3.6.1 available)
+ typed_data 1.3.2
+ url_launcher 6.2.5
+ url_launcher_android 6.3.0
+ url_launcher_ios 6.2.5
+ url_launcher_linux 3.1.1
+ url_launcher_macos 3.1.0
+ url_launcher_platform_interface 2.3.2
+ url_launcher_web 2.2.3 (2.3.0 available)
+ url_launcher_windows 3.1.1
! uuid 4.3.3 (overridden)
+ vector_math 2.1.4
+ vm_service 13.0.0 (14.1.0 available)
+ web 0.4.2 (0.5.1 available)
+ win32 5.3.0
+ xdg_directories 1.0.4
Changed 88 dependencies!
32 packages have newer versions incompatible with dependency constraints.
Try `flutter pub outdated` for more information.
Launching lib\main.dart on TA 1004 in debug mode...
Upgrading build.gradle
You are applying Flutter's app_plugin_loader Gradle plugin imperatively using the apply script method, which is deprecated and will be removed in a future release. Migrate to applying Gradle plugins with the declarative plugins block: https://flutter.dev/go/flutter-gradle-plugin-apply

You are applying Flutter's main Gradle plugin imperatively using the apply script method, which is deprecated and will be removed in a future release. Migrate to applying Gradle plugins with the declarative plugins block: https://flutter.dev/go/flutter-gradle-plugin-apply

Running Gradle task 'assembleDebug'...                            247.8s
√  Built build\app\outputs\flutter-apk\app-debug.apk.
Installing build\app\outputs\flutter-apk\app-debug.apk...          18.8s
D/vndksupport(21553): Loading /vendor/lib64/hw/android.hardware.graphics.mapper@2.0-impl.so from current namespace instead of sphal namespace.
D/vndksupport(21553): Loading /vendor/lib64/hw/gralloc.msm8998.so from current namespace instead of sphal namespace.
Syncing files to device TA 1004...                               1,734ms

Flutter run key commands.
r Hot reload.
R Hot restart.
h List all available interactive commands.
d Detach (terminate "flutter run" but leave application running).
c Clear the screen
q Quit (terminate the application on the device).

A Dart VM Service on TA 1004 is available at: http://127.0.0.1:59139/nuepK6iQtuE=/
The Flutter DevTools debugger and profiler on TA 1004 is available at: http://127.0.0.1:9100?uri=http://127.0.0.1:59139/nuepK6iQtuE=/
D/ViewRootImpl(21553): com.mycompany.achievepotentialsqltest: (1, 0, 0, 0, -1)
W/otentialsqltes(21553): Accessing hidden method Landroid/view/View;->getAccessibilityViewId()I (light greylist, reflection)
D/ViewRootImpl(21553): com.mycompany.achievepotentialsqltest: (4, 6, 4, 0, 11726)
W/otentialsqltes(21553): Unknown chunk type '200'.
D/InputConnectionAdaptor(21553): The input method toggled cursor monitoring on
D/InputConnectionAdaptor(21553): The input method toggled cursor monitoring on
E/SQLiteLog(21553): (1) near ";": syntax error
E/flutter (21553): [ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: DatabaseException(near ";": syntax error (code 1 SQLITE_ERROR): , while compiling: INSERT INTO habits (title; done) VALUES (`ufjccjc`; 0);) sql 'INSERT INTO habits (title; done) VALUES (`ufjccjc`; 0);
E/flutter (21553): ' args []
E/flutter (21553): #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
E/flutter (21553): <asynchronous suspension>
E/flutter (21553): #1      SqfliteDatabaseMixin.txnRawQuery.<anonymous closure> (package:sqflite_common/src/database_mixin.dart:586:30)
E/flutter (21553): <asynchronous suspension>
E/flutter (21553): #2      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:33:16)
E/flutter (21553): <asynchronous suspension>
E/flutter (21553): #3      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:517:14)
E/flutter (21553): <asynchronous suspension>
E/flutter (21553): #4      _HabitCreateWidgetState.build.<anonymous closure> (package:achieve_potential_s_q_l_test/pages/habit_create/habit_create_widget.dart:113:19)
E/flutter (21553): <asynchronous suspension>
E/flutter (21553): #5      _FFButtonWidgetState.build.<anonymous closure> (package:achieve_potential_s_q_l_test/flutter_flow/flutter_flow_widgets.dart:110:19)
E/flutter (21553): <asynchronous suspension>
E/flutter (21553):

解决方案 #

1. 修正SQL语法错误 #

SQL语句中，列名之间、值之间必须用**逗号(,)分隔，而非分号(;)；字符串类型的值需要用单引号(')**包裹，不能用反引号(`)。

修正后的SQL语句：

INSERT INTO habits (title, done) VALUES ('${title}', 0);

2. 推荐使用参数化查询（避免SQL注入） #

直接拼接字符串存在SQL注入风险，建议使用sqflite提供的insert方法，自动处理参数：

await db.insert(
  'habits',
  {'title': title, 'done': 0},
  conflictAlgorithm: ConflictAlgorithm.replace,
);

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