SELECT:ing a row with a id blob using a parameterized query

Question

I have a sqlite database where all rows have a UUID as the primary key, the db column is defined as a BLOB.

The keys are inserted as byte[] instead of Strings to avoid wasting storage and index spaces. I can insert, update and delete rows using SQLiteDatabase.compileStatement and using bindBlob on the SQLiteStatement but I can't find anyway to bind a blob to the parameters of a SELECT query.

Both SQLiteDatabase.query and .rawQuery expects my WHERE arguments to be Strings which will never match my byte array blobs. I can find my row if I construct my WHERE manually using a BLOB literal like this:

final Cursor query = db.query(getTableName(),
                              getColumns(),
                              "id = X'" + bytesToHex(getByteArrayFromUUID(id)) + "'" ,
                              null,
                              null,
                              null,
                              null);

But then I am vulnerable to SQL injections...

In every other language I have used SQLite in this is not a problem, is there really no way to get a standard prepared SELECT statement with android SQLite?

laalto · Accepted Answer · 2014-08-01 13:32:33Z

1

Most of the APIs in Android sqlite expect the parameter to be strings.

You can use compileStatement() to prepare your query and then use bindBlob() to bind a blob argument to it. Getting useful results out from the query is not easy though, SQLiteStatement has methods for only a few 1x1 result sets.

On the other hand, using a blob as a key doesn't seem like a good idea.

edited Aug 1, 2014 at 13:32

answered Aug 1, 2014 at 13:26

laalto

153k68 gold badges293 silver badges306 bronze badges

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

2 Comments

Mattias Over a year ago

compileStatement() does not return a response / cursor so it can't be used to execute SELECT queries.

Mattias Over a year ago

"On the other hand, using a blob as a key doesn't seem like a good idea." The client have to generate a unique ID without talking to the server so we can't use a INT id, instead we use a UUID. We can store the UUID as TEXT but that will just waste space since the binary BLOB representation only occupies 16 bytes.

Collectives™ on Stack Overflow

SELECT:ing a row with a id blob using a parameterized query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related