Re: [GENERAL] a stored procedure ..with integer as the parameter
Title: Re: [GENERAL] a stored procedure ..with integer as the parameter 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:[EMAIL PROTECTED]Sent: Wed 10/26/2005 12:40 PMTo: surabhi.ahujaCc: Tino Wildenhain; Stephan Szabo; pgsql-general@postgresql.orgSubject: 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 froma non-trusted source (i.e. outside your application) and preferably toall 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 havewrapper functions that ensure all queries are well-formed.What language are you using, and what framework?-- Richard Huxton Archonet Ltd
Re: [GENERAL] a stored procedure ..with integer as the parameter
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] a stored procedure ..with integer as the parameter
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] a stored procedure ..with integer as the parameter
In article [EMAIL PROTECTED], Alex Turner [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] a stored procedure ..with integer as the parameter
Title: Re: [GENERAL] a stored procedure ..with integer as the parameter
what do u suggest i do then
in that case?
i mean how should i make a query - i mean
howdo i make a command?
From: Tino Wildenhain
[mailto:[EMAIL PROTECTED]Sent: Tue 10/25/2005 11:22
AMTo: surabhi.ahujaCc: Richard Huxton; Stephan Szabo;
pgsql-general@postgresql.orgSubject: 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); endI'm not sure this serves much purpose if it isnt
justfor 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 serioussecurity
hole btw.
Re: [GENERAL] a stored procedure ..with integer as the parameter
Title: Re: [GENERAL] a stored procedure ..with integer as the parameter
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 procedureas
follows:
insert(integer,
varchar(256),smallint)
from the clientI 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:[EMAIL PROTECTED]Sent: Fri 10/21/2005 12:11
PMTo: surabhi.ahujaCc:
pgsql-general@postgresql.orgSubject: 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 problemDon'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
beconverted into one (we explicitly removed code that handled this
casebecause we thought it didn't make
sense).
Re: [GENERAL] a stored procedure ..with integer as the parameter
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] a stored procedure ..with integer as the parameter
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:[EMAIL PROTECTED]
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).
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] a stored procedure ..with integer as the parameter
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? AlexOn 10/21/05, [EMAIL PROTECTED] [EMAIL PROTECTED] 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[EMAIL PROTECTED]To: pgsql-general@postgresql.org.in cc: Sent by:Subject:[GENERAL] a stored procedure ..with integer as the parameter|---|[EMAIL PROTECTED] | [ ] Expand Groups |tgresql.org |---| 10/20/2005 11:03PM i have a stored procedureinsert_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 tablebut suppose i giveselect insert_table();it gives an error ...saying invalid input syntax for integer: please suggest a solution to this problemthanks,reagrdssurabhi--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
Re: [GENERAL] a stored procedure ..with integer as the parameter
In article [EMAIL PROTECTED], Alex Turner [EMAIL PROTECTED] 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'? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] a stored procedure ..with integer as the parameter
On 24 Oct 2005 22:00:55 +0200, Harald Fuchs [EMAIL PROTECTED] wrote: In article [EMAIL PROTECTED],Alex Turner [EMAIL PROTECTED] 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
Re: [GENERAL] a stored procedure ..with integer as the parameter
Title: Re: [GENERAL] a stored procedure ..with integer as the parameter
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:[EMAIL PROTECTED]Sent: Mon 10/24/2005 3:04 PMTo:
surabhi.ahujaCc: Stephan Szabo;
pgsql-general@postgresql.orgSubject: 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-quotingindicates 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'sPEAR:DB classes,
JDBC?-- Richard Huxton Archonet
Ltd
Re: [GENERAL] a stored procedure ..with integer as the parameter
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.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] a stored procedure ..with integer as the parameter
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
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). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] a stored procedure ..with integer as the parameter
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 [EMAIL PROTECTED]To: pgsql-general@postgresql.org .in cc: Sent by: Subject: [GENERAL] a stored procedure ..with integer as the parameter |---| [EMAIL PROTECTED] | [ ] 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
