RE: need some advice
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
DMuey
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
On 08 Mar 2003 16:06:35 -0600 Rob Benton
[EMAIL PROTECTED] wrote:
I know there's probably a better way to do what I'm trying to
accomplish so I'd like some opinions.
I have 2 cgi programs. One has a form full of text boxes
sending data
to the 2nd program. What I need is an easy way to add the
single-quotes to each variable that is not an integer
before sending
the statement to my Oracle database.
So say I had -
$name
$address
$zip
NAME and ADDRESS being character and ZIP being integer
variables, and
I wanted to make sure the went in as
Using an NUMBER column for zipcodes is just begging for
problems. For example, what about Canadian addresses?
$statement = qw/select * from otable where name='$name' and
address='$address' and zip=$zip/; $sth = $dbh-prepare($statement);
I need a way (hopefully not messy) to interpolate those variables
into the sql statement but also have them single-quoted. I could
probably work something out but I usually find that there's
an easier
way to do things after I have made a mess of them.
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the appropriate mail list. ** Ask Smarter:
http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: need some advice
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey [EMAIL PROTECTED] wrote:
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
The original poster wanted to make sure the values were properly
quoted. If any ' characters are in $character, the SQL you've given
will not parse correctly, if the user is lucky. If the user is
unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values. Oddly
enough it is named quote(). Read the fine manual to learn about it.
That said, for DBDs that support them (including DBD::Oracle),
placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
RE: need some advice
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
[EMAIL PROTECTED] wrote:
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
The original poster wanted to make sure the values were
properly quoted. If any ' characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it will take care of
any charcaters that could casue problems and not just single quotes ( IE (), etc.. )
Sorry for misunderstanding
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values.
Oddly enough it is named quote(). Read the fine manual to
learn about it.
That said, for DBDs that support them (including
DBD::Oracle), placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the appropriate
mail list. ** Ask Smarter:
http://www.catb.org/~esr/faqs/smart- questions.html
Give a
hobbit a fish and he eats fish for a
day.
Give a hobbit a ring and he eats fish for an age.
Re: need some advice
Dan Muey [EMAIL PROTECTED] wrote:
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
[EMAIL PROTECTED] wrote:
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
The original poster wanted to make sure the values were
properly quoted. If any ' characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it will take care of
any charcaters that could casue problems and not just single quotes ( IE (), etc.. )
Sorry for misunderstanding
But really, as Michael has already said twice, use placeholders.
Many reasons to use them, and no reasons not to. They handle
*ALL* quoting issues and give you other good things too.
Read about placeholders in the perldocs by doing
perldoc DBI
at a command prompt, and search for 'Placeholder'.
HTH.
--
Hardy Merrill
Senior Software Engineer
Red Hat, Inc.
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values.
Oddly enough it is named quote(). Read the fine manual to
learn about it.
That said, for DBDs that support them (including
DBD::Oracle), placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the appropriate
mail list. ** Ask Smarter:
http://www.catb.org/~esr/faqs/smart- questions.html
Give a
hobbit a fish and he eats fish for a
day.
Give a hobbit a ring and he eats fish for an age.
RE: need some advice
There won't be any ['] (read that as reg. expression) inside the fields
so that's not a problem. All I need to do is decide whether to
single-quote the variable based on its data-type. Also this will just
be a select statement.
On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
[EMAIL PROTECTED] wrote:
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
The original poster wanted to make sure the values were
properly quoted. If any ' characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it will take care of
any charcaters that could casue problems and not just single quotes ( IE (), etc.. )
Sorry for misunderstanding
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values.
Oddly enough it is named quote(). Read the fine manual to
learn about it.
That said, for DBDs that support them (including
DBD::Oracle), placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the appropriate
mail list. ** Ask Smarter:
http://www.catb.org/~esr/faqs/smart- questions.html
Give a
hobbit a fish and he eats fish for a
day.
Give a hobbit a ring and he eats fish for an age.
Re: need some advice
Are you not listening to these people giving you helpful advice? Use
the placeholders. I gaurantee you will be glad you did. C'mon man!!!
Embrace the change.
Rob Benton wrote:
There won't be any ['] (read that as reg. expression) inside the fields
so that's not a problem. All I need to do is decide whether to
single-quote the variable based on its data-type. Also this will just
be a select statement.
On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
[EMAIL PROTECTED] wrote:
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
The original poster wanted to make sure the values were
properly quoted. If any ' characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it will take care of
any charcaters that could casue problems and not just single quotes ( IE (), etc.. )
Sorry for misunderstanding
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values.
Oddly enough it is named quote(). Read the fine manual to
learn about it.
That said, for DBDs that support them (including
DBD::Oracle), placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the appropriate
mail list. ** Ask Smarter:
http://www.catb.org/~esr/faqs/smart- questions.html
Give a
hobbit a fish and he eats fish for a
day.
Give a hobbit a ring and he eats fish for an age.
Re: need some advice
Here, I'll try and be more helpful than berating. I'm sorry I'm having
a bad day. The code below is not totally complete. Some error
handling needs to be added for sure. I would consider this to be psuedo
code simply because I did not check my work for syntax. I think it
fairly accruate though.
## this assumes you want to insert all form elements
my $cgi = CGI-new();
my $dbh = DBI-connect(connect info goes here);
my $sql = INSERT INTO table (;
my $val = values(;
my $array_ref = [];
## build your statement assuming the form element names are
## the same as your column names.
foreach ( $cgi-param() ){
$sql .= $_,;
$val .= ?,;
push(@{$array_ref}, $cgi-param($_));
}
$sql =~ s/,$/) /; ## strip the last comma and add a closing paren.
$val =~ s/,$/)/;
my $sth = $dbh-prepare($sql.$val);
my $result = $sth-execute($array_ref);
$dbh-commit(); ## if autocommit is not on.
Ian Harisay wrote:
Are you not listening to these people giving you helpful advice? Use
the placeholders. I gaurantee you will be glad you did. C'mon man!!!
Embrace the change.
Rob Benton wrote:
There won't be any ['] (read that as reg. expression) inside the fields
so that's not a problem. All I need to do is decide whether to
single-quote the variable based on its data-type. Also this will just
be a select statement.
On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey [EMAIL PROTECTED]
wrote:
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
The original poster wanted to make sure the values were properly
quoted. If any ' characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it
will take care of any charcaters that could casue problems and not
just single quotes ( IE (), etc.. )
Sorry for misunderstanding
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting values. Oddly
enough it is named quote(). Read the fine manual to learn about it.
That said, for DBDs that support them (including DBD::Oracle),
placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in the fine
DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the appropriate mail
list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-
questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
RE: need some advice
OK I see what you're doing. What I would need to do is just
tweak that a little. I would need something like:
foearch ($cgi-param() ) {
foreach $p(@params) {
if(param($p) ...) {
$dbh-(param($p));
To do param() instead of $cgi-param() you may need to do
use CGI qw/:standard/;
Not sure if
if($cgi-param($p) ...) would work or not, it may though.
DMuey
if ( some test on the type here ) {
$dbh-quote($_);
}
$sql .= $_,;
$val .= ?,;
push(@{$array_ref}, $cgi-param($_));
}
Does that make any sense?
On Mon, 2003-03-10 at 12:45, Ian Harisay wrote:
Here, I'll try and be more helpful than berating. I'm
sorry I'm having
a bad day. The code below is not totally complete. Some error
handling needs to be added for sure. I would consider this to be
psuedo
code simply because I did not check my work for syntax. I think it
fairly accruate though.
## this assumes you want to insert all form elements
my $cgi = CGI-new();
my $dbh = DBI-connect(connect info goes here);
my $sql = INSERT INTO table (;
my $val = values(;
my $array_ref = [];
## build your statement assuming the form element names are ## the
same as your column names. foreach ( $cgi-param() ){
$sql .= $_,;
$val .= ?,;
push(@{$array_ref}, $cgi-param($_));
}
$sql =~ s/,$/) /; ## strip the last comma and add a closing paren.
$val =~ s/,$/)/;
my $sth = $dbh-prepare($sql.$val);
my $result = $sth-execute($array_ref);
$dbh-commit(); ## if autocommit is not on.
Ian Harisay wrote:
Are you not listening to these people giving you helpful advice?
Use
the placeholders. I gaurantee you will be glad you did.
C'mon man!!!
Embrace the change.
Rob Benton wrote:
There won't be any ['] (read that as reg. expression)
inside the
fields so that's not a problem. All I need to do is
decide whether
to single-quote the variable based on its data-type. Also this
will just be a select statement.
On Mon, 2003-03-10 at 09:39, Dan Muey wrote:
On Mon, 10 Mar 2003 08:18:31 -0600 Dan Muey
[EMAIL PROTECTED]
wrote:
Since you know how your table is structured :
$query =INSERT INTO stuff VALUES(NULL,$num,\'$character\');
Or if you wanted to do it dynamically :
$query = INSERT INTO stuff VALUES(;
if($data =~ m/^\d+$/) { $query .= $data\, ; }
else { $query .= \'$data\'\, ; }
The original poster wanted to make sure the values
were properly
quoted. If any ' characters are in $character,
Aahhh gotcha, in that case yes definitely use quote() because it
will take care of any charcaters that could casue
problems and not
just single quotes ( IE (), etc.. )
Sorry for misunderstanding
DMuey
the SQL you've given will not parse correctly, if the user is
lucky. If the user is unlucky, it could contain malicious SQL.
DBI already includes a method for properly quoting
values. Oddly
enough it is named quote(). Read the fine manual to
learn about it.
That said, for DBDs that support them (including DBD::Oracle),
placeholders are far superior.
Again http://xmlproj.com/fom-serve/cache/49.html .
-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 08, 2003 9:02 PM
To: [EMAIL PROTECTED]; Rob Benton
Subject: Re: need some advice
Placeholders. There are examples of using them in
the fine DBI
and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ .
http://xmlproj.com/fom-serve/cache/49.html
--
Mac :})
** I normally forward private questions to the
appropriate mail
list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-
questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: need some advice
On 10 Mar 2003 14:34:28 -0600 Rob Benton [EMAIL PROTECTED] wrote:
OK I see what you're doing. What I would need to do is just tweak that
a little. I would need something like:
foearch ($cgi-param() ) {
if ( some test on the type here ) {
$dbh-quote($_);
}
$sql .= $_,;
$val .= ?,;
push(@{$array_ref}, $cgi-param($_));
}
Does that make any sense?
No.
If you are using placeholders, quote() is not necessary and *should
not* be used. That is one of the advantages of placeholders.
Read up on them in the fine DBI manual.
Again read http://xmlproj.com/fom-serve/cache/49.html .
# Untested example, there is always more than one way to do it
$dbh - {RaiseErrors} = 1; # Error checking is essential
my ( @cols, @vals ) = ();
foreach ( $cgi-param() ) {
push @cols, $_;
push @vals, $cgi - param( $_ );
}
# Note: Under Oracle, a SQL statement that is *IDENTICAL* to a
# previously parsed statement does not have to be replanned.
my $sql = INSERT INTO table ( . join( , , @cols ) . )\n .
VALUES ( . join( , , map { ? } @cols ) . );
my $sth = $dbh - prepare( $sql );
$sth - execute( @vals );
$dbh - commit();
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
Re: need some advice
On 08 Mar 2003 16:06:35 -0600 Rob Benton [EMAIL PROTECTED] wrote: I know there's probably a better way to do what I'm trying to accomplish so I'd like some opinions. I have 2 cgi programs. One has a form full of text boxes sending data to the 2nd program. What I need is an easy way to add the single-quotes to each variable that is not an integer before sending the statement to my Oracle database. So say I had - $name $address $zip NAME and ADDRESS being character and ZIP being integer variables, and I wanted to make sure the went in as Using an NUMBER column for zipcodes is just begging for problems. For example, what about Canadian addresses? $statement = qw/select * from otable where name='$name' and address='$address' and zip=$zip/; $sth = $dbh-prepare($statement); I need a way (hopefully not messy) to interpolate those variables into the sql statement but also have them single-quoted. I could probably work something out but I usually find that there's an easier way to do things after I have made a mess of them. Placeholders. There are examples of using them in the fine DBI and DBD::Oracle manuals and in DBD-Oracle-xxx/Oracle.ex/ . http://xmlproj.com/fom-serve/cache/49.html -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
