Scalar Functions

Although the sqlite3_limit() function makes a good example, it might not be the kind of thing you’d want to expose to the SQL language in a real-world application. In practice, exposing this C API call to the SQL level brings up some security concerns. Anyone that can issue arbitrary SQL calls would have the capability of altering the SQLite soft limits. This could be used for some types of denial-of-service attacks by raising or lowering the limits to their extremes.

sql_set_limit

In order to call the sqlite3_limit() function, we need to determine the limit_type and value parameters. This will require an SQL function that takes two parameters. The first parameter will be the limit type, expressed as a text constant. The second parameter will be the new limit. The SQL function can be called like this to set a new expression-depth limit:

SELECT sql_limit( 'EXPR_DEPTH', 400 );

The C function that implements the SQL function sql_limit() has four main parts. The first task is to verify that the first SQL function parameter (passed in as values[0]) is a text value. If it is, the function extracts the text to the str pointer:

static void sql_set_limit( sqlite3_context *ctx, int
                                                 num_values, sqlite3_value **values )
{
    sqlite3      *db = sqlite3_context_db_handle( ctx );
    const char   *str = NULL;
    int           limit = -1, val = -1, result = -1;

    /* verify the first param is a string and extract pointer */
    if ( sqlite3_value_type( values[0] ) == SQLITE_TEXT ) {
        str = (const char*) sqlite3_value_text( values[0] );
    } else {
        sqlite3_result_error( ctx, "sql_limit(): wrong parameter type", -1 );
        return;
    }

Next, the function verifies that the second SQL parameter (values[1]) is an integer value, and extracts it into the val variable:

    /* verify the second parameter is an integer and extract value */
    if ( sqlite3_value_type( values[1] ) == SQLITE_INTEGER ) {
        val = sqlite3_value_int( values[1] );
    } else {
        sqlite3_result_error( ctx, "sql_limit(): wrong parameter type", -1 );
        return;
    }

Although our SQL function uses a text value to indicate which limit we would like to modify, the C function sqlite3_limit() requires a predefined integer value. We need to decode the str text value into an integer limit value. I’ll show the code to decode_limit_str() in just a bit:

    /* translate string into integer limit */
    limit = decode_limit_str( str );
    if ( limit == -1 ) {
        sqlite3_result_error( ctx, "sql_limit(): unknown limit type", -1 );
        return;
    }

After verifying our two SQL function parameters, extracting their values, and translating the text limit indicator into a proper integer value, we finally call sqlite3_limit(). The result is set as the result value of the SQL function and the function returns:

    /* call sqlite3_limit(), return result */
    result = sqlite3_limit( db, limit, val );
    sqlite3_result_int( ctx, result );
    return;
}

The decode_limit_str() function is very simple, and simply looks for a predefined set of text values:

int decode_limit_str( const char *str )
{
    if ( str == NULL ) return -1;    
    if ( !strcmp( str, "LENGTH"          ) ) return SQLITE_LIMIT_LENGTH;
    if ( !strcmp( str, "SQL_LENGTH"      ) ) return SQLITE_LIMIT_SQL_LENGTH;
    if ( !strcmp( str, "COLUMN"          ) ) return SQLITE_LIMIT_COLUMN;
    if ( !strcmp( str, "EXPR_DEPTH"      ) ) return SQLITE_LIMIT_EXPR_DEPTH;
    if ( !strcmp( str, "COMPOUND_SELECT" ) ) return SQLITE_LIMIT_COMPOUND_SELECT;
    if ( !strcmp( str, "VDBE_OP"         ) ) return SQLITE_LIMIT_VDBE_OP;
    if ( !strcmp( str, "FUNCTION_ARG"    ) ) return SQLITE_LIMIT_FUNCTION_ARG;
    if ( !strcmp( str, "ATTACHED"        ) ) return SQLITE_LIMIT_ATTACHED;
    if ( !strcmp( str, "LIKE_LENGTH"     ) ) return SQLITE_LIMIT_LIKE_PATTERN_LENGTH;
    if ( !strcmp( str, "VARIABLE_NUMBER" ) ) return SQLITE_LIMIT_VARIABLE_NUMBER;
    if ( !strcmp( str, "TRIGGER_DEPTH"   ) ) return SQLITE_LIMIT_TRIGGER_DEPTH;
    return -1;
}

With these two functions in place, we can create the sql_limit() SQL function by registering the sql_set_limit() C function pointer.

sqlite3_create_function( db, "sql_limit", 2, SQLITE_UTF8, 
                         NULL, sql_set_limit, NULL, NULL );

The parameters for this function include the database connection (db), the name of the SQL function (sql_limit), the required number of parameters (2), the expected text encoding (UTF-8), the user-data pointer (NULL), and finally the C function pointer that implements this function (sql_set_limit). The last two parameters are only used when creating aggregate functions, and are set to NULL.

Once the SQL function has been created, we can now manipulate the limits of our SQLite environment by issuing SQL commands. Here are some examples of what the sql_limit() SQL function might look like if we integrated it into the sqlite3 tool (we’ll see how to do this using a loadable extension later in the chapter).

First, we can look up the current COLUMN limit by passing a new limit value of -1:

