[sqlite] Wish list: allow developers use the power of sqliteparser

[Hick Gunter]

Use the documented and supported sqlite3_set_authorizer() function to achieve 
security vetting of SQL Statements. This calls a user-supplied function with 
the details of which tables and fields the user is attempting to access and if 
this is a read or write access.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von David 
Barrett
Gesendet: Dienstag, 19. J?nner 2016 07:28
An: SQLite mailing list
Betreff: Re: [sqlite] Wish list: allow developers use the power of sqliteparser

One use of this I would like is to create a security framework around arbitrary 
SQL queries from the user.  So, for example, I'd love to determine which tables 
(and which columns of those tables) a particular query is going to access, and 
then compare that list against a whitelist of columns the user is authorized to 
access.  I'm not confident enough in my own parsing skills to make something 
foolproof, but if I were using the same exact parser as sqlite, then it would 
be impossible to "trick".

Any suggestions on how to use the private Lemon parser methods to accomplish 
this?  Thanks!

-david



On Mon, Jan 18, 2016 at 7:17 AM, Domingo Alvarez Duarte < sqlite-mail at 
dev.dadbiz.es> wrote:

> Is this something crazy to ask as a developer ?
>
> I think that even for the author something like this would make
> several tasks easier.
>
> Ideally I would like to feed the parser with an sql string, get it's
> syntax tree, maybe do some rewrite and feed it execute it, this
> possibility can open the door to amazing things.
>
>
>
> Thanks for all answers so far, I still want to hear any other idea
> that can lead to achieve the original request !
>
>
>
> Cheers !
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Wish list: allow developers use the power of sqliteparser

[David Barrett]

Whoaa!!  This is amazing!

https://www.sqlite.org/c3ref/c_alter_table.html

Wow, thanks!

-david

On Mon, Jan 18, 2016 at 10:54 PM, Scott Hess  wrote:

> On Mon, Jan 18, 2016 at 10:27 PM, David Barrett 
> wrote:
>
> > One use of this I would like is to create a security framework around
> > arbitrary SQL queries from the user.  So, for example, I'd love to
> > determine which tables (and which columns of those tables) a particular
> > query is going to access, and then compare that list against a whitelist
> of
> > columns the user is authorized to access.
>
>
> You shouldn't use the parser interface for access control, you should use
> the authorizer interface.
>
> -scott
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Wish list: allow developers use the power of sqliteparser

[Scott Hess]

On Mon, Jan 18, 2016 at 10:27 PM, David Barrett 
wrote:

> One use of this I would like is to create a security framework around
> arbitrary SQL queries from the user.  So, for example, I'd love to
> determine which tables (and which columns of those tables) a particular
> query is going to access, and then compare that list against a whitelist of
> columns the user is authorized to access.


You shouldn't use the parser interface for access control, you should use
the authorizer interface.

-scott

[sqlite] Wish list: allow developers use the power of sqliteparser

[David Barrett]

One use of this I would like is to create a security framework around
arbitrary SQL queries from the user.  So, for example, I'd love to
determine which tables (and which columns of those tables) a particular
query is going to access, and then compare that list against a whitelist of
columns the user is authorized to access.  I'm not confident enough in my
own parsing skills to make something foolproof, but if I were using the
same exact parser as sqlite, then it would be impossible to "trick".

Any suggestions on how to use the private Lemon parser methods to
accomplish this?  Thanks!

-david



On Mon, Jan 18, 2016 at 7:17 AM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Is this something crazy to ask as a developer ?
>
> I think that even for the author something like this would make several
> tasks
> easier.
>
> Ideally I would like to feed the parser with an sql string, get it's syntax
> tree, maybe do some rewrite and feed it execute it, this possibility can
> open
> the door to amazing things.
>
>
>
> Thanks for all answers so far, I still want to hear any other idea that can
> lead to achieve the original request !
>
>
>
> Cheers !
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Wish list: allow developers use the power of sqliteparser

[Domingo Alvarez Duarte]

Is this something crazy to ask as a developer ?  

I think that even for the author something like this would make several tasks
easier.  

Ideally I would like to feed the parser with an sql string, get it's syntax
tree, maybe do some rewrite and feed it execute it, this possibility can open
the door to amazing things.  

?  

Thanks for all answers so far, I still want to hear any other idea that can
lead to achieve the original request !  

?  

Cheers !

[sqlite] Wish list: allow developers use the power of sqliteparser

[Domingo Alvarez Duarte]

Thank you for your reply !  

I understand your point.  

But without remove your freedom could we have somehow an optional callback
that could be registered to receive what the parser found ?  

It's a shame that we can not reuse the parser power !  

Can we have something that do not remove your freedom and can provide a way
to reuse the parser power ?  

