Escaping single quotes and double quotes in a string in dart

Question

I am creating a basic chat application in flutter. It involves a Text Field where the user can enter any text and click on the send button.

The application works fine for any string you enter in the text box except for the string containing quotes. I get a Database exception when trying to add that string to the sql database as the quotes are not escaped.

Doing replaceAll("'", "\'").replaceAll('"', "\'") on a string works as i'm using double quotes in sql queries, but all the double quotes are now single quotes.

Thanks for the help.

Answer 1

Does the database support bind parameters? If not, does the package you are using to talk to the database have a string escape function?

Those will work better than doing it manually, especially since there can be very unsafe stuff in the user input beyond quotes. If you are manually putting together a query string and sending it to the DB it will be open to SQL attacks.

For your immediate question, you are replacing with single quotes in both places. Assuming you can escape quotes by prefixing with a slash it should look like .replaceAll('"', '\\"').

Please look for a more secure way to sanitize user input.

Answer 2

The best and safest way to run queries SQL in Dart is to use the bind parameters.

For example, if you are using sqflite you'll need to pass parameters in a List in this way using the ? as wildcard in the query:

INSERT

int id2 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
      ['another name', 12345678, 3.1416]);

UPDATE

int count = await database.rawUpdate(
    'UPDATE Test SET name = ?, value = ? WHERE name = ?',
    ['updated name', '9876', 'some name']);

DELETE

count = await database
    .rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);