Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?
On Tue, Dec 18, 2018 at 4:00 PM Peter da Silva wrote: > On Tue, Dec 18, 2018 at 3:49 PM Nathan Green wrote: > > > Except the problem isn't just in Chrome. Apparently, any system that > allows > > SQL injection is vulnerable. > > > > That's kind of a tautology isn't it? Isn't there some kind of Godwin's Law > variant for XKCD 327? > > I notice that the 12 points on https://www.sqlite.org/appfileformat.html > don't include "secure". > > I mean, sure, we used to distribute software on Usenet as shell scripts > (look up "shar archive") but it's not 1984 any more. > > SQL injection in the generic sense isn't exactly RCE because SQL is declarative. Arbitrarily messing up things in a database is not the same as running any executable code that the database process might have permission to execute. Nathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?
On Tue, Dec 18, 2018 at 3:14 PM Simon Slavin wrote: > On 18 Dec 2018, at 9:00pm, Peter da Silva wrote: > > > I have to say I'm pretty boggled that Chrome allows hostile users to > feed code directly into an SQL interpreter that wasn't written from the > ground up to be secure. > > Chrome has problems far more serious than that. And one can do all sorts > of nasty things in Chrome extensions. It's difficult for the developers of > Chrome to both prevent exploits by webmaster and extension writers, and > also allow those people to do wonderful, entirely legitimate, things. At > the level of making an API call it's not possible for the called function > to work out whether it's being used legitimately or not without a lot of > extra processing which would make it so slow nobody would use it. > > The tencent.com report is not entirely straightforward about precisely > where the problem lies, and what an exploit could do. It would be just as > useful a report if it mentioned the problem in Chrome and avoided all > mention of SQLite. And implying that SQLite ever had a remote code > execution problem is incorrect. > > Simon. > > Except the problem isn't just in Chrome. Apparently, any system that allows SQL injection is vulnerable. Since SQLite can be used as a file format to transport application data (https://www.sqlite.org/appfileformat.html), other applications might be also be vulnerable. It's not hard to conceive of exploiting an application with a "restore from backup" feature. How "remote" the RCE is depends on the application architecture. I'm thankful that SQLite works really well for my use cases, and also that I have sandboxed all of my code to run in unprivileged accounts. Nathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Allow inclusion of generate_series function
On Tue, Dec 4, 2018 at 11:25 AM Digital Dog wrote: > On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp wrote: > > > > On 12/4/18, Digital Dog wrote: > > > > > > I vote for GENERATE_SERIES to be included in official sqlite3 binary > and > > > libraries. > > > > We are under pressure to keep SQLite as small and compact as possible. > > We cannot go adding every feature that everyone requests without the > > size of the library growing out of control. Trade-offs have to be > > made. We try to provide the ability for people who actually want lots > > of features to include them in their own builds. But we do no think it > > is appropriate to add things that grow the size of the library unless > > there is a compelling need. > > Okay - keeping the core library small - that makes sense. > > But for the Command Line tools for general purpose OS like Windows, Linux, > BSD, macOS which you offer for download? I'm not so sure. It could contain > more features because there are no restrictions. Windows, macOS, typical > Linux server or desktop won't work without gigabytes of RAM. Mobile devices > are joining the club. I think the scenario can be completely reversed - > majority of platforms is capable enough that they can include almost all > features, and only some, embedded, esoteric, have real constraints and > require trimmed feature set. I think they need to be custom-built anyway. > > Or maybe it is possible to offer different builds - minimal shell and > fully-packed shell on the download page? > Yes, generate_series can be simulated, but adding 5 extra lines to a query is quite unpleasant. Making it an optional flag in the amalgamation build has no impact on library size unless one takes the extra step to include it. I've already put together a patch that makes this possible, I just need to open a pull request I guess. I just don't want to put too much time into it if it's going to be rejected out of hand. -- Nathan > ___ > 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] Displaying row count
My typical use case is trying to determine how many rows I just wrote out to a file. Window functions are no help because they alter the output format, which is usually undesirable. It's sort of absurd to run `wc -l` over the newest 25MiB file in my system to get an answer that's probably already in RAM. Nathan On Wed, Oct 31, 2018 at 12:27 PM R Smith wrote: > On 2018/10/31 4:52 PM, David Fletcher wrote: > > Hi all, > > > > Is there a mode in the sqlite shell, or some fancy extension, that > > will display a row > > number when outputting results? You know, something like this: > > > > sqlite> .row on > > sqlite> select * from SomeTable where ... ; > > 1. a|17|93|... > > 2. b|212|104|... > > Well you're in luck, SQLite has just recently adopted the great > Windowing functions addition which provides a way to achieve this. > (Note: this will only work from sqlite version 3.25 onward) > > Simply add a column to any select like this: "row_number() OVER > (partition by 1)", et voila... > > Example: >-- SQLite version 3.25.1 [ Release: 2018-09-18 ] on SQLitespeed > version 2.1.1.37. >-- > > > CREATE TABLE t(a,b); > > INSERT INTO t VALUES > (1,'AAA') > ,(6,'BBB') > ,(2,'CCC') > ,(4,'DDD') > ; > > SELECT row_number() OVER (PARTITION BY 1) No, * >FROM t > ; >-- No | a | b >-- | | --- >-- 1 | 1 | AAA >-- 2 | 2 | CCC >-- 3 | 4 | DDD >-- 4 | 6 | BBB > > > You can get further creative by adding an ORDER BY clause inside the > window function if you like to have the same row number in a repeatable > order on subsequent queries. > See here: > > https://www.sqlite.org/windowfunctions.html#introduction_to_window_functions > > Or, as others suggested, simply use a GUI - in https://sqlitespeed.com > as an example, the query has a simple setting switching row numbering on > and off - but it is blind to order, it will number any row the DB engine > spits out in the order it is spat out. Use the Windowing functions if > you need a repeatable/reference-able solution or one that will work in > the CLI. > > > Good luck! > Ryan > > > > > I tend to use the sqlite shell for debugging new queries and seeing a row > > number would save me from always doing a 'select count(*) from (... > > previous select ...)' > > command. > > > > Thanks, > > > > David > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Allow inclusion of generate_series function
Hi, According to the online documentation (https://www.sqlite.org/series.html), generate_series is compiled into the command line shell. As it turns out, this is not so. It is not even an option in the build system from what I can tell. It would be nice to at least have a build flag to enable it. I searched the list archives and noticed an email from late 2015 that indicates that generate_series was never actually a part of the shell. Apparently no one ever took up the task of making that happen. Would it be okay if I put together a patch for this? I would prefer it to be enabled by default, but I can implement it either way. Thanks, Nathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
