Saving webview geturl and getitle in Sqlite database. Problem with syntax error apostrophe '

Question

When im saving webview.getTitle (); in Sqlite database, if in title has apostrophe ('), then i got error -

android.database.sqlite.SQLiteException: near "at": syntax error (code 1): , while compiling: INSERT INTO Favorite VALUES('4 madhab bid'at tavassul', 'file:///android_asset/web/akaid/4maddhab/4.htm' );

My code like this

mysql objcon = new mysql(this, null, null, 1);
SQLiteDatabase db = objcon.getReadableDatabase();
db.execSQL(
    "INSERT INTO Favorite VALUES('"
     + txtnombre.getText()
     + "', '"
     + txtlink2.getText()
     +"' );"
);

How to solve this problem?

by calling db.insert(...)?

pskink
– pskink

2019-02-09 18:26:33 +00:00
Commented Feb 9, 2019 at 18:26 — pskink
– pskink, Commented Feb 9, 2019 at 18:26

GMB · Accepted Answer · 2019-02-09 19:08:27Z

2

There is a single quote embedded within txtnombre.getText() : '4 madhab bid'at tavassul'. This causes SQLite to wrongly consider that this quote marks the end of the first value to insert.

To avoid that, you could consider manually doubling the single quotes :

db.execSQL(
    "INSERT INTO Favorite VALUES('"
    + txtnombre.getText().replaceAll("'","\''")
    + "', '"
    + txtlink2.getText().replaceAll("'","\''")
    +"' );"
);

I would recommend using bind parameters. With this option, your database driver handles escaping behind the hood :

q = "INSERT INTO Favorite VALUES(?, ?)";
t1 = txtnombre.getText();
t2 = txtlink2.getText();
db.rawQuery(q, new String[] { t1, t2 });

Finally, another approach in Android would be to use native method sqlEscapeString(), which is primarily built for this purpose.

Also, as commented by pskink, using insert() would better fit your use case than raw SQL.

edited Feb 9, 2019 at 19:08

answered Feb 9, 2019 at 18:33

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

pskink Over a year ago

rawQuery is not meant for inserting new records - instead simply call SQLiteDatabase#insert method

GMB Over a year ago

@pskink : I updated my answer to mention that... Thanks !

alizulfuqar Over a year ago

I checked, thanks guys. The most nearly suited answer was for me replaceAll regex answer. txtnombre and txtlink2 were EditTexts, so i used like this:

db.execSQL("INSERT INTO Favorite VALUES('"  + txtnombre.getText().toString().replaceAll("'","\''") + "', '"  + txtlink2.getText().toString().replaceAll("'","\''")  + "' );" );

pskink Over a year ago

@alizulfuqar "The most nearly suited answer was for me replaceAll regex answer" - no, just use insert() method - thats all - execSQL is not meant for inserting new data, see execSql, the docs say: "Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE."

forpas Over a year ago

@pskink from developer.android.com/reference/android/database/sqlite/…: Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data. So it can be INSERT/UPDATE/DELETE

|

Collectives™ on Stack Overflow

Saving webview geturl and getitle in Sqlite database. Problem with syntax error apostrophe '

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related