Cannot start task on GSA with GVM 10. Error: sql_x_internal: sql_prepare failed

[fcolista]

Expected behavior

Running a task on GSA, the task is completed

Current behavior

Task does not run.

Steps to reproduce

I've made an How-To which describes the steps to install GVM on Alpine:
https://wiki.alpinelinux.org/wiki/Setting_up_GVM10
Basically, install the packages, run GSA (with or without signature is the same), create a task.
Task does not run.

GVM versions

gsa: (gsad --version)
Greenbone Security Assistant 8.0.0

gvm: (gvmd --version)
Greenbone Vulnerability Manager 8.0.0
Manager DB revision 205

openvas-scanner: (openvassd --version)
OpenVAS Scanner 6.0.0

gvm-libs:
gvm-libs-10.0.0

openvas-smb:
Not used

Environment

Operating system:
Alpine Linux edge on x86_64 arch.

Installation method / source: (packages, source installation)
Packages

I get these errors when I start a task on gsa:

md manage:WARNING:2019-04-09 19h36.51 UTC:2469: sql_prepare_internal: sqlite3_prepare failed: not an error
md manage:WARNING:2019-04-09 19h36.51 UTC:2469: init_iterator: sql_prepare failed

This happens with the following packages installed:

sqlite-3.27.2
gvm-libs-10.0.0
gvmd-8.0.0
openvas-scanner-6.0.0
greenbone-security-assistant-8.0.0

What I've noticed (perhaps is linked to this problem), is that importing the nvt i got tons of these WARNING:

md manage:WARNING:2019-04-09 19h27.24 utc:1035: parse_ctime: Failed to parse time '2018-02-27T19:15:33.110Z'

Please note that Alpine doesn't use /etc/TZ to set local time, rather tzdata

According with gvmd logs, seems that somehow I'm hitting a limit:

md manage:WARNING:2019-04-09 19h59.02 UTC:4052: sql_prepare_internal: sqlite3_prepare failed: no more rows available
md manage:WARNING:2019-04-09 19h59.02 UTC:4052: sql_x_internal: sql_prepare failed
md manage:WARNING:2019-04-09 19h59.02 UTC:4052: sql_close: attempt to close db with open statement(s)

No more rows available.
But this is impossible, since the limit is 140Terabyte and SQLite documentation states:

Maximum Number Of Rows In A Table

The theoretical maximum number of rows in a table is 264 (18446744073709551616 or about 1.8e+19). This limit is unreachable since the maximum database size of 140 terabytes will be reached first. A 140 terabytes database can hold no more than approximately 1e+13 rows, and then only if there are no indices and if each row contains very little data.

SQlite on Alpine is built with the following options:

-DSQLITE_ENABLE_FTS4 \
	-DSQLITE_ENABLE_FTS3_PARENTHESIS \
	-DSQLITE_ENABLE_FTS3 \
	-DSQLITE_ENABLE_FTS5 \
	-DSQLITE_ENABLE_COLUMN_METADATA \
	-DSQLITE_SECURE_DELETE \
	-DSQLITE_ENABLE_UNLOCK_NOTIFY \
	-DSQLITE_ENABLE_RTREE \
	-DSQLITE_ENABLE_GEOPOLY \
	-DSQLITE_USE_URI \
	-DSQLITE_ENABLE_DBSTAT_VTAB \
	-DSQLITE_MAX_VARIABLE_NUMBER=250000 \
	-DSQLITE_ENABLE_JSON1"

I've not tested with PostgreSQL as yet.
Also appears to be impossible to build gvmd supporting both postgres and sqlite since -DBACKEND takes only SQLITE3 or POSTGRESQL.

.: Francesco

[0x4c-code]

I'm experiencing the same problem. I'm running alpine edge in a docker container with the following package versions installed:

sqlite-3.29.0
gvmd-8.0.1
openvas-scanner-6.0.1
greenbone-security-assistant-8.0.1

[fcolista]

This issue is opened since 4 months and nobody answered?

[b40yd]

@fcolista @0x4c-code
this problem, you need change sql "%" to "%%", it's init_otp_pref_iterator function in manage_sql.c

test code

#include <glib.h>
#include <stdio.h>

