The InspIRCd SQL API

From the makers of InspIRCd.

Introduction

InspIRCd does not directly support SQL in its core. This is done for several reasons, the most important being:

• Directly supporting SQL in the core can add a dependency to InspIRCd itself, which is undesirable
• Directly supporting SQL in the core slows down the core
• It would mean it cannot be removed once compiled in, without another recompile

Therefore, in InspIRCd, SQL support is provided by a module, m_sql.so. The SQL module provides module developers with a documented API which is not specific to any particular type of database. Currently, m_sql.so only supports MySQL, however there may be other sql provider modules in the future which support other databases. The m_sql.so module supports the following:

• Event based API which allows for loose integration of modules
• Performing of queries which produce results as rows
• Performing of queries which produce a single result (e.g. a count of effected rows)
• Performing of queries which produce no output (e.g. CREATE TABLE)
• Abstraction of SQL login credentials from modules

Using the API

The easy way

Since 1.0.0, there is an abstraction placed on top of the lower level SQL API (see below, if you dare) which makes it much easier to use, although probably not as design 'correct'. You may still use the more complex API as documented in the next section, if you really want to, though.

The SQL API can be accessed via a class of type SQLQuery, which you can obtain by including the file m_sql.h, and is defined below:

class SQLQuery
{
 private:

    (Not documented)

 public:

    SQLQuery(Server* S);
    SQLQuery(Module* initiator, unsigned long dbid, Server* S);
    ~SQLQuery();
    bool Query(std::string qry);
    bool QueryCount(std::string qry);
    bool GetRow();
    std::string GetField(std::string fname);
    int GetCount();
    void SQLDone();
    std::string Sanitise(std::string data);
};

The methods for this class are documented below:

SQLQuery(Module* initiator, unsigned long dbid, Server* S)

Use this constructor to instantiate an SQLQuery object. You must pass it your module's class pointer (as the value of 'initiator') and the database id you wish this class to operate on, and also a pointer to the Server class (usually passed to your module's constructor).

bool Query(std::string qry)

• qry: The MySQL query to be executed.

Notes:

This function is to be used if you wish to read records, or if you expect no reply.

• Returns True if the query is sucessful.
• In the event of a failure, the MySQL Error will be placed in the Debug Log.

bool QueryCount(std::string qry)

• qry: The MySQL query to be executed.

Notes:

• This function is to be used if you wish to get the number of rows effected by query.
• Returns True if the query is sucessful.
• In the event of a failure, the MySQL Error will be placed in the Debug Log.
• To be used in conjunction with GetCount().

bool GetRow()

Notes:

• Returns True if there is a new row to be parsed, False is returned if you have reached the end of the dataset.
• Can be used in an if statement to receive a single row.
• Can be used in a while statement to receive multiple rows.

std::string GetField(std::string field)

• field: This is the name of the field you wish to read

Notes:

• Will return the data held in the CURRENT dataset, and ALWAYS in the std::string cast, regardless of how the data is held.

int GetCount()

Notes:

• To be used in conjunction with QueryCount(), returns the number of modified rows.

void SQLDone()

Notes:

• Signals that the Query is complete, and cleans up resources.
• In the event of Query() or QueryCount() failing, this function is not required.

std::string Sanitise(std::string data)

• data: The data to be sanitised.

Notes:

• This function was created to allow a user to 'Clean' data, and help prevent SQL injections.
• It escapes characters such as " and \ so they will insert safely into MySQL
• REMEMBER: do not sanitise your ENTIRE query, just the data that is going into data fields.

A Working Example Of The Easy Way

Here is a working example of how to use the SQLQuery class. It is taken from a working third-party InspIRCd module:

std::string is_registered(std::string nick, Server* Srv)
{
       /* Create a new SQLQuery class */
       SQLQuery *query = new SQLQuery(this, dbid, Srv);
       /* Perform the query to look for the nick in question */
       if (!query->Query("SELECT ngid FROM nicknames WHERE nick='" + query->Sanitize(nick) + "'"))
       {
               /* The query failed */
               return "";
       }
       if (query->GetRow())
       {
               /* The query succeeded */
               std::string nickgroup_id = query->GetField("ngid");
               query->SQLDone();
               return nickgroup_id;
       }
       else
       {
               /* The query didn't have any rows to return.
                * Tidy up and return nothing
                */
               query->SQLDone();
               return "";
       }
}

