Kelly Jones wrote:
> Many sites let you search databases of information, but the search
> queries are very limited.
A fact I can agree with time and again.
> I'm creating a site that'll allow arbitrary SQL queries to my data (I
> realize I'll need to handle injection attacks).
Now by "arbitrary SQL queries", you mean "arbitrary SQL SELECT statements" I
assume; otherwise, no need for injection as you're already letting them do
whatever they want.
Generally speaking, if you truly want arbitrary queries, you essentially have
to
provide a facility to users that is analogous to letting them write in a
programming language, like SQL. To a large extent, you could accomplish this
either by providing lots of form fields where they build up queries by picking
from smaller rules, or alternately you could let them type an actual query like
one would with SQL but instead they write in some simpler language, say one of
your own design, which you then parse and translate to SQL for SQLite to run.
To prevent injection attacks, you in the many-fields case make sure to escape
or
validate/restrict all inputs to allowed values, or in the latter case you
simply
don't provide features in your simpler query language that you don't want them
to have, such as non-read queries.
> Are there other viable ways to query data? I read a little on
> "Business System 12" (BS12), Tutorial D, and even something called
> T-SQL (I think), but they all seem theoretical and not fully
> implemented.
Business System 12 is a legacy project, one of the original relational database
implementations, that predates SQL. It isn't a separate language and has no
bearing on using with SQLite.
T-SQL is a variant of SQL used by the likes of Sybase and MS SQL Server, I
think, and maybe some other DBMSs; T-SQL is for those DBMSs what PL-SQL is for
Oracle. You see T-SQL/PL-SQL in SQL stored procedures, which SQLite doesn't
natively support anyway.
Tutorial D is indeed an actual language which isn't tied to a specific DBMS, as
with generic SQL itself, and could potentially be something SQLite could
support
directly in the future, but it doesn't now. There *are* several Tutorial D
implementations, but not over SQLite. A Java DBMS named "Rel" supports it for
one thing, and also the major SQL DBMS named Ingress is looking to add support
for it as a native language.
The syntax of Tutorial D is superficially like SQL and has most of the same
features, but with some extra features and some omission of mis-features.
For example, here are some simple query comparisons (I think):
SQL:
SELECT * FROM mytable
TD:
mytable
SQL:
SELECT col1, col2 FROM mytable
TD:
mytable{col1, col2}
SQL:
SELECT * FROM mytable WHERE col1 = 'foo' AND col2 = 'bar' OR col1 = 'baz'
AND
col2 = 'quux'
TD:
mytable WHERE col1 = 'foo' AND col2 = 'bar' OR col1 = 'baz' AND col2 = 'quux'
or:
mytable MATCHING RELATION { TUPLE { col1('foo'), col2('bar') }, TUPLE {
col1('baz'), col2('quux') } }
> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?
That actually describes SQL to some extent (and Tutorial D). Compared to other
general purpose languages, SQL is fundamentally easier to use, because it
focuses on people just saying "what" they want to happen rather than "how".
If you want your solution now, and use SQLite, you either may have to roll your
own solution, and/or look at the various database wrapper frameworks out there
(there are a bunch for Perl for example) which may help you do this.
I will also say that I'm making a solution for constructing arbitrarily complex
relational or SQL queries out of data structures in Perl, focusing on enabling
what you can do with stored procedures (which includes all other queries),
which
would work with SQLite. But it isn't ready to use yet. You might be able to
use it though depending on your time table. This project is multi-pronged, and
see
http://mm.darrenduncan.net/pipermail/muldis-db-devel/2009-August/thread.html
for the most recent prong that is expected to deliver useables the soonest,
maybe even in a month.
-- Darren Duncan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
