4

I'm making a command line tool that allows end users to query a statically-schemed database; however, I want users to be able to specify boolean matchers in their query (effectively things like "get rows where (field1=abcd && field2=efgh) || field3=1234"). I did Googling a solution, but I couldn't find anything suitable for end users--still, this seems like it would be a very common problem so I suspect there is a standard solution.

So:

  1. What (if any) standard query "languages" are there that might be appropriate for end users?
  2. What (if any) de facto standards are there (for example, Unix tools that solve similar problems).
  3. Failing the previous two options, can you suggest a syntax that would be simple, concise, and easy to validate?
weberc2
  • 258
  • 1
  • 6
  • 1: SQL; 2: ANSI standard SQL; 3: SQL will be validated by whatever RDBMS you submit it to; most likely you'll get a helpful message back saying exactly where the problem was. If the schema naming convention is not end-user friendly define views to present appropriately and grant rights accordingly. – Michael Green Jul 27 '14 at 11:25
  • Thanks for the comment @MichaelGreen. I naturally considered SQL, but I think I want a read-only subset thereof. If possible, I would like to avoid manually enforcing that rule. I suppose most SQL implementations support read-only configuration, but I would like to return to the user a friendlier message than what the database is likely to yield and translating each low-level database error to a *useful* high level user error sounds like it would require in-depth knowledge of each sql backend I intend to use. Perhaps this is still the best solution, however... – weberc2 Jul 27 '14 at 14:08
  • this is an off topic comment wrt the commandline/language stuff, but im wondering if you have already looked at any (foss) BI reporting tools if they meet ur requirement – dbza Aug 14 '14 at 02:57
  • Just wondering, what is statically-schemed? Does it consist of 1 or more tables? Why not simply analyze the user entered SQL Query string and ensure it starts with a 'Select' if you only want queries only? – NoChance Aug 25 '14 at 22:10
  • @EmmadKareem "statically-schemed" means the schema won't change after the db has been initialized. I'm not sure, but I'm guessing your "SELECT" filter proposal doesn't give me the read-only guarantees I'm looking for. If you can prove (i.e., offer a credible link) that any SQL string beginning with SELECT is guaranteed read-only, then submit it as an answer. – weberc2 Aug 25 '14 at 22:17
  • You have a point. A SELECT can be harmful (e.g. http://stackoverflow.com/questions/444248/how-to-prevent-sql-injection-on-user-generated-sql-queries). However, you can protect the database in several ways such as (a) control user privileges and/or Create views that don't allow updates and let the user use the views not the tables (this may be controlled by privileges). If the data is static after loading, you may be able to make the entire database read only but this depends greatly on your database engine. – NoChance Aug 25 '14 at 22:49

1 Answers1

2

This is called faceted search. There are tools like Solr that can help you do it, but you don't want to use a language unless your users are highly technical (like consumers of a web service API), in which case you just use something SQL-like.

For inspiration on user interfaces, look at search results of companies with huge product lines, like Amazon or Digikey. Basically after you start a search you are presented with a list of parameters by which you can refine your search, allowing you to drill down until you get the products you want. This kind of interface is probably so familiar to you that you may not even have realized you were effectively querying specific fields of a database.

Another example of end user searching based on many fields is bug tracking websites. They generally have a general purpose GUI-based search, and also a SQL-like query language for advanced searches. If your users are technical or semi-technical, that might be a good model to follow.

Karl Bielefeldt
  • 146,727
  • 38
  • 279
  • 479