Changeset 518

Timestamp:

10/18/12 17:49:01 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

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

File:

• trunk/cgi-bin/IPDB.pm (modified) (3 diffs)

trunk/cgi-bin/IPDB.pm

@@ -228 +228 @@
   # Wrap all the SQL in a transaction
   eval {
-    my $sth = $dbh->prepare("select count(*) from masterblocks where cidr <<= '$cidr'");
-    $sth->execute;
-    my @data = $sth->fetchrow_array;
-
-    if ($data[0] eq 0) {
+    my ($mexist) = $dbh->selectrow_array("SELECT cidr FROM masterblocks WHERE cidr <<= ?", undef, ($cidr) );
+
+    if (!$mexist) {
       # First case - master is brand-spanking-new.
 ##fixme: rwhois should be globally-flagable somewhere, much like a number of other things
 ## maybe a db table called "config"?
-      $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')");
-      $sth->execute;
+      $dbh->do("INSERT INTO masterblocks (cidr,rwhois) VALUES (?,?)", undef, ($cidr,'y') );
 
 # Unrouted blocks aren't associated with a city (yet).  We don't rely on this
 # elsewhere though;  legacy data may have traps and pitfalls in it to break this.
 # Thus the "routed" flag.
-
-      $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
-        " values ('$cidr',".$cidr->masklen.",'<NULL>','n')");
-      $sth->execute;
+      $dbh->do("INSERT INTO freeblocks (cidr,maskbits,city,routed) VALUES (?,?,?,?)", undef,
+        ($cidr, $cidr->masklen, '<NULL>', 'n') );
 
       # If we get here, everything is happy.  Commit changes.
       $dbh->commit;
 
-    } # new master does not contain existing master(s)
+    } # done new master does not contain existing master(s)
     else {
 
       # collect the master(s) we're going to absorb, and snag the longest netmask while we're at it.
       my $smallmask = $cidr->masklen;
-      $sth = $dbh->prepare("select cidr as mask from masterblocks where cidr <<= '$cidr'");
-      $sth->execute;
+      my $sth = $dbh->prepare("SELECT cidr FROM masterblocks WHERE cidr <<= ?");
+      $sth->execute($cidr);
       my @cmasters;
       while (my @data = $sth->fetchrow_array) {
@@ -275 +270 @@
 
       # collect the unrouted free blocks within the new master
-      $sth = $dbh->prepare("select cidr from freeblocks where ".
-                "maskbits>=$smallmask and cidr <<= '$cidr' and routed='n'");
-      $sth->execute;
+      $sth = $dbh->prepare("SELECT cidr FROM freeblocks WHERE maskbits <= ? AND cidr <<= ? AND routed = 'n'");
+      $sth->execute($smallmask, $cidr);
       while (my @data = $sth->fetchrow_array) {
         my $freeblock = new NetAddr::IP $data[0];
@@ -289 +283 @@
 
       # freeblocks
-      $sth = $dbh->prepare("delete from freeblocks where cidr <<= ?");
-      my $sth2 = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed) values (?,?,'<NULL>','n')");
+      $sth = $dbh->prepare("DELETE FROM freeblocks WHERE cidr <<= ?");
+      my $sth2 = $dbh->prepare("INSERT INTO freeblocks (cidr,maskbits,city,routed) VALUES (?,?,'<NULL>','n')");
       foreach my $newblock (@blocklist) {
-        $sth->execute("$newblock");
-        $sth2->execute("$newblock", $newblock->masklen);
+        $sth->execute($newblock);
+        $sth2->execute($newblock, $newblock->masklen);
       }
 
       # master
-      $sth = $dbh->prepare("delete from masterblocks where cidr <<= '$cidr'");
-      $sth->execute;
-      $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')");
-      $sth->execute;
+      $dbh->do("DELETE FROM masterblocks WHERE cidr <<= ?", undef, ($cidr) );
+      $dbh->do("INSERT INTO masterblocks (cidr,rwhois) VALUES (?,?)", undef, ($cidr, 'y') );
 
       # *whew*  If we got here, we likely suceeded.