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

On Fri, 21 Oct 2005, surabhi.ahuja wrote:

>  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

Don't try to use an empty string as an integer?

Seriously, you need to give information about what you want to happen,
because an empty string isn't a valid integer and can't really be
converted into one (we explicitly removed code that handled this case
because we thought it didn't make sense).

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@iiitb.ac        To:       <pgsql-general@postgresql.org>
                  
                      .in>                           cc:
                  
                           Sent by:                  Subject:  [GENERAL] 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
----------------------------------------------------------------------------------------------

surabhi.ahuja wrote:
> from the client I get : "200", "surabhi", "10"
> now i have to make a call to the stored procedure using the above received strings.
> will not the "200" be converted to a valid integer before saving into the database ..and same with the smallint
values.

That wasn't the error you reported. You reported a problem with storing
"" which not only isn't a number, it isn't even text. Double-quoting
indicates it's a name (i.e. an identifier - a table name or similar).

So - this suggests some questions:
1. Do you know what data-types the client is providing?
2. What validation/checking do you do on the client-supplied data?
3. How are you building your query - via Perl's DBI system, PHP's
PEAR:DB classes, JDBC?

--
   Richard Huxton
   Archonet Ltd

On Mon, 24 Oct 2005, surabhi.ahuja wrote:

> from the client i receive a vector of strings ...which i have to pass as
> arguments to the stored procedure.
>
> That stored procedure has valid arguments data types
>
> for instance
> i have a stored procedure as follows:
>
> insert(integer, varchar(256), smallint)
>
> from the client I get : "200", "surabhi", "10"
>
> now i have to make a call to the stored procedure using the above received strings.
>  will not the "200" be converted to a valid integer before saving into
> the database ..and same with the smallint values.

I don't see how this example is directly related to what you sent before
with an empty string.

Theoretically, yes, I believe a quoted 200 ('200') and a quoted 10 ('10')
should work for those two positions (unless there's a question where
there's another insert function that takes different arguments that grabs
it first). As an aside unquoted/uncast 10 will not currently match the
smallint argument, though, so it may be better to simply not use smallint
arguments.

However, from your first mail, there was a question of ('') which is not a
valid integer.  Calling insert('200', 'surabhi', '') or insert('',
'surabhi', '10') are going to give the "invalid input syntax for integer"
because an empty string doesn't match the pattern for an integer.

> From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
> Sent: Fri 10/21/2005 12:11 PM
> To: surabhi.ahuja
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] a stored procedure ..with integer as the parameter
>
> On Fri, 21 Oct 2005, surabhi.ahuja wrote:
>
> >  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
>
> Don't try to use an empty string as an integer?
>
> Seriously, you need to give information about what you want to happen,
> because an empty string isn't a valid integer and can't really be
> converted into one (we explicitly removed code that handled this case
> because we thought it didn't make sense).
>
>
>
>
>

In article <33c6269f0510241144s680be862pfdc0c59dcba06eee@mail.gmail.com>,
Alex Turner <armtuk@gmail.com> writes:

> 1.  ( ) text/plain          (*) text/html
> 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?

The former :-)
The correct defense against SQL injection is proper escaping, not quoting.
How about $in_value = '1''; delete * from user'?

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@gmail.com        To:       "SCassidy@overlandstorage.com" <SCassidy@overlandstorage.com>
            
                      >                        cc:       "surabhi.ahuja" <surabhi.ahuja@iiitb.ac.in>,
pgsql-general@postgresql.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
----------------------------------------------------------------------------------------------

Am Dienstag, den 25.10.2005, 10:24 +0530 schrieb surabhi.ahuja:
>  oops i am sorry,
> i mean from the client i ll be getting values (which i need to insert
> into the table) in the form of strings:
>
> and i form the insert command as follows:
>
> function(char *a, char *b, char *c)
> {
> char command[1024];
> sprintf(command, "select insert('%s','%s','%s')", a,b,c);
> execute the above command;
> }
>
> the above is just the pseudo code
>
> the stored procedure in turn is as follows (psudocode):
>
> insert(smallint , smallint, varchar(256))
> begin
> insert into table 1 values ($1, $2, $3);
> end

I'm not sure this serves much purpose if it isnt just
for experimenting ;)

char -> int is simply done by casting (even automatically)
so your insert reduces to:

INSERT INTO table1 (col_a,col_b,col_c) VALUES (a,b,c);

(with or w/o stored function)

simply sprintf into a string can be a very serious
security hole btw.

In article <33c6269f0510241421p2802061dhe79b3d99495b8087@mail.gmail.com>,
Alex Turner <armtuk@gmail.com> writes:

>      delete * from user; > select * from table where my_id=$in_value > Am
>      I just smoking crack here, or does this approach have some merit?
>      The former :-) The correct defense against SQL injection is proper
>      escaping, not quoting.  How about $in_value = '1''; delete from
>      user'?

> This would be escaped by magic_quotes resulting in:
> select * from table where my_id='\'1\'\'; delete from user \'', which would
> result in an error, and a failed attack would it not, which would be a good
> thing?

If your "magic_quotes" are magic enough to not blindly surrounding the
argument in quotes, but also escape dangerous chars like "'" inside
the argument, then you're safe.

> I tried to create this scenario, but in a trasactional environment, it
> executes, but blew the transation so the data never committed as the select
> query generated an error with the insert on the end...

... and that's exactly what it should do.  You just need to catch the error
and generate a meaningful error message.

Am Mittwoch, den 26.10.2005, 09:37 +0530 schrieb surabhi.ahuja:
> what do u suggest i do then in that case?
> i mean how should i make a query - i mean how do i make a command?
>
Need more details. What language are you using, what is the exact
problem and so on.

surabhi.ahuja wrote:
> what do u suggest i do then in that case?
> i mean how should i make a query - i mean how do i make a command?

You should always provide well-defined escaping to all data coming from
a non-trusted source (i.e. outside your application) and preferably to
all data in any case.

If you are using "C" then libpq offers functions to escape strings.
Almost all other languages offer something similar.

In general, I never use "raw" functions to build my queries, I have
wrapper functions that ensure all queries are well-formed.

What language are you using, and what framework?

--
   Richard Huxton
   Archonet Ltd

surabhi.ahuja wrote:
> My appliaction is in C++
>
> and i am getting char* ..s which i need to insert into the
> table...and for insert i am calling a stored procedure.
>
> But i need to form the call to the stored procedure with the above
> char*s as the argument.

Fine - just make sure you validate your data and format it properly.

If you are expecting an integer and a text field then check that the
first is a valid integer and escape any single quotes in the text-field.
Then you can build your query as you are at the moment.

I'm afraid I don't know much about the libpqxx C++ library, but it must
have facilities to escape quotes etc.

--
   Richard Huxton
   Archonet Ltd