Changeset 518


Ignore:
Timestamp:
10/18/12 17:49:01 (11 years ago)
Author:
Kris Deugau
Message:

/trunk

Update addMaster() to use ?-substitution in SQL. See #34.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/cgi-bin/IPDB.pm

    r517 r518  
    228228  # Wrap all the SQL in a transaction
    229229  eval {
    230     my $sth = $dbh->prepare("select count(*) from masterblocks where cidr <<= '$cidr'");
    231     $sth->execute;
    232     my @data = $sth->fetchrow_array;
    233 
    234     if ($data[0] eq 0) {
     230    my ($mexist) = $dbh->selectrow_array("SELECT cidr FROM masterblocks WHERE cidr <<= ?", undef, ($cidr) );
     231
     232    if (!$mexist) {
    235233      # First case - master is brand-spanking-new.
    236234##fixme: rwhois should be globally-flagable somewhere, much like a number of other things
    237235## maybe a db table called "config"?
    238       $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')");
    239       $sth->execute;
     236      $dbh->do("INSERT INTO masterblocks (cidr,rwhois) VALUES (?,?)", undef, ($cidr,'y') );
    240237
    241238# Unrouted blocks aren't associated with a city (yet).  We don't rely on this
    242239# elsewhere though;  legacy data may have traps and pitfalls in it to break this.
    243240# Thus the "routed" flag.
    244 
    245       $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
    246         " values ('$cidr',".$cidr->masklen.",'<NULL>','n')");
    247       $sth->execute;
     241      $dbh->do("INSERT INTO freeblocks (cidr,maskbits,city,routed) VALUES (?,?,?,?)", undef,
     242        ($cidr, $cidr->masklen, '<NULL>', 'n') );
    248243
    249244      # If we get here, everything is happy.  Commit changes.
    250245      $dbh->commit;
    251246
    252     } # new master does not contain existing master(s)
     247    } # done new master does not contain existing master(s)
    253248    else {
    254249
    255250      # collect the master(s) we're going to absorb, and snag the longest netmask while we're at it.
    256251      my $smallmask = $cidr->masklen;
    257       $sth = $dbh->prepare("select cidr as mask from masterblocks where cidr <<= '$cidr'");
    258       $sth->execute;
     252      my $sth = $dbh->prepare("SELECT cidr FROM masterblocks WHERE cidr <<= ?");
     253      $sth->execute($cidr);
    259254      my @cmasters;
    260255      while (my @data = $sth->fetchrow_array) {
     
    275270
    276271      # collect the unrouted free blocks within the new master
    277       $sth = $dbh->prepare("select cidr from freeblocks where ".
    278                 "maskbits>=$smallmask and cidr <<= '$cidr' and routed='n'");
    279       $sth->execute;
     272      $sth = $dbh->prepare("SELECT cidr FROM freeblocks WHERE maskbits <= ? AND cidr <<= ? AND routed = 'n'");
     273      $sth->execute($smallmask, $cidr);
    280274      while (my @data = $sth->fetchrow_array) {
    281275        my $freeblock = new NetAddr::IP $data[0];
     
    289283
    290284      # freeblocks
    291       $sth = $dbh->prepare("delete from freeblocks where cidr <<= ?");
    292       my $sth2 = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed) values (?,?,'<NULL>','n')");
     285      $sth = $dbh->prepare("DELETE FROM freeblocks WHERE cidr <<= ?");
     286      my $sth2 = $dbh->prepare("INSERT INTO freeblocks (cidr,maskbits,city,routed) VALUES (?,?,'<NULL>','n')");
    293287      foreach my $newblock (@blocklist) {
    294         $sth->execute("$newblock");
    295         $sth2->execute("$newblock", $newblock->masklen);
     288        $sth->execute($newblock);
     289        $sth2->execute($newblock, $newblock->masklen);
    296290      }
    297291
    298292      # master
    299       $sth = $dbh->prepare("delete from masterblocks where cidr <<= '$cidr'");
    300       $sth->execute;
    301       $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')");
    302       $sth->execute;
     293      $dbh->do("DELETE FROM masterblocks WHERE cidr <<= ?", undef, ($cidr) );
     294      $dbh->do("INSERT INTO masterblocks (cidr,rwhois) VALUES (?,?)", undef, ($cidr, 'y') );
    303295
    304296      # *whew*  If we got here, we likely suceeded.
Note: See TracChangeset for help on using the changeset viewer.