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
----------------------------------------------------------------------------------------------

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.

thanks,
regards
Surabhi

________________________________

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

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********

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).

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.

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@overlandstorage.com <SCassidy@overlandstorage.com>
wrote:

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
----------------------------------------------------------------------------------------------

On 24 Oct 2005 22:00:55 +0200, Harald Fuchs <hf0923x@protecting.net> wrote:

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'?

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?

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 because the return type
was no longer a result set, but a status in PyGresql:

AttributeError: 'long' object has no attribute 'ntuples'

So maybe there isn't an easy way to create a SQL injection attack in a
xactional environment that will actualy work?

Alex

---------------------------(end of broadcast)---------------------------

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

________________________________

From: Richard Huxton [mailto:dev@archonet.com]
Sent: Mon 10/24/2005 3:04 PM
To: surabhi.ahuja
Cc: Stephan Szabo; pgsql-general@postgresql.org
Subject: Re: [GENERAL] a stored procedure ..with integer as the parameter

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********

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

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.

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?

________________________________

From: Tino Wildenhain [mailto:tino@wildenhain.de]
Sent: Tue 10/25/2005 11:22 AM
To: surabhi.ahuja
Cc: Richard Huxton; Stephan Szabo; pgsql-general@postgresql.org
Subject: Re: [GENERAL] a stored procedure ..with integer as the parameter

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********

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.

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

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.

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.

________________________________

From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wed 10/26/2005 12:40 PM
To: surabhi.ahuja
Cc: Tino Wildenhain; Stephan Szabo; pgsql-general@postgresql.org
Subject: Re: [GENERAL] a stored procedure ..with integer as the parameter

***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********

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