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.

miannelle, check out these related questions: http://stackoverflow.com/questions/29699/how-do-i-deal-with-quotes-in-sql http://stackoverflow.com/questions/387198/escape-double-quotes-in-sql-2005-2008 — Nick Dandoulakis, Aug 18 '09 at 20:27
miannelle: You should look at marking some of your questions as "answered" — Kurru, Jun 22 '11 at 13:27

score 130 · Answer 1 · edited Dec 16 '17 at 19:47

130

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.

edited Dec 16 '17 at 19:47

Socowi

25,550
3
32
54

answered Aug 18 '09 at 20:51

Josef Pfleger

74,165
16
97
99

6

I wish I could upmod this more than once! – jrockway Aug 18 '09 at 20:52
1

It is also much faster because Sqlite doesn't have to parse every sql statement. – tuinstoel Aug 19 '09 at 20:33
20

Indeed, you should use query arguments. However, you can also use the DatabaseUtils to escape strings, if needed. For instance, DatabaseUtils.sqlEscapeString(). It's not the best way, but it's available. Could be useful if you're trying to pass a raw SQL statement over to something external. – lilbyrdie Aug 22 '09 at 15:50
1

@lilbyrdie: thanks! As one can't bind vectors of strings for use with the IN operator, sqlEscapeSting() was just what I needed! – Steve Pomeroy Jan 13 '11 at 22:33
This doesn't work as well as you'd think. If you use a string like this it breaks: "sample " string ' that breaks stuff" – Christopher Perry Aug 24 '12 at 03:20
Using 'selectionArgs' parameter doesn't work when the question mark goes within a 'LIKE' string (for example: SELECT * FROM Table WHERE column LIKE '%?%'). In this case, you have to use string concatenation and DatabaseUtils.sqlEscapeString is a good practice in order to avoid SQL injection. – caligari Sep 05 '13 at 11:11

score 14 · Answer 2 · answered Apr 16 '10 at 12:40

14

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.

answered Apr 16 '10 at 12:40

Nikhil

149
1
2

The `DatabaseUtils.sqlEscapeString` replaces one or two special characters where the need is to replace all special characters, isn't there any way to replace specified or all special characters? – blueware Mar 25 '19 at 12:33

score 5 · Answer 3 · answered Jan 30 '17 at 14:01

5

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)+ "'" ;

answered Jan 30 '17 at 14:01

Absar Alam

102
2
9

2

but your need remove quotes because they are added by default – Vlad Jan 14 '18 at 11:23

score 3 · Answer 4 · answered Apr 09 '14 at 15:48

3

I prefer to escape Single quotes and Double quotes in each insert statement with Sqlite like this:

 String sqlite_stament = sqlite_stament.replace("'", "''").replace("\"", "\"\"");

answered Apr 09 '14 at 15:48

Amt87

5,493
4
32
52

score 3 · Answer 5 · answered Jul 20 '11 at 21:27

3

Have you tried replacing a single quote with 2 single quotes? That works for inputting data to a database.

answered Jul 20 '11 at 21:27

gssi

5,043
5
23
23

I would prefer to use the top answer, but in my case it was not possible do to application design. This works too – Patrick Jackson Feb 27 '12 at 17:42

score 0 · Answer 6 · answered Oct 19 '12 at 07:36

0

I have same problem but now it is solved by just writing the code like in your case you want to insert value uvalue .Then write as

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

cool..!!

answered Oct 19 '12 at 07:36

Nency

482
1
8
21

2

that's stupid, you're replacing ' with '' which will still causes issues. – stealthcopter Jul 19 '13 at 10:34
@stealthcopter That's not stupid, the standard SQL way of escaping a single quote in a string literal is to double it so `''''` is an SQL string literal which contains one single quote. Manually escaping strings for SQL may not be the best idea but the result is correct in this case. – mu is too short Dec 16 '17 at 20:06

Android quotes within an sql query string

6 Answers6

Linked