SQLite error in lighttpd

Currently I am using lighttpd’s WebDAV plugin to provide file service, while I encountered very strange SQLite database error, here is the error message:

(mod_webdav.c.2182) sql-set failed: SQL logic error or missing database
(mod_webdav.c.2182) sql-set failed: not an error
(mod_webdav.c.2182) sql-set failed: not an error
(mod_webdav.c.2182) sql-set failed: not an error
(mod_webdav.c.2182) sql-set failed: not an error
(mod_webdav.c.2182) sql-set failed: not an error
(mod_webdav.c.2182) sql-set failed: not an error
(mod_webdav.c.2182) sql-set failed: not an error
(mod_webdav.c.2511) remove lock: bind or column index out of range
(mod_webdav.c.2511) remove lock: bind or column index out of range
(mod_webdav.c.2511) remove lock: bind or column index out of range

Let’s check the SQLite processing code in 'mod_webdav.c' of lighttpd:

sqlite3_stmt *stmt;

stmt = (0 == xmlStrcmp(cmd->name, BAD_CAST "remove")) ?
	p->conf.stmt_delete_prop : p->conf.stmt_update_prop;

for (props = cmd->children; props; props = props->next) {
	if (0 == xmlStrcmp(props->name, BAD_CAST "prop")) {
		xmlNode *prop;
		int r;

		prop = props->children;

		sqlite3_reset(stmt);

		/* bind the values to the insert */

		sqlite3_bind_text(stmt, 1,
				  con->uri.path->ptr,
				  con->uri.path->used - 1,
				  SQLITE_TRANSIENT);
		sqlite3_bind_text(stmt, 2,
				  (char *)prop->name,
				  strlen((char *)prop->name),
				  SQLITE_TRANSIENT);
		if (prop->ns) {
			sqlite3_bind_text(stmt, 3,
					  (char *)prop->ns->href,
					  strlen((char *)prop->ns->href),
					  SQLITE_TRANSIENT);
		} else {
			sqlite3_bind_text(stmt, 3,
					  "",
					  0,
					  SQLITE_TRANSIENT);
		}
		if (stmt == p->conf.stmt_update_prop) {
			sqlite3_bind_text(stmt, 4,
				  (char *)xmlNodeGetContent(prop),
				  strlen((char *)xmlNodeGetContent(prop)),
				  SQLITE_TRANSIENT);
		}

		if (SQLITE_DONE != (r = sqlite3_step(stmt))) {
			log_error_write(srv, __FILE__, __LINE__, "ss",
					"sql-set failed:", sqlite3_errmsg(p->conf.sql));
		}
	}
}

lighttpd use SQLite’s 'sqlite3_bind_text' and 'sqlite3_step' function to bind data and do database operation.

Now we can check opening SQLite database and creating SQL statement code in mod_webdav.c of lighttpd:

const char *next_stmt;
char *err;

if (SQLITE_OK != sqlite3_open(s->sqlite_db_name->ptr, &(s->sql))) {
	log_error_write(srv, __FILE__, __LINE__, "sbs", "sqlite3_open failed for",
			s->sqlite_db_name,
			sqlite3_errmsg(s->sql));
	return HANDLER_ERROR;
}

if (SQLITE_OK != sqlite3_exec(s->sql,
		"CREATE TABLE properties ("
		"  resource TEXT NOT NULL,"
		"  prop TEXT NOT NULL,"
		"  ns TEXT NOT NULL,"
		"  value TEXT NOT NULL,"
		"  PRIMARY KEY(resource, prop, ns))",
		NULL, NULL, &err)) {

	if (0 != strcmp(err, "table properties already exists")) {
		log_error_write(srv, __FILE__, __LINE__, "ss", "can't open transaction:", err);
		sqlite3_free(err);

		return HANDLER_ERROR;
	}
	sqlite3_free(err);
}

...

if (SQLITE_OK != sqlite3_prepare(s->sql,
	CONST_STR_LEN("REPLACE INTO properties (resource, prop, ns, value) VALUES (?, ?, ?, ?)"),
	&(s->stmt_update_prop), &next_stmt)) {
	/* prepare failed */

	log_error_write(srv, __FILE__, __LINE__, "ss", "sqlite3_prepare failed:", sqlite3_errmsg(s->sql));
	return HANDLER_ERROR;
}

