RE: need some advice

2003-03-10 Thread Dan Muey
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

2003-03-10 Thread Michael A Chase
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

2003-03-10 Thread Dan Muey
 
 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

2003-03-10 Thread Hardy Merrill
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

2003-03-10 Thread Rob Benton
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

2003-03-10 Thread Ian Harisay
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

2003-03-10 Thread Ian Harisay
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

2003-03-10 Thread Dan Muey

 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

2003-03-10 Thread Michael A Chase
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

2003-03-08 Thread Michael A Chase
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.