[Kea-users] mysql connection in hooks
Bryan Perry
bryan at sfcn.org
Tue Dec 6 15:43:07 UTC 2016
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
>
More information about the Kea-users
mailing list