modules/gpgsqlbackend/spgsql.cc

   1 /*
   2  * This file is part of PowerDNS or dnsdist.
   3  * Copyright -- PowerDNS.COM B.V. and its contributors
   4  *
   5  * This program is free software; you can redistribute it and/or modify
   6  * it under the terms of version 2 of the GNU General Public License as
   7  * published by the Free Software Foundation.
   8  *
   9  * In addition, for the avoidance of any doubt, permission is granted to
  10  * link this program with OpenSSL and to (re)distribute the binaries
  11  * produced as the result of such linking.
  12  *
  13  * This program is distributed in the hope that it will be useful,
  14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16  * GNU General Public License for more details.
  17  *
  18  * You should have received a copy of the GNU General Public License
  19  * along with this program; if not, write to the Free Software
  20  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
  21  */
  22
  23 #ifdef HAVE_CONFIG_H
  24 #include "config.h"
  25 #endif
  26 #include <string>
  27 #include "spgsql.hh"
  28 #include <sys/time.h>
  29 #include <iostream>
  30 #include "pdns/logger.hh"
  31 #include "pdns/dns.hh"
  32 #include "pdns/namespaces.hh"
  33 #include <algorithm>
  34
  35 class SPgSQLStatement: public SSqlStatement
  36 {
  37 public:
  38   SPgSQLStatement(const string& query, bool dolog, int nparams, SPgSQL* db, unsigned int nstatement) {
  39     d_query = query;
  40     d_dolog = dolog;
  41     d_parent = db;
  42     d_prepared = false;
  43     d_nparams = nparams;
  44     d_res = NULL;
  45     d_res_set = NULL;
  46     paramValues = NULL;
  47     paramLengths = NULL;
  48     d_nstatement = nstatement;
  49     d_paridx = 0;
  50     d_residx = 0;
  51     d_resnum = 0;
  52     d_fnum = 0;
  53     d_cur_set = 0;
  54   }
  55
  56   SSqlStatement* bind(const string& name, bool value) { return bind(name, string(value ? "t" : "f")); }
  57   SSqlStatement* bind(const string& name, int value) { return bind(name, std::to_string(value)); }
  58   SSqlStatement* bind(const string& name, uint32_t value) { return bind(name, std::to_string(value)); }
  59   SSqlStatement* bind(const string& name, long value) { return bind(name, std::to_string(value)); }
  60   SSqlStatement* bind(const string& name, unsigned long value) { return bind(name, std::to_string(value)); }
  61   SSqlStatement* bind(const string& name, long long value) { return bind(name, std::to_string(value)); }
  62   SSqlStatement* bind(const string& name, unsigned long long value) { return bind(name, std::to_string(value)); }
  63   SSqlStatement* bind(const string& name, const std::string& value) {
  64     prepareStatement();
  65     allocate();
  66     if (d_paridx>=d_nparams) {
  67       releaseStatement();
  68       throw SSqlException("Attempt to bind more parameters than query has: " + d_query);
  69     }
  70     paramValues[d_paridx] = new char[value.size()+1];
  71     memset(paramValues[d_paridx], 0, sizeof(char)*(value.size()+1));
  72     value.copy(paramValues[d_paridx], value.size());
  73     paramLengths[d_paridx] = value.size();
  74     d_paridx++;
  75     return this;
  76   }
  77   SSqlStatement* bindNull(const string& name) { prepareStatement(); d_paridx++; return this; } // these are set null in allocate()
  78   SSqlStatement* execute() {
  79     prepareStatement();
  80     if (d_dolog) {
  81       g_log<<Logger::Warning<<"Query: "<<d_query<<endl;
  82     }
  83     d_res_set = PQexecPrepared(d_db(), d_stmt.c_str(), d_nparams, paramValues, paramLengths, NULL, 0);
  84     ExecStatusType status = PQresultStatus(d_res_set);
  85     if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK && status != PGRES_NONFATAL_ERROR) {
  86       string errmsg(PQresultErrorMessage(d_res_set));
  87       releaseStatement();
  88       throw SSqlException("Fatal error during query: " + d_query + string(": ") + errmsg);
  89     }
  90     d_cur_set = 0;
  91     nextResult();
  92     return this;
  93   }
  94
  95   void nextResult() {
  96     if (d_res_set == NULL) return; // no refcursor
  97     if (d_cur_set >= PQntuples(d_res_set)) {
  98       PQclear(d_res_set);
  99       d_res_set = NULL;
 100       return;
 101     }
 102     // this code handles refcursors if they are returned
 103     // by stored procedures. you can return more than one
 104     // if you return SETOF refcursor.
 105     if (PQftype(d_res_set, 0) == 1790) { // REFCURSOR
 106 #if PG_VERSION_NUM > 90000
 107       // PQescapeIdentifier was added to libpq in postgresql 9.0
 108       char *val = PQgetvalue(d_res_set, d_cur_set++, 0);
 109       char *portal =  PQescapeIdentifier(d_db(), val, strlen(val));
 110       string cmd = string("FETCH ALL FROM \"") + string(portal) + string("\"");
 111       PQfreemem(portal);
 112 #else
 113       string portal = string(PQgetvalue(d_res_set, d_cur_set++, 0));
 114       string cmd = string("FETCH ALL FROM \"") + portal + string("\"");
 115 #endif
 116       // execute FETCH
 117       if (d_dolog)
 118          g_log<<Logger::Warning<<"Query: "<<cmd<<endl;
 119       d_res = PQexec(d_db(),cmd.c_str());
 120       d_resnum = PQntuples(d_res);
 121       d_fnum = PQnfields(d_res);
 122       d_residx = 0;
 123     } else {
 124       d_res = d_res_set;
 125       d_res_set = NULL;
 126       d_resnum = PQntuples(d_res);
 127       d_fnum = PQnfields(d_res);
 128     }
 129   }
 130
 131   bool hasNextRow()
 132   {
 133     return d_residx<d_resnum;
 134   }
 135
 136   SSqlStatement* nextRow(row_t& row) {
 137     int i;
 138     row.clear();
 139     if (d_residx>=d_resnum || !d_res) return this;
 140     row.reserve(PQnfields(d_res));
 141     for(i=0;i<PQnfields(d_res);i++) {
 142       if (PQgetisnull(d_res, d_residx, i)) {
 143         row.push_back("");
 144       } else if (PQftype(d_res, i) == 16) { // BOOLEAN
 145         char *val = PQgetvalue(d_res, d_residx, i);
 146         row.push_back(val[0] == 't' ? "1" : "0");
 147       } else {
 148         row.push_back(string(PQgetvalue(d_res, d_residx, i)));
 149       }
 150     }
 151     d_residx++;
 152     if (d_residx >= d_resnum) {
 153       PQclear(d_res);
 154       d_res = NULL;
 155       nextResult();
 156     }
 157     return this;
 158   }
 159
 160   SSqlStatement* getResult(result_t& result) {
 161     result.clear();
 162     if (d_res == NULL) return this;
 163     result.reserve(d_resnum);
 164     row_t row;
 165     while(hasNextRow()) { nextRow(row); result.push_back(row); }
 166     return this;
 167   }
 168
 169   SSqlStatement* reset() {
 170      int i;
 171      if (d_res)
 172        PQclear(d_res);
 173      if (d_res_set)
 174        PQclear(d_res_set);
 175      d_res_set = NULL;
 176      d_res = NULL;
 177      d_paridx = d_residx = d_resnum = 0;
 178      if (paramValues)
 179        for(i=0;i<d_nparams;i++)
 180          if (paramValues[i]) delete [] paramValues[i];
 181      delete [] paramValues;
 182      paramValues = NULL;
 183      delete [] paramLengths;
 184      paramLengths = NULL;
 185      return this;
 186   }
 187
 188   const std::string& getQuery() { return d_query; }
 189
 190   ~SPgSQLStatement() {
 191     releaseStatement();
 192   }
 193 private:
 194   PGconn* d_db() {
 195     return d_parent->db();
 196   }
 197
 198   void releaseStatement() {
 199     d_prepared = false;
 200     reset();
 201     if (!d_stmt.empty()) {
 202       string cmd = string("DEALLOCATE " + d_stmt);
 203       PGresult *res = PQexec(d_db(), cmd.c_str());
 204       PQclear(res);
 205       d_stmt.clear();
 206     }
 207   }
 208
 209   void prepareStatement() {
 210     if (d_prepared) return;
 211     // prepare a statement; name must be unique per session (using d_nstatement to ensure this).
 212     this->d_stmt = string("stmt") + std::to_string(d_nstatement);
 213     PGresult* res = PQprepare(d_db(), d_stmt.c_str(), d_query.c_str(), d_nparams, NULL);
 214     ExecStatusType status = PQresultStatus(res);
 215     string errmsg(PQresultErrorMessage(res));
 216     PQclear(res);
 217     if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK && status != PGRES_NONFATAL_ERROR) {
 218       releaseStatement();
 219       throw SSqlException("Fatal error during prepare: " + d_query + string(": ") + errmsg);
 220     }
 221     paramValues=NULL;
 222     d_cur_set=d_paridx=d_residx=d_resnum=d_fnum=0;
 223     paramLengths=NULL;
 224     d_res=NULL;
 225     d_res_set=NULL;
 226     d_prepared = true;
 227   }
 228
 229   void allocate() {
 230      if (paramValues != NULL) return;
 231      paramValues = new char*[d_nparams];
 232      paramLengths = new int[d_nparams];
 233      memset(paramValues, 0, sizeof(char*)*d_nparams);
 234      memset(paramLengths, 0, sizeof(int)*d_nparams);
 235   }
 236
 237   string d_query;
 238   string d_stmt;
 239   SPgSQL *d_parent;
 240   PGresult *d_res_set;
 241   PGresult *d_res;
 242   bool d_dolog;
 243   bool d_prepared;
 244   int d_nparams;
 245   int d_paridx;
 246   char **paramValues;
 247   int *paramLengths;
 248   int d_residx;
 249   int d_resnum;
 250   int d_fnum;
 251   int d_cur_set;
 252   unsigned int d_nstatement;
 253 };
 254
 255 bool SPgSQL::s_dolog;
 256
 257 SPgSQL::SPgSQL(const string &database, const string &host, const string& port, const string &user,
 258                const string &password, const string &extra_connection_parameters)
 259 {
 260   d_db=0;
 261   d_in_trx = false;
 262   d_connectstr="";
 263   d_nstatement = 0;
 264
 265   if (!database.empty())
 266     d_connectstr+="dbname="+database;
 267
 268   if (!user.empty())
 269     d_connectstr+=" user="+user;
 270
 271   if(!host.empty())
 272     d_connectstr+=" host="+host;
 273
 274   if(!port.empty())
 275     d_connectstr+=" port="+port;
 276
 277   if(!extra_connection_parameters.empty())
 278     d_connectstr+=" " + extra_connection_parameters;
 279
 280   d_connectlogstr=d_connectstr;
 281
 282   if(!password.empty()) {
 283     d_connectlogstr+=" password=<HIDDEN>";
 284     d_connectstr+=" password="+password;
 285   }
 286
 287   d_db=PQconnectdb(d_connectstr.c_str());
 288
 289   if (!d_db || PQstatus(d_db)==CONNECTION_BAD) {
 290     try {
 291       throw sPerrorException("Unable to connect to database, connect string: "+d_connectlogstr);
 292     }
 293     catch(...) {
 294       if(d_db)
 295         PQfinish(d_db);
 296       d_db = 0;
 297       throw;
 298     }
 299   }
 300 }
 301
 302 void SPgSQL::setLog(bool state)
 303 {
 304   s_dolog=state;
 305 }
 306
 307 SPgSQL::~SPgSQL()
 308 {
 309   PQfinish(d_db);
 310 }
 311
 312 SSqlException SPgSQL::sPerrorException(const string &reason)
 313 {
 314   return SSqlException(reason+string(": ")+(d_db ? PQerrorMessage(d_db) : "no connection"));
 315 }
 316
 317 void SPgSQL::execute(const string& query)
 318 {
 319   PGresult* res = PQexec(d_db, query.c_str());
 320   ExecStatusType status = PQresultStatus(res);
 321   string errmsg(PQresultErrorMessage(res));
 322   PQclear(res);
 323   if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK && status != PGRES_NONFATAL_ERROR) {
 324     throw sPerrorException("Fatal error during query: " + errmsg);
 325   }
 326 }
 327
 328 std::unique_ptr<SSqlStatement> SPgSQL::prepare(const string& query, int nparams)
 329 {
 330   d_nstatement++;
 331   return std::unique_ptr<SSqlStatement>(new SPgSQLStatement(query, s_dolog, nparams, this, d_nstatement));
 332 }
 333
 334 void SPgSQL::startTransaction() {
 335   execute("begin");
 336   d_in_trx = true;
 337 }
 338
 339 void SPgSQL::commit() {
 340   execute("commit");
 341   d_in_trx = false;
 342 }
 343
 344 void SPgSQL::rollback() {
 345   execute("rollback");
 346   d_in_trx = false;
 347 }
 348
 349 bool SPgSQL::isConnectionUsable()
 350 {
 351   if (PQstatus(d_db) != CONNECTION_OK) {
 352     return false;
 353   }
 354
 355   bool usable = false;
 356   int sd = PQsocket(d_db);
 357   bool wasNonBlocking = isNonBlocking(sd);
 358
 359   if (!wasNonBlocking) {
 360     if (!setNonBlocking(sd)) {
 361       return usable;
 362     }
 363   }
 364
 365   usable = isTCPSocketUsable(sd);
 366
 367   if (!wasNonBlocking) {
 368     if (!setBlocking(sd)) {
 369       usable = false;
 370     }
 371   }
 372
 373   return usable;
 374 }
 375
 376 void SPgSQL::reconnect()
 377 {
 378   PQreset(d_db);
 379 }