Getting AND/OR functionality to a MySQL query Builder via POST data

This could easily fit in to the JavaScript section as well, but I believe it is at least equally as likely to be solved in PHP. All that to say, if it is solved in JavaScript, don't burn anything down because it's here, please.

I have an interface that is used to simplify queries against a mysql database. The interface has a "query input" form with a single text type input control. User's can type h:host.domain; and that gets POSTed to a php script which will generate a query "SELECT blah FROM blah WHERE host='host.domain'" and echo back the json formatted data via ajax. I'm just starting to use ajax and json, and I generally have to use a reference for my SQL queries, so just ignore any mistakes, or misterminology there. All that currently works.

Where I'm hitting a wall is mainly in concept. I want to integrate the ability to do something like (h:host1 | h:host2; ) to generate a (host='host1' OR host='host2'), and the same for ANDs, which are currently implicit.

Right now, a (h:host1,host2; mc:some string; ) will send two post variables to the php (hostEquals="host1,host2" and messageContains="some string") <- formatted for post of course. The PHP them just parses through all $_POST and uses the key to decide what the data is (hostEquals, messageContains) and splits up by comma (for now) the values which are dropped in the proper arrays and later those arrays are parsed through to generate the SQL query.

Right now, I'm thinking to just do separate POSTS for each | (logical OR), but I'm thinking there has to be a better way to handle ORs in a single POST.

In short I'm looking for a way to format the data posted so that I can cleanly/easily interpret the ANDs and ORs in PHP. OR (not a pun) any better ideas to restructure the entire application from the ground up, if I'm handling it poorly as a whole.

Thanks for any ideas.

Another example because I'm notoriously bad at explaining what I'm thinking:
(h:host1; | mc:host1; ) & (m:interesting message; )
generates a query to find any records where "[host IS host1 OR message CONTAINS host1] AND [message IS "interesting message]"

And another (note that the comma delimited values already get handled as implicit ORs but are limited to common database key name. Also, greater than is already handled)
h:host1,host2; | l:>3
generates a query to find any records where "[host IS host1 OR host2] OR [level GREATER_THAN 3]"

 

Well... Unless you're building a replacement for phpmyadmim, this sounds like a very bad idea. Unless you're very good at cleaning the input from the user, this is easily exploited.
Also, why would there be more than one host?
Using relatively cryptic variable names is also not recommended while developing, especially when asking for help. Providing some actual code examples is also helpful instead of abstracts.
Since this is all done via some form, why don't you just provide a form where the user selects how to build the query? If there's more than one condition, force the user to select either AND or OR. Then send the query variables in an array where you save the and/or inputs as well, and parse the array in sequence, building up the query.

 

It's a front-end for a syslog server. Think Splunk minus most of Splunk. The idea is a powerful means to search syslog messages stored in mysql database tables for people who don't do SQL.

