inserting text in database how to take care of '

Question

Hi I am inserting general comments in to my database dynamically. and my query looks like this:

String sql = "insert into table (comment) values('"+ dynamic_comment + "');";

I am getting an error when a use insert something like this:

I'm doing great

becuase at that time my query becomes:

insert into table (comment) values('I'm doing great');

'I'm doing great'

Error: Caused by: android.database.sqlite.SQLiteException: near "t": 
syntax error (code 1): , while compiling: 
insert into timeline(comment) values('Can't belive it really works');

Can someone tell me how to overcome this problem and how many other problems I can face like this while working with database.

thanks

You're not the first person to encounter this issue. Did you try searching for the answer? — eggyal, Mar 19 '13 at 09:39
Use [**parameters**](http://www.devart.com/dotconnect/sqlite/docs/Parameters.html). — Tim Schmelter, Mar 19 '13 at 09:40
http://stackoverflow.com/questions/433392/how-do-i-use-prepared-statements-in-sqlite-in-android — pablochan, Mar 19 '13 at 09:40

score 2 · Accepted Answer · edited May 23 '17 at 12:04

2

You need to use prepared statements, concatenating raw strings into queries has been bad practice for a very long time now.

From here:

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("insert into table (comment) values(?)");
stmt.bindString(1, dynamic_comment);
stmt.execute();

edited May 23 '17 at 12:04

Community

1
1

answered Mar 19 '13 at 09:42

Boris the Spider

54,398
6
98
152

why did you put 1 here: stmt.bindString(1, dynamic_comment); – Mar 19 '13 at 09:59
Take a look at [the javadoc](http://developer.android.com/reference/android/database/sqlite/SQLiteProgram.html#bindString(int,%20java.lang.String)) - I am binding the first parameter (the `?`) to the value of `dynamic_comment`. – Boris the Spider Mar 19 '13 at 10:19

hjpotter92 · Answer 2 · 2013-03-19T09:50:32.913

1

You need to escape your string variable before insertion.

String sql = "insert into table (comment) values('"+ DatabaseUtils.sqlEscapeString(dynamic_comment) + "');";

The above method is prone to SQL inject attacks. To be on the safer side, learn more about using parametric statements.

A sample for the same query as yours will be:

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("INSERT INTO `table` (`comment`) values(?)");
stmt.bindString( 1, dynamic_comment );
stmt.execute();

edited Mar 19 '13 at 09:50

answered Mar 19 '13 at 09:41

hjpotter92

71,576
32
131
164

2

Always use parameterised statements, other wise you're just asking for SQL injection attacks. – NickJ Mar 19 '13 at 09:43
hmm, Didn't know about prepared statements in sqlite/android too. Thanks. Editing reply now @pablochan – hjpotter92 Mar 19 '13 at 09:47
dint help at all, inserting abcd and getting result as 'abcd'. – Mar 19 '13 at 09:49

inserting text in database how to take care of '

2 Answers2