Re: [sqlite] BedrockDB interview on Floss Weekly

[David Barrett]

On Fri, Oct 27, 2017 at 4:34 AM, Warren Young  wrote:

> Before I get to the questions, I haven’t listened to the FLOSS episode
> yet, so please forgive me if these were asked and answered on the podcast.
> Just tell me if so, because I will eventually get to it.
>
> 1. I don’t see a C API.  If so, that means it’s up to the caller to handle
> quoting and escaping properly to avoid inadvertently creating SQL injection
> vulnerabilities.  Is that true, and if so, is there a plan to solve it?  I
> don’t mind running my queries through an HTTP[-ish] layer, but getting
> quoting and escaping right in hand-written code is a big source of errors
> I’d rather not return to.
>

There is a PHP binding (https://github.com/Expensify/Bedrock-PHP) but none
for C yet -- PRs welcome!  That said because it uses standard HTTP escaping
it should also be compatible with pretty much any HTTP C API -- or it's
very easy to hand roll.  When it comes to escaping, for example, you can
generally put the content directly in the HTTP content body, so Bedrock
introduces no "extra" escaping (beyond what is normally required by SQL, of
course).



> 2. No DBD::BedrockDB yet, I see.  When? :)
>

Heh, we don't have much Perl in our environment, but again, this would be
straightforward to add.



> 3. What happens if a local program attaches to the SQLite DB and queries
> it in the face of active replication?
>

SQLite is crazy hardened and reliable in this scenario.  You are free to
access the underlying SQLite database via the command line if you like, but
the locking behavior will be more restrictive.  BedrockDB not only supports
concurrent reads (via SQLite's standard WAL functionality), but concurrent
*writes* as well (via an experimental "BEGIN CONCURRENT" branch developed
by the SQLite team that we're hammering the kinks out of before its merged
into the mainline).  So use the Bedrock plugin architecture where possible
to get maximum concurrency, and never do writes from the command line as
it'll screw up our replication journal, but you are free to do reads via
the command line when more convenient (and we do it all the time).


4. What happens if a local program *inserts* data via the SQLite
> interface?  Does the data get replicated, or does that happen only if you
> insert via port ?
>

Correct, only insertions done via port  will get replicated, so it's
not recommended (but always possible for extreme maintenance scenarios --
about as often as you'd directly modify a local MySQL file with a hex
editor -- meaning, almost never).



> 5. I think your web pages answer this one implicitly, but let me ask just
> to be sure I’m guessing right: Does the MySQL API smooth out any of the
> semantic and language differences between MySQL and SQLite, or must you
> simply give SQLite-compatible queries over the MySQL interface?  (Expected
> answer: “No.”)
>

No -- we only support SQLite syntax (which is really more modeled off of
Postgres), but in practice the various MySQL clients we've tried don't
actually care about the syntax -- only the protocol -- so it works well
enough in practice.


6. What MySQL API version are you compatible with?  As maintainer of
> MySQL++, I can tell you, there is not just one version. :)
>

Good question, we don't have an exhaustive list.  The versions we use, at
least. :)  But the most recent version of SequelPro, the MySQL command line
client that ships with Ubuntu, and Mode Analytics to start.



> 7. Does using your MySQL API solve problem #1?  That is, if you use one of
> the available ways for constructing guaranteed-sound queries via
> libmysqlclient or one of the many libraries built atop it, does BedrockDB
> get the benefit?
>

I'm not sure what "guaranteed-sound" means, sorry!



> 8. Does your MySQL API solve problem #2?  That is, have you tested it
> against DBD::mysql?
>

I don't think we've tested against that as we don't use a lot of Perl, but
I'd be curious for your results!

-david
Founder and CEO of Expensify (and BedrockDB)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BedrockDB interview on Floss Weekly

[David Barrett]

Thanks for the fantastic questions!  Answers inline:

I had never heard of you guys, this looks amazing! How can you only have
> 222 github stars?!
>

Heh, feel free to give it one more!


> Paxos, so needs at least 3 nodes?
>

It works with any number of nodes, but I recommend 6 as the ideal: two
nodes in each of three different datacenters.  More on how the
synchronization technology works is here:
http://bedrockdb.com/synchronization.html


> How do you use it from an application that normally uses sqlite? Is it a
> drop-in replacement? I use Node.JS…
>

BedrockDB has an extremely simple wire protocol based around the concept of
"plugins".  The "db" plugin is described here: http://bedrockdb.com/db.html,
but there is also a full featured "cache" and *extremely* full featured
"jobs" plugin as well.  The protocol is easy enough that you can "netcat"
in and use it effectively by hand, or you can use our PHP binding:
https://github.com/Expensify/Bedrock-PHP

Interesting that you emulate mysql, given that sqlite tries to be
> postgresql compatible…
>

