CODEWITHSUNDEEP
androidsqlitespecial-characterssql-update
Prashant Kadam
Prashant Kadam

Reputation: 1207

update query not working in sqlite

my problem is unable to update data because of special character single quote (') in sq-lite database. i want update data but special character(') single quote in string give error into sq-lite database in android how to update. The Query is : 

update pollm set dt_lastaccessedon='null',tx_choice1responsecount='1',tx_choice2responsecount='0',tx_choice3responsecount='0',tx_choice4responsecount='0',tx_choice5responsecount='0',tx_userresponse='TX_OPTION2',tx_questiontext='Is BPM mandatory?',tx_title='BPM',tx_choice1='yes',tx_choice2='no',tx_choice3='don't know', tx_choice4='null',tx_choice5='null',dt_availfrom='2011-07-08 00:00:00.0',dt_availto='2011-07-09 00:00:00.0',tx_status='Closed' where lmspollid='321'

in above query the problem is single quote in "don't know" this string. because of this query not working. please help me

Upvotes: 0

Views: 506

Answers (1)

user370305
user370305

Reputation: 109237

1.

You should be using the method update instead of generating the SQL yourself. The built-in methods will do all the needed escaping for you.

Look at Android - SQLiteDatabase

This not only solves your quotes problem but also mitigates SQL Injection.

2.

Form here SQLite - Frequently Asked Questions

(14) How do I use a string literal that contains an embedded single-quote (') character?

The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in the regard. SQLite follows this standard. Example:

        INSERT INTO xyz VALUES('5 O''clock');

3.

Use DatabaseUtils.sqlEscapeString. The string is enclosed by single quotes and the single quotes inside the string become double quotes.

Upvotes: 1

Related Questions