What is the correct syntax for the SELECT statement and the WHERE clause?

advertisements

I am trying to explore android and I just started using SQLite database. I'm wondering on what is the right syntax for selecting a single row from a table, where the row I want to select is from the value entered from a user using editText. Thanks in advance.


I'm going to disagree with both of the answers above. What if the user enters this query:

Bobby Tables'; drop table yourTable;

See: http://xkcd.com/327/

I believe you should do this instead:

String query = "select * from TABLE_NAME WHERE column_name=?";
String[] selection = new String[1];
selection[0] = users_entered_value;
Cursor c = db.rawQuery(query, selection);

ETA: Actually, the more I think about it, the more I think you're going in the wrong direction. If your app depends on a database query returning exactly one unique match to an arbitrary string entered by the user, it's probably going to be broken a great deal of the time.

What you should probably do is something like this:

String query = "select * from TABLE_NAME WHERE column_name LIKE ?";
String[] selection = new String[1];
selection[0] = "%" + users_entered_value + "%";
Cursor c = db.rawQuery(query, selection);

and then iterate through the results and pick a "best" match according to your own criteria.

Also, you should create the table with case-insensitive matching for the column(s) you're going to be searching.