eval function

On Thu, Jul 28, 2011 at 6:18 AM, Sim Zacks <sim@compulab.co.il> wrote:

I need an eval function that will evaluate a valid SQL expression and return
the value.

I've seen variations of  this asked before with no real answer.

I wrote a function to handle it, but it looks like there should be a better
way to do this (see below).

My use case is a table with date range definitions that should be evaluated
dynamically:

For example:

Period            DateFrom
         DateTo

Last Month     $expr$current_date-interval '1 month'$expr$
 $expr$current_date$expr$

...

select datefrom,dateto into v_datefrom, v_dateto from movingperiods where
period='Last Month';

select * from sales where orderdate between eval(v_datefrom)::date and
eval(v_dateto)

...

CREATE OR REPLACE FUNCTION eval(str text)
 RETURNS text AS
$BODY$
 declare
   row record;
   res text;
 begin
   if lower(str) in ('true','false') then
       res:=str;
   else
       for row in execute 'select (' || str || ')::text as res1' loop
           res:=row.res1;
       end loop;
   end if;
   return res;
 end;
$BODY$
 LANGUAGE plpgsql VOLATILE;

Couple points:
*) why a special case for boolean values?
*) this should be immutable
*) why have a loop? old version of postgres maybe? EXECUTE INTO...

merlin

Merlin Moncure <mmoncure@gmail.com> writes:

Couple points:
*) why a special case for boolean values?

That seemed weird to me too ...

*) this should be immutable

What if the passed expression is volatile? Better to be safe.

regards, tom lane

On Thu, Jul 28, 2011 at 4:18 AM, Sim Zacks <sim@compulab.co.il> wrote:

I need an eval function that will evaluate a valid SQL expression and return
the value.

I've seen variations of  this asked before with no real answer.

I wrote a function to handle it, but it looks like there should be a better
way to do this (see below).

My use case is a table with date range definitions that should be evaluated
dynamically:

There's a major security cost to doing things this way, btw. If you
have a function that is doing this and can possibly be called by the
client app, then you have the possibility of sql injection. Similarly
if any function's arguments can be parsed this way, it's also a
potential issue.

I know you are doing a lot of your work in PL/Python. The plpgsql
code is pretty straight-forward and I am not sure there is any issue
in just simply adding a couple lines of code to relevant PL/Pgsql
functions to avoid making this an interface. With PL/Python, I wonder
if it wouldn't be a bad idea to create a separate class which can
handle this and pass the data down that way. But I would try to avoid
exposing portions of dynamic SQL to a SQL-level API.

Just my $0.02

Best Wishes,
Chris Travers

Merlin Moncure <mmoncure@gmail.com> writes:

Couple points:
*) why a special case for boolean values?

That seemed weird to me too ...

*) this should be immutable

What if the passed expression is volatile? Better to be safe.

---------------------------------

At best, based upon the example using "current_timestamp()", you could only
mark it as being stable, right?

Also not mentioned; what risk is there of this function being hacked? It
places the supplied data within a "SELECT (....) AS column_alias" structure
so it seems to be pretty safe but can you devise a string that would, say,
delete data or something similar. I would expect the following: '1); DELETE
FROM table; SELECT (2' to be dangerous. What functions would you use to
make the input string safe? Does "quote_literal()" plug this hole?

Thanks,

David J.

On Thu, Jul 28, 2011 at 10:08 AM, David Johnston <polobo@yahoo.com> wrote:

Merlin Moncure <mmoncure@gmail.com> writes:

Couple points:
*) why a special case for boolean values?

That seemed weird to me too ...

*) this should be immutable

What if the passed expression is volatile?  Better to be safe.

---------------------------------

At best, based upon the example using "current_timestamp()", you could only
mark it as being stable, right?

