6

I'm making an Android app and I have used an SQLite database. But I found out if you type characters like single quotes ('), (also for using as the primary key) the data won't be saved/retrieved correctly.

Is it a problem with me or is it true? If its true are there any more characters like that?

Thanks.

@bdares and @mu Thanks for the tips, but can you please tell me how to use placeholders and/or prepared statements in SQLite?

I have always used direct String concatenation before but now, as it appears that's a bad practice, I would like to use prepared statements and/or placeholders.

Roshnal
  • 1,284
  • 3
  • 16
  • 39
  • 1
    There's always the official SQLite docs (http://www.sqlite.org/docs.html) but they're more about general issues and the C/C++ interface. A bit of googling for "android sqlite placeholder tutorial" might be fruitful though. – mu is too short Sep 01 '11 at 14:56
  • @mu Thanks again for the tip! I will google it. – Roshnal Sep 01 '11 at 16:17

4 Answers4

4

Possibly you'll have problems with characters like ASCII STOP and such non-printing characters, but if you use prepared statements and parameter binding, you won't have any trouble even with characters like '.

If you don't want to use parameter binding and prepared statements, you can replace all of your input ' with \' and you'll be fine.

SQL typically uses ' as its special character to tell when a string literal starts or stops. If your input has this character, it will stop treating the current line as a string and start treating it as commands. This is not a good thing, security wise. It also keeps you from inputting that character unless you "escape" it by placing a backslash in front of it, which tells SQL to ignore the ' and continue treating the following characters as a string until an unescaped ' is met. Of course, backslash literals are also escaped as double-backslashes.

Prepared statements typically look like this:

String sql = "INSERT INTO MYTABLE (NAME, EMP_NO, DATE_HIRED) VALUES (?, ?, ?)";
PreparedStatement ps = sqlite.prepareStatement(sql);
ps.setString(1, myString);
ps.setInt(2, myInt);
ps.setDate(3, myDate);
ps.executeUpdate();

Unfortunately, I don't know exactly what library you'd be using to access sqlite from Android, so I can't give you more details at this time.

  • Thank you for the answer. It worked nicely. So only the `'` character should be "escaped" (in a typical instance-- say, like a to-do task) ? – Roshnal Sep 01 '11 at 09:52
  • @Roshnal: In a typical instance you should use prepared statements with placeholders, then bind values to the placeholders, and let the libraries worry about what needs to be escaped and how it needs to be done. Avoid building complete SQL statements with string concatenation, it looks quick and easy until it blows up and leaves a mess all over the place. – mu is too short Sep 01 '11 at 10:55
  • @mu Thanks for the warning :) But where can I find more information about prepared statements and placeholders? As I'm new to SQLite, I don't have much knowledge about this. So if you can direct me to some link (or just post it here), It will really help me. – Roshnal Sep 01 '11 at 14:44
  • @Roshnal, maybe googling or readin the PreparedStatement javadocs might be a start. – mP. Sep 02 '11 at 01:09
  • @bdares Thank you very much for the code! It really helped me. But what do you mean by "library" that I'm using? I just use the built in functions for Android. So I should use a library too? – Roshnal Sep 02 '11 at 08:01
  • @Roshnal sorry, I never actually used Android so I don't know exactly what api Android offers for interacting with SQLite... but if you can figure out what I mean by the code, you'll be fine. –  Sep 02 '11 at 08:05
  • @bdares Your code worked fine! So I guess its okay for android also. – Roshnal Sep 02 '11 at 08:27
1

SQLite statements use quotes -- single or double -- for strings. If you need to INSERT a string with (') for example, you can use double quotes (") to wrap the string:

INSERT INTO my_table (some_column) VALUES("'a string'");

Or the other way around:

INSERT INTO my_table (some_column) VALUES('"a string"');

(Of course, you will need to escape any (") in your Java code.)

An alternative is to use a SQLiteStatment (Prepared statement) and bindString()

As for the "characters allowed", SQLite internally stores strings (type TEXT) as UTF-8 or UTF-16. Android's build uses the default of UTF-8. Therefor, you can store any string you like.

NuSkooler
  • 5,391
  • 1
  • 34
  • 58
0

SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into on of these fields before saving them in the database. SQLight itself does not validate if the types written to the columns are actually of the defined type, you can write an integer into a string column.

Vineet Shukla
  • 23,865
  • 10
  • 55
  • 63
0

The accepted answer is correct, except that Sqlite escapes the ' character as '' and not as \'

This was taken from this answer

Mikki M
  • 28
  • 4
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/34852400) – dcolazin Aug 22 '23 at 15:52