// use centos or other linux
/*
void
test_glib(const char *format, ...)
{
    gchar *formatted = NULL;
    va_list args;
    va_start (args, format);

    formatted = g_strdup_vprintf(format, args);
    printf("format: %d %s\n", len, formatted);
    va_end(args);
}
*/
// use vasprintf 
void
test_vasprintf(const char *format, ...)
{
    gint len;
    gchar *formatted = NULL;
    va_list args;
    va_start (args, format);
    len = vasprintf (&formatted, format, args);
    if (len < 0)
        formatted = NULL;
    printf("format: %d %s\n", len, formatted);
    va_end(args);
}

void 
main(void)
{
    long long int config = 5;
    gchar *t1 = "SERVER_PREFS";
    gchar *t2 = "NOT LIKE '%[%]%'";
    
    /**
        simale test
    **/
    //test_glib("%llu, %s, %s, %llu\n", config, t1, t2, config);
    test_vasprintf("%llu, %s, %s, %llu\n", config, t1, t2, config);
    
    /*
        origin sql code
        results are different on alpine linux and centos
    */
    const char *format = "SELECT config_preferences.name, config_preferences.value"
         " FROM config_preferences, nvt_preferences"
         " WHERE config_preferences.config = %llu"
         " AND config_preferences.type = '%s'"
         " AND (config_preferences.name = nvt_preferences.name"
         "      OR config_preferences.name LIKE 'timeout.%')"
         " AND config_preferences.name != 'max_checks'"
         " AND config_preferences.name != 'max_hosts'"            
         " UNION"                                                       
         " SELECT nvt_preferences.name, nvt_preferences.value"
         " FROM nvt_preferences"
         " WHERE nvt_preferences.name %s"
         " AND (SELECT COUNT(*) FROM config_preferences"
         "      WHERE config = %llu"
         "      AND config_preferences.name = nvt_preferences.name) = 0;\n\n";
    
    //test_glib(format, config, t1, t2, config);
    
    test_vasprintf(format, config, t1, t2, config); // It' ok on centos result, But it turns out to be null on alpine linux
    
    /*
        change 'timeout.%' to 'timeout.%%'
    */
    
    const char *format1 = "SELECT config_preferences.name, config_preferences.value"
         " FROM config_preferences, nvt_preferences"
         " WHERE config_preferences.config = %llu"
         " AND config_preferences.type = '%s'"
         " AND (config_preferences.name = nvt_preferences.name"
         "      OR config_preferences.name LIKE 'timeout.%%')"
         " AND config_preferences.name != 'max_checks'"
         " AND config_preferences.name != 'max_hosts'"            
         " UNION"                                                       
         " SELECT nvt_preferences.name, nvt_preferences.value"
         " FROM nvt_preferences"
         " WHERE nvt_preferences.name %s"
         " AND (SELECT COUNT(*) FROM config_preferences"
         "      WHERE config = %llu"
         "      AND config_preferences.name = nvt_preferences.name) = 0;\n\n";
    //test_glib(format1, config, t1, t2, config);
    
    test_vasprintf(format1, config, t1, t2, config);// same result, on centos and alpine linux
    
}

compiling:
gcc $(pkg-config --cflags --libs glib-2.0) -o test test.c

[fcolista]

Thanks, I've applied the patch, the first task started, the second one remain stucked at 1% with the following errors in the log:

md manage:WARNING:2019-09-30 12h10.26 UTC:9633: sql_prepare_internal: sqlite3_prepare failed: no more rows available
md manage:WARNING:2019-09-30 12h10.26 UTC:9633: sql_x_internal: sql_prepare failed
md manage:WARNING:2019-09-30 12h10.26 UTC:9633: sql_close: attempt to close db with open statement(s)

This happens also when trying to open the report from the 1st completed target scan.
Thanks.

[b40yd]

@fcolista
This same problem.
patch

[beastie29a]

@fcolista, on this issue:

What I've noticed (perhaps is linked to this problem), is that importing the nvt i got tons of these WARNING:

md manage:WARNING:2019-04-09 19h27.24 utc:1035: parse_ctime: Failed to parse time '2018-02-27T19:15:33.110Z'

This has to do with the difference in Musl-lib and Gliibc's strptime. I've opened a merge request on the aports repo and wanted to let the upstream (Greenbone) as well as any others having the same issue know about it.
https://gitlab.alpinelinux.org/alpine/aports/merge_requests/4427
Similar issue with gvm-libs:
https://gitlab.alpinelinux.org/alpine/aports/merge_requests/4426

[bjoernricks]

sqlite support has been dropped some versions ago. therefore closing this issue.