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
