How to insert special character with sqflte in Flutter?

I want to save html String with sqflite in Flutter.

But there are many special character in html String like '?','=','&'......

It will throw error like: DB Error: 1 "near "?": syntax error".

I am use iOS simulator, so it is base on FMDB.

This is my code:

await database.execute("INSERT INTO ChapterCache(bookName,chapterName,content) select '$bookName','$chapterName','$content' where not exists (select * from ChapterCache where bookName = '$bookName' and chapterName='$chapterName')");

Solution

I'm not sure what your query is supposed to do, it looks kind of strange, especially the first select statement. I suppose you want to insert a new row into the database only if it does not exist.

In general, what you are doing is very dangerous! Hackers could easily hijack your database using SQL injections.

To solve your problem, and to remove the danger of SQL injections, you have to use prepared statements (? Placeholders in your insert query, and then supply the values as a separate list. That will also allow you to insert special characters into the DB.

sqflite automatically does this for you. The safest way to insert a new row into the database:

final values = <String, dynamic>{
  'bookName': bookName,
  'chapterName': chapterName,
  'content': content,
};

await database.insert('ChapterCache', values, conflictAlgorithm: ConflictAlgorithm.ignore);

This will insert the row if it does not exist, and do nothing if it is already there (ignore).

Also, you probably want to set up a compound primary key for your table:

CREATE TABLE `ChapterCache` (
  `bookName`     text NOT NULL,
  `chapterName`  text NOT NULL,
  `content`      text NOT NULL,
  PRIMARY KEY(`bookName`, `chapterName`)
)

That will make sure that only one row exists for every combination of bookName-chapterName.

Now if you want to write your own SQL query that does exactly the same thing as the insert call above, this is how you would do it safely:

await database.execute(
  "INSERT OR IGNORE INTO `ChapterCache` (`bookName`, `chapterName`, `content`) VALUES (?, ?, ?)",
  [bookName, chapterName, content],
);