sqlite> SELECT sql_limit( 'COLUMN', -1 );
2000

We verify the function works correctly by setting the maximum column limit to two, and then generating a result with three columns. The previous limit value is returned when we set the new value:

sqlite> SELECT sql_limit( 'COLUMN', 2 );
2000
sqlite> SELECT 1, 2, 3;
Error: too many columns in result set

We see from the error that the soft limit is correctly set, meaning our function is working.

One thing you might be wondering about is parameter value count. Although the sql_set_limit() function carefully checks the types of the parameters, it doesn’t actually verify that num_values is equal to two. In this case, it doesn’t have to, since it was registered with sqlite3_create_function() with a required parameter count of two. SQLite will not even call our sql_set_limit() function unless we have exactly two parameters:

sqlite> SELECT sql_limit( 'COLUMN', 2000, 'extra' );
Error: wrong number of arguments to function sql_limit()

SQLite sees the wrong number of parameters and generates an error for us. This means that as long as a function is registered correctly, SQLite will do some of our value checking for us.

sql_get_limit

While having a fixed parameter count simplifies the verification code, it might be useful to provide a single-parameter version that can be used to look up the current value. This can be done a few different ways. First, we can define a second C function called sql_get_limit(). This function would be the same as sql_set_limit(), but with the second block of code removed:

    /* remove this block of code from a copy of   */
    /* sql_set_limit() to produce sql_get_limit() */
    if ( sqlite3_value_type( values[1] ) == SQLITE_INTEGER ) {
        val = sqlite3_value_int( values[1] );
    } else {
        sqlite3_result_error( ctx, "sql_limit(): wrong parameter type", -1 );
        return;
    }

With this code removed, the function will never decode the second SQL function parameter. Since val is initialized to –1, this effectively makes every call a query call. We register each of these functions separately:

sqlite3_create_function( db, "sql_limit", 1,
        SQLITE_UTF8, NULL, sql_get_limit, NULL, NULL );
sqlite3_create_function( db, "sql_limit", 2,
        SQLITE_UTF8, NULL, sql_set_limit, NULL, NULL );

This dual registration overloads the SQL function name sql_limit(). Overloading is allowed because the two calls to sqlite3_create_function() have a different number of required parameters. If the SQL function sql_limit() is called with one parameter, then the C function sql_get_limit() is called. If two parameters are provided to the SQL function, then the C function sql_set_limit() is called.

sql_getset_limit

Although the two C functions sql_get_limit() and sql_set_limit() provide the correct functionality, the majority of their code is the same. Rather than having two functions, it might be simpler to combine these two functions into one function that can deal with either one or two parameters, and is capable of both getting or setting a limit value.

This combine sql_getset_limit() function can be created by taking the original sql_set_limit() function and modifying the second section. Rather than eliminating it, as we did to create sql_get_limit(), we’ll simply wrap it in an if statement, so the second section (which extracts the second SQL function parameter) is only run if we have two parameters:

    /* verify the second parameter is an integer and extract value */
    if ( num_values == 2 ) {
        if ( sqlite3_value_type( values[1] ) == SQLITE_INTEGER ) {
            val = sqlite3_value_int( values[1] );
        } else {
            sqlite3_result_error( ctx, "sql_limit(): wrong parameter type", -1 );
            return;
        }
    }

We register the same sql_getset_limit() C function under both parameter counts:

sqlite3_create_function( db, "sql_limit", 1,
        SQLITE_UTF8, NULL, sql_getset_limit, NULL, NULL );
sqlite3_create_function( db, "sql_limit", 2,
        SQLITE_UTF8, NULL, sql_getset_limit, NULL, NULL );

For this specific task, this is likely the best choice. SQLite will verify the SQL function sql_limit() has exactly one or two parameters before calling our C function, which can easily deal with either one of those two cases.

sql_getset_var_limit

If for some reason you don’t like the idea of registering the same function twice, we could also have SQLite ignore the parameter count and call our function no matter what. This leaves verification of a valid parameter count up to us. To do that, we’d start with the sql_getset_limit() function and change it to sql_getset_var_limit(), by adding this block at the top of the function:

    if ( ( num_values < 1 )||( num_values > 2 ) ) {
        sqlite3_result_error( ctx, "sql_limit(): bad parameter count", -1 );
        return;
    }

We register just one version. By passing a required parameter count of -1, we’re telling the SQLite engine that we’re willing to accept any number of parameters:

sqlite3_create_function( db, "sql_limit", -1, SQLITE_UTF8,
        NULL, sql_getset_var_limit, NULL, NULL );

Although this works, the sql_getset_limit() version is still my preferred version. The registration makes it clear which versions of the function are considered valid, and the function code is reasonably clear and compact.

Completely free-form parameter counts are usually used by items like the built-in function coalesce(). The coalesce() function will take any number of parameters (greater than one) and return the first non-NULL value in the list. Since you might pass anywhere from two to a dozen or more parameters, it is impractical to register each possible configuration, and is better to just allow the function to do its own parameter management.

On the other hand, something like sql_getset_limit() can really only accept two configurations: one parameter or two. In that case, I find it easier to explicitly register both parameter counts and allow SQLite to do my parameter verification for me.