CODEWITHSUNDEEP
c++sqlitebindunsigned-integer
g19fanatic
g19fanatic

Reputation: 10941

Binding an 'unsigned long' (uint64) in an sqlite3 statement? C++

I'm using the sqlite3 library that is available at sqlite.org.

I have some unsigned longs that I would like store in a database. I do not want to construct the query myself and leave it open to some sort of injection (whether it be accidental or not). Thus, I'm using the sqlite_bind_* functions to 'sanitize' my parameters.

The issue is that there isn't a function type for unsigned long integers, just integers.

int sqlite3_bind_int(sqlite3_stmt*, int, int);

int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);

I am definitely going to have numbers that will overflow if I am unable to store them in an unsigned manner.

Am I going to need to manage this myself? (i.e. casting to an unsigned type after selecting from the db or casting to signed type before inserting into database)

If I do have to manage this myself, how would one do some comparison queries that are stored as a signed long integer when the comparisons are really meant to be in the unsigned range?

Looking at the INTEGER datatypes that get converted, one would think that unsigned longs could be represented without issue.

If there are other solutions available, please enlighten me! Thanks!

Upvotes: 19

Views: 29504

Answers (6)

Nicol Bolas
Nicol Bolas

Reputation: 473577

An SQLite database does not have the ability to store unsigned 64-bit integers in it. It's just a limitation of the data.

Your options are:

  • Store it as a string, converting as needed.
  • Store it as a binary blob, converting as needed.
  • Pretend that it is a signed 64-bit integer with a cast, thus converting as necessary.
  • Store two pieces of information as two columns: the unsigned 63-bit integer (the lower 63-bits), and a value that represents the high bit.

Since these are hashes, you probably don't care about comparisons other than equality testing. So most of these methods would work just fine for you.

Upvotes: 16

IUnknown
IUnknown

Reputation: 659

I tried a number of different approaches, mainly trying to get the column type to work as a sqlite3_uint64 as explained at https://www.sqlite.org/c3ref/int64.html

Had no luck as the uint64 was not stored as a uint64 in the table. When I looked at the stored data it was signed, so I assumed it was stored as int64.

I ended up prefixing the value with a single char to force sqlite to store it as text without any conversion taking place. It was much easier to just add the char to the select query value for comparison. Thus 9223360144090060712 became A9223360144090060712, and I could simply do a SELECT WHERE HASH = 'A9223360144090060712'

Upvotes: 1

obmarg
obmarg

Reputation: 9569

If you wanted to store a uint64_t in a sqlite database and still allow it to be used as a uint64_t in SQL, then you're probably going to need to write some custom functions.

You simply need to cast the uint64_t to an int64_t when sending to and from the database, and then write a custom function to perform whatever comparison etc. you need. For example, to do a greater than comparison:

void greaterThan( sqlite3_context* ctx, sqlite3_value** values )
{
    uint64_t value1 = boost::numeric_cast< uint64_t >( sqlite3_value_int64( value[ 0 ] ) );
    uint64_t value2 = boost::numeric_cast< uint64_t >( sqlite3_value_int64( value[ 1 ] ) );
    sqlite3_result_int( ctx, value1 > value2 );
}

//Register this function using sqlite3_create_function
sqlite3_create_function( db, "UINT_GT", 2, SQLITE3_ANY, NULL, &greaterThan, NULL, NULL );

Then to use this in SQL:

SELECT something FROM aTable WHERE UINT_GT(value1,value2);

Alternatively, if you need custom collation based on uint64_t, you should be able to use sqlite3_create_collation in a similar manner.

It's not a perfect solution as you'll need to write a custom function for every operation you want to do, but it should at least work.

Upvotes: 13

obmarg
obmarg

Reputation: 9569

Personally, I've found that the easiest way to deal with this problem while still allowing the database to perform comparisons etc. is to store unsigned integers in the database using the sqlite3_bind_int64 function. I'm assuming that you are using 32 bit unsigned integers, which can easily be stored in a 64 bit signed integer.

For example:

uint32_t x = 0xFFFFFFFF;
sqlite3_bind_int64( stmt, 1, x );

Then to retrieve your variable (assuming you're using boost, otherwise you'll have to write your own overflow check code):

 #include <boost/numeric/conversion/cast.hpp>
 ...
 uint32_t x = boost::numeric_cast< uint32_t >( sqlite3_column_int64( stmt, 0 ) );

Of course this falls apart if you want to store unsigned int64s, but that's a bit of a limitation of sqlite.

Upvotes: 0

Tom Kerr
Tom Kerr

Reputation: 10720

uint32_t fits entirely inside of a int64_t without any worries, so you can do a simple assignment.

    int64_t i64;
    uint32_t u32 = 32;
    i64 = u32;

Assigning the other way you should check the bounds of the int64_t so that any changes made to the value inside of the database are caught early.

    int64_t i64 = 32;
    uint32_t u32;
    if (i64 < 0 || i64 > std::numeric_limits<uint32_t>::max())
        throw std::runtime_error("invalid uint32_t");
    u32 = i64;

You can then send your int64 to the sqlite database as you normally would.

Upvotes: 1

111111
111111

Reputation: 16158

it is a hack but int and unsigned have the same bit depth so you could 

int i=static_cast<int>(unsigned_int);

and convert it back

unsigned unsigned_int=static_cast<unsigned>(i);

to be on the safe side on your plaftform

static_assert(sizeof(int)==sizeof(unsigned));

or use the width specific

#include <stdint.h>
uint32_t
int32_t

Upvotes: 0

Related Questions