Re: [sqlite] [sqlite-dev] Error 25: column index out of range when binding for a DATETIME
On Thu, Oct 31, 2019 at 9:52 AM Keith Medcalf wrote:
> On Thursday, 31 October, 2019 07:17, Jeffrey Walton
> wrote:
> ...
> >/* negative for days in the past */
> >int days = 120;
> >days = -days;
>
> >const char DELETE_STMT[] = "DELETE from blacklist " \
> >"WHERE dtime < datetime('now', '? days');";
>
> This statement contains no parameter. You have a string constant with a ?
> character inside the string. Parameters go outside of constants, not inside
> them. Perhaps try something like this (which will work only if days is
> negative):
>
> const char DELETE_STMT[] = "DELETE from blacklist " \
> "WHERE dtime < datetime('now', ? || ' days');";
Perfect, thanks.
That information may make good reading at
https://www.sqlite.org/lang_datefunc.html . I would never have figured
out the syntax on my own.
Jeff
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Clear sqlite3 terminal enhancement
Hi, When working in the Linux terminal we can clear the scrollback with the 'clear' command; and we can delete all history and scrollback with the 'reset' command. I am not able to do the same within the sqlite3 terminal. I'd like to request a '.clear' command and a '.reset' command to do the same in the sqlite3 terminal. They should perform the same actions that are performed in a typical shell. Jeff ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Clang 3.3 and Scan-Build results
On Wed, Jan 22, 2014 at 4:31 PM, Richard Hipp wrote: > > On Wed, Jan 22, 2014 at 3:46 PM, Jeffrey Walton wrote: >> >> Here are some results from Clang 3.3 and its scan-build engine on >> sqlite-amalgamation32k-201401171527.zip. Its a pretty good analyzer >> and it keeps getting better. > > We do run SQLite through scan-build and with -fsanitize=undefined (among > countless other tests, http://www.sqlite.org/checklists/3080200/index) prior > to every release. But for 3.8.0, we used Clang 3.0. It looks like Clang > 3.3 will be vexing us with a whole new fresh crop of warnings. :) The coverage is very impressive. I noticed Intel's ICC is missing. ICC is ruthless about removing code with undefined behavior in an effort to generate the fastest code. Because its ruthless, I try to include it in my projects to tease out non-portable and undefined behavior. ICC might make a good test platform for Sqlite. You'll know if you have some UB because Sqlite's self tests will break in an inexplicable manner under ICC (and run fine under all other compilers). You can get the non-commercial version at http://software.intel.com/en-us/non-commercial-software-development (if interested). > Static analysis has not be helpful, historically, in locating bugs in > SQLite. See http://www.sqlite.org/testing.html#staticanalysis for further > discussion on this. Yeah, agreed. They can produce a lot of noise at times. Its great that the project keeps up with them. It really helps folks downstream who have a rigorous engineering process. > We've actually created more bugs trying to deal with > warnings from static analyzers that static analyzers have found in the first > place. So compiler warnings and static analysizers have been a net-loss for > SQLite. Yeah, that sucks at times. Its too bad there's no easy way to separate the wheat from the chaff. > It is an even greater loss when you realize that the considerable > time we spend trying to squash compiler warnings is time taken away from > actually improving the code. Nevertheless, we have and continue to work very > hard to get SQLite to compile warning-free on as many platforms as possible. Great, keep up the good work. > I have recently updated my desktop and now have a brand new Clang 3.4 > installed, which will be used for the next release coming up in a few weeks. > I'll try to eliminate as many of these false-positive warnings as I can > prior to the 3.8.3 release. Oh, that's cool. I did not know Clang 3.4 was ready for general consumption. Please let me know if you would like some independent testing. I'd be happy to oblige. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Clang 3.3 and Scan-Build results
Here are some results from Clang 3.3 and its scan-build engine on
sqlite-amalgamation32k-201401171527.zip. Its a pretty good analyzer
and it keeps getting better.
The analyzer will perform interprocedural analysis, but only if the
procedures reside within the same translation units. So it will hit on
false positives at times. But I don't think that applies since this is
the amalgamation.
/usr/local/bin/scan-build/ccc-analyzer -g3 -DDEBUG=1 -c sqlite3.c
sqlite3.c:14847:14: warning: Array subscript is undefined
while( sqlite3Isspace(*z) ) z++;
^~
sqlite3.c:11957:31: note: expanded from macro 'sqlite3Isspace'
# define sqlite3Isspace(x) (sqlite3CtypeMap[(unsigned char)(x)]&0x01)
^~~
sqlite3.c:22073:5: warning: Assigned value is garbage or undefined
b = *p;
^ ~~
sqlite3.c:38243:27: warning: Division by zero
unsigned int h = iKey % pCache->nHash;
~^~~
sqlite3.c:47237:9: warning: Array access (from variable 'aHash')
results in a null pointer dereference
if( aHash[i]>iLimit ){
^~~~
sqlite3.c:49001:11: warning: Access to field 'pData' results in a
dereference of a null pointer (loaded from variable 'pPage')
pData = pPage->pData;
^~~~
sqlite3.c:60438:10: warning: Assigned value is garbage or undefined
r1 = pMem1->r;
^
sqlite3.c:61172:15: warning: Access to field 'opcode' results in a
dereference of a null pointer (loaded from variable 'pOp')
pOp->opcode = (u8)op;
~~~ ^
sqlite3.c:62297:9: warning: Access to field 'flags' results in a
dereference of a null pointer (loaded from variable 'pSub')
if( pSub->flags&MEM_Blob ){
^~~
sqlite3.c:64052:11: warning: Access to field 'aMem' results in a
dereference of a null pointer (loaded from variable 'p')
p->aMem = (Mem*)&((char*)p)[ROUND8(sizeof(UnpackedRecord))];
~ ^
sqlite3.c:74862:32: warning: Access to field 'pNext' results in a
dereference of a null pointer (loaded from variable 'pChunk')
} while( nRead>=0 && (pChunk=pChunk->pNext)!=0 && nRead>0 );
^
sqlite3.c:75646:5: warning: Access to field 'nErr' results in a
dereference of a null pointer (loaded from variable 'pTopNC')
pTopNC->nErr++;
^~
sqlite3.c:92737:19: warning: Access to field 'op' results in a
dereference of a null pointer (loaded from variable 'pStep')
pStep->op = TK_SELECT;
~ ^
sqlite3.c:92741:21: warning: Access to field 'op' results in a
dereference of a null pointer (loaded from variable 'pStep')
pStep->op = TK_DELETE;
~ ^
sqlite3.c:92745:19: warning: Access to field 'op' results in a
dereference of a null pointer (loaded from variable 'pStep')
pStep->op = TK_UPDATE;
~ ^
sqlite3.c:94868:45: warning: Access to field 'tnum' results in a
dereference of a null pointer (loaded from variable 'pSrcIdx')
sqlite3VdbeAddOp3(v, OP_OpenRead, iSrc, pSrcIdx->tnum, iDbSrc);
^
sqlite3.c:97994:11: warning: Value stored to 'x' is never read
x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
^ ~~
sqlite3.c:99574:3: warning: Address of stack memory associated with
local variable 'standin' returned to caller
return pNew;
^~~
sqlite3.c:100639:15: warning: Assigned value is garbage or undefined
pNC = pNC->pNext;
^ ~~
sqlite3.c:121056:13: warning: Access to field 'enc' results in a
dereference of a null pointer (loaded from variable 'p')
if( p->enc==pColl->enc ){
^~
19 warnings generated.
Sorry about all the extra noise. I wanted to do some static and
dynamic analysis on my project today (and its using Sqlite).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Potential memory issue in sqlite3.c under Clang 3.3 and memory sanitzier
I'm running my program under Clang 3.3 and its address sanitzers
(-fsanitze=address).
I noticed there's a persistent memory issue flagged in sqlite3.c:
sqlite3.c:60249:22: runtime error: member access within
null pointer of type 'Mem' (aka 'struct Mem')
60249 is shown below, and the line is the `memcpy`:
SQLITE_PRIVATE void sqlite3VdbeMemShallowCopy(Mem *pTo, const Mem
*pFrom, int srcType){
assert( (pFrom->flags & MEM_RowSet)==0 );
VdbeMemRelease(pTo);
memcpy(pTo, pFrom, MEMCELLSIZE);
pTo->xDel = 0;
if( (pFrom->flags&MEM_Static)==0 ){
pTo->flags &= ~(MEM_Dyn|MEM_Static|MEM_Ephem);
assert( srcType==MEM_Ephem || srcType==MEM_Static );
pTo->flags |= srcType;
}
}
I'm working from sqlite-amalgamation-3080200.zip.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Differentiate between CREATE TABLE failures (Exists vs Other Failures?)
On Wed, Jan 22, 2014 at 8:19 AM, Dave Wellman wrote: > Hi, > Why not use the "create table if not exists" syntax, that way any error must > be classed as 'other'. > Cheers, Thanks Dave. I'll try it. Jeff > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jeffrey Walton > Sent: 21 January 2014 17:54 > To: sqlite-users@sqlite.org > Subject: [sqlite] Differentiate between CREATE TABLE failures (Exists vs > Other Failures?) > > My startup routine attempts to create all needed tables in my database. For > example: > > sqlite3_extended_result_codes(db, 1); > ... > > int rc; > char* err = NULL; > > const char* stmt = "CREATE TABLE users ( " > " userid INTEGER PRIMARY KEY AUTOINCREMENT, " > " username TEXT, usergroup TEXT, " > " hashalg INTEGER, salt BLOB, hmac BLOB, " > " verifier BLOB " > ");"; > rc = sqlite3_exec(db, stmt, NULL, NULL, &err); > > If the table exists, I receive SQLITE_ERROR as a result even with > sqlite3_extended_result_codes. > > Is there a way to get extended error information to differentiate between an > existing table and other errors? Or do I have to parse the error string > returned in `err`? > > Thanks in advance? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Differentiate between CREATE TABLE failures (Exists vs Other Failures?)
My startup routine attempts to create all needed tables in my database. For example: sqlite3_extended_result_codes(db, 1); ... int rc; char* err = NULL; const char* stmt = "CREATE TABLE users ( " " userid INTEGER PRIMARY KEY AUTOINCREMENT, " " username TEXT, usergroup TEXT, " " hashalg INTEGER, salt BLOB, hmac BLOB, " " verifier BLOB " ");"; rc = sqlite3_exec(db, stmt, NULL, NULL, &err); If the table exists, I receive SQLITE_ERROR as a result even with sqlite3_extended_result_codes. Is there a way to get extended error information to differentiate between an existing table and other errors? Or do I have to parse the error string returned in `err`? Thanks in advance? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on VACCUUM, WAL, and Encryption Codecs
Hi All, Can anyone verify that VACCUUM and WAL uses encryption codecs if available? I think I found answers for other components such as rollback journals, but I'm not clear on the two items above. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building SQLIte Commands or Sanitzing User Input
Thanks for the quick response Simon. Happy holidays. On Fri, Dec 31, 2010 at 8:25 PM, Simon Slavin wrote: > > On 1 Jan 2011, at 12:35am, Jeffrey Walton wrote: > >> I'm using SQLite on embedded devices (iPhone and, SmartPhone, and >> PocketPC). Ichecked OWASP, and they don't have anything for SQLite or >> C/C++. http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet. >> >> Does the SQLite API offer the ability to create a 'command object'. Or >> a call to sanitize user input (which would probably include escaping >> special characters)? > > Yep. sqlite3_prepare() creates a compiled SQL statement, which is more or > less what you mean by 'command object'. It's not possible to inject if > you're using sqlite3_prepare(): it will execute only a single instruction and > syntax characters in parameters are interpreted as if they're part of the > parameter, not part of the command. > > If you're using sqlite3_exec() then you have bigger problems. You can, of > course, screen your input string for semi-colons. You can check that INSERT, > UPDATE and DELETE are followed only by your desired table names. You can > screen the first two words of each SQL command. Further precautions vary > depending on what you know is a legitimate use in your particular application. > > So I recommend that if you're nervous about injection, you use the prepare > sequence: > > <http://www.sqlite.org/c3ref/stmt.html> > > In the case of the iPhone you other special security measures acting in your > favour. For instance, it's impossible to use one application to get at the > data belonging to other applications. Other operating systems have their own > measures. > > Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Building SQLIte Commands or Sanitzing User Input
Hi All, I'm using SQLite on embedded devices (iPhone and, SmartPhone, and PocketPC). Ichecked OWASP, and they don't have anything for SQLite or C/C++. http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet. Does the SQLite API offer the ability to create a 'command object'. Or a call to sanitize user input (which would probably include escaping special characters)? Thanks, Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
