Re: [sqlite] Header corruption
Right, that's the bug we found. The statements are in the reverse order. It's not quite that simple as the code below, but it ends up happening in that order. It's one of the most fundamental things a senior dev asks themselves on every call that that differentiates them from a junior dev - "What is the lifetime semantics of this thing I'm handing over / getting back?". You don't even need the documentation for this one after looking at how OpenSSL gets the socket handle in the first place - the bug is obvious, I just wasn't aware this codepath even existed. (Big product, big team - you know how it goes). - Deon -Original Message- From: sqlite-users On Behalf Of Olivier Mascia Sent: Tuesday, April 17, 2018 2:28 PM To: SQLite mailing list Subject: Re: [sqlite] Header corruption > Le 17 avr. 2018 à 22:07, Deon Brewis a écrit : > > closesocket(_socket); // posix socket > SSL_shutdown(_ssl); // openssl (_ssl was initialized using the _socket > above) These two statements are inherently wrong, in this order. First you SSL_shutdown(), then you closesocket(). Not the reverse. And in Windows code it even is good citizenship to call shutdown() properly in between. AFAIK it should also be on Linux, though I'm told it is not commonly seen. But please check the respective OS'es SDK and OpenSSL documentation for the details. Called in the right order there is no file descriptor re-use syndrome to fear, because the closesocket() rightfully comes as the very last step. -- Best regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia (from mobile device) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Header corruption
Fair enough. Agreed, just less of a chance (and we haven't seen it) because Windows seems to provide more spacing between these values. - Deon -Original Message- From: sqlite-users On Behalf Of Warren Young Sent: Tuesday, April 17, 2018 1:54 PM To: SQLite mailing list Subject: Re: [sqlite] Header corruption On Apr 17, 2018, at 2:07 PM, Deon Brewis wrote: > > One just has to be in the mindset that on unix based platforms, a socket is a > file handle. (Not instinctive if you're coming from a Windows background). You’re either using Winsock 1.1 or are dragging forward obsolete knowledge from the DOS-based versions of Windows: https://msdn.microsoft.com/library/windows/desktop/ms740522.aspx Unless your code accidentally works on Windows because of differing semantics, there was a risk of seeing this bug bite on Windows, too. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Header corruption
Yes, but the socket values differ by the thousands in Windows from handle values returned by CreateFile. On MAC they don't differ at all - it immediately gets re-used. - Deon -Original Message- From: sqlite-users On Behalf Of Peter Da Silva Sent: Tuesday, April 17, 2018 1:13 PM To: SQLite mailing list Subject: Re: [sqlite] Header corruption On 4/17/18, 3:08 PM, "sqlite-users on behalf of Deon Brewis" wrote: > So this was a special case of re-using the File handle as per the corruption > guide. One just has to be in the mindset that on unix based platforms, a > socket is a file handle. (Not instinctive if you're coming from a Windows > background). Even on Windows, wouldn't doing an ssl_shutdown on a socket you'd already closed still have a risk of unexpected behavior? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users ?
No problems with spam on my first day back on this list. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users ?
On 4/17/18, Simon Slavin wrote: > > Can the list admins please take appropriate action ? If you can identify the mailing list member that is sending the spam, then I can banish them. But beyond that, what can I do? If somebody is subscribed using email ab...@gmail.com, but then they turn around and send spam replies using w...@evil.com, how am I suppose to make the connection between these two addresses? I can set the list serve to obscure sender emails. We tried that once before, you might recall, and it did not go well. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users?
On 17 Apr 2018, at 11:11pm, Rich Shepard wrote: > FWIW, I haven't seen any spam from this mail list. I do run my own MTA > which aggressively rejects known spam. I won't give details in public but I will say that one or more characteristics of the spam messages appear in the URIBL blacklist. I suspect that if you use a score filter your system filtered it out. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users?
On Tue, Apr 17, 2018 at 3:11 PM, Rich Shepard wrote: > On Tue, 17 Apr 2018, Simon Slavin wrote: > > Okay, that's enough. Thanks for the help, everyone. >> > > Simon, > > FWIW, I haven't seen any spam from this mail list. I do run my own MTA > which aggressively rejects known spam. Over 20 years I doubt there have > been > more than a handfull from all mail lists to which I have been subscribed. > > Just really wanted to get some huh? :) > Rich > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users?
On Tue, 17 Apr 2018, Simon Slavin wrote: Okay, that's enough. Thanks for the help, everyone. Simon, FWIW, I haven't seen any spam from this mail list. I do run my own MTA which aggressively rejects known spam. Over 20 years I doubt there have been more than a handfull from all mail lists to which I have been subscribed. Rich ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users ?
> Le 17 avr. 2018 à 23:39, Simon Slavin a écrit : > > Are you getting a new dating-spam each time you post to this list ? I indeed have got one minutes ago after my recent post here today. I was about to write and ask about it here. :) -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users ?
On 17 Apr 2018, at 11:00pm, Warren Young wrote: > Yes. My recent reply to the header corruption thread triggered one. I got a couple of direct email replies too, with enough information for me to isolate the offending mail server. Okay, that's enough. Thanks for the help, everyone. Can the list admins please take appropriate action ? If you need headers from a message to someone else, email me and I'll send you a raw copy of one of the spam messages I received. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users ?
having replied in this thread I got one. (can the real simon slavin please stand up?) On Tue, Apr 17, 2018 at 3:00 PM, Warren Young wrote: > On Apr 17, 2018, at 3:39 PM, Simon Slavin wrote: > > > > Are you getting a new dating-spam each time you post to this list ? If > you are, please post a brief follow-up to this message. > > Yes. My recent reply to the header corruption thread triggered one. > > This is not new: some spammer subscribes to the mailing list and responds > for each new post. It’s difficult to cope with because the messages > usually go out with a different source email than the one subscribed to the > mailing list. > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users ?
On Apr 17, 2018, at 3:39 PM, Simon Slavin wrote: > > Are you getting a new dating-spam each time you post to this list ? If you > are, please post a brief follow-up to this message. Yes. My recent reply to the header corruption thread triggered one. This is not new: some spammer subscribes to the mailing list and responds for each new post. It’s difficult to cope with because the messages usually go out with a different source email than the one subscribed to the mailing list. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users ?
march 26, apr 7 I found one each day in 'spam' On Tue, Apr 17, 2018 at 2:39 PM, Simon Slavin wrote: > Dear list-posters, > > Are you getting a new dating-spam each time you post to this list ? If > you are, please post a brief follow-up to this message. Please do /not/ > include any details about the spam, its headers, or the person it's > apparently from. Just a "me too" until I say I have seen enough responses. > > I'm trying to work out if I personally am being targeted or whether it's > being sent to everyone who posts here. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CLI thoughts
Martin wrote:
...
Example (maybe via .read):
.once .dat
select date('now');
.let f system echo "words-`cat .dat`.txt"
.once -let f
select word from words order by 1;
..
Sorry for replying only to this single point. The proposed ".let"
command is not essential here. As a student I was impressed by the
SNOBOL programming language because it allowed "dynamic code
generation" (writing before the foot). From the SQLite command line
this works too:
.once tmp1.sql
values ('.once words.' || date() || '.dat'),
('select * from words;');
sqlite> .read tmp1.sql
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Are you getting spam when you post to sqlite-users ?
Dear list-posters, Are you getting a new dating-spam each time you post to this list ? If you are, please post a brief follow-up to this message. Please do /not/ include any details about the spam, its headers, or the person it's apparently from. Just a "me too" until I say I have seen enough responses. I'm trying to work out if I personally am being targeted or whether it's being sent to everyone who posts here. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Header corruption
> Le 17 avr. 2018 à 22:07, Deon Brewis a écrit : > > closesocket(_socket); // posix socket > SSL_shutdown(_ssl); // openssl (_ssl was initialized using the _socket above) These two statements are inherently wrong, in this order. First you SSL_shutdown(), then you closesocket(). Not the reverse. And in Windows code it even is good citizenship to call shutdown() properly in between. AFAIK it should also be on Linux, though I'm told it is not commonly seen. But please check the respective OS'es SDK and OpenSSL documentation for the details. Called in the right order there is no file descriptor re-use syndrome to fear, because the closesocket() rightfully comes as the very last step. -- Best regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia (from mobile device) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Header corruption
On Apr 17, 2018, at 2:07 PM, Deon Brewis wrote: > > One just has to be in the mindset that on unix based platforms, a socket is a > file handle. (Not instinctive if you're coming from a Windows background). You’re either using Winsock 1.1 or are dragging forward obsolete knowledge from the DOS-based versions of Windows: https://msdn.microsoft.com/library/windows/desktop/ms740522.aspx Unless your code accidentally works on Windows because of differing semantics, there was a risk of seeing this bug bite on Windows, too. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Header corruption
On 17 Apr 2018, at 9:13pm, Peter Da Silva wrote: > Even on Windows, wouldn't doing an ssl_shutdown on a socket you'd already > closed still have a risk of unexpected behavior? Under Windows an attempting to shutdown a connection which is already shutdown returns SOCKET_ERROR . You can then make further calls to learn that the exact problem was an attempt to close something that isn't an open socket. The major problem is analogous to one with SQLite: most software doesn't check the return value because if the return value is not 0 there's nothing simple that can be done about it. It's simpler, and 99% of the time perfectly okay, to just quit as if no error had occurred. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Header corruption
On 4/17/18, 3:08 PM, "sqlite-users on behalf of Deon Brewis" wrote: > So this was a special case of re-using the File handle as per the corruption > guide. One just has to be in the mindset that on unix based platforms, a > socket is a file handle. (Not instinctive if you're coming from a Windows > background). Even on Windows, wouldn't doing an ssl_shutdown on a socket you'd already closed still have a risk of unexpected behavior? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Header corruption
After months, I managed to track this down. I'd like to extend a BIG thanks to Richard Hip and Dan Kennedy for their help in helping me instrument and understand the SQLITE internal data structures better, as well as giving me a way to programmatically do this, as well as to teach me about the showdb tool. It has been invaluable to help me understand and categorize the corruption which in turn helped me to know what common pattern to look out for. Turns out, in some shutdown scenarios we would call what ends up being this: closesocket(_socket); // posix socket SSL_shutdown(_ssl); // openssl (_ssl was initialized using the _socket above) If in between those two commands, we opened a new SQLITE connection on another thread, SQLITE may get the same file handle value as what the old socket value used to be. SSL_shutdown then sends out a sequence resembling the following, to what used to be the socket: 150301002071476f3be1f3fa76f22b9addbe0f520ebbe007fcc1d6536c19ec9d69c5334799 However, since the old socket handle value is now being used as a file handle value, the sequence ends up in the database file instead. So this was a special case of re-using the File handle as per the corruption guide. One just has to be in the mindset that on unix based platforms, a socket is a file handle. (Not instinctive if you're coming from a Windows background). Thanks again guys! I'll leave the following search terms here as well in case someone searches for this in the future: 1503010020 15030100 352518400 (decimal version) OpenSSL - Deon -Original Message- From: sqlite-users On Behalf Of Deon Brewis Sent: Tuesday, February 6, 2018 8:57 AM To: SQLite mailing list Subject: [sqlite] Header corruption I’m trying to track down SQLITE corruptions that seems to corrupt our databases in a consistent way. (Running on 3.20.1). This isn’t related to a crash or hardware failure. The app is running and reading and writing to the database fine, and then suddenly we start getting a SQLITE_NOTADB errors. The first error we got was on trying to execute a ‘BEGIN TRANSACTION’, and everything thereafter started failing. When our database headers are fine, I see this: h: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 ; SQLite format 3. 0010h: 10 00 02 02 00 40 20 20 00 00 10 9B 00 01 34 42 ; .@ ...›..4B On corruption #1, I see this: h: 15 03 01 00 20 01 91 21 85 44 17 2C ED BE 21 FA ; .‘!…D.,í¾!ú 0010h: 9E 74 E7 EA 9A 0A 15 2C 99 B0 B7 3B C6 F0 35 FB ; žtçêš..,™°·;Æð5û On corruption #2, I see this: h: 15 03 01 00 20 0F 1E CB B1 FF 9C 1E D0 D6 BB 22 ; ..˱ÿœ.ÐÖ»" 0010h: 15 64 D3 F4 DD 38 FB DF A3 E0 47 B8 D7 F6 21 BC ; .dÓôÝ8ûߣàG¸×ö!¼ This bit pattern of “15 03 01 00” for the first 4 characters where it should be “SQLi” is a bit too much of a coincidence to ignore or write off as disk error. The rest of the header and file after the first 25-ish bytes or so, seem fine. It’s just these first 25 bytes that gets corrupted. If I restore Offsets 0 to Offsets 27 out of a “working” database, I get a usable file. Under what circumstances would SQLITE even attempt to re-write the header string? - Deon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://nam03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7C%7Cfdf8fd84436048062e6c08d56d82ae47%7C84df9e7fe9f640afb435%7C1%7C0%7C636535330385784749&sdata=qjk7S7H6FW%2FaZIBD2XKPULL4v1wH7p2UN4GaKkx1O7I%3D&reserved=0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Changes on sqlite3 parser and why not ?
Hello Richard !
Now that you are making changes on sqlite3 parser could you please add
the table alias to delete/insert/update ?
I already have it working and got conflicts with the latest changes,
they are not big or complicated but it helps make some queries easier.
Cheers !
fossil diff parse.y
Index: src/parse.y
==
--- src/parse.y
+++ src/parse.y
@@ -779,20 +779,20 @@
{A = sqlite3PExpr(pParse,TK_LIMIT,Y,X);}
/// The DELETE statement
/
//
%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with DELETE FROM fullname(X) indexed_opt(I) where_opt(W)
+cmd ::= with DELETE FROM fullname(X) as(Z) indexed_opt(I) where_opt(W)
orderby_opt(O) limit_opt(L). {
sqlite3SrcListIndexedBy(pParse, X, &I);
- sqlite3DeleteFrom(pParse,X,W,O,L);
+ sqlite3DeleteFrom(pParse,X,&Z,W,O,L);
}
%endif
%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
+cmd ::= with DELETE FROM fullname(X) as(Z) indexed_opt(I) where_opt(W). {
sqlite3SrcListIndexedBy(pParse, X, &I);
- sqlite3DeleteFrom(pParse,X,W,0,0);
+ sqlite3DeleteFrom(pParse,X,&Z,W,0,0);
}
%endif
%type where_opt {Expr*}
%destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
@@ -801,23 +801,23 @@
where_opt(A) ::= WHERE expr(X). {A = X;}
// The UPDATE command
//
%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
+cmd ::= with UPDATE orconf(R) fullname(X) as(Z) indexed_opt(I) SET
setlist(Y)
where_opt(W) orderby_opt(O) limit_opt(L). {
sqlite3SrcListIndexedBy(pParse, X, &I);
sqlite3ExprListCheckLength(pParse,Y,"set list");
- sqlite3Update(pParse,X,Y,W,R,O,L);
+ sqlite3Update(pParse,X,&Z,Y,W,R,O,L);
}
%endif
%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
+cmd ::= with UPDATE orconf(R) fullname(X) as(Z) indexed_opt(I) SET
setlist(Y)
where_opt(W). {
sqlite3SrcListIndexedBy(pParse, X, &I);
sqlite3ExprListCheckLength(pParse,Y,"set list");
- sqlite3Update(pParse,X,Y,W,R,0,0);
+ sqlite3Update(pParse,X,&Z,Y,W,R,0,0);
}
%endif
%type setlist {ExprList*}
%destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
fossil diff update.c
Index: src/update.c
==
--- src/update.c
+++ src/update.c
@@ -87,10 +87,11 @@
* onError pTabList pChanges pWhere
*/
void sqlite3Update(
Parse *pParse, /* The parser context */
SrcList *pTabList, /* The table in which we should change things */
+ Token *pAlias, /* The right-hand side of the AS subexpression */
ExprList *pChanges, /* Things to be changed */
Expr *pWhere, /* The WHERE clause. May be null */
int onError, /* How to handle constraint errors */
ExprList *pOrderBy, /* ORDER BY clause. May be null */
Expr *pLimit /* LIMIT clause. May be null */
@@ -123,10 +124,11 @@
int hasFK; /* True if foreign key processing is required */
int labelBreak; /* Jump here to break out of UPDATE loop */
int labelContinue; /* Jump here to continue next step of UPDATE
loop */
int flags; /* Flags for sqlite3WhereBegin() */
+ struct SrcList_item *pItem; /*To namage table alias*/
#ifndef SQLITE_OMIT_TRIGGER
int isView; /* True when updating a view (INSTEAD OF
trigger) */
Trigger *pTrigger; /* List of triggers on pTab, if required */
int tmask; /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
#endif
@@ -153,10 +155,16 @@
if( pParse->nErr || db->mallocFailed ){
goto update_cleanup;
}
assert( pTabList->nSrc==1 );
+ /*Manage table alias*/
+ pItem = &pTabList->a[pTabList->nSrc-1];
+ if( pAlias && pAlias->n ){
+ pItem->zAlias = sqlite3NameFromToken(db, pAlias);
+ }
+
/* Locate the table which we want to update.
*/
pTab = sqlite3SrcListLookup(pParse, pTabList);
if( pTab==0 ) goto update_cleanup;
iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
fossil diff delete.c
Index: src/delete.c
==
--- src/delete.c
+++ src/delete.c
@@ -219,10 +219,11 @@
** pTabList pWhere
*/
void sqlite3DeleteFrom(
Parse *pParse, /* The parser context */
SrcList *pTabList, /* The table from which we should delete
things */
+ Token *pAlias, /* The right-hand side of the AS subexpression */
Expr *pWhere, /* The WHERE clause. May be null */
ExprList *pOrderBy, /* ORDER BY clause. May be null */
Expr *pLimit /* LIMIT clause. May be null */
){
Vdbe *v; /* The virtual
