SQLite query throwing exception in Android

Question

Im trying to get an object form my SQLite database in my Android application. I know that the contact is in the database because when I add it and try finding by ID, it returns the object. When I try finding by first and last name, though, it throws an exception and Im left with a null reference. Here is the query:

cursor = mDb.query(DB_CONTACTS_NAME,
    new String[] {KEY_ID, KEY_LAST_NAME, KEY_FIRST_NAME, KEY_EMAIL_ADDRESS,
                 KEY_ORGANIZATION_NAME, KEY_LABEL, KEY_LABEL_POSITION},
    KEY_FIRST_NAME + "=" + first + " AND " + KEY_LAST_NAME + "=" + last,
    null, null, null, null
);

Ive also tried to find just by first name and just by last name. Same result as above.

Edit: The exception is an SQLite exception

no such column: aurelius: , while compiling: SELECT _id, last_name, first_name,
email_address, organization_name, label, label_position FROM contacts WHERE
first_name=marcus AND last_name=aurelius

And also happens when trying to select just first or last name

no such column: marcus: , while compiling: SELECT _id, last_name, first_name,
email_address, organization_name, label, label_position FROM contacts WHERE
first_name=marcus

Alrighty, just edited the question to clarify on that. Thanks for any help ahead of time. — Omar Estrella
– Omar Estrella, Commented Jul 29, 2010 at 2:38

Akusete · Accepted Answer · 2010-07-29 03:25:12Z

3

You need to quote the literal strings. This should work:

cursor = mDb.query(DB_CONTACTS_NAME,
    new String[] {KEY_ID, KEY_LAST_NAME, KEY_FIRST_NAME, KEY_EMAIL_ADDRESS,
                 KEY_ORGANIZATION_NAME, KEY_LABEL, KEY_LABEL_POSITION},
    KEY_FIRST_NAME + "='" + first + "' AND " + KEY_LAST_NAME + "='" + last + "'",
    null, null, null, null
);

Otherwise when the SQL interpreter see's

first_name=marcus

You cannot expect it to know that 'marcus' is not a table name or a variable.

On a side note try searching for the last name "Billy" "); drop table contacts;"

DO NOT CONSTRUCT SQL QUERIES FROM DATA STRINGS

Instead use a query which takes in an argument list. Search the docs for something like

String[] myArguments = new String[] {first, last};
cursor = mDb.query("SELECT * FROM contacts WHERE first_name = ? AND last_name = ?;", myArguments);

This is much safer. In your version if the 'first' or 'last' variables contain control characters which SQLite interprets as commands then you may break your database system. DO NOT DO IT ... EVER

edited Jul 29, 2010 at 3:25

answered Jul 29, 2010 at 3:14

Akusete

10.8k7 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Omar Estrella Over a year ago

You sir, are correct. I never thought I would be the one to make that mistake after I read that comic a while back, but alas, I did.

charles young · Accepted Answer · 2011-03-20 21:09:28Z

0

Yes, this is the answer! I wish I had the points to vote this up! Specifically, the strings of the WHERE clause have to have the single quotes around them:

KEY_FIRST_NAME + "='" + first + "' AND " + KEY_LAST_NAME + "='" + last + "'"

answered Mar 20, 2011 at 21:09

charles young

2,2892 gold badges23 silver badges40 bronze badges

Collectives™ on Stack Overflow

SQLite query throwing exception in Android

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related