Our main use for this was to accommodate users who prefer certain clients
like SequelPro or Mode Analytics.  More information on our MySQL emulation
is here: http://bedrockdb.com/mysql.html



> Any war stories around developing this? Unexpected issues due to a missing
> comma etc?
>

Omg, so many war stories.  This has been powering Expensify from day one,
so we have about 8 years of scars as a result.  However, Bedrock gains the
benefit of all that painfully earned experience, and is hardened against a
million real-world edge cases that you won't need to worry about.


> Would you recommend this for any workload? I'm paticularly interested in
> replicating my append-only event log db.


In general I recommend Bedrock for more or less any workload that you would
give to a database (which in my experience, is about any workload involving
persistent data).  I've long wanted to build a "logs" plugin to Bedrock
that would provide full-text indexing ala Elasticsearch (which we use at
Expensify, but have pretty steady problems with), but it hasn't gotten bad
enough yet to worry about.  Thanks for asking!

-david
Founder and CEO of Expensify (and BedrockDB)



On Thu, Oct 26, 2017 at 10:19 PM, Wout Mertens 
wrote:

> I had never heard of you guys, this looks amazing! How can you only have
> 222 github stars?!
>
> Paxos, so needs at least 3 nodes?
>
> How do you use it from an application that normally uses sqlite? Is it a
> drop-in replacement? I use Node.JS…
>
> Interesting that you emulate mysql, given that sqlite tries to be
> postgresql compatible…
>
> Any war stories around developing this? Unexpected issues due to a missing
> comma etc?
>
> Would you recommend this for any workload? I'm paticularly interested in
> replicating my append-only event log db.
>
> Wout.
>
> On Thu, Oct 26, 2017, 8:15 AM David Barrett 
> wrote:
>
> > I'm glad you liked it!  I'd be happy to answer any questions you have
> about
> > http://BedrockDB.com, our use of sqlite, or anything else.  Thanks for
> > listening!
> >
> > -david
> >
> > On Wed, Oct 25, 2017 at 4:19 PM, jungle Boogie 
> > wrote:
> >
> > > Hi All,
> > >
> > > Pardon the usual interruption of complex sqlite questions...
> > >
> > > David Barrett was interviewed on Floss Weekly today and gave a rave
> > > review of his project, which is based on the wonderful sqlite3
> > > database.
> > >
> > > I'm only 10 minutes into the interview and really love it already!
> > > https://twit.tv/shows/floss-weekly/episodes/456
> > >
> > > Thanks to David for appearing on the show and of course to the Sqlite3
> > > team for their amazing efforts to make, and maintain the most widely
> > > deployed database engine in the world - maybe even in the galaxy.
> > >
> > > Thanks,
> > > j.b.
> > >
> > > --
> > > ---
> > > inum: 883510009027723
> > > sip: jungleboo...@sip2sip.info
> > > ___
> > > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_stat4 table

[Richard Hipp]

On 10/29/17, korablev  wrote:
>
> Probably the main question is not "why there are 2 samples for 1 value", but
> "why there is so strange (if not wrong) statistics?".
>

STAT4 is designed to help SQLite run faster for queries of tables with
millions of rows.  For a table containing just two rows, it doesn't
really matter.

-- 
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] sqlite_stat4 table

[korablev]

Richard Hipp-3 wrote
> See https://www.sqlite.org/fileformat.html#the_sqlite_stat4_table

Thanks, I have already read it, but there is no explanation about this:

korablev wrote
> statistics in the second row seems to be wrong: there are 0 rows which are
> less than 1(for the reason that the only value in that index is 1). And
> why do nLt and nDLt columns consist of 2 slots instead of 1(index consists
> only of 1 column + 1 PK which shouldn't be taken into account)? 

Probably the main question is not "why there are 2 samples for 1 value", but
"why there is so strange (if not wrong) statistics?". 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite_stat4 table

[Richard Hipp]

On 10/29/17, korablev  wrote:
> DROP TABLE IF EXISTS t1;
> create table t1(id primary key, a, b) without rowid;
> create index t1a on t1(a);
> insert into t1 values(1, 1, 2), (2, 1, 3);
> analyze t1;
> select * from sqlite_stat4 where idx = 't1a';
>
> t1|t1a|2 1|0 0|0 0|   
> t1|t1a|2 1|0 1|0 1|
>
> Firstly, there is no blob values for samples(at least they aren't displayed
> -- default terminal, macOS Sierra 10.12.6). It always happens if index
> consists of integer values.

Try doing this setting first:

.mode quote

> Secondly, I don't understand why table contains
> 2 rows for 1 value of index key. I mean, statistics in the second row seems
> to be wrong: there are 0 rows which are less than 1(for the reason that the
> only value in that index is 1). And why do nLt and nDLt columns consist of 2
> slots instead of 1(index consists only of 1 column + 1 PK which shouldn't be
> taken into account)?

See https://www.sqlite.org/fileformat.html#the_sqlite_stat4_table

There are usually 10 to 40 rows per each index.  In your case there
are only two because it is a small table.

-- 
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

[sqlite] Top 10 web development mistakes leading to security vulnerabuilities

[Simon Slavin]

This article gives details and pointers to examples.  The final list has not 
yet been announced, but this is about a late draft.



"Every few years, the Open Web Application Security Project releases its Top 10 
list of the 10 biggest web development mistakes that often lead to security 
vulnerabilities. Nice idea. But many of the items on the list haven't changed 
since the 2013 and 2010 reports. In other words, we're still screwing up."

In just the last ten years I’ve seen four from the draft list involving SQLite:

1: injection
4: broken access control
8: cross-site forgery
10: exposing underprotected APIs

If you include other SQL engines I think I’ve seen all ten at least once, 
though many vulnerabilities appeared all in the same design, including one 
which accidentally allowed SQL commands to be encoded into the URL (/a la/ 
Little Bobby Tables).

Because the above makes me look holier-than-though, I admit to doing one of 
them myself.  For about two years one of my maintenance tools checked to see 
that it was being accessed from a DNS address /including/ "mysite.co.uk" when 
it should have checked for an address /ending/ in "mysite.co.uk".  Had that 
system been big, important, or well-known someone might have figured that out.

Be careful what you expose to the web, folks.  Don’t be a fool.  Wrap your tool.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite_stat4 table

[korablev]

DROP TABLE IF EXISTS t1;
create table t1(id primary key, a, b) without rowid;
create index t1a on t1(a);
insert into t1 values(1, 1, 2), (2, 1, 3);
analyze t1;
select * from sqlite_stat4 where idx = 't1a';

t1|t1a|2 1|0 0|0 0| 
t1|t1a|2 1|0 1|0 1|

Firstly, there is no blob values for samples(at least they aren't displayed
-- default terminal, macOS Sierra 10.12.6). It always happens if index
consists of integer values. Secondly, I don't understand why table contains
2 rows for 1 value of index key. I mean, statistics in the second row seems
to be wrong: there are 0 rows which are less than 1(for the reason that the
only value in that index is 1). And why do nLt and nDLt columns consist of 2
slots instead of 1(index consists only of 1 column + 1 PK which shouldn't be
taken into account)?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the most flexible way to exact the table name from a SQL statement

[Bart Smissaert]

I have a VB6/VBA procedure that takes a SQL and produces the table that was
altered by that SQL.
I think it works with any SQL, but not sure if is of any help to you.

RBS

On Sat, Oct 28, 2017 at 1:44 PM, Shane Dev  wrote:

> Hello,
>
> Let's say I have a table containing of SQL statements, for example
>
> sqlite> .schema sql
> CREATE TABLE sql(statement text);
>
> sqlite> select * from sql;
> insert into tab1 select 'example text';
> update tab2 set col2 = 123 where col2 = 1;
> delete from tab3 where col1 = 2;
>
> For the first row, I could build a query using instr and substr functions
> to extract the first word after INSERT INTO. That would work for most
> simple INSERT statements, but it would fail if (for example) the statement
> was prepended with a WITH clause which happened to contain the text "INSERT
> INTO". Is there more generalized way of achieving this?
> ___
> 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] What is the most flexible way to exact the table name from a SQL statement

[Gwendal Roué]

Apologies: I have to amend again my suggestion. The authorizer has to be 
attached to a "real" database that already has a definition for the involved 
tables, if you need to know about insertions, deletions, and updates.

Gwendal