Also not mentioned; what risk is there of this function being hacked?  It
places the supplied data within a "SELECT  (....) AS column_alias" structure
so it seems to be pretty safe but can you devise a string that would, say,
delete data or something similar.  I would expect the following: '1); DELETE
FROM table; SELECT (2' to be dangerous.  What functions would you use to
make the input string safe?  Does "quote_literal()" plug this hole?

This function is an absolute no-go if the string literal is coming
from untrusted source, and any robust defenses would ruin the intended
effect of the function. There are a number of nasty ways you can (at
minimum) DOS your database by allowing arbitrary sql. For example,
using generate_series() and advisory_locks you can exhaust lock space.

merlin

On Thu, Jul 28, 2011 at 8:08 AM, David Johnston <polobo@yahoo.com> wrote:

At best, based upon the example using "current_timestamp()", you could only
mark it as being stable, right?

Also not mentioned; what risk is there of this function being hacked?  It
places the supplied data within a "SELECT  (....) AS column_alias" structure
so it seems to be pretty safe but can you devise a string that would, say,
delete data or something similar.  I would expect the following: '1); DELETE
FROM table; SELECT (2' to be dangerous.  What functions would you use to
make the input string safe?  Does "quote_literal()" plug this hole?

I don't think the hole can be plugged. The point of the function is
to execute arbitrary sql code. That means doing SQL injection
purposely in the function. I don't think there is a way around it
because SQL injection is specifically what is desired,

Best Wishes,
Chris Travers

On Thu, Jul 28, 2011 at 8:23 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

This function is an absolute no-go if the string literal is coming
from untrusted source, and any robust defenses would ruin the intended
effect of the function.  There are a number of nasty ways you can (at
minimum) DOS your database by allowing arbitrary sql.  For example,
using generate_series() and advisory_locks you can exhaust lock space.

This is specifically why I would feel extremely uncomfortable exposing
the string literals as function arguments. I don't think you can
generally trust the inputs of general-purpose stored procs.

The thing is if stored proc that might call this is in pl/pgsql, all
you need to do is:

EXECUTE expr INTO myvar;

Or in this case:

DECLARE t_enddate;
t_expr;

BEGIN
SELECT last_date INTO t_expr FROM date_ranges;
EXECUTE $E$ SELECT ('$E$ || t_expr || $E$') $E$ INTO t_enddate;
END;

That's four lines of code extra needed. In PL/Perl or PL/Python, I
think you'd have to create a query and run it. but you could do this
with a module that wouldn't create a stored proc capable of taking
this as the argument.

Best Wishes,
Chris Travers

On Thu, Jul 28, 2011 at 10:36 AM, Chris Travers <chris.travers@gmail.com> wrote:

On Thu, Jul 28, 2011 at 8:23 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

This function is an absolute no-go if the string literal is coming
from untrusted source, and any robust defenses would ruin the intended
effect of the function.  There are a number of nasty ways you can (at
minimum) DOS your database by allowing arbitrary sql.  For example,
using generate_series() and advisory_locks you can exhaust lock space.

This is specifically why I would feel extremely uncomfortable exposing
the string literals as function arguments.  I don't think you can
generally trust the inputs of general-purpose stored procs.

The thing is if stored proc that might call this is in pl/pgsql, all
you need to do is:

EXECUTE expr INTO myvar;

Or in this case:

DECLARE t_enddate;
               t_expr;

BEGIN
SELECT last_date INTO t_expr FROM date_ranges;
EXECUTE $E$ SELECT ('$E$ || t_expr || $E$') $E$ INTO t_enddate;
END;

That's four lines of code extra needed.  In PL/Perl or PL/Python, I
think you'd have to create a query and run it. but you could do this
with a module that wouldn't create a stored proc capable of taking
this as the argument.

That's not the same thing though -- you are just copying a string to
another string essentially. The whole point regarding eval is to
evaluate sql expressions, not string literals...you can't have it both
ways.

You _can_ trust the string inputs for stored procs...static sql is
generally ok, as is dynamic sql via EXECUTE...USING. Certain things
are unsafe, but generally easily avoided. Sanitizing sql is IMO much
easier in a proc than on the client.

merlin

On 07/28/2011 05:11 PM, Tom Lane wrote:

Merlin Moncure<mmoncure@gmail.com> writes:

Couple points:
*) why a special case for boolean values?

That seemed weird to me too ...

I'm using 8.2 and "cannot cast type boolean to text"

*) this should be immutable

What if the passed expression is volatile? Better to be safe.

regards, tom lane
*) why have a loop? old version of postgres maybe? EXECUTE INTO...

That's what I was looking for. Somehow I missed the Into.

On 07/28/2011 06:28 PM, Chris Travers wrote:

On Thu, Jul 28, 2011 at 8:08 AM, David Johnston<polobo@yahoo.com> wrote:

At best, based upon the example using "current_timestamp()", you could only
mark it as being stable, right?

