PQexec failed: ERROR: syntax error at or near "report"

[mettatu]

Expected behavior

Scan ends up with "Done" when using Postgresql as backend with 8.0.1 and a remote scanner.

Current behavior

On master Scan gets stuck at a certain % and the gvmd logs receive "ERROR: syntax error at or near "report"". On the slave scanner the scan is finished.

Steps to reproduce

1. Scan a target
2. See that the remote scanner has finished the scan
3. Check the logs on gvmd

GVM versions

gsa: Greenbone Security Assistant 8.0.1
gvm: Greenbone Vulnerability Manager 8.0.1
openvas-scanner: OpenVAS Scanner 6.0.1
gvm-libs: gvm-libs-10.0.1

Environment

Operating system: Debian 9

Installation method / source: source installation

Logfiles

md manage:WARNING:2019-08-01 14h35.59 UTC:39143: sql_exec_internal: PQexec failed: ERROR:  syntax error at or near "report"
LINE 1: ...e,  description, uuid, qod, qod_type, result_nvt  report) VA...

[mettatu]

At least this needs to be fixed:

diff --git a/src/manage_sql.c b/src/manage_sql.c
index cd10d9ba..58bda6ca 100644
--- a/src/manage_sql.c
+++ b/src/manage_sql.c
@@ -48519,7 +48519,7 @@ buffer_insert (GString *buffer, task_t task, const char* host,
                      "INSERT into results"
                      " (owner, date, task, host, hostname, port,"
                      "  nvt, nvt_version, severity, type,"
-                     "  description, uuid, qod, qod_type, result_nvt"
+                     "  description, uuid, qod, qod_type, result_nvt,"
                      "  report)"
                      " VALUES");
   g_string_append_printf (buffer,

[timopollmeier]

#658 for GVMd 8.0 and #659 for GVMd master should fix the syntax error reported here.

[mettatu]

There is still an error, I unfortunately cannot pinpoint where. gvmd.log after the patch:

md manage:WARNING:2019-08-05 08h47.39 UTC:29207: sql_exec_internal: PQexec failed: ERROR:  syntax error at or near "1.3"
LINE 1: ..._time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4....
                                                             ^
 (7)
md manage:WARNING:2019-08-05 08h47.39 UTC:29207: sql_exec_internal: SQL: INSERT into results (owner, date, task, host, hostname, port,  nvt, nvt_version, severity, type,  description, uuid, qod, qod_type, result_nvt,  report) VALUES (2, m_now (), 189, '192.168.5.91', 'scanner-02.testing.local', '22/tcp',  '1.3.6.1.4.1.25623.1.0.10330', 'SELECT iso_time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4.1.25623.1.0.10330';', '0.0', 'Log Message',  'An ssh server is running on this port', make_uuid (), 80, 'remote_banner',  (SELECT id FROM result_nvts WHERE nvt = '1.3.6.1.4.1.25623.1.0.10330'),  206) (2, m_now (), 189, '192.168.5.91', 'scanner-02.testing.local', '22/tcp',  '1.3.6.1.4.1.25623.1.0.10267', 'SELECT iso_time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4.1.25623.1.0.10267';', '0.0', 'Log Message',  'Remote SSH server banner: SSH-2.0-OpenSSH_7.4
Remote SSH supported authentication: password,publickey
Remote SSH text/login banner: 

BANNER HERE

This is probably:

- OpenSSH

Concluded from remote connection attempt with credentials:

Login:    OpenVAS-VT
Password: OpenVAS-VT', make_uuid (), 80, 'remote_banner',  (SELECT id FROM result_nvts WHERE nvt = '1.3.6.1.4.1.25623.1.0.10267'),  206) (2, m_now (), 189, '192.168.5.91', 'scanner-02.testing.local', 'general/tcp',  '1.3.6.1.4.1.25623.1.0.105937', 'SELECT iso_time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4.1.25623.1.0.105937';', '0.0', 'Log Message',  'No Best matching OS identified. Please see the NVT ''Unknown OS and Service Banner Reporting'' (OID: 1.3.6.1.4.1.25623.1.0.108441) for possible ways to identify this OS.', make_uuid (), 80, 'remote_banner',  (SELECT id FROM result_nvts WHERE nvt = '1.3.6.1.4.1.25623.1.0.105937'),  206) (2, m_now (), 189, '192.168.5.91', 'scanner-02.testing.local', 'general/tcp',  '1.3.6.1.4.1.25623.1.0.108577', 'SELECT iso_time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4.1.25623.1.0.108577';', '0.0', 'Log Message',  'Detected OpenSSH Server

Version:       7.4
Location:      22/tcp
CPE:           cpe:/a:openbsd:openssh:7.4

Concluded from version/product identification result:
SSH-2.0-OpenSSH_7.4', make_uuid (), 80, 'remote_banner',  (SELECT id FROM result_nvts WHERE nvt = '1.3.6.1.4.1.25623.1.0.108577'),  206) (2, m_now (), 189, '192.168.5.91', 'scanner-02.testing.local', '22/tcp',  '1.3.6.1.4.1.25623.1.0.105565', 'SELECT iso_time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4.1.25623.1.0.105565';', '0.0', 'Log Message',  'The following options are supported by the remote ssh service:

kex_algorithms:
curve25519-sha256,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512,diffie-hellman-group-exchange-sha1,diffie-hellman-group14-sha256,diffie-hellman-group14-sha1,diffie-hellman-group1-sha1

server_host_key_algorithms:
ssh-rsa,rsa-sha2-512,rsa-sha2-256,ecdsa-sha2-nistp256,ssh-ed25519

encryption_algorithms_client_to_server:
aes128-ctr,aes192-ctr,aes256-ctr

encryption_algorithms_server_to_client:
aes128-ctr,aes192-ctr,aes256-ctr

mac_algorithms_client_to_server:
hmac-sha1,umac-64@openssh.com,hmac-ripemd160

mac_algorithms_server_to_client:
hmac-sha1,umac-64@openssh.com,hmac-ripemd160

compression_algorithms_client_to_server:
none,zlib@openssh.com

compression_algorithms_server_to_client:
none,zlib@openssh.com', make_uuid (), 80, 'remote_banner',  (SELECT id FROM result_nvts WHERE nvt = '1.3.6.1.4.1.25623.1.0.105565'),  206) (2, m_now (), 189, '192.168.5.91', 'scanner-02.testing.local', 'general/tcp',  '1.3.6.1.4.1.25623.1.0.51662', 'SELECT iso_time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4.1.25623.1.0.51662';', '0.0', 'Log Message',  'Here is the route from 192.168.5.36 to 192.168.5.91:

192.168.5.36
192.168.5.91', make_uuid (), 80, 'remote_banner',  (SELECT id FROM result_nvts WHERE nvt = '1.3.6.1.4.1.25623.1.0.51662'),  206) (2, m_now (), 189, '192.168.5.91', 'scanner-02.testing.local', '22/tcp',  '1.3.6.1.4.1.25623.1.0.813864', 'SELECT iso_time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4.1.25623.1.0.813864';', '5.0', 'Alarm',  'Installed version: 7.4
Fixed version:     7.8
Installation
path / port:       22/tcp', make_uuid (), 30, 'remote_banner_unreliable',  (SELECT id FROM result_nvts WHERE nvt = '1.3.6.1.4.1.25623.1.0.813864'),  206) (2, m_now (), 189, '192.168.5.91', 'scanner-02.testing.local', '22/tcp',  '1.3.6.1.4.1.25623.1.0.812051', 'SELECT iso_time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4.1.25623.1.0.812051';', '5.0', 'Alarm',  'Installed version: 7.4
Fixed version:     7.6
Installation
path / port:       22/tcp', make_uuid (), 30, 'remote_banner_unreliable',  (SELECT id FROM result_nvts WHERE nvt = '1.3.6.1.4.1.25623.1.0.812051'),  206) (2, m_now (), 189, '192.168.5.91', 'scanner-02.testing.local', '22/tcp',  '1.3.6.1.4.1.25623.1.0.813888', 'SELECT iso_time (modification_time) FROM nvts WHERE uuid = '1.3.6.1.4.1.25623.1.0.813888';', '5.0', 'Alarm',  'Installed version: 7.4
Fixed version:     None
Installation
path / port:       22/tcp', make_uuid (), 30, 'remote_banner_unreliable',  (SELECT id FROM result_nvts WHERE nvt = '1.3.6.1.4.1.25623.1.0.813888'),  206) RETURNING id;
md manage:WARNING:2019-08-05 08h47.39 UTC:29207: next: sql_exec_internal failed
md manage:WARNING:2019-08-05 08h47.39 UTC:29207: manage_cleanup_process_error: Error exit, setting running task to Interrupted
md manage:WARNING:2019-08-05 08h47.39 UTC:29207: sql_exec_internal: PQexec failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block
 (7)
md manage:WARNING:2019-08-05 08h47.39 UTC:29207: sql_exec_internal: SQL: UPDATE reports SET scan_run_status = 13 WHERE id = 206;
md manage:WARNING:2019-08-05 08h47.39 UTC:29207: sqlv: sql_exec_internal failed

[cfi-gb]

#665 and #666 seems to now contain an additional fix for this issue.

[mettatu]

Thank you, I confirm that these fixed my issue.