0

I would like to evaluate a pure-calculation string in Postgres.

For example: eval('234 + 65 * 3')

The function is NOT constant, so could also simply be 2 + 2

Expecting something like SELECT eval('2 + 2') AS result

I read about huge security issues regarding eval() functions, but those seem to incorporate SELECT statements. Here pure calculation requirements.

kvdmolen
  • 183
  • 2
  • 13
  • What's the point of the eval? Why not just `SELECT 2 + 2 AS result`? – JJJ Mar 30 '17 at 13:02
  • The string is calculated dynamically, using regexp and results from other queries.. If not.. why would I ask this question.. – kvdmolen Mar 30 '17 at 13:19
  • 1
    See this: http://stackoverflow.com/questions/7433201/are-there-any-way-to-execute-a-query-inside-the-string-value-like-eval-in-post – Oto Shavadze Mar 30 '17 at 13:24
  • @OtoShavadze Thanks, I've seen that, but this worries me: "Also bear in mind that this opens a huge security risk" That's part of my question, is there something safe for pure calculations.. – kvdmolen Mar 30 '17 at 13:35

1 Answers1

4

You need PL/pgSQL:

create or replace function f(_s text)
returns numeric as $$
declare i numeric;
begin
    execute format('select %s', _s) into i;
    return i;
end;
$$ language plpgsql;

select f('1 + 1');
 f 
---
 2
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Exactly! No security issues here? Would there be a need to scan the string for words like "select" or "insert"? – kvdmolen Mar 30 '17 at 13:34
  • @kvdmolen security is a big issue here. f.ex. calling `f('id from (delete from users returning *) s limit 1')` *might* run (and there is no `select` in it). – pozs Mar 30 '17 at 13:39
  • @pozs Yes indeed.. would something like this work you think? `EXECUTE format('SELECT %s', replace(replace(_s, 'delete',''),'select','')) INTO i;` So, replacing the words `select` and `delete` with `''`. It's not sweet, but at least it will return an error. – kvdmolen Mar 30 '17 at 14:29
  • 1
    @kvdmolen if you really only have formulas, try validating it with regex instead (before executing) f.ex. `_s ~ '^[\s\d\.\+\-\*/\(\)]+$'` – pozs Mar 30 '17 at 14:40