9

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.

Nate Bosch
  • 10,145
  • 2
  • 26
  • 22
sujay_br
  • 565
  • 2
  • 7
  • 16

2 Answers2

6

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.

Nate Bosch
  • 10,145
  • 2
  • 26
  • 22
  • Thanks for the reply. Can you please explain how the app could be subjected to SQL attacks? Cause I believe it's all local to the user's DB. – sujay_br Oct 09 '18 at 13:59
  • 1
    It's hard to say exactly what the security holes may be without more detail - I'm also far from an expert on security. It's just that anytime manual escaping and SQL are both in the mix it's a red flag to look for potential issues. – Nate Bosch Oct 09 '18 at 20:32
4

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']);
Leonardo Rignanese
  • 865
  • 11
  • 22