The Hard Way

The SQL API is accessed via InspIRCd module Request objects using a Command Behavioural Pattern. Communication with m_sql.so is done via several steps:

1. Use Server::FindModule() to find the module with the name m_sql.so.
2. Instantiate an object of type Request
3. Instantiate an object of type SQLRequest (which can be found defined in m_sql.h)
4. Use the constructor of Request to set the data for the Request to the SQLRequest object
5. Fill the SQLRequest with your parameters (see the detailed description below)
6. Use Request::Send() to send the event to m_sql.so, and cast the return value of Request::Send() to an object of type SQLResult
7. Process the received SQLResult object

The SQLRequest Class

The SQLRequest class is defined as follows:

class SQLRequest
{
 protected:
        long conn_id;
        int request_type;
        std::string thisquery;
 public:
        SQLRequest(int qt, long cid, std::string query);
        void SetConnID(long id);
        long GetConnID();
        void SetQueryType(int t);
        int GetQueryType();
        void SetQuery(std::string query);
        std::string GetQuery();
};

When you instantiate an object of this type, you must specify in the constructor what it is to be used for. The first value indicates the query type. This can be one of the following:

• SQL_RESULT - Used if you wish to issue a query which will return a result as zero or more rows
• SQL_COUNT - Used if you wish to issue a query which will return a single result as a number of effected rows
• SQL_ROW - Used if you wish to retrieve a row, after first sending an event of SQL_RESULT type
• SQL_DONE - Used to end a query after zero or more rows have been retrieved using SQL_ROW

The second parameter is the connection ID of a connection. These are defined in the configuration of m_sql.so, your module should never need to know SQL login credentials directly, only this connection ID. Usually modules will allow the user to specify which connetion ID should be used, within their own configuration. The third parameter is the actual SQL query to be issued for example SELECT * FROM goobers WHERE newbie="yes".

Based upon this we can summise that there are multiple ways to issue queries, as shown below:

A query which expects rows as a result

First, send an event of type SQL_RESULT. If the response is of type SQL_OK, you must now fetch one or more rows by sending events of type SQL_ROW. Continue to do so until you receive an response which is NOT of type SQL_ROW (e.g. SQL_END or SQL_ERROR). Once you are finished, send SQL_DONE to indicate the end of the query.

A query which expects only a numeric value as its result

First, send an event of type SQL_COUNT. If the response is SQL_OK, read the result from the result's GetCount() method. You do not need to send any further events to mark the query as complete See below for a definition of the SQLResult class.

A query which does not create output

Just send an event of type SQL_COUNT, and discard the results by immediately deleting the pointer you are given. Of course, you will probably want to check the error value in the result's GetError() method first. See below for a definition of the SQLResult class.

The SQLResult class

SQLEvent classes sent to m_sql.so, successful or otherwise while it is loaded will return an object of type SQLResult. You must cast the result from Result::GetData() to an object of this type. It is your modules job to free the pointer to this new object when you are finished with it, by means of the delete operator. Upon a successful or unsuccessful query, you may query the SQLResult type's methods, which are shown in the definition below:

class SQLResult
{
 protected:
        int resptype;
        unsigned long count;
        std::string error;
        std::map<std::string,std::string> row;
 public:
        void SetRow(std::map<std::string,std::string> r);
        std::string GetField(std::string field);
        void SetType(int rt);
        void SetError(std::string err);
        int GetType();
        std::string GetError();
        void SetCount(unsigned long c);
        unsigned long GetCount();
};

The methods of the SQLResult class are documented below.

GetType

This method returns the result type. There are several types of result as shown in the following list:

1. SQL_COUNT - If GetType returns SQL_COUNT, only the GetCount() method can be relied upon to return valid data.
2. SQL_ROW - If GetType returns SQL_ROW, only the GetField() method can be relied upon to return valid data.
3. SQL_ERROR - If GetType returns SQL_ERROR, only the GetError() method can be relied upon to return valid data.
4. SQL_END - If GetType returns SQL_END, this indicates the end of a group of SQL_ROW queries, and there are no more rows to fetch. GetField() may still contain a valid row.
5. SQL_DONE - If GetType returns SQL_DONE, this is aknowledgement that an SQL_DONE request has been received and acted upon. No other methods will return valid data.
6. SQL_OK - If GetType returns SQL_OK, this is aknowledgement of a successful query sent by an SQL_QUERY request. No other methods will return valid data.

GetField

This method returns the value of a field. For example, if you have made a query as follows:

SELECT a,b FROM c WHERE d='e'

and there are results, SQLResult::GetField("a") will return the value in field a for the current row. You should only call GetField if the SQLResults type (found with the GetType() method) is set to SQL_ROW.

GetCount

This method returns the number of rows effected by a query of type SQL_COUNT (e.g. the output of DELTE FROM, UPDATE WHERE etc). You should only call GetCount if the SQLResults type (found with the GetType() method) is set to SQL_COUNT

GetError

This method returns the error message from the underlying SQL engine. You should call this method for details if the type of the result (found with the GetType() method) is SQL_ERROR.

SetRow

This method should not be used by the receiver of the results (e.g. your module). It is used internally by m_sql.so to initialise the class.

SetType

This method should not be used by the receiver of the results (e.g. your module). It is used internally by m_sql.so to initialise the class.

SetError

This method should not be used by the receiver of the results (e.g. your module). It is used internally by m_sql.so to initialise the class.

SetCount

This method should not be used by the receiver of the results (e.g. your module). It is used internally by m_sql.so to initialise the class.

A working Example Of The Hard Way

For a working example of the SQL API in practice, please see the source code of m_sqlauth.cpp within the src/modules/extra directory of InspIRCd.

Multiple Row Example

How to fetch and receive multiple rows isn't demonstrated in our 'Working Example', so below, is an example (taken from the upcoming m_serv module) of how this is achieved.

 // Prepare the Query..
 SQLRequest* query = new SQLRequest(SQL_RESULT,dbid,"SELECT * FROM channels WHERE founder='"+std::string(ngi)+"'");
 Request queryrequest((char*)query, thisclass, SQLModule);
 SQLResult* result = (SQLResult*)queryrequest.Send();
 if (result->GetType() == SQL_OK)
 {
   // Query Flagged as 'OK', proceed to reading rows.
   SQLRequest* rowrequest = new SQLRequest(SQL_ROW,dbid,"");
   Request rowquery((char*)rowrequest, thisclass, SQLModule);
   SQLResult* rowresult = (SQLResult*)rowquery.Send();
   while(rowresult->GetType() == SQL_ROW)
   {
     /*****************************************
      * Conditions:
      * The initial Query has a row.
      * Additional row enquries are of SQL_ROW.
      *
      * Actions:
      * 1) Look up the channel on the IRCd. Does it exist?
      * 2) If so, is the user that identified on this channel?
      * 3) If so, +oq that user.
      *****************************************/
     // 1)
     chanrec* channel = Srv->FindChannel(rowresult->GetField("channel").c_str());
     if (channel) {
       // 2)
       if (Srv->IsOnChannel(user, channel)) {
         // 3)
         chanmode[0] = channel->name;
         chanmode[1] = "+oq";
         chanmode[2] = user->nick;
         chanmode[3] = user->nick;
         Srv->SendMode(chanmode,4,ChanServ);
       }
     }
     /*****************************************
      * Next, we attempt to recieve the next row
      * then begin the 'While loop' again.
      *****************************************/
     rowresult = (SQLResult*)rowquery.Send();
   }
   /*****************************************
    * When we get here, the type was not SQL_ROW
    * be it, SQL_ERROR, SQL_DONE, either way..
    * assume we have finished, and 'clean up'
    * the variables, to prevent memory leaks.
    ******************************************/
   delete result;
   query->SetQueryType(SQL_DONE);
   query->SetConnID(dbid);
   Request donerequest((char*)query, thisclass, SQLModule);
   donerequest.Send();
   delete query;
 }