How can I provide a user edit-level control over currently hardcoded formulas?

Question

Current Situation

I maintain a website that has a simple form that users fill out with simple engineering data. They press Submit, and the PHP code takes over and does various basic arithmetic on those numbers, according to the hardcoded formulas.

Every time there is a change, user has to talk to me, and I have to change the formulas in the code, and then update the repository.

User requested control over formulas. I could give them access to code, but they are not a programmer and changes are sufficiently rare to where most likely I will end up helping them anyways. As in, I might as well keep code access myself.

Excel on the Web approach

Another way I could do so is to implement subset of "Excel On the Web", as essentially that's the functionality that's requested. How though I am not sure. Also, output of formulas is used in several mission-critical code pieces, so it's not just excel on the web where you enter formulas and see results without further action. The output of the formulas is used in critical code used to drive various other functionality. Aka, I need an user-editable code, results of which I can plug back into the main source code.

eval approach

I could have free-form box where user enters specific PHP code and then I just use eval to get the desired result, but eval opens huge security hole on the website.

Parser Approach

I could do like above but instead of eval, implement a full blown parser that does the limited excel functionality for me. I am leaning towards this way but I really don't know how to set it up best, as writing my own parser I may end up doing a lot of work. For example, should the interface be "per line" or "free form box"? Coding up the interface can become a significant amount of work in itself. Coding up improper interface can be even more work if later it will have to be tore down, in order to implement a better one.

Question

What's a good way to allow user-edit-control over formulas that are currently hardcoded in the code, but without it becoming a huge project?

Just to give scale, perhaps something that can be implemented within one or max two week, including testing.

• Kasey Speakman: It sounds like Excel is actually the right tool for the job. Maybe your new feature should just be to import CSV data from Excel that the user ran their custom data functions on.
• Dennis: not sure if I got that.. I can probably import CSV formulas from Excel, but then I'd have to parse them in some way. The issue is that formulas run quite a lot of various other functionality so they need to be computed automatically many times, without user intervention. So entering data once won't do it. The data user submits essentially needs to be a program (aka formulas), which can then be run as needed.
• Winston Ewert: Why don't they just use excel? What is your program offering that excel doesn't?
• Dennis: @Winston: as I know it, using excel that already has formulas keyed in requires you to 1) manually enter/update input data to be fed into formulas, 2) manually collect/observe results of the computation. In my case, formulas in the PHP code are "the cog in the machine" that is used by many users sometimes hundreds of times over the web interface. It needs to be automated. I know how to use Excel manually, but is there is a way to embed Excel sheet to be "a part of my PHP code", to which I feed it input and it returns output, and is also editable by a user without my intervention?

Answer 1

Outsource it.

Just a few days ago, Microsoft opened up its Office 365 Excel REST API for general development. You suggest that these calculations could be done in Excel, so this seems like a very logical solution to me. The docs are here.

If that doesn't work for you, then I would recommend using ANTLR or another parser generator to creat a domain specific language for your end user. You're right about not wanting to eval() arbitrary code. The parser will give you finer grained control, but will also be a bigger pain to implement and maintain than coding against the Excel Web API.

• Dennis: thanks. looking into it, to see if it's suitable. i.e. for 365 if it's across-the-Internet web service call, it may do a performance hit as I have some code pieces that execute 80+ times in a tight loop with every request. If ANTLR allows to compile or cache or create PHP code automatically that should work too. But if it's interpreted with every request that may cause performance issues..

Answer 2

The Symfony Expression language (http://symfony.com/doc/current/components/expression_language.html) might suit your needs. It "provides an engine that can compile and evaluate expressions", allowing you to execute the formulae in a sandboxed environment.

• Dennis: thanks I like that for ability to compile expressions into PHP code, that should prove to be useful with performance.