Re: [sqlite] database like file archive
On 8/27/19, Jens Alfke wrote: > is [SQLite] engineered with the > assumption that a database file may be malicious, or is the assumption > "garbage in, garbage out"? https://www.sqlite.org/security.html https://www.sqlite.org/testing.html Our intent is that SQLite database files are secure in the sense that reading a maliciously corrupted database file is harmless. I spend most of my time testing for this sort of thing, and dreaming up new defenses against yet undiscovered attacks. Research out of the University of Buffalo shows that every Android phone has about 200 SQLite database files and about 14% of those are used for transfer purposes only. In other words, content is downloaded from the cloud as an SQLite database then becomes read-only on the phone. That's about 70 billion databases used as containers. So lots of people are using SQLite as a container. And those numbers are for Android only. Indications are the iOS is even bigger. We work very hard to ensure that those billions and billions of data containers in circulation are not a security risk. That said, Jens remarks made me realize that the SQLite archive code has not been updated recently to implement the best practices outlined in the first document above. The existing code is safe. But I'll get busy and add the extra layers of defense to make it even safer. -- 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] strip off file metadata in sqlar
I just need the return status of sqldiff (as `cmp -s`). Is there an option to suppress all the screen output? Thanks. On 8/27/19, Warren Young wrote: > On Aug 27, 2019, at 2:40 PM, Peng Yu wrote: >> >> Where is the binary or source code of sqldiff? > > The source is part of the complete SQLite source tree: > > https://www.sqlite.org/cgi/src/doc/trunk/README.md > > (As opposed to the amalgamation.) > > You will then find it in tool/sqldiff.c, and you can build it with “make > sqldiff” after configuring the tree as desribed on the above web page. > >> I don't find sqldiff in the package sqlite installed in homebrew. > > It’s a separate package: brew install sqldiff > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database like file archive
On Tuesday, 27 August, 2019 14:40, Jens Alfke wrote: >> On Aug 27, 2019, at 12:21 PM, Keith Medcalf wrote: >> Everything that has been touched by a third-party is inherently >> untrustworthy. Thus it is and thus it has always been. > Yes. I have a lot of experience with network coding and security, so > I'm aware of this, thanks. My question was simply whether SQLite > itself is considered safe when operating on an untrusted database > file. Considered by whom -- and what is the trustworthiness of that "whom" to be making that assessment? Personally speaking I find the developers of SQLite to be trustworthy and generally trust that SQLite3 will operate in accordance with its design. Furthermore, I trust that any issues where SQLite does not operate in accordance with its design will be fixed by said developers. I also trust that when presented with a trustworthy database file the library will operate in a trustworthy fashion. The necessary inference therefore is that if presented with an untrustworthy database file, then the whole shebang is untrustworthy. This inference would stand no matter what trustworthy party may claim that SQLite3 is trustworthy in the face of untrustworthy input. (And this applies to everything not just SQLite3). To assume otherwise is fraught with peril and is the cause of much security problems in the world today. >> Even ZIP files have a database schema that can be manipulated as >> does everything else. > The layout of a Zip file is vastly simpler than a SQLite database. > A Zip codec does not include an interpreter for a sophisticated > programming language. > Zip files do not contain program code that runs when the file is > read; SQLite databases can. In fact, if one forgets about ZIP (presumably meaning something trustworthy like Info-ZIP) then there are in fact a great many things that are capable of executing arbitrary code contained within what is ostensibly data, often by design. Can one trust that any given program when presented with untrustworthy input will not execute arbitrary code? No. One should expect that any given program when presented with untrustworthy input may operate in an untrustworthy manner, unless it is manifestly impossible for this to occur. Since determination of "manifestly impossible" is huge in scope, it is only done in very rare instances. Also, I do not think that SQLite, by design, executes any code that is stored within the database, assuming a trustworthy database file. However, if you are asking if one can craft an (untrustworthy) database file that may execute code contained within the database when it is opened, then this again devolves into the fact that the database file is itself untrustworthy, and as such all such bets are off. >> And how is this in anyway different from a zip process, or a rar >> processess or an uncompress process or any or a number of possibly >> trustworthy programs processing data coming from an untrustworthy >> source? (which includes things like Web Browsers, Video Players, and >> on and on) > Codecs used in such apps are considered attack surfaces and are > screened for vulnerabilities. (For example, Google found and fixed > some security holes in the TrueType font renderer when they added > web-font support to Chrome.) This is precisely what I'm asking about > SQLite — is it engineered with the assumption that a database file > may be malicious, or is the assumption "garbage in, garbage out"? And this is a known security problem (untrustworthy font files) that has existed for as long as I can recall (at least since GOOEYs were invented). The solution is, of course, to not permit the processing of files (including font files) that originate from untrustworthy sources. Just because one such hole has been found and fixed does not mean that others do not exist. In fact, it means that it is more likely that such holes exist but merely have not been published in the local tabloid yet. Nonetheless, it is again a matter of assigning trust and trustworthiness and the boundaries that apply to this trust. So, directly to your question "SQLite — is it engineered with the assumption that a database file may be malicious, or is the assumption "garbage in, garbage out"? The answer of course is a matter of trust. Yes, SQLite is engineered to operate in a trustworthy fashion even when presented with untrustworthy input and in fact the developers endeavour to fix any such issues found/reported. This however does not preclude the possible existence of situations where this is not the case or have not otherwise yet been reported and fixed. So again, we come back to the fact that no matter how trustworthy something is when presented with trusted inputs, its behaviour when presented with untrustworthy input cannot be guaranteed. That is to say that for all pipelines A -> B -> C where either of A or B is untrustworthy, then C must be untrustworthy
[sqlite] SQLite vulns
On 27 Aug 2019, at 9:40pm, Jens Alfke wrote: > My question was simply whether SQLite itself is considered safe when > operating on an untrusted database file. A worthwhile question which I discussed a little in an earlier post. The SQLite devs take this question extremely seriously. SQLite is not meant to crash. SQLite is not meant to execute untrusted code. It should not be possible for the contents of any file SQLite opens to influence the way SQLite works. You will see a fast, high-quality response to identification of any such vulnerability, as has happened in the past. I'll add a note here about what 'untrusted' means. A programmer can tell SQLite to execute external code. This can be done using external functions, external collations, external routines (e.g. the busy handler callback), and other things. These things must be possible for SQlite to work properly. However having SQLite execute external code is something that only the programmer should be able to do. It should not be possible to make it happen using a carefully-crafted database file, or even through some action by the user. And as far as we know, it isn't. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strip off file metadata in sqlar
On Aug 27, 2019, at 2:40 PM, Peng Yu wrote: > > Where is the binary or source code of sqldiff? The source is part of the complete SQLite source tree: https://www.sqlite.org/cgi/src/doc/trunk/README.md (As opposed to the amalgamation.) You will then find it in tool/sqldiff.c, and you can build it with “make sqldiff” after configuring the tree as desribed on the above web page. > I don't find sqldiff in the package sqlite installed in homebrew. It’s a separate package: brew install sqldiff ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database like file archive
> On Aug 27, 2019, at 12:21 PM, Keith Medcalf wrote: > > Everything that has been touched by a third-party is inherently > untrustworthy. Thus it is and thus it has always been. Yes. I have a lot of experience with network coding and security, so I'm aware of this, thanks. My question was simply whether SQLite itself is considered safe when operating on an untrusted database file. > Even ZIP files have a database schema that can be manipulated as does > everything else. The layout of a Zip file is vastly simpler than a SQLite database. A Zip codec does not include an interpreter for a sophisticated programming language. Zip files do not contain program code that runs when the file is read; SQLite databases can. > And how is this in anyway different from a zip process, or a rar processess > or an uncompress process or any or a number of possibly trustworthy programs > processing data coming from an untrustworthy source? (which includes things > like Web Browsers, Video Players, and on and on) Codecs used in such apps are considered attack surfaces and are screened for vulnerabilities. (For example, Google found and fixed some security holes in the TrueType font renderer when they added web-font support to Chrome.) This is precisely what I'm asking about SQLite — is it engineered with the assumption that a database file may be malicious, or is the assumption "garbage in, garbage out"? > Chrome is a Google product. Google's only revenue source is selling > information that they have obtained from third- [anti-Google ranting removed] This is not only off-topic and inaccurate (Google has many other revenue sources), it's the sort of scenery-chewing conspiracy theorizing that's beneath someone with your level of expertise. Check yo'self. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strip off file metadata in sqlar
Where is the binary or source code of sqldiff? I don't find sqldiff in the package sqlite installed in homebrew. I also don't find sqldiff after compiling sqlite-autoconf-329.tar.gz. > https://www.sqlite.org/sqldiff.html -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database like file archive
On Tuesday, 27 August, 2019 12:47, Jens Alfke wrote: >Archive files often get transferred between people. Using this format >for that purpose would involve opening and reading untrusted SQLite >database files. Is that safe? Could maliciously corrupting the schema >or other metadata of a database cause security problems for the >client accessing the database? Everything that has been touched by a third-party is inherently untrustworthy. Thus it is and thus it has always been. Even ZIP files have a database schema that can be manipulated as does everything else. There is no difference other than the misplaced assumption of trust. >(I'm thinking not just of a separate `sqlite3` process accessing the >archive, but also of the archiving code running inside some other >process — consider a web browser or file manager extracting a sqlar >archive.) And how is this in anyway different from a zip process, or a rar processess or an uncompress process or any or a number of possibly trustworthy programs processing data coming from an untrustworthy source? (which includes things like Web Browsers, Video Players, and on and on) >There were some security issues that came up recently involving the >Chrome browser allowing untrusted JS code to run SQLite queries on >local database files. But the scenario I'm thinking of is kind of the >reverse — the queries are trusted but the database itself isn't. Chrome is a Google product. Google's only revenue source is selling information that they have obtained from third-parties by clandestine means. As such, nothing which bears a Google (or Alphabet) name can be considered in any way trustworthy. One must assign trust having an eye to this fact and evaluate all statements made in light of this truth. So Google Chrome permitting untrusted JS to run SQLite queries on local database files should be expected and is not a security problem. How else would Google make money? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database like file archive
On 27 Aug 2019, at 7:47pm, Jens Alfke wrote: > Archive files often get transferred between people. Using this format for > that purpose would involve opening and reading untrusted SQLite database > files. Is that safe? Could maliciously corrupting the schema or other > metadata of a database cause security problems for the client accessing the > database? You're thinking of an exploit like a ZIP bomb. This is a small, maliciously-constructed ZIP file which expands into a huge amount of contents. A well-known example is a 42 kilobyte zip file which unzips into 4.5 petabytes of contents. Other problems include overwriting in-archive filenames with illegal characters like a colon and a slash, then relying on oversights in OS routines to do nasty things to your file structure. I'm going to let the devs handle this one. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database like file archive
> On Aug 27, 2019, at 7:06 AM, Philip Bennefall wrote: > > There is the sqlar archive format, which you can test using the official > sqlite3 command line shell. There is also a library for it as part of the > Sqlite3 repository. "An SQLite Archive is an ordinary SQLite database file that contains the following table as part of its schema…" Archive files often get transferred between people. Using this format for that purpose would involve opening and reading untrusted SQLite database files. Is that safe? Could maliciously corrupting the schema or other metadata of a database cause security problems for the client accessing the database? (I'm thinking not just of a separate `sqlite3` process accessing the archive, but also of the archiving code running inside some other process — consider a web browser or file manager extracting a sqlar archive.) There were some security issues that came up recently involving the Chrome browser allowing untrusted JS code to run SQLite queries on local database files. But the scenario I'm thinking of is kind of the reverse — the queries are trusted but the database itself isn't. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strip off file metadata in sqlar
On Aug 27, 2019, at 9:21 AM, Peng Yu wrote: > > The .sqlar files contain file metadata. I'd like two .sqlar files to > be exactly the same (`cmp` should return 0) when they store the same > content. Is it possible to strip off all metadata of stored files? [XY Problem][1]. Try sqldiff instead: https://www.sqlite.org/sqldiff.html [1]: http://xyproblem.info/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlar: provide file content from stdin
Hi, https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line To add something to sqlite3, I must provide an actual file. sqlite3 alltxt.sqlar -Ac *.txt What if I just want to specify the `name` field in the resulted .sqlar file in the command line, but take the file content from stdin? Is there a way to do so? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] strip off file metadata in sqlar
The .sqlar files contain file metadata. I'd like two .sqlar files to be exactly the same (`cmp` should return 0) when they store the same content. Is it possible to strip off all metadata of stored files? Thanks. $ sqlite3 my.sqlar -Atv -rwxr-xr-x181 2019-08-27 15:18:27 main.sh -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database like file archive
> The standard "sqlite3" command-line tool will read and write SQLite > archive files. See the documentation at > https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line OK. So there is basically no need to install the sqlar command since all features from the sqlar command is accessible via the sqlite3 command? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database like file archive
The earliest version of the shell which ships with the archive support is 3.22.0, according to the page I linked to. If you have an earlier version you could simply grab the Mac OSX precompiled binaries from the download page on sqlite.org and you'll be good to go. Kind regards, Philip Bennefall On 8/27/2019 4:18 PM, Peng Yu wrote: There is the sqlar archive format, which you can test using the official sqlite3 command line shell. There is also a library for it as part of the Sqlite3 repository. https://www.sqlite.org/sqlar.html https://sqlite.org/sqlar/doc/trunk/README.md This is good to know. How to install it? In homebrew's sqlite package, I don't find sqlar. I use Mac OS X. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
It does support natural joins. USING needs parenthesis around the column list: ...using (author_id)...using (book_isbn)... -Original Message- From: sqlite-users On Behalf Of Dominique Devienne Sent: Tuesday, August 27, 2019 10:08 AM To: SQLite mailing list Subject: Re: [sqlite] Query for Many to Many On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne wrote: > select author.*, books.* > from author_books > join author on author.author_id = author_books.author_id > join books on books.book_isbn = author_books.book_isbn > Which can also be written: select author.*, books.* from author_books join author using author_id join books using book_isbn Or even: select author.*, books.* from author_books natural join author natural join books All of the above untested of course :). Not even sure SQLite supports natural join or not (I'd guess it does). --DD https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join ___ 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] database like file archive
On 8/27/19, Peng Yu wrote: > > How to install it? In homebrew's sqlite package, I don't find sqlar. I > use Mac OS X. > The standard "sqlite3" command-line tool will read and write SQLite archive files. See the documentation at https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line -- 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] database like file archive
> There is the sqlar archive format, which you can test using the official > sqlite3 command line shell. There is also a library for it as part of > the Sqlite3 repository. > > https://www.sqlite.org/sqlar.html > https://sqlite.org/sqlar/doc/trunk/README.md This is good to know. How to install it? In homebrew's sqlite package, I don't find sqlar. I use Mac OS X. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database like file archive
https://sqlite.org/sqlar/doc/trunk/README.md On Tue, Aug 27, 2019 at 3:57 PM Peng Yu wrote: > Hi, > > I haven't found an archive format that allows in-place delete (I know > that .zip, .7z and .tar don't). This means that whenever delete is > needed, the original archive must be copied first. This can be > problematic when the archive is large and the file to delete is small. > > Something along the line of the ability of sqlite3 to perform in-place > delete might be a useful feature for archives. But I haven't found any > such archive format. Does anybody know one? Thanks. > > -- > Regards, > Peng > ___ > 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] Query for Many to Many
On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne wrote: > select author.*, books.* > from author_books > join author on author.author_id = author_books.author_id > join books on books.book_isbn = author_books.book_isbn > Which can also be written: select author.*, books.* from author_books join author using author_id join books using book_isbn Or even: select author.*, books.* from author_books natural join author natural join books All of the above untested of course :). Not even sure SQLite supports natural join or not (I'd guess it does). --DD https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database like file archive
There is the sqlar archive format, which you can test using the official sqlite3 command line shell. There is also a library for it as part of the Sqlite3 repository. https://www.sqlite.org/sqlar.html Kind regards, Philip Bennefall On 8/27/2019 3:56 PM, Peng Yu wrote: Hi, I haven't found an archive format that allows in-place delete (I know that .zip, .7z and .tar don't). This means that whenever delete is needed, the original archive must be copied first. This can be problematic when the archive is large and the file to delete is small. Something along the line of the ability of sqlite3 to perform in-place delete might be a useful feature for archives. But I haven't found any such archive format. Does anybody know one? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
On Tue, Aug 27, 2019 at 3:38 PM dboland9 wrote: > I need some help writing some queries for a MTM relationship. The example > tables are: > > author table books table author_books table > author_id PKbook_isbn PKa_b_id PK > author_fnamebook_title author_id FK > author_lnamebook_pub_date book_isbn FK > author_minit > > > Listings desired: > book_isbn book_title book_pub_date author > --++--+--- > > author book_isbnBook_title > +-+ > > Would appreciate the query (inner join - that I do know), and why so I can > learn something from them. Please keep them simple (no alias or other > shortcuts) so I can easily follow what you are doing. Thanks in advance. > Well, that's all you need, inner join, just two of them. Nothing difficult here IMHO. Or I'm missing something. --DD > I assume the query will be something like: > SELECT > books.book_isbn, books.book_title, books.book_pub_date, > author.author_fname, author.author_minit, > author.author_lname > FROM books > JOIN > author_books ON (something ) select author.*, books.* from author_books join author on author.author_id = author_books.author_id join books on books.book_isbn = author_books.book_isbn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query for Many to Many
The basic query is going to be the below select stuff from books inner join author_books on author_books.book_isbn = books.book_isbn inner join author on author_books.author_id = author.author_id where things; -Original Message- From: sqlite-users On Behalf Of dboland9 Sent: Tuesday, August 27, 2019 9:38 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Query for Many to Many All, I need some help writing some queries for a MTM relationship. The example tables are: author table books table author_books table author_id PKbook_isbn PKa_b_id PK author_fnamebook_title author_id FK author_lnamebook_pub_date book_isbn FK author_minit Listings desired: book_isbn book_title book_pub_date author --++--+--- author book_isbn Book_title +-+ Would appreciate the query (inner join - that I do know), and why so I can learn something from them. Please keep them simple (no alias or other shortcuts) so I can easily follow what you are doing. Thanks in advance. I assume the query will be something like: SELECT books.book_isbn, books.book_title, books.book_pub_date, author.author_fname, author.author_minit, author.author_lname FROM books JOIN author_books ON (something ) Dave, Sent with ProtonMail Secure Email. ___ 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] database like file archive
Hi, I haven't found an archive format that allows in-place delete (I know that .zip, .7z and .tar don't). This means that whenever delete is needed, the original archive must be copied first. This can be problematic when the archive is large and the file to delete is small. Something along the line of the ability of sqlite3 to perform in-place delete might be a useful feature for archives. But I haven't found any such archive format. Does anybody know one? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query for Many to Many
All, I need some help writing some queries for a MTM relationship. The example tables are: author table books table author_books table author_id PKbook_isbn PKa_b_id PK author_fnamebook_title author_id FK author_lnamebook_pub_date book_isbn FK author_minit Listings desired: book_isbn book_title book_pub_date author --++--+--- author book_isbn Book_title +-+ Would appreciate the query (inner join - that I do know), and why so I can learn something from them. Please keep them simple (no alias or other shortcuts) so I can easily follow what you are doing. Thanks in advance. I assume the query will be something like: SELECT books.book_isbn, books.book_title, books.book_pub_date, author.author_fname, author.author_minit, author.author_lname FROM books JOIN author_books ON (something ) Dave, Sent with ProtonMail Secure Email. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users