I do a lot of sanitizing the data in php and use pure mysqli prepared statements to help prevent exploitation, and unless someone can break the login system it should only be employees in my IT department using the system. I also plan to (haven't yet) have a system that logs what users POST, and a token system that will require POSTs to be validated to prevent POSTing directly.

It isn't secure currently, but unless I miss something, or some underlying system is vulnerable, it should be in the end. Security is actually the reason I don't generate the SQL in javascript. It's much easier to sanitize the data and generate the query than to sanitize the query IMO.

As for posting code, with something like this the code is very long for what it does. I can post it but it's mainly a lot of switch/case blocks etc for parsing the $_POST data. I assumed pseudocode or functional explanation would be more useful at this point.

Finally, the cryptic names where sure enough not a great idea on my part. In the application you can basically use the shortest unique part of key names (think cisco IOS), so host:hostName becomes h:hostName. I should have probably been more specific that the "query language" (very liberal way to describe it) is structure where keyname and search string(s) are separated by : and multiple search strings are separated by commas. Each criteria is terminated with a ';'. To search for any syslog messages from say outsideFirewall and insideFirewall, we can do

host:outsideFirewall,insideFirewall;

Code (markup):

. There are also !, >, <, etc operators. This is working now, so I didn't think about it being pertinent.

I'm pretty sure I would get ragged if I posted all the code, but I will try to be more specific using the parts relevant to a specific query.

User enters host:outsideFirewall,insideFirewall;

JavaScript:
splits the the string by ';'
then splits each of those by ':' and kills any junk whitespace
then generates an ajax request using post with a variable "hostEqual" (since there is no ! or < or > etc to consider) and a value "outsideFirewall,insideFirewall"

PHP:
has an empty array hostEquals[]
does a foreach($_Post as $key => $value){
   $values = explode(",", $value);
   foreach($values as $v){
      switch($key){
         case "hostEqual":
            $hostEquals[] = $v;
            break;
      }
   }
}

Later, after processing that doesn't matter here, the hostEquals array is parsed and sanitized and each value is attached to a query simply WHERE host='outsideFirewall' OR host='insideFirewall'

A very common one that I use is
program:dhcpd; mcontains:DHCPOFFER, 10.0.0.55; table:logs7
(mcontains is a keyword for message [msg in the table] contains)
generates (something like this. typing it our here, so look over typos)
SELECT host,facility,priority,level,datetime,program,msg,seq FROM ? WHERE msg LIKE CONCAT('%', ?, '%') AND msg LIKE CONCAT('%', ?, '%') AND program=?
Then binds the parameters (ssss, "logs7", "DHCPOFFER", "10.0.0.55", "dhcpd")

Code (markup):

All of that is how it currently works. The question is looking a at a good way to handle AND/OR operators.

 

Aha. Well, that sounds simple enough, but I would still assume (even though the users you have probably knows a bit about the stuff they're looking for) that the users are idiots. Hence, make it as simple as possible (we all know idiot-proof doesn't exist).
I would still suggest something akin to phpmyadmin's search-form - where you can select criteria based on what is already in the database (the actual columns in tables) and select whether you want a specific column to match, not match, be greater than or smaller than and so forth and so on.
Also, as I said, if you have a selector which is AND or OR for each criteria, you can build quite complex structures by combining stuff - of course, to get the right results, you'll have to have a query-builder which is smart enough to cater for which elements go together (in a search-query, multiple and/or's tend to work better with parenthesis around the right selectors).
WHERE id = 1 AND (group = 3 OR group = 5) is different from WHERE id = 1 AND group = 3 OR group = 5. And so forth an so on. It's a rather complex task you've taken upon you, and there's probably gonna be a bit of redoing code while working on it.
As for the sending of content, it could be rather easily parsed by for instance using one , for AND and two ,, for OR - or wouldn't that work? You would still have to have a way to actually know what is what, of course, and it doesn't cater for parenthesis and such.

 

The Query Builder is in the plan, but frankly I will personally lose respect for anyone who uses it.
The interface (as it is planned) is a single text input that stretches the width of the screen, minus two 17x17px buttons (search, and builder) on the right. Below that is a multi functioning "output" div that fills the remainder of the window with a cool (if I do say so myself) custom scrolling control when needed. The "output" div will contain results, errors, and the query builder form when the builder button is clicked. Currently there is a standard dropdown for saved queries, but that will eventually become a small arrow button at the end of the input bar. I plan to put a lot of work into that one input bar to bring intellisense functionality to it, so that anyone who SHOULD be using this SHOULD be able to work from there. I also have a context menu that currently does very little, but I will eventually build in more "context" intelligence, so that users can drill down, copy, email, highlight similar results based on different criteria, etc. Bracketing is also part of the plan, but is kinda the same question. How to translate that between html/javascript and php via post variables.

I'm pretty set on the interface being the way I plan it. It's the only fun piece of the project. The piece that I'm still dwelling on is a good way to handle getting from input to something that I can POST and use in PHP to handle complex AND/ORs. At this point, I'm leaning toward multiple calls to the PHP (multiple POSTS). I can't think of another good clean way to do it.

I should mention, for those who don't find it obvious, that I am not a web developer. I'm a network admin. I play with this stuff because I like it. That means I could easily be missing some technology that makes what I'm doing simple, and it also means that I'm not under any time restraints or design guidelines.