Cheers !  

?  
>  Mon Jan 18 2016 2:15:04 pm CET CET from "Richard Hipp"  
>Subject: Re: [sqlite] Wish list: allow developers use the power of
>sqliteparser
>
>  On 1/18/16, Domingo Alvarez Duarte  wrote:
>  
>>Hello Richard !
>> 
>> Can we have a sqlite function that given a sql string return something
>>like
>> this https://codeschool.github.io/sqlite-parser/demo/ =>
>> 
>> Syntax Tree ?
>> 

>  No.
> 
> That would make the abstract syntax tree an interface, which means
> that we would not be able to refactor it in the future without
> breaking backwards compatibility. We do not change the AST format
> often, but it does happen. And we want the continued freedom to
> further modify the AST without having to think about what applications
> those changes might break.
> 
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Wish list: allow developers use the power of sqliteparser

[Hick Gunter]

AFAICT the abstract syntax tree does not exist outside of the generated lemon 
parser. Only the generated VDBE code makes it to the "outside world".

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Simon 
Slavin
Gesendet: Montag, 18. J?nner 2016 15:36
An: SQLite mailing list
Betreff: Re: [sqlite] Wish list: allow developers use the power of sqliteparser


On 18 Jan 2016, at 2:20pm, Domingo Alvarez Duarte  wrote:

> Can we have something that do not remove your freedom and can provide
> a way to reuse the parser power ?

In the amalgamation source code you will find

/*
** The interface to the LEMON-generated parser */ SQLITE_PRIVATE void 
*sqlite3ParserAlloc(void*(*)(u64));
SQLITE_PRIVATE void sqlite3ParserFree(void*, void(*)(void*)); SQLITE_PRIVATE 
void sqlite3Parser(void*, int, Token, Parse*); #ifdef YYTRACKMAXSTACKDEPTH
SQLITE_PRIVATE   int sqlite3ParserStackPeak(void*);
#endif

If you remove the PRIVATE from these declarations, you can call the functions 
yourself and have access to the same parser that SQLite uses.

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Wish list: allow developers use the power of sqliteparser

[Simon Slavin]

On 18 Jan 2016, at 2:20pm, Domingo Alvarez Duarte  wrote:

> Can we have something that do not remove your freedom and can provide a way
> to reuse the parser power ? 

In the amalgamation source code you will find

/*
** The interface to the LEMON-generated parser
*/
SQLITE_PRIVATE void *sqlite3ParserAlloc(void*(*)(u64));
SQLITE_PRIVATE void sqlite3ParserFree(void*, void(*)(void*));
SQLITE_PRIVATE void sqlite3Parser(void*, int, Token, Parse*);
#ifdef YYTRACKMAXSTACKDEPTH
SQLITE_PRIVATE   int sqlite3ParserStackPeak(void*);
#endif

If you remove the PRIVATE from these declarations, you can call the functions 
yourself and have access to the same parser that SQLite uses.

Simon.

[sqlite] Wish list: allow developers use the power of sqliteparser

[Domingo Alvarez Duarte]

On the right panel there is a live syntax tree of the sql on the left panel,
we can edit the sql on the left panel and the syntax tree on the right panel
is updated.  

The idea is to have a way to get a syntax tree of a sql string through the
sqlite parser: synatx_tree sqlite3_sql_syntax_tree(const char *zSql);  

Can't you see the syntax tree ? (javascript enabled is necessary)  

Cheers !  
>  Mon Jan 18 2016 12:39:10 pm CET CET from "Keith Medcalf"
>  Subject: Re: [sqlite] Wish list: allow developers use
>the power of sqliteparser
>
>  What are we supposed to be seeing there?
> 
>  
>>-Original Message-
>> 
>> Can we have a sqlite function that given a sql string return something
>> like
>> this https://codeschool.github.io/sqlite-parser/demo/ =>
>> 
>> Syntax Tree ?
>> 

>  
>
>  



?

[sqlite] Wish list: allow developers use the power of sqliteparser

[Simon Slavin]

On 18 Jan 2016, at 11:51am, Domingo Alvarez Duarte  wrote:

> The idea is to have a way to get a syntax tree of a sql string through the
> sqlite parser: synatx_tree sqlite3_sql_syntax_tree(const char *zSql);


The vast majority of copies of SQLite don't run on normal computers, they're 
embedded inside mobile phones, TV recorders, SatNav devices, etc..  So there 
would be no point in SQLite having the feature you requested as part of the 
standard installation of SQLite.

SQLite uses the Lemon parser to parse the SQL statement.  Details on it can be 
found here:



If you would like to write something that turns the output of that parser into 
a syntax tree in your preferred format, go ahead.  Should be interesting.