...

/* LOCKS */

if (SQLITE_OK != sqlite3_exec(s->sql,
		"CREATE TABLE locks ("
		"  locktoken TEXT NOT NULL,"
		"  resource TEXT NOT NULL,"
		"  lockscope TEXT NOT NULL,"
		"  locktype TEXT NOT NULL,"
		"  owner TEXT NOT NULL,"
		"  depth INT NOT NULL,"
		"  timeout TIMESTAMP NOT NULL,"
		"  PRIMARY KEY(locktoken))",
		NULL, NULL, &err)) {

	if (0 != strcmp(err, "table locks already exists")) {
		log_error_write(srv, __FILE__, __LINE__, "ss", "can't open transaction:", err);
		sqlite3_free(err);

		return HANDLER_ERROR;
	}
	sqlite3_free(err);
}

if (SQLITE_OK != sqlite3_prepare(s->sql,
	CONST_STR_LEN("INSERT INTO locks (locktoken, resource, lockscope, locktype, owner, depth, timeout) VALUES (?,?,?,?,?,?, CURRENT_TIME + 600)"),
	&(s->stmt_create_lock), &next_stmt)) {
	/* prepare failed */
	log_error_write(srv, __FILE__, __LINE__, "ss", "sqlite3_prepare failed", sqlite3_errmsg(s->sql));

	return HANDLER_ERROR;
}

...

We can see lighttpd open SQLite database first, create 'properties' table, use 'sqlite3_prepare' function to create SQL statement, then create 'locks' table, also use 'sqlite3_prepare' function to create SQL statement about 'locks' table.

'sqlite3_prepare' function can be used to convert SQL string to SQLite bytecode, we can avoid lots of duplicate SQL string instead of 'sqlite3_exec' function, we can direct query or update data after binding data. However after checking previous code, it seems there is nothing wrong with 'stmt_update_prop' related SQL statement. I suspect there is some wrong with 'sqlite3_bind_text' function, maybe there are some special characters need to be transferred before 'sqlite3_step' function, but SQLite database error still happens after I changing binded text to regular text.

After some searching in mod_webdav.c, I finally find something in 'sqlite3.h' header file:

** The sqlite3_prepare_v2() and sqlite3_prepare16_v2() interfaces are
** recommended for all new programs. The two older interfaces are retained
** for backwards compatibility, but their use is discouraged.、
**
** In the "v2" interfaces, the prepared statement
** that is returned (the [sqlite3_stmt] object) contains a copy of the
** original SQL text. This causes the [sqlite3_step()] interface to
** behave differently in three ways:
**
** If the database schema changes, instead of returning [SQLITE_SCHEMA] as it
** always used to do, [sqlite3_step()] will automatically recompile the SQL
** statement and try to run it again. As many as [SQLITE_MAX_SCHEMA_RETRY]
** retries will occur before sqlite3_step() gives up and returns an error.
**
** When an error occurs, [sqlite3_step()] will return one of the detailed
** [error codes] or [extended error codes]. The legacy behavior was that
** [sqlite3_step()] would only return a generic [SQLITE_ERROR] result code
** and the application would have to make a second call to [sqlite3_reset()]
** in order to find the underlying cause of the problem. With the "v2" prepare
** interfaces, the underlying reason for the error is returned immediately.

'sqlite3.h' recommends using 'sqlite3_prepare_v2' function to replace old 'sqlite3_prepare' function, if database schema changes after 'sqlite3_prepare' function, 'sqlite3_step' function may fail. Now we check database initialize code in 'mod_webdav.c' again, we find that 'sqlite3_prepare' is between two 'sqlite3_exec' functions (for creating database), the bytecode generated by 'sqlite3_prepare' function should be incorrect now, this will cause subsequent SQL updating operation fails.

Now we can fix this SQLite error bug, change database initialize code in 'mod_webdav.c', move two creating database 'sqlite3_exec' function above other SQL operations, then replace all 'sqlite3_prepare' to 'sqlite3_prepare_v2', then SQLite error never happens.

BTW: this SQLite bug still exists in latest lighttpd repository code, if there is anything wrong with this article, feel free to contact me.