Re: a stored procedure ..with integer as the parameter

Hi,
Well, as noted in another thread, many databases will not allow it (quoting
an integer), so if you ever have to port it to another db, you will be out
of luck.

Also, the string you mentioned is also not an integer. When I tried your
example with the embedded delete statement (e.g. select testfunc1('4;delete
from test3 where numval = 3')), I got an error from the function:

ERROR: invalid input syntax for integer: "4;delete from test3 where
numval = 3"

Which, of course, was the original complaint about the empty string, too.

I couldn't pass Harald's example with extra quotes in as a parameter to the
function, either. It still says it isn't an integer. Which is true, of
course.

My test procedure was a simple plpgsql function, with an integer input
parameter, returning void. Of course, I don't know exactly how the actual
function really works, but I imagine the same kind of type check is going
to take place, at least for plpgsql.

Personally, my applications tend to validate any values that are input by a
user prior to passing them to the database. So, if a number is to be
entered, I check that the input is numeric before I pass it to the
database.

Of course, if anyone can call a function directly (like from pgsql, as
opposed to it being known only to a specialized application), then they
probably have enough access to be able to do most anything they want.

Susan

Alex Turner
<armtuk(at)gmail(dot)com To: "SCassidy(at)overlandstorage(dot)com" <SCassidy(at)overlandstorage(dot)com>
> cc: "surabhi.ahuja" <surabhi(dot)ahuja(at)iiitb(dot)ac(dot)in>, pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] a stored procedure ..with integer as the parameter
10/24/2005
11:44 AM |-------------------|
| [ ] Expand Groups |
|-------------------|

As sort of a side discussion - I have postulated that quoting all incomming
numbers as string would be an effective defense against SQL Injection style
attacks, as magic quotes would destory any end-quote type syntax:

in_value=1

select * from table where my_id='$in_value';

as an example for PHP - Postgres will silenty perform an atoi on the string
to make it a number, but it would prevent:

in_value=1; delete * from user;

select * from table where my_id=$in_value

Am I just smoking crack here, or does this approach have some merit?

Alex

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------