Thread: eval function
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;
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: > 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 >
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? > 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
> 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>:
>> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
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