Error on cursor query on sqlite database

Question

I am trying to get column info from a cursor, and getting a frustrating error. I want to select all columns containing $SEARCH. Here is the code:

Bundle b = getIntent().getExtras();
        SEARCH = b.getString("searchtext");
        Cursor c = mDBHelper.getReadableDatabase().query("table", null, "name="+ SEARCH, null, null, null, null);

For some reason, the cursor is throwing a runtime exception. here is the error:

12-30 03:55:00.357: E/AndroidRuntime(1302): Caused by: android.database.sqlite.SQLiteException: near "CAP": syntax error: , while compiling: SELECT * FROM kroger WHERE name=john

Not sure why this is happening, there's probably a very simple error in my code, but I'm not sure what it is. THanks for your help!

Marcelo Cantos · Accepted Answer · 2011-12-30 04:13:57Z

3

"name="+SEARCH will become name=CAP, which isn't a valid SQL expression. The naïve solution is to wrap the term in single-quotes:

"name='" + SEARCH + "'"

But this is subject to SQL injection attacks. Use the argument-passing facilities to pass the search term in an injection-free manner:

query("table", null, "name=?", new String[] {SEARCH}, null, null, null);

answered Dec 30, 2011 at 4:13

Marcelo Cantos

187k40 gold badges338 silver badges366 bronze badges

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

7 Comments

barry Over a year ago

Why is the first suggestion subject to attack and not the second?

Marcelo Cantos Over a year ago

@barry: Say the user searches for CAP'; DROP TABLE table; --. With the first solution, this would become the following SQL: SELECT * FROM table where name='CAP'; DROP TABLE table; --'. With the second solution, the SQL remains SELECT * FROM table WHERE name=?, with the ? bound to the bogus search term as a string value, semantically, not syntactically, so the search would fail in a benign manner.

barry Over a year ago

@marcelocantos Thanks for that, very interesting. But wouldn't the second version eventually result in the same sql as the first?

Marcelo Cantos Over a year ago

@barry: No. The variable bindings are applied after the SQL has been parsed. The ? is a placeholder for a value, not a chunk of SQL.

Marcelo Cantos Over a year ago

@barry: It's an internal representation that differs from one engine to the next. Some systems (e.g., SQL Server) convert to an AST, others (Oracle, I think) go straight to an execution tree. I don't know what SQLite does, but suspect it takes a relatively directly path to p-code suitable for its internal VM. The point is that by the time the bindings get applied the original SQL syntax is long gone, so it is meaningless to talk about what the query ends up looking like after variables are bound.

|

Collectives™ on Stack Overflow

Error on cursor query on sqlite database

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related