What characters cannot be used for values in SQLite databases?

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.

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.

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.

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.