akiban sql parser as a filter for user sql statements

44 views
Skip to first unread message

chsn...@gmail.com

unread,
Jul 16, 2013, 6:06:03 PM7/16/13
to akiba...@akiban.com
I am writing a web application where users can execute free format sql select statements against a database.

I would like to allow the user's to write ANSI select statements, but disallow all other types of statement.

Ideally, I would also like to restrict the tables and columns the user may write queries against to a predefined list.

This layer will be implemented in addition to the native database permissions to control access.

Is this a suitable use of akiban sql parser?

Many thanks!

Chris

Nathan Williams

unread,
Jul 18, 2013, 9:49:29 AM7/18/13
to akiba...@akiban.com, chsn...@gmail.com
On Tuesday, July 16, 2013 at 6:06 PM, chsn...@gmail.com wrote:
Is this a suitable use of akiban sql parser?
That sounds like a great use of the parser!

It'll be much more robust than a string matching or regex based approach and additional features, like table or column restrictions, are trivial as well.

Did you need any pointers on how to get started with something like that?

-Nathan

chris snow

unread,
Jul 18, 2013, 12:13:46 PM7/18/13
to akiba...@akiban.com
Hi Nathan,

Your library has been fantastic!!!  It has shielded me from having to know JavaCC which would have been a big time overhead.  The only knowledge required to maintain my filter is the java debugger (to traverse the parser objects - CursorNode, etc), and knowledge of sql structure.

In just one day I have managed to provide a good proof of concept that limits queries to SELECT, only allows selecting from whitelisted tables and columns, and denies the use of character strings in where clauses (i.e. only parameter values are allowed).

Many thanks!

Chris

Nathan Williams

unread,
Jul 18, 2013, 12:25:04 PM7/18/13
to akiba...@akiban.com, chris snow
On Thursday, July 18, 2013 at 12:13 PM, chris snow wrote:
In just one day I have managed to provide a good proof of concept that limits queries to SELECT, only allows selecting from whitelisted tables and columns, and denies the use of character strings in where clauses (i.e. only parameter values are allowed).

Many thanks!
Very glad to hear it!

Do let use know if you run into any snags or have other comments.

-Nathan
Reply all
Reply to author
Forward
0 new messages