Also not mentioned; what risk is there of this function being hacked? It
places the supplied data within a "SELECT (....) AS column_alias" structure
so it seems to be pretty safe but can you devise a string that would, say,
delete data or something similar. I would expect the following: '1); DELETE
FROM table; SELECT (2' to be dangerous. What functions would you use to
make the input string safe? Does "quote_literal()" plug this hole?

I don't think the hole can be plugged. The point of the function is
to execute arbitrary sql code. That means doing SQL injection
purposely in the function. I don't think there is a way around it
because SQL injection is specifically what is desired,

Best Wishes,
Chris Travers

On one hand the hole can't be plugged because as you mentioned that is
the point of the function. On the other hand, if the function is not
being run as security definer, the account running it would need to have
the rights to do whatever he is injecting. If "1); delete..." would
work, then the user could just as easily do Delete... without using the
function.

The only problem that I see (correct me if I'm wrong) is anonymous
injection through a user that has rights that we wouldn't want the
actual user to have, which is not recommended in any case.

Sim

2011/7/30 Sim Zacks <sim@compulab.co.il>:

On 07/28/2011 05:11 PM, Tom Lane wrote:

Merlin Moncure<mmoncure@gmail.com>  writes:

Couple points:
*) why a special case for boolean values?

That seemed weird to me too ...

I'm using 8.2 and "cannot cast type boolean to text"

are you aware that support for 8.2 will stop in December 2011, right?
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

We are in the process of building regression tests for our 700+ functions.

I hope to have it finished in about 6 months.

Sim

On 07/31/2011 08:31 AM, Jaime Casanova wrote:

On Sat, Jul 30, 2011 at 9:50 PM, Sim Zacks <sim@compulab.co.il> wrote:

On one hand the hole can't be plugged because as you mentioned that is the
point of the function. On the other hand, if the function is not being run
as security definer, the account running it would need to have the rights to
do whatever he is injecting. If "1); delete..." would work, then the user
could just as easily do Delete... without using the function.

The only problem that I see (correct me if I'm wrong) is anonymous injection
through a user that has rights that we wouldn't want the actual user to
have, which is not recommended in any case.

You have two issues. One as you mention is privilege escallation.
The other which you don't is the question of whether you are trying to
limit your business logic calls to what the sprocs can do. Once you
add this, that's blown. So the question is whether someone using the
client to your application server can enter data that gets around the
limitations of interface and causes other issues. Maybe they can
overwrite who issued an invoice in order to cover for embezzlement or
otherwise tamper with the assumptions of default values etc that might
be in the database..... You never know.

Now, just because this function is not security definer doesn't mean
it can't result in privilege escalation. Instead I would suggest
thinking of this as a "risk" instead of as a "hole."

1) Do you know everywhere this function is called?
2) What if this is ever called by a security definer function?
3) If it has an unobtrusive name like eval(), will it be missed on a
code audit?

You are right that by itself this isn't a hole. However it increases
the risks I think considerably that holes will happen.

Good security IMHO doesn't start with plugging holes. It starts with
designing so that holes are less likely and less severe. That's why I
wouldn't. YMMV.

Best Wishes,
Chris Travers

On 07/31/2011 09:48 AM, Chris Travers wrote:

On Sat, Jul 30, 2011 at 9:50 PM, Sim Zacks<sim@compulab.co.il> wrote:

On one hand the hole can't be plugged because as you mentioned that is the
point of the function. On the other hand, if the function is not being run
as security definer, the account running it would need to have the rights to
do whatever he is injecting. If "1); delete..." would work, then the user
could just as easily do Delete... without using the function.

The only problem that I see (correct me if I'm wrong) is anonymous injection
through a user that has rights that we wouldn't want the actual user to
have, which is not recommended in any case.

You have two issues. One as you mention is privilege escallation.
The other which you don't is the question of whether you are trying to
limit your business logic calls to what the sprocs can do. Once you
add this, that's blown. So the question is whether someone using the
client to your application server can enter data that gets around the
limitations of interface and causes other issues. Maybe they can
overwrite who issued an invoice in order to cover for embezzlement or
otherwise tamper with the assumptions of default values etc that might
be in the database..... You never know.

If the user has rights to overwrite the invoice, they can do it
regardless of this function. What you are suggesting is that the user
has rights to overwrite an invoice, but the application layer doesn't
allow them to do it easily. That is also a security risk. You are
talking about web clients, with the connection to the database coming
from the application server. With desktop clients, the database
connection is created on the client, so the pg_hba doesn't know the
difference between login with psql or the application.
This type of issue has to be taken care of with a proper audit trail.

Now, just because this function is not security definer doesn't mean
it can't result in privilege escalation. Instead I would suggest
thinking of this as a "risk" instead of as a "hole."

1) Do you know everywhere this function is called?
2) What if this is ever called by a security definer function?
3) If it has an unobtrusive name like eval(), will it be missed on a
code audit?

