Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I have been trying to understand how I can use the ? operator to insert variables into a database; I recieve a number of posted variables eg 
my $c_first = $query->param('c_first');
my $c_address = $query->param('c_address');
etc

[download]
How can I reference these variables later in my script using the ? operator and not the variable name. I found that accessing the variables directing when inserting into a Database can cause problems. (eg with names such as O'Brian, O'Connor etc etc).

Replies are listed 'Best First'.
Re: posting variables for DBI insert
by monarch (Priest) on Sep 25, 2005 at 03:19 UTC
    Let's say you used to do this:
      my $c_first = $query->param('c_first');
  my $c_address = $query->param('c_address');

  my $sql = "SELECT id FROM names WHERE first = '$c_first'";
  my $sth = $dbh->prepare( $sql );
  if ( $sth && $sth->execute() ) {
    while ( my $row = $sth->fetchrow_arrayref() ) {
      print( "$c_first => " . $row->[0] . "\n" );
    }
    $sth->finish();
  }

    [download]

    You can now do this:

      my $sql = "SELECT id FROM names WHERE first = ?";
  my $sth = $dbh->prepare( $sql );
  if ( $sth && $sth->execute( $c_first ) ) {
    while ( my $row = $sth->fetchrow_arrayref() ) {
      print( "$c_first => " . $row->[0] . "\n" );
    }
    $sth->finish();
  }

    [download]
    This is a common and often preferred method. You can have more than one question mark in the SQL command and put the variable names as arguments to the $sth->execute() function. Note that you don't put quote marks around the question marks in the SQL string.

    Alternatively you can do this:

      $c_first = $dbh->quote( $c_first ); # make O'Brian safe

  my $sql = "SELECT id FROM names WHERE first = $c_first";
  my $sth = $dbh->prepare( $sql );
  if ( $sth && $sth->execute() ) {
    while ( my $row = $sth->fetchrow_arrayref() ) {
      print( "$c_first => " . $row->[0] . "\n" );
    }
    $sth->finish();
  }

    [download]
    Note the absense of quote marks around the variable in the SQL statement. $dbh->quote()ing the variable automatically puts quote marks around the string.
[reply]
[d/l]
[select]
Re: posting variables for DBI insert
by BUU (Prior) on Sep 25, 2005 at 03:18 UTC
[reply]
Re: posting variables for DBI insert
by davidrw (Prior) on Sep 25, 2005 at 03:49 UTC
    The term to Super Search for is "placeholders" (or google "dbi placeholders" for lots more). This is a related recent node: Managing Dynamic SQL -- discusses modules that take care of creating where clauses/sql statements for you, including placeholder support.
[reply]
Re: posting variables for DBI insert
by pg (Canon) on Sep 25, 2005 at 03:40 UTC

    The following example demos how you can do this. I also added some logic to create the query dynamically in case the format of the query is determined based on whether a parameter is presented. This demos a select statement, but update or delete has no difference.

    my $sql = "SELECT column1 FROM table1 WHERE 1=1 ";
my @bind; #this is where your parameters get pushed in and later used 
+in execute() as values for placeholders.
if ($query->param('foo')){
  $sql .= " AND column2 = ? ";
  push @bind, $query->param('foo');
}
if ($query->param('bar')){
  $sql .= " AND column3 = ? ";
  push @bind, $query->param('bar');
}
my $sth = $dbh->prepare($sql);
$sth->execute(@bind);

    [download]
[reply]
[d/l]
Re: posting variables for DBI insert
by Anonymous Monk on Sep 25, 2005 at 05:53 UTC
    Thankyou everyone for your prompt answers. I especially liked monarch's answer.
[reply]

Back to Seekers of Perl Wisdom