/*
** Usage: sqlite_compile DB SQL ?TAILVAR?
**
** Attempt to compile an SQL statement. Return a pointer to the virtual
** machine used to execute that statement. Unprocessed SQL is written
** into TAILVAR.
*/
static int test_compile(
void *NotUsed,
Tcl_Interp *interp, /* The TCL interpreter that invoked this command */
int argc, /* Number of arguments */
char **argv /* Text of each argument */
){
sqlite *db;
sqlite_vm *vm;
int rc;
char *zErr = 0;
const char *zTail;
char zBuf[50];
if( argc!=3 && argc!=4 ){
Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
" DB SQL TAILVAR", 0);
return TCL_ERROR;
}
if( getDbPointer(interp, argv[1], &db) ) return TCL_ERROR;
rc = sqlite_compile(db, argv[2], argc==4 ? &zTail : 0, &vm, &zErr);
if( argc==4 ) Tcl_SetVar(interp, argv[3], zTail, 0);
if( rc ){
assert( vm==0 );
sprintf(zBuf, "(%d) ", rc);
Tcl_AppendResult(interp, zBuf, zErr, 0);
sqlite_freemem(zErr);
return TCL_ERROR;
}
if( vm ){
if( makePointerStr(interp, zBuf, vm) ) return TCL_ERROR;
Tcl_AppendResult(interp, zBuf, 0);
}
return TCL_OK;
}
struct _ds_storage_record *
_ds_get_nexttoken (DSPAM_CTX * CTX)
{
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
struct _ds_storage_record *st;
char query[128];
char *err=NULL;
const char **row, *query_tail=NULL;
int ncolumn, x;
if (s->dbh == NULL)
{
LOGDEBUG ("_ds_get_nexttoken: invalid database handle (NULL)");
return NULL;
}
st = calloc (1, sizeof (struct _ds_storage_record));
if (st == NULL)
{
LOG (LOG_CRIT, ERR_MEM_ALLOC);
return NULL;
}
if (s->iter_token == NULL)
{
snprintf (query, sizeof (query),
"select token, spam_hits, innocent_hits, strftime('%%s', "
"last_hit) from dspam_token_data");
if ((sqlite_compile(s->dbh, query, &query_tail, &s->iter_token, &err))
!=SQLITE_OK)
{
_sqlite_drv_query_error (err, query);
free(st);
return NULL;
}
}
if ((x = sqlite_step(s->iter_token, &ncolumn, &row, NULL))
!=SQLITE_ROW) {
if (x != SQLITE_DONE) {
_sqlite_drv_query_error (err, query);
s->iter_token = NULL;
free(st);
return NULL;
}
sqlite_finalize((struct sqlite_vm *) s->iter_token, &err);
s->iter_token = NULL;
free(st);
return NULL;
}
st->token = strtoull (row[0], NULL, 0);
st->spam_hits = strtol (row[1], NULL, 0);
st->innocent_hits = strtol (row[2], NULL, 0);
st->last_hit = (time_t) strtol (row[3], NULL, 0);
return st;
}
/*
** Execute an SQL statement.
** Return a Cursor object if the statement is a query, otherwise
** return the number of tuples affected by the statement.
*/
static int conn_execute(lua_State *L)
{
conn_data *conn = getconnection(L);
const char *statement = luaL_checkstring(L, 2);
int res;
sqlite_vm *vm;
char *errmsg;
int numcols;
const char **col_info;
res = sqlite_compile(conn->sql_conn, statement, NULL, &vm, &errmsg);
if (res != SQLITE_OK)
{
lua_pushnil(L);
lua_pushliteral(L, LUASQL_PREFIX);
lua_pushstring(L, errmsg);
sqlite_freemem(errmsg);
lua_concat(L, 2);
return 2;
}
/* process first result to retrive query information and type */
res = sqlite_step(vm, &numcols, NULL, &col_info);
/* real query? if empty, must have numcols!=0 */
if ((res == SQLITE_ROW) || ((res == SQLITE_DONE) && numcols))
{
sqlite_reset(vm, NULL);
return create_cursor(L, 1, conn, vm, numcols, col_info);
}
if (res == SQLITE_DONE) /* and numcols==0, INSERT,UPDATE,DELETE statement */
{
sqlite_finalize(vm, NULL);
/* return number of columns changed */
lua_pushnumber(L, sqlite_changes(conn->sql_conn));
return 1;
}
/* error */
sqlite_finalize(vm, &errmsg);
lua_pushnil(L);
lua_pushliteral(L, LUASQL_PREFIX);
lua_pushstring(L, errmsg);
sqlite_freemem(errmsg);
lua_concat(L, 2);
return 2;
}
/*
Execute \a query.
*/
bool QSQLite2Result::reset (const QString& query)
{
// this is where we build a query.
if (!driver())
return false;
if (!driver()-> isOpen() || driver()->isOpenError())
return false;
d->cleanup();
// Um, ok. callback based so.... pass private static function for this.
setSelect(false);
char *err = 0;
int res = sqlite_compile(d->access,
d->utf8 ? query.toUtf8().constData()
: query.toAscii().constData(),
&(d->currentTail),
&(d->currentMachine),
&err);
if (res != SQLITE_OK || err) {
setLastError(QSqlError(QCoreApplication::translate("QSQLite2Result",
"Unable to execute statement"), QString::fromAscii(err),
QSqlError::StatementError, res));
sqlite_freemem(err);
}
//if (*d->currentTail != '\000' then there is more sql to eval
if (!d->currentMachine) {
setActive(false);
return false;
}
// we have to fetch one row to find out about
// the structure of the result set
d->skippedStatus = d->fetchNext(d->firstRow, 0, true);
if (lastError().isValid()) {
setSelect(false);
setActive(false);
return false;
}
setSelect(!d->rInf.isEmpty());
setActive(true);
return true;
}
cDBResult cSQLiteDriver::query( const QString& query )
{
char* error = NULL;
sqlite_vm* result;
// Compile a VM and pass it to cSQLiteResult
if ( sqlite_compile( ( sqlite * ) connection, query.local8Bit(), NULL, &result, &error ) != SQLITE_OK )
{
if ( error )
{
QString err( QString( error ) + " (" + query + ")" );
sqlite_freemem( error );
throw err;
}
else
{
throw QString( "Unknown SQLite error while querying: %1" ).arg( query );
}
}
return cDBResult( result, connection, false );
}
/* _sqlite2_prepare_query */
static int _sqlite2_prepare_query(SQLite2 * sqlite,
SQLite2Statement * statement, DatabaseCallback callback,
void * data, va_list args)
{
int type = -1;
char const * name;
char const * s;
char * query = NULL;
sqlite_vm * vm;
const char * tail = NULL;
char * error = NULL;
/* FIXME really implement */
while((type = va_arg(args, int)) != -1)
{
name = va_arg(args, char const *);
switch(type)
{
case DT_VARCHAR:
s = va_arg(args, char const *);
/* FIXME implement */
break;
}
}
query = statement->query; /* XXX remove once really implemented */
if(sqlite_compile(sqlite->handle, query, &tail, &vm, &error)
!= SQLITE_OK)
{
error_set_code(1, "%s", error);
free(error);
return -1;
}
/* ignore errors */
if(sqlite_finalize(vm, &error) != SQLITE_OK)
free(error);
return 0;
}
/*
Execute \a query.
*/
bool QSQLiteResult::reset (const QString& query)
{
// this is where we build a query.
if (!driver())
return FALSE;
if (!driver()-> isOpen() || driver()->isOpenError())
return FALSE;
d->cleanup();
// Um, ok. callback based so.... pass private static function for this.
setSelect(FALSE);
char *err = 0;
int res = sqlite_compile(d->access,
d->utf8 ? (const char*)query.utf8().data() : query.ascii(),
&(d->currentTail),
&(d->currentMachine),
&err);
if (res != SQLITE_OK || err) {
setLastError(QSqlError("Unable to execute statement", err, QSqlError::Statement, res));
sqlite_freemem(err);
}
//if (*d->currentTail != '\000' then there is more sql to eval
if (!d->currentMachine) {
setActive(FALSE);
return FALSE;
}
// we have to fetch one row to find out about
// the structure of the result set
d->skippedStatus = d->fetchNext(0);
setSelect(!d->rInf.isEmpty());
if (isSelect())
init(d->rInf.count());
setActive(TRUE);
return TRUE;
}
static int
smb_nic_hlist_dbget(smb_hosts_t *hlist)
{
smb_hostifs_t *iflist;
sqlite *db;
sqlite_vm *vm;
int err = SMB_NIC_SUCCESS;
const char **values;
char *sql;
char *errmsg = NULL;
int ncol, rc;
sql = sqlite_mprintf("SELECT * FROM hosts");
if (sql == NULL)
return (SMB_NIC_NO_MEMORY);
db = smb_nic_dbopen(SMB_NIC_DB_ORD);
if (db == NULL) {
sqlite_freemem(sql);
return (SMB_NIC_DBOPEN_FAILED);
}
rc = sqlite_compile(db, sql, NULL, &vm, &errmsg);
sqlite_freemem(sql);
if (rc != SQLITE_OK) {
smb_nic_dbclose(db);
syslog(LOG_ERR, "Failed to query hosts info from host " \
"database. Unable to create virtual machine (%s).",
NULL_MSGCHK(errmsg));
return (SMB_NIC_DB_ERROR);
}
do {
rc = sqlite_step(vm, &ncol, &values, NULL);
if (rc == SQLITE_ROW) {
if (ncol != SMB_NIC_HTBL_NCOL) {
err = SMB_NIC_DB_ERROR;
break;
}
if ((iflist = smb_nic_iflist_decode(values, &err)) ==
NULL) {
break;
}
list_insert_tail(&hlist->h_list, iflist);
hlist->h_num++;
hlist->h_ifnum += iflist->if_num;
}
} while (rc == SQLITE_ROW);
if (rc != SQLITE_DONE && err == SMB_NIC_SUCCESS) {
/* set this error if no previous error */
err = SMB_LGRP_DBEXEC_FAILED;
}
rc = sqlite_finalize(vm, &errmsg);
if (rc != SQLITE_OK) {
syslog(LOG_ERR, "Failed to query hosts info from host " \
"database. Unable to destroy virtual machine (%s).",
NULL_MSGCHK(errmsg));
if (err == SMB_NIC_SUCCESS) {
/* set this error if no previous error */
err = SMB_NIC_DB_ERROR;
}
}
smb_nic_dbclose(db);
return (err);
}
bool kr_dbConnect (KR_API *db) {
db->c = sqlite_open (db->database, 0644, &db->dberr);
if ( db->c == NULL ) {
kr_dbError (db);
return false;
}
return true;
}
int kr_dbQuery (KR_API *db, char * sql) {
db->final = 0;
db->rows = 0;
db->cols = 0;
db->r = sqlite_compile (db->c, sql, NULL, &db->vm, &db->dberr);
if ( db->r != SQLITE_OK ) {
kr_dbError (db);
kr_dbFinalize (db);
return 1;
}
return 0;
}
int kr_dbFetch (KR_API *db) {
if ( db->final ) {
db->final = 0;
db->r = SQLITE_OK;
goto finalize;
static array_t *SQLite2_fetch (dbconn_t * c, int row)
{
int last_row, length, i, l, r;
char *p_end;
const char *tail;
double d;
array_t *v;
if (!c->SQLite2.vm) {
/* We don't have a vm yet because the sql has not been compiled.
* This is down to db_exec using sqlite_get_table to execute the sql in the
* first instance. This is the reason we saved the sql into the SQLite
* structure, compile it now and create a vm. We return a null array only
* if the compile fails.
*/
r = sqlite_compile(c->SQLite2.handle, c->SQLite2.sql, NULL, &c->SQLite2.vm, &c->SQLite2.errormsg);
if (r != SQLITE_OK || !c->SQLite2.vm)
return &the_null_array;
}
if (c->SQLite2.step_res && c->SQLite2.step_res != SQLITE_ROW) {
return &the_null_array;
}
if (row < 0 || row > c->SQLite2.nrows) {
return &the_null_array;
}
if (c->SQLite2.ncolumns < 1) {
return &the_null_array;
}
/* If the fetch is for row 0 then we don't return a row containing data values
* instead we return the column names. This has proven quite useful in a number
* of circumstances when they are unknown ahead of the query. Unlike SQLite3 we
* have no means of obtaining them without stepping the virtual machine so we
* have no choice. We will have to check the last_row and step_rc later to make
* sure we use the values here before we step again.
*/
if (row == 0) {
c->SQLite2.step_res = sqlite_step(c->SQLite2.vm, NULL, &c->SQLite2.values, &c->SQLite2.col_names);
if (c->SQLite2.step_res == SQLITE_ROW || c->SQLite2.step_res == SQLITE_DONE) {
v = allocate_empty_array(c->SQLite2.ncolumns);
for (i = 0; i < c->SQLite2.ncolumns; i++) {
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy((char *)c->SQLite2.col_names[i], "SQLite2_fetch");
}
return v;
}
return &the_null_array;
}
/* There is no quick entry to a row in the prepared statement. Thus we have
* too loop through until we reach the desired row, but only if the last row
* that we fetched is not the previous row... confused? join the club.
*/
last_row = c->SQLite2.last_row;
/* If the requested row is before the last row that was accessed then we need
* to re-compile the sql and recreate the virtual machine. SQLite3 provides a
* facility to reset a vm however SQLite2 does not. This is a downfall of
* SQLite in general though, we need to restart everything and walk through
* all of the results again until we get to the row we want... sigh
*/
if (row < last_row) {
free(c->SQLite2.errormsg);
sqlite_finalize(c->SQLite2.vm, NULL);
if (sqlite_compile(c->SQLite2.handle, c->SQLite2.sql, &tail, &c->SQLite2.vm, &c->SQLite2.errormsg) != SQLITE_OK)
return 0;
c->SQLite2.last_row = 0;
c->SQLite2.step_res = 0;
last_row = 0;
}
/* If the requested row is the same as the last one, ie: it's been requested
* again! we do not need to step forward, so we miss the row location loop
* and get straight to the nitty gritty of building the result array. If not
* we loop through from the last_row requested to the one requested this time
* using sqlite_step(). As long as the result is SQLITE_ROW we move on, if
* not then either an error occured or there are no more rows so we return a
* null array. The result is stored in the SQLite structure for later checks
* so if fetch is called again on a completed or errornous statement we can
* fail out sooner saving time.
*/
if ((row != last_row) && (last_row < row)) {
for (i = last_row; i < row; i++) {
c->SQLite2.step_res = sqlite_step(c->SQLite2.vm, NULL, &c->SQLite2.values, &c->SQLite2.col_names);
if (c->SQLite2.step_res == SQLITE_ROW)
break;
else
return &the_null_array;
}
}
/* SQLite v2 does not provide any functions for obtaining the values based on
* their datatypes like v3 does. It is completely typeless and everything is
* returned as a (char *). Thus we need a way of determining if the value is
* numeric or a string. I do make some assumptions here, but all in all it
* does work for the vast majority of cases. There is no support for blobs
* to be returned as LPC buffers with v2. Support for binary data in v2 is
* suspect at best and is not recommended anyway, if you need that use v3.
*
* To determine the datatype, we do the following. Run the value through
* strtoul() if it fails then the value could not be converted to a number
* so we assume it's a string and return it as such. If it works but also
* has trailing data, then it might be a real number or a string. Both
* "12.34" and "12 bottles" will cause strtoul() to work returning 12 but
* both will also have trailing data. Thus we try converting it to a real
* number using strtod() if this fails then we assume its a string that
* starts with a number ie: "12 bottles" and return it as a string. If it
* works then we return it as a real number (float).
*
* It's by no means perfect, but it does catch pretty much everything I've
* thrown at it and is the best solution, bar walking the embedded datatype
* description, if one was set, and working it out from that.
*/
v = allocate_empty_array(c->SQLite2.ncolumns);
for (i = 0; i < c->SQLite2.ncolumns; i++) {
/* If we have a NULL value get out now or we'll segfault */
if (c->SQLite2.values[i] == NULL) {
v->item[i] = const0u;
continue;
}
errno = 0;
l = strtoul(c->SQLite2.values[i], &p_end, 10);
if (errno != 0 || c->SQLite2.values[i] == p_end) {
/* The conversion failed so assume it's a string */
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy((char *)c->SQLite2.values[i], "SQLite2_fetch");
}
else if (*p_end != 0) {
/* The conversion left trailing characters behind, see if its a float */
errno = 0;
d = strtod(c->SQLite2.values[i], &p_end);
if (errno != 0 || c->SQLite2.values[i] == p_end || *p_end != 0) {
/* The conversion to float failed so it must be a string */
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy((char *)c->SQLite2.values[i], "SQLite2_fetch");
}
else {
/* It was a floating point number */
v->item[i].type = T_REAL;
v->item[i].u.real = (double)d;
}
}
else if (errno == 0) {
/* It was an integer */
v->item[i].type = T_NUMBER;
v->item[i].u.number = (int)l;
}
else {
/* No idea what it was */
v->item[i] = const0u;
}
}
c->SQLite2.last_row = row;
return v;
}
/*
** Execute SQL code. Return one of the SQLITE_ success/failure
** codes. Also write an error message into memory obtained from
** malloc() and make *pzErrMsg point to that message.
**
** If the SQL is a query, then for each row in the query result
** the xCallback() function is called. pArg becomes the first
** argument to xCallback(). If xCallback=NULL then no callback
** is invoked, even for queries.
*/
int sqlite_exec(
sqlite *db, /* The database on which the SQL executes */
const char *zSql, /* The SQL to be executed */
sqlite_callback xCallback, /* Invoke this callback routine */
void *pArg, /* First argument to xCallback() */
char **pzErrMsg /* Write error messages here */
){
int rc = SQLITE_OK;
const char *zLeftover;
sqlite_vm *pVm;
int nRetry = 0;
int nChange = 0;
int nCallback;
if( zSql==0 ) return SQLITE_OK;
while( rc==SQLITE_OK && zSql[0] ){
pVm = 0;
rc = sqlite_compile(db, zSql, &zLeftover, &pVm, pzErrMsg);
if( rc!=SQLITE_OK ){
assert( pVm==0 || sqlite_malloc_failed );
return rc;
}
if( pVm==0 ){
/* This happens if the zSql input contained only whitespace */
break;
}
db->nChange += nChange;
nCallback = 0;
while(1){
int nArg;
char **azArg, **azCol;
rc = sqlite_step(pVm, &nArg, (const char***)&azArg,(const char***)&azCol);
if( rc==SQLITE_ROW ){
if( xCallback!=0 && xCallback(pArg, nArg, azArg, azCol) ){
sqlite_finalize(pVm, 0);
return SQLITE_ABORT;
}
nCallback++;
}else{
if( rc==SQLITE_DONE && nCallback==0
&& (db->flags & SQLITE_NullCallback)!=0 && xCallback!=0 ){
xCallback(pArg, nArg, azArg, azCol);
}
rc = sqlite_finalize(pVm, pzErrMsg);
if( rc==SQLITE_SCHEMA && nRetry<2 ){
nRetry++;
rc = SQLITE_OK;
break;
}
if( db->pVdbe==0 ){
nChange = db->nChange;
}
nRetry = 0;
zSql = zLeftover;
while( isspace(zSql[0]) ) zSql++;
break;
}
}
}
return rc;
}
struct _ds_storage_signature *
_ds_get_nextsignature (DSPAM_CTX * CTX)
{
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
struct _ds_storage_signature *st;
unsigned long length;
char query[128];
unsigned char *mem;
char *err=NULL;
const char **row, *query_tail=NULL;
int ncolumn, x;
if (s->dbh == NULL)
{
LOGDEBUG ("_ds_get_nextsignature: invalid database handle (NULL)");
return NULL;
}
st = calloc (1, sizeof (struct _ds_storage_signature));
if (st == NULL)
{
LOG (LOG_CRIT, ERR_MEM_ALLOC);
return NULL;
}
if (s->iter_sig == NULL)
{
snprintf (query, sizeof (query),
"select data, signature, strftime('%%s', created_on), "
"length(data) from dspam_signature_data");
if ((sqlite_compile(s->dbh, query, &query_tail, &s->iter_sig, &err))
!=SQLITE_OK)
{
_sqlite_drv_query_error (err, query);
free(st);
return NULL;
}
}
if ((x = sqlite_step(s->iter_sig, &ncolumn, &row, NULL))
!=SQLITE_ROW) {
if (x != SQLITE_DONE) {
_sqlite_drv_query_error (err, query);
s->iter_sig = NULL;
free(st);
return NULL;
}
sqlite_finalize((struct sqlite_vm *) s->iter_sig, &err);
s->iter_sig = NULL;
free(st);
return NULL;
}
length = strtol(row[3], NULL, 0);
if (length == 0)
{
free(st);
return _ds_get_nextsignature(CTX);
}
mem = malloc (length+1);
if (mem == NULL)
{
LOG (LOG_CRIT, ERR_MEM_ALLOC);
sqlite_finalize(s->iter_sig, &err);
s->iter_sig = NULL;
free(st);
return NULL;
}
length = sqlite_decode_binary((const unsigned char *) &row[ncolumn], mem);
if (length<0) {
LOG(LOG_ERR, "sqlite_decode_binary() failed with error %d", length);
s->iter_sig = NULL;
free(st);
return NULL;
}
st->data = realloc(mem, length);
strlcpy(st->signature, row[1], sizeof(st->signature));
st->length = length;
st->created_on = (time_t) strtol(row[2], NULL, 0);
return st;
}