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

[Alex Turner <armtuk@xxxxxxxxx>]

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

On 10/21/05, SCassidy@xxxxxxxxxxxxxxxxxxx <SCassidy@xxxxxxxxxxxxxxxxxxx > wrote:

First, you should not quote an integer value going into an integer column -
bad habit to get into.

Second, empty string is not an integer.

Susan



                           "surabhi.ahuja"
                      <surabhi.ahuja@xxxxxxxx        To:       <pgsql-general@xxxxxxxxxxxxxx>
                      .in>                           cc:
                           Sent by:                  Subject:   a stored procedure ..with integer as the parameter

                                                      |-------------------|
                      pgsql-general-owner@pos         | [ ] Expand Groups |
                      tgresql.org                     |-------------------|


                           10/20/2005 11:03
                      PM






i have a stored procedure

insert_table(integer)
which does "insert into table (x) value ($1)";

now in my client i call the stored procedure as

select insert_table("3");

it works fine and inserts 3 into the table

but suppose i give

select insert_table("");

it gives an error ...saying "  invalid input syntax for integer: "

please suggest a solution to this problem

thanks,
reagrds
surabhi




----------------------------------------------------------------------------------------------
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster