[Kea-users] mysql connection in hooks

[Bryan Perry]

Igor,

I had to use a MySQL connection with the subnet4_select hook in order to 
set the correct subnet for host reservations where the DHCP relay server 
was on a different subnet than the client's reservation. I do not claim 
to be a guru and it took a LOT of tinkering to get this to work right, 
but here's how I did it.

Hope it helps,
Bryan



// subnet4_select.cc

#include <hooks/hooks.h>
#include <dhcp/pkt4.h>
#include <dhcpsrv/subnet.h>
#include <dhcpsrv/mysql_connection.h>
#include <algorithm/string.hpp>
#include "library_common.h"
#include <string>

using namespace isc::dhcp;
using namespace isc::hooks;
using namespace std;

     // Define a structure for the mysql connection instance
     struct connection_details {
       const char *server;
       const char *user;
       const char *password;
       const char *database;
     };

     MYSQL_RES *Result;     // The mysql query results set variable

     // A function to establish the mysql connection
     MYSQL* mysql_connection_setup2(struct connection_details 
mysql_details) {
       // Create a mysql instance and initialize the variables
       MYSQL *connection = mysql_init(NULL);

       // Connect to the database with the details in the connection 
instance
       if (!mysql_real_connect(connection,mysql_details.server, 
mysql_details.user, mysql_details.password, mysql_details.database, 0, 
NULL, 0)) {
         printf("Conection error : %s\n", mysql_error(connection));
         //exit(1);
       }
       return connection;
     }

     // A function to run the query and return the results set
     MYSQL_RES* mysql_perform_query2(MYSQL *connection, const char 
*sql_query) {
       // send the query to the database
       // cout << sql_query << " in function mysql_perform_query2" << endl;
       if (mysql_query(connection, sql_query))
       {
          printf("MySQL query error : %s\n", mysql_error(connection));
          // exit(1);
       }
       Result = mysql_store_result(connection);
       int RowsReturned = mysql_num_rows( Result );
       // cout << "NumRows in the function: " << RowsReturned << endl;
       // return mysql_use_result(connection);
       return Result;
     }

extern "C" {

// This callout is called at the "subnet4_select" hook.
// We will intercept the request, check the MySQL hosts table
// for reservations, and if found, set the correct subnet ID

int subnet4_select(CalloutHandle& handle) {

     // A pointer to the packet is passed to the callout via a "boost" smart
     // pointer. The include file "pkt4.h" typedefs a pointer to the Pkt4
     // object as Pkt4Ptr.  Retrieve a pointer to the object.
     Pkt4Ptr query4_ptr;
     handle.getArgument("query4", query4_ptr);

     // Get a pointer to the subnet4 object
     Subnet4Ptr subnet4_ptr;
     handle.getArgument("subnet4", subnet4_ptr);

     // Get the collection of subnets from the callout argument set
     const isc::dhcp::Subnet4Collection* subnets;
     handle.getArgument("subnet4collection", subnets);

     // Get a pointer to the hardware address.
     HWAddrPtr hwaddr_ptr = query4_ptr->getHWAddr();

     // Get the subnet ID from the initial request
     SubnetID subnetId = subnet4_ptr->getID();

     // Exclude hardware type from hardware address string
     bool include_htype=false;
     string colonized_hwaddr = hwaddr_ptr->toText(include_htype);

     // Strip colons from colonized_hwaddr to get hwaddr
     string hwaddr = colonized_hwaddr;
     boost::erase_all(hwaddr, ":");

     // Define some variables we will use
     int newSubnetId = 0;
     string ipaddr = "BLANK";
     long RowsReturned;

     // cout << hwaddr << " wants subnet "  <<  subnetId << "\n";
     // Use a try...catch here to help prevent MySQL errors from killing 
the server.
try {
     // Here is where we query the database and set new subnet ID if 
necessary
     MYSQL *conn2;        // the connection
     MYSQL_RES *res2;     // the results
     MYSQL_ROW row2;      // the results row (line by line)

     struct connection_details mysqlD2;
       mysqlD2.server = "localhost";   // where the mysql database is
       mysqlD2.user = "USERNAME";     // the root user of mysql
       mysqlD2.password = "PASSWORD"; // the password of the root user 
in mysql
       mysqlD2.database = "DB_NAME";          // the databse to pick

     // Build the query string
     ostringstream ss2;
     ss2 << "select hex(dhcp_identifier), dhcp4_subnet_id, 
INET_NTOA(ipv4_address), host_id from hosts where hex(dhcp_identifier) = 
'" << hwaddr << "' limit 1";
     string ss2_str = ss2.str();
     const char *full_query2 = ss2_str.c_str();

     // Connect to the mysql database
     conn2 = mysql_connection_setup2(mysqlD2);

     // Assign the results returned to res2 if valid
     if (res2 = mysql_perform_query2(conn2, full_query2)) {
       // Get the number of rows in the results set. Should be 0 or 1
       int rCount = mysql_num_rows(res2);
       // If we got more than 0 rows in the results then we found a 
reservation for that client
       if (rCount > 0) {
         while ((row2 = mysql_fetch_row(res2)) !=NULL) {
             // Convert the subnet ID to an integer for use further on
             string input(row2[1]); stringstream SS(input); SS >> 
newSubnetId;
             // Handle a NULL ipv4_address field
             if (row2[2]) {
               ipaddr = row2[2];
             } // end if
             // Log that we found a reservation
             leaselog << "Static reservation " << ipaddr << " found for 
" << row2[0] << " on row " << row2[3] << ", subnet " << row2[1] << endl;
         }
       } // end if
       // cout << "New subnet ID: " << newSubnetId << endl;

       // Clean up the database result set
       mysql_free_result(res2);

       // Clean up the database connectio
       mysql_close(conn2);
     } // end if
     else {
       // Throw an error if something went wrong in the mysql lookup
       leaselog << "MySQL query failed in lookup for " << hwaddr << " -- 
Results processing skipped." << endl;
     } // end else

} catch (...) {
   // Throw an error if something more serious happened
   leaselog << "# ERR: Caught an error in subnet4_select.cc" << endl;
}



     // Next, if we have to change the subnet ID, we iterate through the 
collection
     // of subnets, looking for the ID we want and then set it.
     if ((newSubnetId != 0) && (subnetId != newSubnetId)) {
       for (int i = 0; i < subnets->size();  ++i) {
           Subnet4Ptr new_subnet = (*subnets)[i];
           if (new_subnet->getID() == newSubnetId) {
               // id matched so replace the selected subnet by
               // setting the "subnet4" callout argument with
               // the new subnet
               handle.setArgument("subnet4", new_subnet);
               break;
           }
       }
     }

     flush(leaselog);
     return (0);

};

}




On 12/6/2016 8:07 AM, Igor Smitran wrote:
> Hi List,
>
> is one able to access mysql structure (existing connection to be
> precise) from a hook?
>
> If it is can i have an example of accessing mysql structure from a hook?
>
> Thank you,
>
> Igor
>
> _______________________________________________
> Kea-users mailing list
> Kea-users at lists.isc.org
> https://lists.isc.org/mailman/listinfo/kea-users
>