PHP: What is the best way to create higher level query language to create criteria filter in yii

Question

I have database in mysql. On the php application I want to create a textarea and allow users to write a "query" to filter the data on the table.

I want to create a query parser for users to filter data by writing a query. For example:

name="John" AND (age > 20 OR status = 1)

Something like this. This is what users will type and press search button and the system creates sql query out of the "query" provided by user and return filtered results. In the example above, it will create sql query like this:

SELECT * FROM users WHERE name="John" AND (age>20 OR status=1)

I am thinking of parsing this query with regex and create sql from it. Is there any better approach?

Answer 1

Due to the flexibility of SQL, and also for security, you'll want to use a lexer. This way you can allow for very complex queries, validate every field and value, and provide useful feedback on errors. I can think of two good options:

If the user input is relatively simple (e.g. no sub-selects) it shouldn't take long to define the sets of queries you want to support using a lexer already written in PHP, like Phlexy.

If you need more robust support for SQL, I would suggest Antlr because it already has various forms of SQL supported. The downside is PHP doesn't seem to be a supported target language at the moment, so integration will be more difficult.