The Carray() Table-Valued Function

1. Overview

Carray($PTR,$N) is a table-valued function with a single column (named "value") and zero or more rows. The "value" of each row in the carray() is taken from a C-language array that is $N elements long and begins at address $PTR. In this way, the carray() function provides a convenient mechanism to bind C-language arrays to SQL queries.

2. Availability

The carray() function is not compiled into SQLite by default. It is available as a loadable extension in the ext/misc/carray.c source file.

The carray() function is dangerous. The first parameter is a 64-bit integer which gets cast into a pointer to an array. In an application that runs user-generated or untrusted SQL, the carray() function could be used to crash the application or to leak sensitive information. For that reason, the carray() function will never be a standard part of SQLite. Carray() will only be available in applications that deliberately request it. Presumably, applications that deliberately link carray() will also have protections in place to prevent carray() from being misused.

3. Details

The carray() function takes two or three arguments. The first argument is a 64-bit integer that will be cast into a pointer to the C-language array that is to be returned by the function. The second argument is the number of elements in the array. The optional third argument is a string that determines the datatype of the elements in the C-language array. Allowed values for the third argument are:

1. 'int32'
2. 'int64'
3. 'double'
4. 'char*'

The default datatype is 'int32'.

The carray() function can be used in the FROM clause of a query. For example, to query two entries from the OBJ table using rowids taken from a C-language array at address 0x7b3830:

SELECT obj.* FROM obj, carray(0x7b3830, 10) AS x
 WHERE obj.rowid=x.value;

This query gives the same result:

SELECT * FROM obj WHERE rowid IN carray(0x7b3830, 10);