How to accomplish a task of evaluating editable variables and formulas, using PHP?

Question

Task as defined:

User needs to be able to define their own variables and formulas to operate on those variables, to get output based on their inputs. Since formulas and variables and their inputs change often, user needs to have control.

Example

//inputs (may be entered directly or provided by database)
$a = 4;
$b = 6;

//formulas:
$c = $a + $b * 5.35;

//outputs
print $c;

This is to be entered by a user into i.e. a textbox on a website, and have that be saved as "master template" to be used to compute the author's computations.

Thought #1

Use PHP Eval: (function that runs arbitrary source code)

But ...

If eval() is the answer, you're almost certainly asking the wrong question.
-- Rasmus Lerdorf, BDFL of PHP

but eval() may actually work well for this, but that means

• sanitizing inputs
• filtering outputs
• allowing expected user and unexpected (malicious) users free run of eval
• teaching users subset of PHP

Thought #2

Implement a simple parser for a simple language (i.e capable of processing the above example). It is a tad more work, or a bit more work depending on what functionality users will ask for and how the project will develop in the future.

Question

Are there any other ways to solve this problem? In effect, I am designing a module that is supposed to work like a mini-Excel spreadsheet, with power of using arbitrary user-modifiable formulas and variables, and outputs.

Answer 1

One reason "you're almost certainly asking the wrong question" is that properly sanitizing and filtering requires parsing. In other words, you have to implement thought #2 in order to implement thought #1 safely for untrusted input. Most people don't realize that and so end up doing a very poor job of it. Trust me (and trust Rasmus Lerdorf for that matter), you're better off biting the bullet and going with a proper parser from the beginning.

Google "PHP parser generator" for lots of resources to get you started. There's a bit of a learning curve, but luckily for you, mathematical expression evaluation is the "hello world" of these kinds of libraries.

Answer 2

There's an XY problem floating around in this question. I'll try to answer both parts of it.

From comments, the core problem:

I am trying to ease the job of the people in my company who came to me requesting to find a better solution than using Excel spreadsheets to do various custom-product computations, that change frequently. They want to move away from Excel, and onto the web, and they want to be able to update formulas and variables themselves. Excel is not on the web and they have to pass excel sheets to each other each time they make a change, creating tons of outdated excel sheets in the process that linger on people's machines. I am to try and make this process better.

This is a content management problem - not a programming one. You are looking for solutions that involve the use of Excel on the web. One option is a enterprise content management solution such as Sharepoint. Another is the Excel Web App. This is the problem you are really trying to solve - having a consistent single source of the data and calculation.

---

Now, the "lets try to implement this in another language." Your options are:

• Unsafe full language with eval
• Writing a domain specific language that is as powerful as Excel

One should realize that the spreadsheet is, at its heart, a functional language with equations and data mapped onto a cartesian plane of rows and columns.

You are going to run headlong into Greenspun's tenth rule

Any sufficiently complicated C or Fortran program contains an ad hoc, informally-specified, bug-ridden, slow implementation of half of Common Lisp.

You are writing another functional language in php (if you're lucky - thats to help users retain as much of their skills from Excel to this language). You are going to try to make it so that it is 'safe', and 'easily understood by someone who has no more programing experience than writing Excel equations' (and lacking the useful checking that excel provides you).

You are going to persist this information (the code is data) into a database (if you are lucky - some other data store if you aren't) somehow, pull it out of the persistence, evaluate it all on the fly, allow someone to make a change, content management on top of that (who made the change?), and possible versioning too once you're done with the project.

The thing you are going to end up building to try to support the core problem is not going to be small. Ever time you turn around users are going to be asking for another function to be added. Could you add ACCRINT? How about LINEST? How do I do a LOOKUP or VLOOKUP in this language?

This is a wonderful idea to have a team of talented coders to be working on as a product to sell. It is an impossible task for a side project for a single programmer (or even small team) with other priorities and fires going.

I'm not saying you can't do it. But the problem that you are trying to solve with this solution isn't even going to be a suitable duct tape solution for any duration of time... and the support of the hypothetical application will be a full time job of its own.

Use an existing tool that solves your problem. Its price is likely a small fraction of a FTE.