src/modules/extra/m_mysql.cpp

   1 /*
   2  * InspIRCd -- Internet Relay Chat Daemon
   3  *
   4  *   Copyright (C) 2019 linuxdaemon <linuxdaemon.irc@gmail.com>
   5  *   Copyright (C) 2015 Daniel Vassdal <shutter@canternet.org>
   6  *   Copyright (C) 2014, 2016 Adam <Adam@anope.org>
   7  *   Copyright (C) 2013-2014 Attila Molnar <attilamolnar@hush.com>
   8  *   Copyright (C) 2013, 2016-2020 Sadie Powell <sadie@witchery.services>
   9  *   Copyright (C) 2012 Robby <robby@chatbelgie.be>
  10  *   Copyright (C) 2012 ChrisTX <xpipe@hotmail.de>
  11  *   Copyright (C) 2009-2010 Daniel De Graaf <danieldg@inspircd.org>
  12  *   Copyright (C) 2009 Uli Schlachter <psychon@inspircd.org>
  13  *   Copyright (C) 2007, 2009 Dennis Friis <peavey@inspircd.org>
  14  *   Copyright (C) 2005, 2008-2010 Craig Edwards <brain@inspircd.org>
  15  *
  16  * This file is part of InspIRCd.  InspIRCd is free software: you can
  17  * redistribute it and/or modify it under the terms of the GNU General Public
  18  * License as published by the Free Software Foundation, version 2.
  19  *
  20  * This program is distributed in the hope that it will be useful, but WITHOUT
  21  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  22  * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
  23  * details.
  24  *
  25  * You should have received a copy of the GNU General Public License
  26  * along with this program.  If not, see <http://www.gnu.org/licenses/>.
  27  */
  28
  29 /// $CompilerFlags: execute("mysql_config --include" "MYSQL_CXXFLAGS")
  30 /// $LinkerFlags: execute("mysql_config --libs_r" "MYSQL_LDFLAGS" "-lmysqlclient")
  31
  32 /// $PackageInfo: require_system("arch") mariadb-libs
  33 /// $PackageInfo: require_system("centos" "6.0" "6.99") mysql-devel
  34 /// $PackageInfo: require_system("centos" "7.0") mariadb-devel
  35 /// $PackageInfo: require_system("darwin") mysql-connector-c
  36 /// $PackageInfo: require_system("debian") libmysqlclient-dev
  37 /// $PackageInfo: require_system("ubuntu") libmysqlclient-dev
  38
  39 #ifdef __GNUC__
  40 # pragma GCC diagnostic push
  41 #endif
  42
  43 // Fix warnings about the use of `long long` on C++03.
  44 #if defined __clang__
  45 # pragma clang diagnostic ignored "-Wc++11-long-long"
  46 #elif defined __GNUC__
  47 # pragma GCC diagnostic ignored "-Wlong-long"
  48 #endif
  49
  50 #include "inspircd.h"
  51 #include <mysql.h>
  52 #include "modules/sql.h"
  53
  54 #ifdef __GNUC__
  55 # pragma GCC diagnostic pop
  56 #endif
  57
  58 #ifdef _WIN32
  59 # pragma comment(lib, "mysqlclient.lib")
  60 #endif
  61
  62 /* VERSION 3 API: With nonblocking (threaded) requests */
  63
  64 /* THE NONBLOCKING MYSQL API!
  65  *
  66  * MySQL provides no nonblocking (asyncronous) API of its own, and its developers recommend
  67  * that instead, you should thread your program. This is what i've done here to allow for
  68  * asyncronous SQL requests via mysql. The way this works is as follows:
  69  *
  70  * The module spawns a thread via class Thread, and performs its mysql queries in this thread,
  71  * using a queue with priorities. There is a mutex on either end which prevents two threads
  72  * adjusting the queue at the same time, and crashing the ircd. Every 50 milliseconds, the
  73  * worker thread wakes up, and checks if there is a request at the head of its queue.
  74  * If there is, it processes this request, blocking the worker thread but leaving the ircd
  75  * thread to go about its business as usual. During this period, the ircd thread is able
  76  * to insert further pending requests into the queue.
  77  *
  78  * Once the processing of a request is complete, it is removed from the incoming queue to
  79  * an outgoing queue, and initialized as a 'response'. The worker thread then signals the
  80  * ircd thread (via a loopback socket) of the fact a result is available, by sending the
  81  * connection ID through the connection.
  82  *
  83  * The ircd thread then mutexes the queue once more, reads the outbound response off the head
  84  * of the queue, and sends it on its way to the original calling module.
  85  *
  86  * XXX: You might be asking "why doesnt it just send the response from within the worker thread?"
  87  * The answer to this is simple. The majority of InspIRCd, and in fact most ircd's are not
  88  * threadsafe. This module is designed to be threadsafe and is careful with its use of threads,
  89  * however, if we were to call a module's OnRequest even from within a thread which was not the
  90  * one the module was originally instantiated upon, there is a chance of all hell breaking loose
  91  * if a module is ever put in a re-enterant state (stack corruption could occur, crashes, data
  92  * corruption, and worse, so DONT think about it until the day comes when InspIRCd is 100%
  93  * guaranteed threadsafe!)
  94  */
  95
  96 class SQLConnection;
  97 class MySQLresult;
  98 class DispatcherThread;
  99
 100 struct QueryQueueItem
 101 {
 102         // An SQL database which this query is executed on.
 103         SQLConnection* connection;
 104
 105         // An object which handles the result of the query.
 106         SQL::Query* query;
 107
 108         // The SQL query which is to be executed.
 109         std::string querystr;
 110
 111         QueryQueueItem(SQL::Query* q, const std::string& s, SQLConnection* c)
 112                 : connection(c)
 113                 , query(q)
 114                 , querystr(s)
 115         {
 116         }
 117 };
 118
 119 struct ResultQueueItem
 120 {
 121         // An object which handles the result of the query.
 122         SQL::Query* query;
 123
 124         // The result returned from executing the MySQL query.
 125         MySQLresult* result;
 126
 127         ResultQueueItem(SQL::Query* q, MySQLresult* r)
 128                 : query(q)
 129                 , result(r)
 130         {
 131         }
 132 };
 133
 134 typedef insp::flat_map<std::string, SQLConnection*> ConnMap;
 135 typedef std::deque<QueryQueueItem> QueryQueue;
 136 typedef std::deque<ResultQueueItem> ResultQueue;
 137
 138 /** MySQL module
 139  *  */
 140 class ModuleSQL : public Module
 141 {
 142  public:
 143         DispatcherThread* Dispatcher;
 144         QueryQueue qq;       // MUST HOLD MUTEX
 145         ResultQueue rq;      // MUST HOLD MUTEX
 146         ConnMap connections; // main thread only
 147
 148         ModuleSQL();
 149         void init() CXX11_OVERRIDE;
 150         ~ModuleSQL();
 151         void ReadConfig(ConfigStatus& status) CXX11_OVERRIDE;
 152         void OnUnloadModule(Module* mod) CXX11_OVERRIDE;
 153         Version GetVersion() CXX11_OVERRIDE;
 154 };
 155
 156 class DispatcherThread : public SocketThread
 157 {
 158  private:
 159         ModuleSQL* const Parent;
 160  public:
 161         DispatcherThread(ModuleSQL* CreatorModule) : Parent(CreatorModule) { }
 162         ~DispatcherThread() { }
 163         void Run() CXX11_OVERRIDE;
 164         void OnNotify() CXX11_OVERRIDE;
 165 };
 166
 167 /** Represents a mysql result set
 168  */
 169 class MySQLresult : public SQL::Result
 170 {
 171  public:
 172         SQL::Error err;
 173         int currentrow;
 174         int rows;
 175         std::vector<std::string> colnames;
 176         std::vector<SQL::Row> fieldlists;
 177
 178         MySQLresult(MYSQL_RES* res, int affected_rows)
 179                 : err(SQL::SUCCESS)
 180                 , currentrow(0)
 181                 , rows(0)
 182         {
 183                 if (affected_rows >= 1)
 184                 {
 185                         rows = affected_rows;
 186                         fieldlists.resize(rows);
 187                 }
 188                 unsigned int field_count = 0;
 189                 if (res)
 190                 {
 191                         MYSQL_ROW row;
 192                         int n = 0;
 193                         while ((row = mysql_fetch_row(res)))
 194                         {
 195                                 if (fieldlists.size() < (unsigned int)rows+1)
 196                                 {
 197                                         fieldlists.resize(fieldlists.size()+1);
 198                                 }
 199                                 field_count = 0;
 200                                 MYSQL_FIELD *fields = mysql_fetch_fields(res);
 201                                 if(mysql_num_fields(res) == 0)
 202                                         break;
 203                                 if (fields && mysql_num_fields(res))
 204                                 {
 205                                         colnames.clear();
 206                                         while (field_count < mysql_num_fields(res))
 207                                         {
 208                                                 std::string a = (fields[field_count].name ? fields[field_count].name : "");
 209                                                 if (row[field_count])
 210                                                         fieldlists[n].push_back(SQL::Field(row[field_count]));
 211                                                 else
 212                                                         fieldlists[n].push_back(SQL::Field());
 213                                                 colnames.push_back(a);
 214                                                 field_count++;
 215                                         }
 216                                         n++;
 217                                 }
 218                                 rows++;
 219                         }
 220                         mysql_free_result(res);
 221                 }
 222         }
 223
 224         MySQLresult(SQL::Error& e)
 225                 : err(e)
 226                 , currentrow(0)
 227                 , rows(0)
 228         {
 229
 230         }
 231
 232         int Rows() CXX11_OVERRIDE
 233         {
 234                 return rows;
 235         }
 236
 237         void GetCols(std::vector<std::string>& result) CXX11_OVERRIDE
 238         {
 239                 result.assign(colnames.begin(), colnames.end());
 240         }
 241
 242         bool HasColumn(const std::string& column, size_t& index) CXX11_OVERRIDE
 243         {
 244                 for (size_t i = 0; i < colnames.size(); ++i)
 245                 {
 246                         if (colnames[i] == column)
 247                         {
 248                                 index = i;
 249                                 return true;
 250                         }
 251                 }
 252                 return false;
 253         }
 254
 255         SQL::Field GetValue(int row, int column)
 256         {
 257                 if ((row >= 0) && (row < rows) && (column >= 0) && (column < (int)fieldlists[row].size()))
 258                 {
 259                         return fieldlists[row][column];
 260                 }
 261                 return SQL::Field();
 262         }
 263
 264         bool GetRow(SQL::Row& result) CXX11_OVERRIDE
 265         {
 266                 if (currentrow < rows)
 267                 {
 268                         result.assign(fieldlists[currentrow].begin(), fieldlists[currentrow].end());
 269                         currentrow++;
 270                         return true;
 271                 }
 272                 else
 273                 {
 274                         result.clear();
 275                         return false;
 276                 }
 277         }
 278 };
 279
 280 /** Represents a connection to a mysql database
 281  */
 282 class SQLConnection : public SQL::Provider
 283 {
 284  private:
 285         bool EscapeString(SQL::Query* query, const std::string& in, std::string& out)
 286         {
 287                 // In the worst case each character may need to be encoded as using two bytes and one
 288                 // byte is the NUL terminator.
 289                 std::vector<char> buffer(in.length() * 2 + 1);
 290
 291                 // The return value of mysql_escape_string() is either an error or the length of the
 292                 // encoded string not including the NUL terminator.
 293                 //
 294                 // Unfortunately, someone genius decided that mysql_escape_string should return an
 295                 // unsigned type even though -1 is returned on error so checking whether an error
 296                 // happened is a bit cursed.
 297                 unsigned long escapedsize = mysql_escape_string(&buffer[0], in.c_str(), in.length());
 298                 if (escapedsize == static_cast<unsigned long>(-1))
 299                 {
 300                         SQL::Error err(SQL::QSEND_FAIL, InspIRCd::Format("%u: %s", mysql_errno(connection), mysql_error(connection)));
 301                         query->OnError(err);
 302                         return false;
 303                 }
 304
 305                 out.append(&buffer[0], escapedsize);
 306                 return true;
 307         }
 308
 309  public:
 310         reference<ConfigTag> config;
 311         MYSQL *connection;
 312         Mutex lock;
 313
 314         // This constructor creates an SQLConnection object with the given credentials, but does not connect yet.
 315         SQLConnection(Module* p, ConfigTag* tag)
 316                 : SQL::Provider(p, tag->getString("id"))
 317                 , config(tag)
 318                 , connection(NULL)
 319         {
 320         }
 321
 322         ~SQLConnection()
 323         {
 324                 mysql_close(connection);
 325         }
 326
 327         // This method connects to the database using the credentials supplied to the constructor, and returns
 328         // true upon success.
 329         bool Connect()
 330         {
 331                 connection = mysql_init(connection);
 332
 333                 // Set the connection timeout.
 334                 unsigned int timeout = config->getDuration("timeout", 5, 1, 30);
 335                 mysql_options(connection, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);
 336
 337                 // Attempt to connect to the database.
 338                 const std::string host = config->getString("host");
 339                 const std::string user = config->getString("user");
 340                 const std::string pass = config->getString("pass");
 341                 const std::string dbname = config->getString("name");
 342                 unsigned int port = config->getUInt("port", 3306, 1, 65535);
 343                 if (!mysql_real_connect(connection, host.c_str(), user.c_str(), pass.c_str(), dbname.c_str(), port, NULL, CLIENT_IGNORE_SIGPIPE))
 344                 {
 345                         ServerInstance->Logs->Log(MODNAME, LOG_DEFAULT, "Unable to connect to the %s MySQL server: %s",
 346                                 GetId().c_str(), mysql_error(connection));
 347                         return false;
 348                 }
 349
 350                 // Set the default character set.
 351                 const std::string charset = config->getString("charset");
 352                 if (!charset.empty() && mysql_set_character_set(connection, charset.c_str()))
 353                 {
 354                         ServerInstance->Logs->Log(MODNAME, LOG_DEFAULT, "Could not set character set for %s to \"%s\": %s",
 355                                 GetId().c_str(), charset.c_str(), mysql_error(connection));
 356                         return false;
 357                 }
 358
 359                 // Execute the initial SQL query.
 360                 const std::string initialquery = config->getString("initialquery");
 361                 if (!initialquery.empty() && mysql_real_query(connection, initialquery.data(), initialquery.length()))
 362                 {
 363                         ServerInstance->Logs->Log(MODNAME, LOG_DEFAULT, "Could not execute initial query \"%s\" for %s: %s",
 364                                 initialquery.c_str(), name.c_str(), mysql_error(connection));
 365                         return false;
 366                 }
 367
 368                 return true;
 369         }
 370
 371         ModuleSQL* Parent()
 372         {
 373                 return (ModuleSQL*)(Module*)creator;
 374         }
 375
 376         MySQLresult* DoBlockingQuery(const std::string& query)
 377         {
 378
 379                 /* Parse the command string and dispatch it to mysql */
 380                 if (CheckConnection() && !mysql_real_query(connection, query.data(), query.length()))
 381                 {
 382                         /* Successful query */
 383                         MYSQL_RES* res = mysql_use_result(connection);
 384                         unsigned long rows = mysql_affected_rows(connection);
 385                         return new MySQLresult(res, rows);
 386                 }
 387                 else
 388                 {
 389                         /* XXX: See /usr/include/mysql/mysqld_error.h for a list of
 390                          * possible error numbers and error messages */
 391                         SQL::Error e(SQL::QREPLY_FAIL, InspIRCd::Format("%u: %s", mysql_errno(connection), mysql_error(connection)));
 392                         return new MySQLresult(e);
 393                 }
 394         }
 395
 396         bool CheckConnection()
 397         {
 398                 if (!connection || mysql_ping(connection) != 0)
 399                         return Connect();
 400                 return true;
 401         }
 402
 403         void Submit(SQL::Query* q, const std::string& qs) CXX11_OVERRIDE
 404         {
 405                 ServerInstance->Logs->Log(MODNAME, LOG_DEBUG, "Executing MySQL query: " + qs);
 406                 Parent()->Dispatcher->LockQueue();
 407                 Parent()->qq.push_back(QueryQueueItem(q, qs, this));
 408                 Parent()->Dispatcher->UnlockQueueWakeup();
 409         }
 410
 411         void Submit(SQL::Query* call, const std::string& q, const SQL::ParamList& p) CXX11_OVERRIDE
 412         {
 413                 std::string res;
 414                 unsigned int param = 0;
 415                 for(std::string::size_type i = 0; i < q.length(); i++)
 416                 {
 417                         if (q[i] != '?')
 418                                 res.push_back(q[i]);
 419                         else if (param < p.size() && !EscapeString(call, p[param++], res))
 420                                 return;
 421                 }
 422                 Submit(call, res);
 423         }
 424
 425         void Submit(SQL::Query* call, const std::string& q, const SQL::ParamMap& p) CXX11_OVERRIDE
 426         {
 427                 std::string res;
 428                 for(std::string::size_type i = 0; i < q.length(); i++)
 429                 {
 430                         if (q[i] != '$')
 431                                 res.push_back(q[i]);
 432                         else
 433                         {
 434                                 std::string field;
 435                                 i++;
 436                                 while (i < q.length() && isalnum(q[i]))
 437                                         field.push_back(q[i++]);
 438                                 i--;
 439
 440                                 SQL::ParamMap::const_iterator it = p.find(field);
 441                                 if (it != p.end() && !EscapeString(call, it->second, res))
 442                                         return;
 443                         }
 444                 }
 445                 Submit(call, res);
 446         }
 447 };
 448
 449 ModuleSQL::ModuleSQL()
 450         : Dispatcher(NULL)
 451 {
 452 }
 453
 454 void ModuleSQL::init()
 455 {
 456         if (mysql_library_init(0, NULL, NULL))
 457                 throw ModuleException("Unable to initialise the MySQL library!");
 458
 459         Dispatcher = new DispatcherThread(this);
 460         ServerInstance->Threads.Start(Dispatcher);
 461 }
 462
 463 ModuleSQL::~ModuleSQL()
 464 {
 465         if (Dispatcher)
 466         {
 467                 Dispatcher->join();
 468                 Dispatcher->OnNotify();
 469                 delete Dispatcher;
 470         }
 471
 472         for(ConnMap::iterator i = connections.begin(); i != connections.end(); i++)
 473         {
 474                 delete i->second;
 475         }
 476
 477         mysql_library_end();
 478 }
 479
 480 void ModuleSQL::ReadConfig(ConfigStatus& status)
 481 {
 482         ConnMap conns;
 483         ConfigTagList tags = ServerInstance->Config->ConfTags("database");
 484         for(ConfigIter i = tags.first; i != tags.second; i++)
 485         {
 486                 if (!stdalgo::string::equalsci(i->second->getString("module"), "mysql"))
 487                         continue;
 488                 std::string id = i->second->getString("id");
 489                 ConnMap::iterator curr = connections.find(id);
 490                 if (curr == connections.end())
 491                 {
 492                         SQLConnection* conn = new SQLConnection(this, i->second);
 493                         conns.insert(std::make_pair(id, conn));
 494                         ServerInstance->Modules->AddService(*conn);
 495                 }
 496                 else
 497                 {
 498                         conns.insert(*curr);
 499                         connections.erase(curr);
 500                 }
 501         }
 502
 503         // now clean up the deleted databases
 504         Dispatcher->LockQueue();
 505         SQL::Error err(SQL::BAD_DBID);
 506         for(ConnMap::iterator i = connections.begin(); i != connections.end(); i++)
 507         {
 508                 ServerInstance->Modules->DelService(*i->second);
 509                 // it might be running a query on this database. Wait for that to complete
 510                 i->second->lock.Lock();
 511                 i->second->lock.Unlock();
 512                 // now remove all active queries to this DB
 513                 for (size_t j = qq.size(); j > 0; j--)
 514                 {
 515                         size_t k = j - 1;
 516                         if (qq[k].connection == i->second)
 517                         {
 518                                 qq[k].query->OnError(err);
 519                                 delete qq[k].query;
 520                                 qq.erase(qq.begin() + k);
 521                         }
 522                 }
 523                 // finally, nuke the connection
 524                 delete i->second;
 525         }
 526         Dispatcher->UnlockQueue();
 527         connections.swap(conns);
 528 }
 529
 530 void ModuleSQL::OnUnloadModule(Module* mod)
 531 {
 532         SQL::Error err(SQL::BAD_DBID);
 533         Dispatcher->LockQueue();
 534         unsigned int i = qq.size();
 535         while (i > 0)
 536         {
 537                 i--;
 538                 if (qq[i].query->creator == mod)
 539                 {
 540                         if (i == 0)
 541                         {
 542                                 // need to wait until the query is done
 543                                 // (the result will be discarded)
 544                                 qq[i].connection->lock.Lock();
 545                                 qq[i].connection->lock.Unlock();
 546                         }
 547                         qq[i].query->OnError(err);
 548                         delete qq[i].query;
 549                         qq.erase(qq.begin() + i);
 550                 }
 551         }
 552         Dispatcher->UnlockQueue();
 553         // clean up any result queue entries
 554         Dispatcher->OnNotify();
 555 }
 556
 557 Version ModuleSQL::GetVersion()
 558 {
 559         return Version("Provides the ability for SQL modules to query a MySQL database.", VF_VENDOR);
 560 }
 561
 562 void DispatcherThread::Run()
 563 {
 564         this->LockQueue();
 565         while (!this->GetExitFlag())
 566         {
 567                 if (!Parent->qq.empty())
 568                 {
 569                         QueryQueueItem i = Parent->qq.front();
 570                         i.connection->lock.Lock();
 571                         this->UnlockQueue();
 572                         MySQLresult* res = i.connection->DoBlockingQuery(i.querystr);
 573                         i.connection->lock.Unlock();
 574
 575                         /*
 576                          * At this point, the main thread could be working on:
 577                          *  Rehash - delete i.connection out from under us. We don't care about that.
 578                          *  UnloadModule - delete i.query and the qq item. Need to avoid reporting results.
 579                          */
 580
 581                         this->LockQueue();
 582                         if (!Parent->qq.empty() && Parent->qq.front().query == i.query)
 583                         {
 584                                 Parent->qq.pop_front();
 585                                 Parent->rq.push_back(ResultQueueItem(i.query, res));
 586                                 NotifyParent();
 587                         }
 588                         else
 589                         {
 590                                 // UnloadModule ate the query
 591                                 delete res;
 592                         }
 593                 }
 594                 else
 595                 {
 596                         /* We know the queue is empty, we can safely hang this thread until
 597                          * something happens
 598                          */
 599                         this->WaitForQueue();
 600                 }
 601         }
 602         this->UnlockQueue();
 603 }
 604
 605 void DispatcherThread::OnNotify()
 606 {
 607         // this could unlock during the dispatch, but OnResult isn't expected to take that long
 608         this->LockQueue();
 609         for(ResultQueue::iterator i = Parent->rq.begin(); i != Parent->rq.end(); i++)
 610         {
 611                 MySQLresult* res = i->result;
 612                 if (res->err.code == SQL::SUCCESS)
 613                         i->query->OnResult(*res);
 614                 else
 615                         i->query->OnError(res->err);
 616                 delete i->query;
 617                 delete i->result;
 618         }
 619         Parent->rq.clear();
 620         this->UnlockQueue();
 621 }
 622
 623 MODULE_INIT(ModuleSQL)