> Le 29 oct. 2017 à 15:37, Gwendal Roué  a écrit :
> 
> I should have added that you can check for inserted/deleted/updated tables by 
> looking for more codes than SQLITE_CREATE_TABLE. The provided sample code 
> only checks for table creation.
> 
> Gwendal
> 
>> Le 29 oct. 2017 à 15:28, Gwendal Roué > > a écrit :
>> 
>> Yes, there is a general way.
>> 
>> To know if a statement creates a database table, 
>> 
>> 1. Open a private, in-memory, database connection
>> 2. Register an authorizer with sqlite3_set_authorizer 
>> (https://sqlite.org/c3ref/set_authorizer.html 
>> ).
>> 3. Compile the statement with sqlite3_prepare_v2 
>> (https://sqlite.org/c3ref/prepare.html 
>> )
>> 4. In the registered authorizer callback, check for SQLITE_CREATE_TABLE 
>> (https://www.sqlite.org/c3ref/c_alter_table.html 
>> ).
>> 
>> Find attached a C program that demonstrates the technique.
>> 
>> $ cc -lsqlite3 created_table.c && ./a.out 
>> Create table foo: CREATE TABLE foo(a, b)
>> No table creation: INSERT INTO bar (a) VALUES (1)
>> No table creation: Some invalid SQL
>> 
>> 
>> 
>> Gwendal Roué
>> 
>>> Le 28 oct. 2017 à 14:44, Shane Dev >> > a écrit :
>>> 
>>> Hello,
>>> 
>>> Let's say I have a table containing of SQL statements, for example
>>> 
>>> sqlite> .schema sql
>>> CREATE TABLE sql(statement text);
>>> 
>>> sqlite> select * from sql;
>>> insert into tab1 select 'example text';
>>> update tab2 set col2 = 123 where col2 = 1;
>>> delete from tab3 where col1 = 2;
>>> 
>>> For the first row, I could build a query using instr and substr functions
>>> to extract the first word after INSERT INTO. That would work for most
>>> simple INSERT statements, but it would fail if (for example) the statement
>>> was prepended with a WITH clause which happened to contain the text "INSERT
>>> INTO". Is there more generalized way of achieving this?
>>> ___
>>> 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] What is the most flexible way to exact the table name from a SQL statement

[Gwendal Roué]

I should have added that you can check for inserted/deleted/updated tables by 
looking for more codes than SQLITE_CREATE_TABLE. The provided sample code only 
checks for table creation.

Gwendal

> Le 29 oct. 2017 à 15:28, Gwendal Roué  a écrit :
> 
> Yes, there is a general way.
> 
> To know if a statement creates a database table, 
> 
> 1. Open a private, in-memory, database connection
> 2. Register an authorizer with sqlite3_set_authorizer 
> (https://sqlite.org/c3ref/set_authorizer.html 
> ).
> 3. Compile the statement with sqlite3_prepare_v2 
> (https://sqlite.org/c3ref/prepare.html 
> )
> 4. In the registered authorizer callback, check for SQLITE_CREATE_TABLE 
> (https://www.sqlite.org/c3ref/c_alter_table.html 
> ).
> 
> Find attached a C program that demonstrates the technique.
> 
> $ cc -lsqlite3 created_table.c && ./a.out 
> Create table foo: CREATE TABLE foo(a, b)
> No table creation: INSERT INTO bar (a) VALUES (1)
> No table creation: Some invalid SQL
> 
> 
> 
> Gwendal Roué
> 
>> Le 28 oct. 2017 à 14:44, Shane Dev > > a écrit :
>> 
>> Hello,
>> 
>> Let's say I have a table containing of SQL statements, for example
>> 
>> sqlite> .schema sql
>> CREATE TABLE sql(statement text);
>> 
>> sqlite> select * from sql;
>> insert into tab1 select 'example text';
>> update tab2 set col2 = 123 where col2 = 1;
>> delete from tab3 where col1 = 2;
>> 
>> For the first row, I could build a query using instr and substr functions
>> to extract the first word after INSERT INTO. That would work for most
>> simple INSERT statements, but it would fail if (for example) the statement
>> was prepended with a WITH clause which happened to contain the text "INSERT
>> INTO". Is there more generalized way of achieving this?
>> ___
>> 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] What is the most flexible way to exact the table name from a SQL statement

[Gwendal Roué]

Yes, there is a general way.

To know if a statement creates a database table, 

1. Open a private, in-memory, database connection
2. Register an authorizer with sqlite3_set_authorizer 
(https://sqlite.org/c3ref/set_authorizer.html 
).
3. Compile the statement with sqlite3_prepare_v2 
(https://sqlite.org/c3ref/prepare.html )
4. In the registered authorizer callback, check for SQLITE_CREATE_TABLE 
(https://www.sqlite.org/c3ref/c_alter_table.html 
).

Find attached a C program that demonstrates the technique.

$ cc -lsqlite3 created_table.c && ./a.out 
Create table foo: CREATE TABLE foo(a, b)
No table creation: INSERT INTO bar (a) VALUES (1)
No table creation: Some invalid SQL



Gwendal Roué

> Le 28 oct. 2017 à 14:44, Shane Dev  a écrit :
> 
> Hello,
> 
> Let's say I have a table containing of SQL statements, for example
> 
> sqlite> .schema sql
> CREATE TABLE sql(statement text);
> 
> sqlite> select * from sql;
> insert into tab1 select 'example text';
> update tab2 set col2 = 123 where col2 = 1;
> delete from tab3 where col1 = 2;
> 
> For the first row, I could build a query using instr and substr functions
> to extract the first word after INSERT INTO. That would work for most
> simple INSERT statements, but it would fail if (for example) the statement
> was prepended with a WITH clause which happened to contain the text "INSERT
> INTO". Is there more generalized way of achieving this?
> ___
> 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