Hello all, * Short story: To support better security through binding, we would like to request a new C/C++ API function similar to sqlite3_exec(), however it will take a prepared statement instead of an sql string.
* Long story: We finally allowed a user string input directly into our database (embbeded C, ARM7, UCOS-II port). We immediately found SQL injection errors in testing. To solve sql injection we used sqlite3_preapre_v2() with sqlite3_bind_text(). When looking to refactor the code, it was easier to create the following function directly in sqlite3.c. It appears this function could be called from sqlite3_exec() to save code space and avoid duplication. We feel this would make sense in the mainline code, so we are offering it here, hopefully it might be useful to others: #define ADD_SQLITE3_EXEC_PREPARED 1 #if ADD_SQLITE3_EXEC_PREPARED /* ** Execute a prepared sqlite3 statement. Borrowed from SQLITE_API int sqlite3_exec(). ** 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. */ // @note: added for the ViperFish project by klabar 3/24/2017 SQLITE_API int sqlite3_exec_prepared( sqlite3 *db, /* The database on which the SQL executes */ sqlite3_stmt *pStmt, /* The prepared SQL to be executed */ sqlite3_callback xCallback, /* Invoke this callback routine */ void *pArg, /* First argument to xCallback() */ char **pzErrMsg /* Write error messages here */ ){ int rc = SQLITE_OK; /* Return code */ const char *zLeftover; /* Tail of unprocessed SQL */ char **azCols = 0; /* Names of result columns */ int callbackIsInit; /* True if callback data is initialized */ if( !sqlite3SafetyCheckOk(db) ) return SQLITE_MISUSE_BKPT; sqlite3_mutex_enter(db->mutex); sqlite3Error(db, SQLITE_OK, 0); while( rc==SQLITE_OK && pStmt ){ int nCol; char **azVals = 0; callbackIsInit = 0; nCol = sqlite3_column_count(pStmt); while( 1 ){ int i; rc = sqlite3_step(pStmt); /* Invoke the callback function if required */ if( xCallback && (SQLITE_ROW==rc || (SQLITE_DONE==rc && !callbackIsInit && db->flags&SQLITE_NullCallback)) ){ if( !callbackIsInit ){ azCols = sqlite3DbMallocZero(db, 2*nCol*sizeof(const char*) + 1); if( azCols==0 ){ goto exec_prep_out; } for(i=0; i<nCol; i++){ azCols[i] = (char *)sqlite3_column_name(pStmt, i); /* sqlite3VdbeSetColName() installs column names as UTF8 ** strings so there is no way for sqlite3_column_name() to fail. */ assert( azCols[i]!=0 ); } callbackIsInit = 1; } if( rc==SQLITE_ROW ){ azVals = &azCols[nCol]; for(i=0; i<nCol; i++){ azVals[i] = (char *)sqlite3_column_text(pStmt, i); if( !azVals[i] && sqlite3_column_type(pStmt, i)!=SQLITE_NULL ){ db->mallocFailed = 1; goto exec_prep_out; } } } if( xCallback(pArg, nCol, azVals, azCols) ){ rc = SQLITE_ABORT; sqlite3VdbeFinalize((Vdbe *)pStmt); pStmt = 0; sqlite3Error(db, SQLITE_ABORT, 0); goto exec_prep_out; } } if( rc!=SQLITE_ROW ){ rc = sqlite3VdbeFinalize((Vdbe *)pStmt); pStmt = 0; break; } } sqlite3DbFree(db, azCols); azCols = 0; } exec_prep_out: if( pStmt ) sqlite3VdbeFinalize((Vdbe *)pStmt); sqlite3DbFree(db, azCols); rc = sqlite3ApiExit(db, rc); if( rc!=SQLITE_OK && ALWAYS(rc==sqlite3_errcode(db)) && pzErrMsg ){ int nErrMsg = 1 + sqlite3Strlen30(sqlite3_errmsg(db)); *pzErrMsg = sqlite3Malloc(nErrMsg); if( *pzErrMsg ){ memcpy(*pzErrMsg, sqlite3_errmsg(db), nErrMsg); }else{ rc = SQLITE_NOMEM; sqlite3Error(db, SQLITE_NOMEM, 0); } }else if( pzErrMsg ){ *pzErrMsg = 0; } assert( (rc&db->errMask)==rc ); sqlite3_mutex_leave(db->mutex); return rc; } #endif //ADD_SQLITEUTIL_EXEC_PREPARED Here is an example of how we used it: /** \brief \return rc // the SQLite return code **/ int HLFF_DB_logfile_get_byLognameSource(char *logname, int source, void* relay_args) { int rc; sqlite3_stmt *pStmt; if (!logname) { return(-1); } if (!logname[0]) { return(-1); } //build the query Str_Copy( sql, "SELECT id, filename, logname, size, " "source, start_time, stop_time," " modified_timestamp FROM `logfile` WHERE logname=?1 and source=?2"); // prepare the query rc = sqlite3_prepare_v2( _pHLFF_DB, sql, SQL_CMD_BUFF_SIZE, &pStmt, NULL ); // bind the parameters if(!rc) { rc |= sqlite3_bind_text( pStmt, 1, logname , Str_Len( logname ), SQLITE_STATIC ); rc |= sqlite3_bind_int( pStmt, 2, source ); } // Execute the statement. rc = sqliteUtil_exec_prepared( _pHLFF_DB, pStmt, hlff_db_logfile_get_callback, relay_args, NULL); return(rc); } Thank you, ken labar | Embedded Firmware Engineer OTT Hydromet | www.otthydromet.com<http://www.otthydromet.com> Innovating technology behind better data Please be advised that this email may contain confidential information. If you are not the intended recipient, please notify us by email by replying to the sender and delete this message. The sender disclaims that the content of this email constitutes an offer to enter into, or the acceptance of, any agreement; provided that the foregoing does not invalidate the binding effect of any digital or other electronic reproduction of a manual signature that is included in any attachment. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users