from:"Labar, Ken"

[sqlite] sqlite3_exec() on prepared/bound statement

2017-04-04 Thread Labar, Ken

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_NullCallback)) ){
if( !callbackIsInit ){
  azCols = sqlite3DbMallocZero(db, 2*nCol*sizeof(const char*) + 1);
  if( azCols==0 ){
goto exec_prep_out;
  }
  for(i=0; imallocFailed = 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>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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-02-03 Thread Labar, Ken

Hello Simon, Clemens, and Richard,

Thank you for your help.


> Simon: "What are you seeing from your SELECT that you weren't expecting ?"
0 | Batt
0 | ClockBatt
0 | Batt
0 | BP
0 | ORP
0 | Ref
0 | pH
0 | pH
0 | DO
...


> Simon: "Can you reduce your INSERTs to just two rows, and still get results 
> you consider to be incorrect from the SELECT ?"
Yes, I reduced the table to 2 rows by:
DELETE FROM userparameter WHERE hepi_parameter_id !=32961 AND hepi_parameter_id 
!= 32881;

select (rtTableSort < 1000) as selected, abbrString from userParameter order by 
abbrString;

results:

0 | ORP

0 | DO


> DRH: "Can you try recompiling with all compiler optimizations turned off and 
> see if you still get the error?"

I have turned off all optimizations (FYI: only C++ optimizations were on before)


> DRH: ", can you compile with -DSQLITE_DEBUG then run the query *after* first 
> running "PRAGMA vdbe_debug=ON" and then send us the output?"
I have been struggling to enable SQLITE_DEBUG with our uCOS-II VFS port.
(namely our code does not have stdout or printf as compiled now. I love 
embedded;-) I'm working on a solution for debugging, more soon).


> Clemens: "What is the EXPLAIN QUERY PLAN output for this query on the 
> handheld?"
0|0|0|SCAN TABLE userParameter
0|0|0|USE TEMP B-TREE FOR ORDER BY

Thank you,
Ken

ken labar | Embedded Firmware Engineer
Hach Hydromet | www.hachhydromet.com | 
kla...@hach.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@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-02-02 Thread Labar, Ken

Hello Simon,

Below is the query, and the schema I’m trying to debug.
FYI: I’m signed up for the daily digest, so please CC 
kla...@hach.com on replies for a faster response.

CREATE TABLE userParameter (
hepi_parameter_id INT NOT NULL
,hepi_category INT NOT NULL
,hepi_number INT NOT NULL
,preferredUnit INT
,rtTableSort INT
,rtGraphSort INT
,rtGridSort INT
,dpTableSort INT
,dpGraphSort INT
,dpGridSort INT
,name_deviceString_id INT
,abbr_deviceString_id INT
,units_deviceString_id INT
,nameString TEXT COLLATE BINARY
,abbrString TEXT COLLATE BINARY
,unitsString TEXT
);

CREATE INDEX idxHepi_parameter_id ON userParameter (hepi_parameter_id);
CREATE INDEX idxRtTableSort ON userParameter (rtTableSort);
CREATE INDEX idxDpTableSort ON userParameter (dpTableSort);
CREATE INDEX idxNameString ON userParameter (nameString COLLATE BINARY);
CREATE INDEX idxAbbrString ON userParameter (abbrString COLLATE BINARY);

INSERT INTO "userParameter" 
VALUES(32785,1,1,1,0,1000,1000,1000,1000,1000,2,24,127,'Temperature','Temp','°C');
INSERT INTO "userParameter" 
VALUES(32786,1,2,1,1000,1000,1000,1000,1000,1000,2,24,128,'Temperature','Temp','°F');
INSERT INTO "userParameter" 
VALUES(32787,1,3,1,1000,1000,1000,1000,1000,1000,2,24,129,'Temperature','Temp','°K');
INSERT INTO "userParameter" 
VALUES(32817,3,1,1,1000,1000,1000,1000,1000,1000,138,125,139,'Resistivity','Res','kΩ-cm');
INSERT INTO "userParameter" 
VALUES(32833,4,1,1,1000,1000,1000,1000,1000,1000,140,141,137,'Specific 
Conductivity','SpCond','mS/cm');
INSERT INTO "userParameter" 
VALUES(32834,4,2,1,1000,1000,1000,1000,1000,1000,140,141,142,'Specific 
Conductivity','SpCond','µS/cm');
INSERT INTO "userParameter" 
VALUES(32849,5,1,1,1000,1000,1000,1000,1000,1000,143,144,145,'Salinity','Sal','psu');
INSERT INTO "userParameter" 
VALUES(32850,5,2,1,1000,1000,1000,1000,1000,1000,143,144,146,'Salinity','Sal','ppt');
INSERT INTO "userParameter" 
VALUES(32865,6,1,1,1000,1000,1000,1000,1000,1000,147,148,149,'Total Dissolved 
Solids','TDS','g/L');
INSERT INTO "userParameter" 
VALUES(32881,7,1,1,1000,1000,1000,1000,1000,1000,203,203,204,'DO','DO','%SAT');
INSERT INTO "userParameter" 
VALUES(32897,8,1,1,1000,1000,1000,1000,1000,1000,203,203,196,'DO','DO','mg/L');
INSERT INTO "userParameter" 
VALUES(32929,10,1,1,1000,1000,1000,1000,1000,1000,6,6,213,'pH','pH','units');
INSERT INTO "userParameter" 
VALUES(32945,11,1,1,1000,1000,1000,1000,1000,1000,6,6,214,'pH','pH','mV');
INSERT INTO "userParameter" 
VALUES(33249,30,1,1,1000,1000,1000,1000,1000,1000,223,224,214,'Reference','Ref','mV');
INSERT INTO "userParameter" 
VALUES(32961,12,1,1,1000,1000,1000,1000,1000,1000,7,7,214,'ORP','ORP','mV');
INSERT INTO "userParameter" 
VALUES(32977,13,1,1,1000,1000,1000,1000,1000,1000,8,8,240,'Depth','Depth','meters');
INSERT INTO "userParameter" 
VALUES(32978,13,2,1,1000,1000,1000,1000,1000,1000,8,8,241,'Depth','Depth','feet');
INSERT INTO "userParameter" 
VALUES(33377,38,1,1,1000,1000,1000,1000,1000,1000,794,794,242,'Pressure','Pressure','psig');
INSERT INTO "userParameter" 
VALUES(33378,38,2,1,1000,1000,1000,1000,1000,1000,794,794,250,'Pressure','Pressure','psia');
INSERT INTO "userParameter" 
VALUES(32993,14,1,1,1000,1000,1000,1000,1000,1000,257,257,259,'TDG','TDG','mmHg');
INSERT INTO "userParameter" 
VALUES(32994,14,2,1,1000,1000,1000,1000,1000,1000,257,257,250,'TDG','TDG','psia');
INSERT INTO "userParameter" 
VALUES(33009,15,1,1,1000,1000,1000,1000,1000,1000,10,268,272,'Turbidity','Turb','NTU');
INSERT INTO "userParameter" 
VALUES(33010,15,2,1,1000,1000,1000,1000,1000,1000,10,268,273,'Turbidity','Turb','FNU');
INSERT INTO "userParameter" 
VALUES(33025,16,1,1,1000,1000,1000,1000,1000,1000,287,288,294,'Chlorophyll 
a','CHL','µg/L');
INSERT INTO "userParameter" 
VALUES(33041,17,1,1,1000,1000,1000,1000,1000,1000,287,288,245,'Chlorophyll 
a','CHL','V');
INSERT INTO "userParameter" 
VALUES(32913,9,1,1,1000,1000,1000,1000,1000,1000,14,299,259,'Barometric 
Pressure','BP','mmHg');
INSERT INTO "userParameter" 
VALUES(33057,18,1,1,1000,1000,1000,1000,1000,1000,331,332,334,'Rhodamine 
WT','RWT','ppb');
INSERT INTO "userParameter" 
VALUES(33073,19,1,1,1000,1000,1000,1000,1000,1000,331,332,245,'Rhodamine 
WT','RWT','V');
INSERT INTO "userParameter" 
VALUES(33089,20,1,1,1000,1000,1000,1000,1000,1000,343,344,345,'Phycocyanin','PCY','cells/mL');
INSERT INTO "userParameter" 
VALUES(33105,21,1,1,1000,1000,1000,1000,1000,1000,343,344,245,'Phycocyanin','PCY','V');
INSERT INTO "userParameter" 
VALUES(33121,22,1,1,1000,1000,1000,1000,1000,1000,352,353,345,'Phycoerythrin','PRY','cells/mL');
INSERT INTO "userParameter" 
VALUES(33137,23,1,1,1000,1000,1000,1000,1000,1000,352,353,245,'Phycoerythrin','PRY','V');
INSERT INTO

[sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-01-31 Thread Labar, Ken

Hello,

We have a small sqlite3 database compiled into our handheld unit (system 
details below).
Upgrading  from  3.7.14.1 to 3.8.2 our previously sorted queries are no longer 
sorted.
Testing this same database with the win7 binary 3.8.2 the query is correctly 
sorted.

I have tried:

* adding specific indexes

* reindex immediately before the query

* removing all indexes

* collating binary

* collating nocase

* debugging the sqlite engine, but nothing jumped out at me, and this 
is out of my knowledge base.

Any ideas on why the amalgamation would behave differently compiled into our 
project?

System details:

-  LPC2468 (ARM7)

-  uCOS-II (RTOS)

-  IAR C compiler




Thank you,
Ken

ken labar | Embedded Firmware Engineer
Hach Hydromet | www.hachhydromet.com | 
kla...@hach.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@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_exec() on prepared/bound statement

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[sqlite] ORDER BY issue v3.8.2 amalgamtion

4 matches

Site Navigation

Mail list logo

Footer information