Android quotes within an sql query string

Question

I want to perform a query like the following:

uvalue = EditText( some user value );
p_query = "select * from mytable where name_field = '" +  uvalue + "'" ;
mDb.rawQuery( p_query, null );

if the user enters a single quote in their input it crashes. If you change it to:

p_query = "select * from mytable where name_field = \"" +  uvalue + "\"" ;

it crashes if the user enters a double quote in their input. and of course they could always enter both single and double quotes.

Answer 1

You should make use of the rawQuery method's selectionArgs parameter:

p_query = "select * from mytable where name_field = ?";
mDb.rawQuery(p_query, new String[] { uvalue });

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

Answer 2

DatabaseUtils.sqlEscapeString worked properly for me. The string is enclosed by single quotes and the single quotes inside the string become double quotes. Tried using selectionArgs in the getContentResolver().query() but it didn't work at all.

Answer 3

You should change

p_query = "select * from mytable where name_field = '" +  uvalue + "'" ;

like

p_query = "select * from mytable where name_field = '" + android.database.DatabaseUtils.sqlEscapeString(uvalue)+ "'" ;