Android SQLite - replace part of a string safely

Question

I need to rename tags in the task titles in database. I saw similar questions like this one But in my app user could create a task with custom title. So the following solution could be dangerous:

db.execSQL("UPDATE " + Task.TABLE +
            " SET title = REPLACE(title, '" + fromTagName + "', '" + toTagName + "');");

What if user will create a task with some dangerous commands in the title (injection)? Will this approach crash? Is there alternative to use special android methods instead of raw SQL commands? Or is it safe as it is?

score 3 · Answer 1 · answered Jul 24 '19 at 15:22

3

You can use the update() method with ContentValues():

ContentValues cv = new ContentValues();
cv.put("title", "new value");
int rows = db.update(Task.TABLE, cv, "id = ?", new String[] {String.valueOf(yourid)})

The 3d argument is the WHERE clause of the update statement and ? is a placeholder for which you need to provide a value in the 4th argument.
So the statement is equivalent to:

UPDATE <Task.TABLE> SET title = 'new value' WHERE id = <yourid>

In the variable rows the method update() returns the number of rows that where updated.
This method is sql-injection safe.

answered Jul 24 '19 at 15:22

forpas

160,666
10
38
76

In your example I need to to know the new value to replace the old value. But in my case the new value is build based on the old value by replacing the string. So I do not understand how could I use it in my case. It is only way to query cursor, proces a data in the java code, and use db.update to update the db, but it is overkilling. – Oleksandr Albul Jul 24 '19 at 16:45
*it is overkilling* maybe, but your question is about safety, right? You have to decide what you prefer. – forpas Jul 24 '19 at 16:49

Gabe Sechan · Accepted Answer · 2019-07-25T05:35:26.067

2

Use bind variables. Simply place a ? where you have a variable, and pass an array of parmaeters, in order, to the rawQuery function.

You should NEVER use concatenation like you did above. Use bind variables everywhere. Anything user input should be in the bind variable, anything constant should be in the query. That eliminates all possible SQL injection.

edited Jul 25 '19 at 05:35

answered Jul 24 '19 at 15:27

Gabe Sechan

90,003
9
87
127

db.execSQL("UPDATE " + Task.TABLE +" title = REPLACE(title, '?', '?');", new Object[]{fromTagName, toTagName}); Something like this ? – Oleksandr Albul Jul 24 '19 at 16:48
@OleksandrAlbul for this overload of execSql() the documentation: https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL(java.lang.String,%2520java.lang.Object%5B%5D) is clear: *Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE* – forpas Jul 24 '19 at 22:38
@OleksandrAlbul My bad, as forpas said, you can't use execSQL for query, you need to use rawQuery() instead. But the parameters and format are the same – Gabe Sechan Jul 25 '19 at 05:35
@GabeSechan it is not a query, it is UPDATE statement. Thanks I have rewritten it with bind args. – Oleksandr Albul Jul 25 '19 at 05:48
@OleksandrAlbul An update is a query. A query is any command to a db that effects or looks up data. – Gabe Sechan Jul 25 '19 at 05:52

touhid udoy · Answer 3 · 2019-07-24T15:46:49.327

-1

Use DatabaseUtils.sqlEscapeString(stringToEscape) to sanitize user input before inserting into database. Reference here.

edited Jul 24 '19 at 15:46

answered Jul 24 '19 at 15:11

touhid udoy

4,005
2
18
31

It has been drilled into me by others that simply escaping strings is not sufficient and to always used prepared statements and/or parameterized queries. Of course it simply becomes a matter of trust if it is 3rd party code (not native to the DBMS), but the point of questioning such generic methods is whether they fully and properly escape strings for the **specific DBMS**. Does it properly handle alternative string delimiters? What about multi-byte characters? Etc. – C Perkins Jul 24 '19 at 15:29
Its too easy to miss one of these, or to forget to unescape properly. Use bind variables instead. – Gabe Sechan Jul 24 '19 at 15:29
@CPerkins actually, the better phrase would have been "Reference" instead "Read more" thanks – touhid udoy Jul 24 '19 at 15:46

Android SQLite - replace part of a string safely

3 Answers3