Any security definer function should be designed with security in mind.
That is the responsibility of the dba. You can't limit the dba in what
he can do just in case he doesn't know what he is doing. You can
suggest, but if the dba thinks he knows what he is doing, give him all
the tools to do it.
If the function can cause privilege escalation when not in a security
definer function, then I would say there is a serious problem with the
security system of the engine. Can you think of any possibility where a
function would allow privilege escalation when it is not in a security
definer function?

Any security definer function should be designed with security in mind. That
is the responsibility of the dba. You can't limit the dba in what he can do
just in case he doesn't know what he is doing. You can suggest, but if the
dba thinks he knows what he is doing, give him all the tools to do it.
If the function can cause privilege escalation when not in a security
definer function, then I would say there is a serious problem with the
security system of the engine. Can you think of any possibility where a
function would allow privilege escalation when it is not in a security
definer function?

No I can't. But you can actually prevent this problem by making the
function security definer. Something like:

CREATE SCHEMA evaljail;
CREATE USER evaljail;
GRANT USAGE ON SCHEMA evaljail TO evaljail;
REVOKE CREATE ON SCHEMA evaljail FROM evaljail;
REVOKE USAGE ON SCHEMA public FROM evaljail;
CREATE FUNCTION evaljail.eval......
ALTER FUNCTION evaljail.eval OWNER TO evaljail;
ALTER FUNCTION evaljail.eval SECURITY DEFINER;

Now the function has no table access at all.

postgres=# select evaltest.eval('select * from public.test');
ERROR: permission denied for schema public
LINE 1: select (select * from public.test)::text as res1
^
QUERY: select (select * from public.test)::text as res1
CONTEXT: PL/pgSQL function "eval" line 8 at EXECUTE statement
postgres=# select evaltest.eval('1 - 2');
eval
------
-1
(1 row)

Best Wishes,
Chris Travers

i am new to postgresql and not very good in english language.i need
help to understand,how i can make,(by using postgresql)something like
microsofts access.i remebre you see a lot of buttons and
functions.when you finish inserting some data,you have something
permanent like a software that remains for ever.and everytime you need
to insert new data ,you can simply.
another example is openoffice's base.where you can make tables,queries
and lot more.
what i want to know is how can one use postgresql like microsoft
access or openoffices base?

2011/7/31, Chris Travers <chris.travers@gmail.com>:

On 07/31/11 9:43 AM, saeed ahmed wrote:

i am new to postgresql and not very good in english language.i need
help to understand,how i can make,(by using postgresql)something like
microsofts access.i remebre you see a lot of buttons and
functions.when you finish inserting some data,you have something
permanent like a software that remains for ever.and everytime you need
to insert new data ,you can simply.
another example is openoffice's base.where you can make tables,queries
and lot more.
what i want to know is how can one use postgresql like microsoft
access or openoffices base?

Access and Base are not database servers, they are application
development systems, which happen to have a database behind them. In
fact, you can use Access or Base with a PostgreSQL database.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

Le dimanche 31 juillet 2011 ￜ 18:43 +0200, saeed ahmed a ￜcrit :

i am new to postgresql and not very good in english language.i need
help to understand,how i can make,(by using postgresql)something like
microsofts access.i remebre you see a lot of buttons and
functions.when you finish inserting some data,you have something
permanent like a software that remains for ever.and everytime you need
to insert new data ,you can simply.
another example is openoffice's base.where you can make tables,queries
and lot more.
what i want to know is how can one use postgresql like microsoft
access or openoffices base?

Hi saeed,

It can be done by using ODBC, or SDBC drivers. See
OpenOffice/LibreOffice documentation.

But I find installing a postgres server is overkill for office
applications. May be it would be easier for you to use SQLite? very
performant file-based database engine:

http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Using_SQLite_With_OpenOffice.org

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique