Обсуждение: 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: 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. > 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_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
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
----------------------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
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 ----------------------------------------------------------------------------------------------
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)---------------------------
TIP 5: don't forget to increase your free space map settings
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.
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.
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
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