在Flutter中使用sqflite数据库存储数据时遇到语法错误。

huangapple go评论59阅读模式
英文:

Getting a syntax error while storing a data in sqflite databse in flutter

问题

你好,我理解你的问题。你遇到的问题可能是由于SQL查询中的数据不正确引起的,尤其是包含空格或逗号的数据。你可以尝试将字符串数据用单引号括起来,以避免这些问题。下面是修正后的代码:

Future<String> saveRecipe(int recipesId, String recipesName, String recipesCategory) async {
  try {
    var dbClient = await db;
    await dbClient!.rawQuery(
        'INSERT INTO $recipeTable ($recipeId, $recipeName, $recipeCategory) VALUES ($recipesId, \'$recipesName\', \'$recipesCategory\')');
    return 'Success';
  } catch (e) {
    return 'Failure';
  }
}

通过在字符串数据周围添加单引号,你可以确保数据库不会将逗号或空格解释为SQL语句的一部分。这应该解决你遇到的错误。

祝你好运!如果还有其他问题,请随时提问。

英文:

Hello Guys I am working on a flutter project Where I am trying to save data in a sqflite database.I tried different ways like storing it using Json, Map but nothing works than I found the old classic way that is using a raw query to store the data.

This is how I am storing data

//Storing data into database
DatabaseHelper().saveRecipe(int.parse(recipes[i+1].recipeID),recipes[i+1].recipeName,recipes[i+1].recipeCategory);

//DatabaseHelper Class:
  Future&lt;String&gt; saveRecipe(int recipesId, String recipesName, String recipesCategory) async {
    try{
      var dbClient = await db;
      await dbClient!.rawQuery(&#39;INSERT INTO $recipeTable ($recipeId,$recipeName,$recipeCategory) VALUES ($recipesId,$recipesName,$recipesCategory)&#39;);
      return &#39;Success&#39;;
    }catch(e){
      return &#39;Failure&#39;;
    }
  }

The error I am getting is

E/SQLiteLog( 5185): (1) near &quot;Chicken&quot;: syntax error in &quot;INSERT INTO recipeTable (recipeId,recipeName,recipeCategory) VALUES (101,Butter Chicken,Indian Cuisine)&quot;
E/SQLiteLog( 5185): (1) near &quot;Cuisine&quot;: syntax error in &quot;INSERT INTO recipeTable (recipeId,recipeName,recipeCategory) VALUES (102,Tikka,Indian Cuisine)&quot;
E/SQLiteLog( 5185): (1) near &quot;Cuisine&quot;: syntax error in &quot;INSERT INTO recipeTable (recipeId,recipeName,recipeCategory) VALUES (103,Pizza,Italian Cuisine)&quot;
E/SQLiteLog( 5185): (1) near &quot;Food&quot;: syntax error in &quot;INSERT INTO recipeTable (recipeId,recipeName,recipeCategory) VALUES (104,Burger,Fast Food)&quot;
E/SQLiteLog( 5185): (1) near &quot;Cuisine&quot;: syntax error in &quot;INSERT INTO recipeTable (recipeId,recipeName,recipeCategory) VALUES (105,Karai,Pakistani Cuisine)&quot;
E/SQLiteLog( 5185): (1) near &quot;Cuisine&quot;: syntax error in &quot;INSERT INTO recipeTable (recipeId,recipeName,recipeCategory) VALUES (106,Handi,Indian Cuisine)&quot;
E/SQLiteLog( 5185): (1) table recipeTable has no column named recipeCategory in &quot;INSERT INTO recipeTable (recipeId,recipeName,recipeCategory) VALUES (107,Soup,Starter)&quot;
E/SQLiteLog( 5185): (1) near &quot;Cuisine&quot;: syntax error in &quot;INSERT INTO recipeTable (recipeId,recipeName,recipeCategory) VALUES (109,Shawarma,Eastern Cuisine)&quot;
E/SQLiteLog( 5185): (1) near &quot;Fires&quot;: syntax error in &quot;INSERT INTO recipeTable (recipeId,recipeName,recipeCategory) VALUES (110,French Fires,Fast Food)&quot;

I think i show me error because I have data in two word like in first line 101,Butter Chicken,Indian Cuisine Because of butter and chicken are two different words so may be it is showing me error. I am not sure about this. Please help me to solve the issue, I appreciate all the effort, thankyou.

答案1

得分: 0

在SQL中,字符串文字由单引号(&#39;)表示。由于您没有引用传递给插入语句的字符串,数据库认为它们是插入语句语法的一部分,因此在它们上失败。

您可以引用它们,但坦白说,如果这些字符串可能受用户输入影响,更好的方法是使用占位符 - 这既更容易,又更安全:

await dbClient!.rawQuery(
  'INSERT INTO $recipeTable ($recipeId, $recipeName, $recipeCategory) VALUES (?, ?, ?)', 
  [recipesId, recipesName, recipesCategory]);
英文:

String literals in SQL are denoted by single quotes (&#39;). Since you're not quoting the strings you're passing to the insert statement, the database thinks they're part of the insert statement's syntax, and fails on them.

You could quote them, but TBH, a better approach would be to use placeholders - it's both easier and safe if there's a chance these strings can be influenced by user input:

await dbClient!.rawQuery(
  &#39;INSERT INTO $recipeTable ($recipeId, $recipeName, $recipeCategory) VALUES (?, ?, ?)&#39;, 
  [recipesId, recipesName, recipesCategory]);

huangapple
  • 本文由 发表于 2023年3月3日 20:15:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/75626961.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定