pdns/backends/gsql/gsqlbackend.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 #ifdef HAVE_CONFIG_H
  23 #include "config.h"
  24 #endif
  25 #include "pdns/dns.hh"
  26 #include "pdns/dnsbackend.hh"
  27 #include "gsqlbackend.hh"
  28 #include "pdns/dnspacket.hh"
  29 #include "pdns/pdnsexception.hh"
  30 #include "pdns/logger.hh"
  31 #include "pdns/arguments.hh"
  32 #include "pdns/base32.hh"
  33 #include "pdns/dnssecinfra.hh"
  34 #include <boost/algorithm/string.hpp>
  35 #include <sstream>
  36 #include <boost/format.hpp>
  37 #include <boost/scoped_ptr.hpp>
  38
  39 #define ASSERT_ROW_COLUMNS(query, row, num) { if (row.size() != num) { throw PDNSException(std::string(query) + " returned wrong number of columns, expected "  #num  ", got " + std::to_string(row.size())); } }
  40
  41 GSQLBackend::GSQLBackend(const string &mode, const string &suffix)
  42 {
  43   setArgPrefix(mode+suffix);
  44   d_db=0;
  45   d_logprefix="["+mode+"Backend"+suffix+"] ";
  46
  47   try
  48   {
  49     d_dnssecQueries = mustDo("dnssec");
  50   }
  51   catch (const ArgException&)
  52   {
  53     d_dnssecQueries = false;
  54   }
  55
  56   d_NoIdQuery=getArg("basic-query");
  57   d_IdQuery=getArg("id-query");
  58   d_ANYNoIdQuery=getArg("any-query");
  59   d_ANYIdQuery=getArg("any-id-query");
  60
  61   d_listQuery=getArg("list-query");
  62   d_listSubZoneQuery=getArg("list-subzone-query");
  63
  64   d_InfoOfDomainsZoneQuery=getArg("info-zone-query");
  65   d_InfoOfAllSlaveDomainsQuery=getArg("info-all-slaves-query");
  66   d_SuperMasterInfoQuery=getArg("supermaster-query");
  67   d_GetSuperMasterIPs=getArg("supermaster-name-to-ips");
  68   d_InsertZoneQuery=getArg("insert-zone-query");
  69   d_InsertRecordQuery=getArg("insert-record-query");
  70   d_UpdateMasterOfZoneQuery=getArg("update-master-query");
  71   d_UpdateKindOfZoneQuery=getArg("update-kind-query");
  72   d_UpdateSerialOfZoneQuery=getArg("update-serial-query");
  73   d_UpdateLastCheckofZoneQuery=getArg("update-lastcheck-query");
  74   d_UpdateAccountOfZoneQuery=getArg("update-account-query");
  75   d_InfoOfAllMasterDomainsQuery=getArg("info-all-master-query");
  76   d_DeleteDomainQuery=getArg("delete-domain-query");
  77   d_DeleteZoneQuery=getArg("delete-zone-query");
  78   d_DeleteRRSetQuery=getArg("delete-rrset-query");
  79   d_DeleteNamesQuery=getArg("delete-names-query");
  80   d_getAllDomainsQuery=getArg("get-all-domains-query");
  81
  82   d_InsertEmptyNonTerminalOrderQuery=getArg("insert-empty-non-terminal-order-query");
  83   d_DeleteEmptyNonTerminalQuery = getArg("delete-empty-non-terminal-query");
  84   d_RemoveEmptyNonTerminalsFromZoneQuery = getArg("remove-empty-non-terminals-from-zone-query");
  85
  86   d_ListCommentsQuery = getArg("list-comments-query");
  87   d_InsertCommentQuery = getArg("insert-comment-query");
  88   d_DeleteCommentRRsetQuery = getArg("delete-comment-rrset-query");
  89   d_DeleteCommentsQuery = getArg("delete-comments-query");
  90
  91   d_firstOrderQuery = getArg("get-order-first-query");
  92   d_beforeOrderQuery = getArg("get-order-before-query");
  93   d_afterOrderQuery = getArg("get-order-after-query");
  94   d_lastOrderQuery = getArg("get-order-last-query");
  95
  96   d_updateOrderNameAndAuthQuery = getArg("update-ordername-and-auth-query");
  97   d_updateOrderNameAndAuthTypeQuery = getArg("update-ordername-and-auth-type-query");
  98   d_nullifyOrderNameAndUpdateAuthQuery = getArg("nullify-ordername-and-update-auth-query");
  99   d_nullifyOrderNameAndUpdateAuthTypeQuery = getArg("nullify-ordername-and-update-auth-type-query");
 100
 101   d_AddDomainKeyQuery = getArg("add-domain-key-query");
 102   d_GetLastInsertedKeyIdQuery = getArg("get-last-inserted-key-id-query");
 103   d_ListDomainKeysQuery = getArg("list-domain-keys-query");
 104
 105   d_GetAllDomainMetadataQuery = getArg("get-all-domain-metadata-query");
 106   d_GetDomainMetadataQuery = getArg("get-domain-metadata-query");
 107   d_ClearDomainMetadataQuery = getArg("clear-domain-metadata-query");
 108   d_ClearDomainAllMetadataQuery = getArg("clear-domain-all-metadata-query");
 109   d_SetDomainMetadataQuery = getArg("set-domain-metadata-query");
 110
 111   d_ActivateDomainKeyQuery = getArg("activate-domain-key-query");
 112   d_DeactivateDomainKeyQuery = getArg("deactivate-domain-key-query");
 113   d_RemoveDomainKeyQuery = getArg("remove-domain-key-query");
 114   d_ClearDomainAllKeysQuery = getArg("clear-domain-all-keys-query");
 115
 116   d_getTSIGKeyQuery = getArg("get-tsig-key-query");
 117   d_setTSIGKeyQuery = getArg("set-tsig-key-query");
 118   d_deleteTSIGKeyQuery = getArg("delete-tsig-key-query");
 119   d_getTSIGKeysQuery = getArg("get-tsig-keys-query");
 120
 121   d_SearchRecordsQuery = getArg("search-records-query");
 122   d_SearchCommentsQuery = getArg("search-comments-query");
 123
 124   d_query_stmt = NULL;
 125   d_NoIdQuery_stmt = NULL;
 126   d_IdQuery_stmt = NULL;
 127   d_ANYNoIdQuery_stmt = NULL;
 128   d_ANYIdQuery_stmt = NULL;
 129   d_listQuery_stmt = NULL;
 130   d_listSubZoneQuery_stmt = NULL;
 131   d_InfoOfDomainsZoneQuery_stmt = NULL;
 132   d_InfoOfAllSlaveDomainsQuery_stmt = NULL;
 133   d_SuperMasterInfoQuery_stmt = NULL;
 134   d_GetSuperMasterIPs_stmt = NULL;
 135   d_InsertZoneQuery_stmt = NULL;
 136   d_InsertRecordQuery_stmt = NULL;
 137   d_InsertEmptyNonTerminalOrderQuery_stmt = NULL;
 138   d_UpdateMasterOfZoneQuery_stmt = NULL;
 139   d_UpdateKindOfZoneQuery_stmt = NULL;
 140   d_UpdateSerialOfZoneQuery_stmt = NULL;
 141   d_UpdateLastCheckofZoneQuery_stmt = NULL;
 142   d_UpdateAccountOfZoneQuery_stmt = NULL;
 143   d_InfoOfAllMasterDomainsQuery_stmt = NULL;
 144   d_DeleteDomainQuery_stmt = NULL;
 145   d_DeleteZoneQuery_stmt = NULL;
 146   d_DeleteRRSetQuery_stmt = NULL;
 147   d_DeleteNamesQuery_stmt = NULL;
 148   d_firstOrderQuery_stmt = NULL;
 149   d_beforeOrderQuery_stmt = NULL;
 150   d_afterOrderQuery_stmt = NULL;
 151   d_lastOrderQuery_stmt = NULL;
 152   d_updateOrderNameAndAuthQuery_stmt = NULL;
 153   d_updateOrderNameAndAuthTypeQuery_stmt = NULL;
 154   d_nullifyOrderNameAndUpdateAuthQuery_stmt = NULL;
 155   d_nullifyOrderNameAndUpdateAuthTypeQuery_stmt = NULL;
 156   d_RemoveEmptyNonTerminalsFromZoneQuery_stmt = NULL;
 157   d_DeleteEmptyNonTerminalQuery_stmt = NULL;
 158   d_AddDomainKeyQuery_stmt = NULL;
 159   d_GetLastInsertedKeyIdQuery_stmt = NULL;
 160   d_ListDomainKeysQuery_stmt = NULL;
 161   d_GetAllDomainMetadataQuery_stmt = NULL;
 162   d_GetDomainMetadataQuery_stmt = NULL;
 163   d_ClearDomainMetadataQuery_stmt = NULL;
 164   d_ClearDomainAllMetadataQuery_stmt = NULL;
 165   d_SetDomainMetadataQuery_stmt = NULL;
 166   d_RemoveDomainKeyQuery_stmt = NULL;
 167   d_ActivateDomainKeyQuery_stmt = NULL;
 168   d_DeactivateDomainKeyQuery_stmt = NULL;
 169   d_ClearDomainAllKeysQuery_stmt = NULL;
 170   d_getTSIGKeyQuery_stmt = NULL;
 171   d_setTSIGKeyQuery_stmt = NULL;
 172   d_deleteTSIGKeyQuery_stmt = NULL;
 173   d_getTSIGKeysQuery_stmt = NULL;
 174   d_getAllDomainsQuery_stmt = NULL;
 175   d_ListCommentsQuery_stmt = NULL;
 176   d_InsertCommentQuery_stmt = NULL;
 177   d_DeleteCommentRRsetQuery_stmt = NULL;
 178   d_DeleteCommentsQuery_stmt = NULL;
 179   d_SearchRecordsQuery_stmt = NULL;
 180   d_SearchCommentsQuery_stmt = NULL;
 181 }
 182
 183 void GSQLBackend::setNotified(uint32_t domain_id, uint32_t serial)
 184 {
 185   try {
 186     reconnectIfNeeded();
 187
 188     d_UpdateSerialOfZoneQuery_stmt->
 189       bind("serial", serial)->
 190       bind("domain_id", domain_id)->
 191       execute()->
 192       reset();
 193   }
 194   catch(SSqlException &e) {
 195     throw PDNSException("GSQLBackend unable to refresh domain_id "+itoa(domain_id)+": "+e.txtReason());
 196   }
 197 }
 198
 199 void GSQLBackend::setFresh(uint32_t domain_id)
 200 {
 201   try {
 202     reconnectIfNeeded();
 203
 204     d_UpdateLastCheckofZoneQuery_stmt->
 205       bind("last_check", time(0))->
 206       bind("domain_id", domain_id)->
 207       execute()->
 208       reset();
 209   }
 210   catch (SSqlException &e) {
 211     throw PDNSException("GSQLBackend unable to refresh domain_id "+itoa(domain_id)+": "+e.txtReason());
 212   }
 213 }
 214
 215 bool GSQLBackend::setMaster(const DNSName &domain, const string &ip)
 216 {
 217   try {
 218     reconnectIfNeeded();
 219
 220     d_UpdateMasterOfZoneQuery_stmt->
 221       bind("master", ip)->
 222       bind("domain", domain)->
 223       execute()->
 224       reset();
 225   }
 226   catch (SSqlException &e) {
 227     throw PDNSException("GSQLBackend unable to set master of domain '"+domain.toLogString()+"' to IP address " + ip + ": "+e.txtReason());
 228   }
 229   return true;
 230 }
 231
 232 bool GSQLBackend::setKind(const DNSName &domain, const DomainInfo::DomainKind kind)
 233 {
 234   try {
 235     reconnectIfNeeded();
 236
 237     d_UpdateKindOfZoneQuery_stmt->
 238       bind("kind", toUpper(DomainInfo::getKindString(kind)))->
 239       bind("domain", domain)->
 240       execute()->
 241       reset();
 242   }
 243   catch (SSqlException &e) {
 244     throw PDNSException("GSQLBackend unable to set kind of domain '"+domain.toLogString()+"' to " + toUpper(DomainInfo::getKindString(kind)) + ": "+e.txtReason());
 245   }
 246   return true;
 247 }
 248
 249 bool GSQLBackend::setAccount(const DNSName &domain, const string &account)
 250 {
 251   try {
 252     reconnectIfNeeded();
 253
 254     d_UpdateAccountOfZoneQuery_stmt->
 255             bind("account", account)->
 256             bind("domain", domain)->
 257             execute()->
 258             reset();
 259   }
 260   catch (SSqlException &e) {
 261     throw PDNSException("GSQLBackend unable to set account of domain '"+domain.toLogString()+"' to '" + account + "': "+e.txtReason());
 262   }
 263   return true;
 264 }
 265
 266 bool GSQLBackend::getDomainInfo(const DNSName &domain, DomainInfo &di, bool getSerial)
 267 {
 268   /* fill DomainInfo from database info:
 269      id,name,master IP(s),last_check,notified_serial,type,account */
 270   try {
 271     reconnectIfNeeded();
 272
 273     d_InfoOfDomainsZoneQuery_stmt->
 274       bind("domain", domain)->
 275       execute()->
 276       getResult(d_result)->
 277       reset();
 278   }
 279   catch(SSqlException &e) {
 280     throw PDNSException("GSQLBackend unable to retrieve information about a domain: "+e.txtReason());
 281   }
 282
 283   int numanswers=d_result.size();
 284   if(!numanswers)
 285     return false;
 286
 287   ASSERT_ROW_COLUMNS("info-zone-query", d_result[0], 7);
 288
 289   di.id=pdns_stou(d_result[0][0]);
 290   try {
 291     di.zone=DNSName(d_result[0][1]);
 292   } catch (...) {
 293     return false;
 294   }
 295   vector<string> masters;
 296   stringtok(masters, d_result[0][2], " ,\t");
 297   for(const auto& m : masters)
 298     di.masters.emplace_back(m, 53);
 299   di.last_check=pdns_stou(d_result[0][3]);
 300   di.notified_serial = pdns_stou(d_result[0][4]);
 301   string type=d_result[0][5];
 302   di.account=d_result[0][6];
 303   di.backend=this;
 304
 305   di.serial = 0;
 306   if(getSerial) {
 307     try {
 308       SOAData sd;
 309       if(!getSOA(domain, sd))
 310         g_log<<Logger::Notice<<"No serial for '"<<domain<<"' found - zone is missing?"<<endl;
 311       else
 312         di.serial = sd.serial;
 313     }
 314     catch(PDNSException &ae){
 315       g_log<<Logger::Error<<"Error retrieving serial for '"<<domain<<"': "<<ae.reason<<endl;
 316     }
 317   }
 318
 319   di.kind = DomainInfo::stringToKind(type);
 320
 321   return true;
 322 }
 323
 324 void GSQLBackend::getUnfreshSlaveInfos(vector<DomainInfo> *unfreshDomains)
 325 {
 326   /* list all domains that need refreshing for which we are slave, and insert into SlaveDomain:
 327      id,name,master IP,serial */
 328   try {
 329     reconnectIfNeeded();
 330
 331     d_InfoOfAllSlaveDomainsQuery_stmt->
 332       execute()->
 333       getResult(d_result)->
 334       reset();
 335   }
 336   catch (SSqlException &e) {
 337     throw PDNSException("GSQLBackend unable to retrieve list of slave domains: "+e.txtReason());
 338   }
 339
 340   vector<DomainInfo> allSlaves;
 341
 342   for(const auto& row : d_result) { // id,name,master,last_check
 343     DomainInfo sd;
 344     ASSERT_ROW_COLUMNS("info-all-slaves-query", row, 4);
 345     try {
 346       sd.id=pdns_stou(row[0]);
 347       sd.zone= DNSName(row[1]);
 348
 349       vector<string> masters;
 350       stringtok(masters, row[2], ", \t");
 351       for(const auto& m : masters)
 352         sd.masters.emplace_back(m, 53);
 353
 354       sd.last_check=pdns_stou(row[3]);
 355       sd.backend=this;
 356       sd.kind=DomainInfo::Slave;
 357       allSlaves.push_back(sd);
 358     } catch (...) {
 359       continue;
 360     }
 361   }
 362
 363   for (auto& slave : allSlaves) {
 364     try {
 365       SOAData sdata;
 366       sdata.serial=0;
 367       sdata.refresh=0;
 368       getSOA(slave.zone, sdata);
 369       if(static_cast<time_t>(slave.last_check + sdata.refresh) < time(nullptr)) {
 370         slave.serial=sdata.serial;
 371         unfreshDomains->push_back(slave);
 372       }
 373     }
 374     catch(const std::exception& exp) {
 375       g_log<<Logger::Warning<<"Error while parsing SOA data for slave zone '"<<slave.zone.toLogString()<<"': "<<exp.what()<<endl;
 376       continue;
 377     }
 378     catch(...) {
 379       g_log<<Logger::Warning<<"Error while parsing SOA data for slave zone '"<<slave.zone.toLogString()<<"', skipping"<<endl;
 380       continue;
 381     }
 382   }
 383 }
 384
 385 void GSQLBackend::getUpdatedMasters(vector<DomainInfo> *updatedDomains)
 386 {
 387   /* list all domains that need notifications for which we are master, and insert into updatedDomains
 388      id,name,master IP,serial */
 389   try {
 390     reconnectIfNeeded();
 391
 392     d_InfoOfAllMasterDomainsQuery_stmt->
 393       execute()->
 394       getResult(d_result)->
 395       reset();
 396   }
 397   catch(SSqlException &e) {
 398     throw PDNSException("GSQLBackend unable to retrieve list of master domains: "+e.txtReason());
 399   }
 400
 401   vector<DomainInfo> allMasters;
 402   size_t numanswers=d_result.size();
 403   for(size_t n=0;n<numanswers;++n) { // id,name,master,last_check,notified_serial
 404     DomainInfo sd;
 405     ASSERT_ROW_COLUMNS("info-all-master-query", d_result[n], 6);
 406     sd.id=pdns_stou(d_result[n][0]);
 407     try {
 408       sd.zone= DNSName(d_result[n][1]);
 409     } catch (...) {
 410       continue;
 411     }
 412     sd.last_check=pdns_stou(d_result[n][3]);
 413     sd.notified_serial=pdns_stou(d_result[n][4]);
 414     sd.backend=this;
 415     sd.kind=DomainInfo::Master;
 416     allMasters.push_back(sd);
 417   }
 418
 419   for(vector<DomainInfo>::iterator i=allMasters.begin();i!=allMasters.end();++i) {
 420     SOAData sdata;
 421     sdata.serial=0;
 422     sdata.refresh=0;
 423     getSOA(i->zone,sdata);
 424     if(i->notified_serial!=sdata.serial) {
 425       i->serial=sdata.serial;
 426       updatedDomains->push_back(*i);
 427     }
 428   }
 429 }
 430
 431 bool GSQLBackend::updateDNSSECOrderNameAndAuth(uint32_t domain_id, const DNSName& qname, const DNSName& ordername, bool auth, const uint16_t qtype)
 432 {
 433   if(!d_dnssecQueries)
 434     return false;
 435
 436   if (!ordername.empty()) {
 437     if (qtype == QType::ANY) {
 438       try {
 439         reconnectIfNeeded();
 440
 441         d_updateOrderNameAndAuthQuery_stmt->
 442           bind("ordername", ordername.labelReverse().toString(" ", false))->
 443           bind("auth", auth)->
 444           bind("domain_id", domain_id)->
 445           bind("qname", qname)->
 446           execute()->
 447           reset();
 448       }
 449       catch(SSqlException &e) {
 450         throw PDNSException("GSQLBackend unable to update ordername and auth for domain_id "+itoa(domain_id)+": "+e.txtReason());
 451       }
 452     } else {
 453       try {
 454         reconnectIfNeeded();
 455
 456         d_updateOrderNameAndAuthTypeQuery_stmt->
 457           bind("ordername", ordername.labelReverse().toString(" ", false))->
 458           bind("auth", auth)->
 459           bind("domain_id", domain_id)->
 460           bind("qname", qname)->
 461           bind("qtype", QType(qtype).getName())->
 462           execute()->
 463           reset();
 464       }
 465       catch(SSqlException &e) {
 466         throw PDNSException("GSQLBackend unable to update ordername and auth per type for domain_id "+itoa(domain_id)+": "+e.txtReason());
 467       }
 468     }
 469   } else {
 470     if (qtype == QType::ANY) {
 471       reconnectIfNeeded();
 472
 473       try {
 474         d_nullifyOrderNameAndUpdateAuthQuery_stmt->
 475           bind("auth", auth)->
 476           bind("domain_id", domain_id)->
 477           bind("qname", qname)->
 478           execute()->
 479           reset();
 480       }
 481       catch(SSqlException &e) {
 482         throw PDNSException("GSQLBackend unable to nullify ordername and update auth for domain_id "+itoa(domain_id)+": "+e.txtReason());
 483       }
 484     } else {
 485       try {
 486         reconnectIfNeeded();
 487
 488         d_nullifyOrderNameAndUpdateAuthTypeQuery_stmt->
 489           bind("auth", auth)->
 490           bind("domain_id", domain_id)->
 491           bind("qname", qname)->
 492           bind("qtype", QType(qtype).getName())->
 493           execute()->
 494           reset();
 495       }
 496       catch(SSqlException &e) {
 497         throw PDNSException("GSQLBackend unable to nullify ordername and update auth per type for domain_id "+itoa(domain_id)+": "+e.txtReason());
 498       }
 499     }
 500   }
 501   return true;
 502 }
 503
 504 bool GSQLBackend::updateEmptyNonTerminals(uint32_t domain_id, set<DNSName>& insert, set<DNSName>& erase, bool remove)
 505 {
 506   if(remove) {
 507     try {
 508       reconnectIfNeeded();
 509
 510       d_RemoveEmptyNonTerminalsFromZoneQuery_stmt->
 511         bind("domain_id", domain_id)->
 512         execute()->
 513         reset();
 514     }
 515     catch (SSqlException &e) {
 516       throw PDNSException("GSQLBackend unable to delete empty non-terminal records from domain_id "+itoa(domain_id)+": "+e.txtReason());
 517       return false;
 518     }
 519   }
 520   else
 521   {
 522     for(const auto& qname: erase) {
 523       try {
 524         reconnectIfNeeded();
 525
 526         d_DeleteEmptyNonTerminalQuery_stmt->
 527           bind("domain_id", domain_id)->
 528           bind("qname", qname)->
 529           execute()->
 530           reset();
 531       }
 532       catch (SSqlException &e) {
 533         throw PDNSException("GSQLBackend unable to delete empty non-terminal rr '"+qname.toLogString()+"' from domain_id "+itoa(domain_id)+": "+e.txtReason());
 534         return false;
 535       }
 536     }
 537   }
 538
 539   for(const auto& qname: insert) {
 540     try {
 541       reconnectIfNeeded();
 542
 543       d_InsertEmptyNonTerminalOrderQuery_stmt->
 544         bind("domain_id", domain_id)->
 545         bind("qname", qname)->
 546         bindNull("ordername")->
 547         bind("auth", true)->
 548         execute()->
 549         reset();
 550     }
 551     catch (SSqlException &e) {
 552       throw PDNSException("GSQLBackend unable to insert empty non-terminal rr '"+qname.toLogString()+"' in domain_id "+itoa(domain_id)+": "+e.txtReason());
 553       return false;
 554     }
 555   }
 556
 557   return true;
 558 }
 559
 560 bool GSQLBackend::doesDNSSEC()
 561 {
 562     return d_dnssecQueries;
 563 }
 564
 565 bool GSQLBackend::getBeforeAndAfterNamesAbsolute(uint32_t id, const DNSName& qname, DNSName& unhashed, DNSName& before, DNSName& after)
 566 {
 567   if(!d_dnssecQueries)
 568     return false;
 569   after.clear();
 570
 571   SSqlStatement::row_t row;
 572   try {
 573     reconnectIfNeeded();
 574
 575     d_afterOrderQuery_stmt->
 576       bind("ordername", qname.labelReverse().toString(" ", false))->
 577       bind("domain_id", id)->
 578       execute();
 579     while(d_afterOrderQuery_stmt->hasNextRow()) {
 580       d_afterOrderQuery_stmt->nextRow(row);
 581       ASSERT_ROW_COLUMNS("get-order-after-query", row, 1);
 582       if(! row[0].empty()) { // Hack because NULL values are passed on as empty strings
 583         after=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 584       }
 585     }
 586     d_afterOrderQuery_stmt->reset();
 587   }
 588   catch(SSqlException &e) {
 589     throw PDNSException("GSQLBackend unable to find before/after (after) for domain_id "+itoa(id)+" and qname '"+ qname.toLogString() +"': "+e.txtReason());
 590   }
 591
 592   if(after.empty()) {
 593     try {
 594       reconnectIfNeeded();
 595
 596       d_firstOrderQuery_stmt->
 597         bind("domain_id", id)->
 598         execute();
 599       while(d_firstOrderQuery_stmt->hasNextRow()) {
 600         d_firstOrderQuery_stmt->nextRow(row);
 601         ASSERT_ROW_COLUMNS("get-order-first-query", row, 1);
 602         after=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 603       }
 604       d_firstOrderQuery_stmt->reset();
 605     }
 606     catch(SSqlException &e) {
 607       throw PDNSException("GSQLBackend unable to find before/after (first) for domain_id "+itoa(id)+" and qname '"+ qname.toLogString() + "': "+e.txtReason());
 608     }
 609   }
 610
 611   if (before.empty()) {
 612     unhashed.clear();
 613
 614     try {
 615       reconnectIfNeeded();
 616
 617       d_beforeOrderQuery_stmt->
 618         bind("ordername", qname.labelReverse().toString(" ", false))->
 619         bind("domain_id", id)->
 620         execute();
 621       while(d_beforeOrderQuery_stmt->hasNextRow()) {
 622         d_beforeOrderQuery_stmt->nextRow(row);
 623         ASSERT_ROW_COLUMNS("get-order-before-query", row, 2);
 624         before=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 625         try {
 626           unhashed=DNSName(row[1]);
 627         } catch (...) {
 628           continue;
 629         }
 630       }
 631       d_beforeOrderQuery_stmt->reset();
 632     }
 633     catch(SSqlException &e) {
 634       throw PDNSException("GSQLBackend unable to find before/after (before) for domain_id "+itoa(id)+" and qname '"+ qname.toLogString() + ": "+e.txtReason());
 635     }
 636
 637     if(! unhashed.empty())
 638     {
 639       // cerr<<"unhashed="<<unhashed<<",before="<<before<<", after="<<after<<endl;
 640       return true;
 641     }
 642
 643     try {
 644       reconnectIfNeeded();
 645
 646       d_lastOrderQuery_stmt->
 647         bind("domain_id", id)->
 648         execute();
 649       while(d_lastOrderQuery_stmt->hasNextRow()) {
 650         d_lastOrderQuery_stmt->nextRow(row);
 651         ASSERT_ROW_COLUMNS("get-order-last-query", row, 2);
 652         before=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 653         try {
 654           unhashed=DNSName(row[1]);
 655         } catch (...) {
 656           continue;
 657         }
 658       }
 659       d_lastOrderQuery_stmt->reset();
 660     }
 661     catch(SSqlException &e) {
 662       throw PDNSException("GSQLBackend unable to find before/after (last) for domain_id "+itoa(id)+" and qname '"+ qname.toLogString() + ": "+e.txtReason());
 663     }
 664   } else {
 665     before=qname;
 666   }
 667
 668   return true;
 669 }
 670
 671 bool GSQLBackend::addDomainKey(const DNSName& name, const KeyData& key, int64_t& id)
 672 {
 673   if(!d_dnssecQueries)
 674     return false;
 675
 676   try {
 677     reconnectIfNeeded();
 678
 679     d_AddDomainKeyQuery_stmt->
 680       bind("flags", key.flags)->
 681       bind("active", key.active)->
 682       bind("content", key.content)->
 683       bind("domain", name)->
 684       execute()->
 685       reset();
 686   }
 687   catch (SSqlException &e) {
 688     throw PDNSException("GSQLBackend unable to store key for domain '"+ name.toLogString() + "': "+e.txtReason());
 689   }
 690
 691   try {
 692     reconnectIfNeeded();
 693
 694     d_GetLastInsertedKeyIdQuery_stmt->execute();
 695     if (!d_GetLastInsertedKeyIdQuery_stmt->hasNextRow()) {
 696       id = -2;
 697       return true;
 698     }
 699     SSqlStatement::row_t row;
 700     d_GetLastInsertedKeyIdQuery_stmt->nextRow(row);
 701     ASSERT_ROW_COLUMNS("get-last-inserted-key-id-query", row, 1);
 702     id = std::stoi(row[0]);
 703     d_GetLastInsertedKeyIdQuery_stmt->reset();
 704     return true;
 705   }
 706   catch (SSqlException &e) {
 707     id = -2;
 708     return true;
 709   }
 710
 711   return false;
 712 }
 713
 714 bool GSQLBackend::activateDomainKey(const DNSName& name, unsigned int id)
 715 {
 716   if(!d_dnssecQueries)
 717     return false;
 718
 719   try {
 720     reconnectIfNeeded();
 721
 722     d_ActivateDomainKeyQuery_stmt->
 723       bind("domain", name)->
 724       bind("key_id", id)->
 725       execute()->
 726       reset();
 727   }
 728   catch (SSqlException &e) {
 729     throw PDNSException("GSQLBackend unable to activate key with id "+ std::to_string(id) + " for domain '" + name.toLogString() + "': "+e.txtReason());
 730   }
 731   return true;
 732 }
 733
 734 bool GSQLBackend::deactivateDomainKey(const DNSName& name, unsigned int id)
 735 {
 736   if(!d_dnssecQueries)
 737     return false;
 738
 739   try {
 740     reconnectIfNeeded();
 741
 742     d_DeactivateDomainKeyQuery_stmt->
 743       bind("domain", name)->
 744       bind("key_id", id)->
 745       execute()->
 746       reset();
 747   }
 748   catch (SSqlException &e) {
 749     throw PDNSException("GSQLBackend unable to deactivate key with id "+ std::to_string(id) + " for domain '" + name.toLogString() + "': "+e.txtReason());
 750   }
 751   return true;
 752 }
 753
 754 bool GSQLBackend::removeDomainKey(const DNSName& name, unsigned int id)
 755 {
 756   if(!d_dnssecQueries)
 757     return false;
 758
 759   try {
 760     reconnectIfNeeded();
 761
 762     d_RemoveDomainKeyQuery_stmt->
 763       bind("domain", name)->
 764       bind("key_id", id)->
 765       execute()->
 766       reset();
 767   }
 768   catch (SSqlException &e) {
 769     throw PDNSException("GSQLBackend unable to remove key with id "+ std::to_string(id) + " for domain '" + name.toLogString() + "': "+e.txtReason());
 770   }
 771   return true;
 772 }
 773
 774 bool GSQLBackend::getTSIGKey(const DNSName& name, DNSName* algorithm, string* content)
 775 {
 776   try {
 777     reconnectIfNeeded();
 778
 779     d_getTSIGKeyQuery_stmt->
 780       bind("key_name", name)->
 781       execute();
 782
 783     SSqlStatement::row_t row;
 784
 785     content->clear();
 786     while(d_getTSIGKeyQuery_stmt->hasNextRow()) {
 787       d_getTSIGKeyQuery_stmt->nextRow(row);
 788       ASSERT_ROW_COLUMNS("get-tsig-key-query", row, 2);
 789       try{
 790         if(algorithm->empty() || *algorithm==DNSName(row[0])) {
 791           *algorithm = DNSName(row[0]);
 792           *content = row[1];
 793         }
 794       } catch (...) {}
 795     }
 796
 797     d_getTSIGKeyQuery_stmt->reset();
 798   }
 799   catch (SSqlException &e) {
 800     throw PDNSException("GSQLBackend unable to retrieve TSIG key with name '" + name.toLogString() + "': "+e.txtReason());
 801   }
 802
 803   return !content->empty();
 804 }
 805
 806 bool GSQLBackend::setTSIGKey(const DNSName& name, const DNSName& algorithm, const string& content)
 807 {
 808   try {
 809     reconnectIfNeeded();
 810
 811     d_setTSIGKeyQuery_stmt->
 812       bind("key_name", name)->
 813       bind("algorithm", algorithm)->
 814       bind("content", content)->
 815       execute()->
 816       reset();
 817   }
 818   catch (SSqlException &e) {
 819     throw PDNSException("GSQLBackend unable to store TSIG key with name '" + name.toLogString() + "' and algorithm '" + algorithm.toString() + "': "+e.txtReason());
 820   }
 821   return true;
 822 }
 823
 824 bool GSQLBackend::deleteTSIGKey(const DNSName& name)
 825 {
 826   try {
 827     reconnectIfNeeded();
 828
 829     d_deleteTSIGKeyQuery_stmt->
 830       bind("key_name", name)->
 831       execute()->
 832       reset();
 833   }
 834   catch (SSqlException &e) {
 835     throw PDNSException("GSQLBackend unable to delete TSIG key with name '" + name.toLogString() + "': "+e.txtReason());
 836   }
 837   return true;
 838 }
 839
 840 bool GSQLBackend::getTSIGKeys(std::vector< struct TSIGKey > &keys)
 841 {
 842   try {
 843     reconnectIfNeeded();
 844
 845     d_getTSIGKeysQuery_stmt->
 846       execute();
 847
 848     SSqlStatement::row_t row;
 849
 850     while(d_getTSIGKeysQuery_stmt->hasNextRow()) {
 851       d_getTSIGKeysQuery_stmt->nextRow(row);
 852       ASSERT_ROW_COLUMNS("get-tsig-keys-query", row, 3);
 853       struct TSIGKey key;
 854       try {
 855         key.name = DNSName(row[0]);
 856         key.algorithm = DNSName(row[1]);
 857       } catch (...) {
 858         continue;
 859       }
 860       key.key = row[2];
 861       keys.push_back(key);
 862     }
 863
 864     d_getTSIGKeysQuery_stmt->reset();
 865   }
 866   catch (SSqlException &e) {
 867     throw PDNSException("GSQLBackend unable to retrieve TSIG keys: "+e.txtReason());
 868   }
 869
 870   return keys.empty();
 871 }
 872
 873 bool GSQLBackend::getDomainKeys(const DNSName& name, std::vector<KeyData>& keys)
 874 {
 875   if(!d_dnssecQueries)
 876     return false;
 877
 878   try {
 879     reconnectIfNeeded();
 880
 881     d_ListDomainKeysQuery_stmt->
 882       bind("domain", name)->
 883       execute();
 884
 885     SSqlStatement::row_t row;
 886     KeyData kd;
 887     while(d_ListDomainKeysQuery_stmt->hasNextRow()) {
 888       d_ListDomainKeysQuery_stmt->nextRow(row);
 889       ASSERT_ROW_COLUMNS("list-domain-keys-query", row, 4);
 890       //~ for(const auto& val: row) {
 891         //~ cerr<<"'"<<val<<"'"<<endl;
 892       //~ }
 893       kd.id = pdns_stou(row[0]);
 894       kd.flags = pdns_stou(row[1]);
 895       kd.active = row[2] == "1";
 896       kd.content = row[3];
 897       keys.push_back(kd);
 898     }
 899
 900     d_ListDomainKeysQuery_stmt->reset();
 901   }
 902   catch (SSqlException &e) {
 903     throw PDNSException("GSQLBackend unable to list keys: "+e.txtReason());
 904   }
 905
 906   return true;
 907 }
 908
 909 void GSQLBackend::alsoNotifies(const DNSName &domain, set<string> *ips)
 910 {
 911   vector<string> meta;
 912   getDomainMetadata(domain, "ALSO-NOTIFY", meta);
 913   for(const auto& str: meta) {
 914     ips->insert(str);
 915   }
 916 }
 917
 918 bool GSQLBackend::getAllDomainMetadata(const DNSName& name, std::map<std::string, std::vector<std::string> >& meta)
 919 {
 920   try {
 921     reconnectIfNeeded();
 922
 923     d_GetAllDomainMetadataQuery_stmt->
 924       bind("domain", name)->
 925       execute();
 926
 927     SSqlStatement::row_t row;
 928
 929     while(d_GetAllDomainMetadataQuery_stmt->hasNextRow()) {
 930       d_GetAllDomainMetadataQuery_stmt->nextRow(row);
 931       ASSERT_ROW_COLUMNS("get-all-domain-metadata-query", row, 2);
 932
 933       if (!isDnssecDomainMetadata(row[0]))
 934         meta[row[0]].push_back(row[1]);
 935     }
 936
 937     d_GetAllDomainMetadataQuery_stmt->reset();
 938   }
 939   catch (SSqlException &e) {
 940     throw PDNSException("GSQLBackend unable to list metadata for domain '" + name.toLogString() + "': "+e.txtReason());
 941   }
 942
 943   return true;
 944 }
 945
 946
 947 bool GSQLBackend::getDomainMetadata(const DNSName& name, const std::string& kind, std::vector<std::string>& meta)
 948 {
 949   if(!d_dnssecQueries && isDnssecDomainMetadata(kind))
 950     return false;
 951
 952   try {
 953     reconnectIfNeeded();
 954
 955     d_GetDomainMetadataQuery_stmt->
 956       bind("domain", name)->
 957       bind("kind", kind)->
 958       execute();
 959
 960     SSqlStatement::row_t row;
 961
 962     while(d_GetDomainMetadataQuery_stmt->hasNextRow()) {
 963       d_GetDomainMetadataQuery_stmt->nextRow(row);
 964       ASSERT_ROW_COLUMNS("get-domain-metadata-query", row, 1);
 965       meta.push_back(row[0]);
 966     }
 967
 968     d_GetDomainMetadataQuery_stmt->reset();
 969   }
 970   catch (SSqlException &e) {
 971     throw PDNSException("GSQLBackend unable to get metadata kind '" + kind + "' for domain '" + name.toLogString() + "': "+e.txtReason());
 972   }
 973
 974   return true;
 975 }
 976
 977 bool GSQLBackend::setDomainMetadata(const DNSName& name, const std::string& kind, const std::vector<std::string>& meta)
 978 {
 979   if(!d_dnssecQueries && isDnssecDomainMetadata(kind))
 980     return false;
 981
 982   try {
 983     reconnectIfNeeded();
 984
 985     d_ClearDomainMetadataQuery_stmt->
 986       bind("domain", name)->
 987       bind("kind", kind)->
 988       execute()->
 989       reset();
 990     if(!meta.empty()) {
 991       for(const auto& value: meta) {
 992          d_SetDomainMetadataQuery_stmt->
 993            bind("kind", kind)->
 994            bind("content", value)->
 995            bind("domain", name)->
 996            execute()->
 997            reset();
 998       }
 999     }
1000   }
1001   catch (SSqlException &e) {
1002     throw PDNSException("GSQLBackend unable to set metadata kind '" + kind + "' for domain '" + name.toLogString() + "': "+e.txtReason());
1003   }
1004
1005   return true;
1006 }
1007
1008 void GSQLBackend::lookup(const QType &qtype,const DNSName &qname, DNSPacket *pkt_p, int domain_id)
1009 {
1010   try {
1011     reconnectIfNeeded();
1012
1013     if(qtype.getCode()!=QType::ANY) {
1014       if(domain_id < 0) {
1015         d_query_name = "basic-query";
1016         d_query_stmt = &d_NoIdQuery_stmt;
1017         (*d_query_stmt)->
1018           bind("qtype", qtype.getName())->
1019           bind("qname", qname);
1020       } else {
1021         d_query_name = "id-query";
1022         d_query_stmt = &d_IdQuery_stmt;
1023         (*d_query_stmt)->
1024           bind("qtype", qtype.getName())->
1025           bind("qname", qname)->
1026           bind("domain_id", domain_id);
1027       }
1028     } else {
1029       // qtype==ANY
1030       if(domain_id < 0) {
1031         d_query_name = "any-query";
1032         d_query_stmt = &d_ANYNoIdQuery_stmt;
1033         (*d_query_stmt)->
1034           bind("qname", qname);
1035       } else {
1036         d_query_name = "any-id-query";
1037         d_query_stmt = &d_ANYIdQuery_stmt;
1038         (*d_query_stmt)->
1039           bind("qname", qname)->
1040           bind("domain_id", domain_id);
1041       }
1042     }
1043
1044     (*d_query_stmt)->
1045       execute();
1046   }
1047   catch(SSqlException &e) {
1048     throw PDNSException("GSQLBackend unable to lookup '" + qname.toLogString() + "|" + qtype.getName() + "':"+e.txtReason());
1049   }
1050
1051   d_qname=qname;
1052 }
1053
1054 bool GSQLBackend::list(const DNSName &target, int domain_id, bool include_disabled)
1055 {
1056   DLOG(g_log<<"GSQLBackend constructing handle for list of domain id '"<<domain_id<<"'"<<endl);
1057
1058   try {
1059     reconnectIfNeeded();
1060
1061     d_query_name = "list-query";
1062     d_query_stmt = &d_listQuery_stmt;
1063     (*d_query_stmt)->
1064       bind("include_disabled", (int)include_disabled)->
1065       bind("domain_id", domain_id)->
1066       execute();
1067   }
1068   catch(SSqlException &e) {
1069     throw PDNSException("GSQLBackend unable to list domain '" + target.toLogString() + "': "+e.txtReason());
1070   }
1071
1072   d_qname.clear();
1073   return true;
1074 }
1075
1076 bool GSQLBackend::listSubZone(const DNSName &zone, int domain_id) {
1077
1078   string wildzone = "%." + zone.makeLowerCase().toStringNoDot();
1079
1080   try {
1081     reconnectIfNeeded();
1082
1083     d_query_name = "list-subzone-query";
1084     d_query_stmt = &d_listSubZoneQuery_stmt;
1085     (*d_query_stmt)->
1086       bind("zone", zone)->
1087       bind("wildzone", wildzone)->
1088       bind("domain_id", domain_id)->
1089       execute();
1090   }
1091   catch(SSqlException &e) {
1092     throw PDNSException("GSQLBackend unable to list SubZones for domain '" + zone.toLogString() + "': "+e.txtReason());
1093   }
1094   d_qname.clear();
1095   return true;
1096 }
1097
1098 bool GSQLBackend::get(DNSResourceRecord &r)
1099 {
1100   // g_log << "GSQLBackend get() was called for "<<qtype.getName() << " record: ";
1101   SSqlStatement::row_t row;
1102
1103 skiprow:
1104   if((*d_query_stmt)->hasNextRow()) {
1105     try {
1106       (*d_query_stmt)->nextRow(row);
1107       ASSERT_ROW_COLUMNS(d_query_name, row, 8);
1108     } catch (SSqlException &e) {
1109       throw PDNSException("GSQLBackend get: "+e.txtReason());
1110     }
1111     try {
1112       extractRecord(row, r);
1113     } catch (...) {
1114       goto skiprow;
1115     }
1116     return true;
1117   }
1118
1119   try {
1120     (*d_query_stmt)->reset();
1121   } catch (SSqlException &e) {
1122       throw PDNSException("GSQLBackend get: "+e.txtReason());
1123   }
1124   d_query_stmt = NULL;
1125   return false;
1126 }
1127
1128 bool GSQLBackend::superMasterBackend(const string &ip, const DNSName &domain, const vector<DNSResourceRecord>&nsset, string *nameserver, string *account, DNSBackend **ddb)
1129 {
1130   // check if we know the ip/ns couple in the database
1131   for(vector<DNSResourceRecord>::const_iterator i=nsset.begin();i!=nsset.end();++i) {
1132     try {
1133       reconnectIfNeeded();
1134
1135       d_SuperMasterInfoQuery_stmt->
1136         bind("ip", ip)->
1137         bind("nameserver", i->content)->
1138         execute()->
1139         getResult(d_result)->
1140         reset();
1141     }
1142     catch (SSqlException &e) {
1143       throw PDNSException("GSQLBackend unable to search for a supermaster with IP " + ip + " and nameserver name '" + i->content + "' for domain '" + domain.toLogString() + "': "+e.txtReason());
1144     }
1145     if(!d_result.empty()) {
1146       ASSERT_ROW_COLUMNS("supermaster-query", d_result[0], 1);
1147       *nameserver=i->content;
1148       *account=d_result[0][0];
1149       *ddb=this;
1150       return true;
1151     }
1152   }
1153   return false;
1154 }
1155
1156 bool GSQLBackend::createDomain(const DNSName &domain, const string &type, const string &masters, const string &account)
1157 {
1158   try {
1159     reconnectIfNeeded();
1160
1161     d_InsertZoneQuery_stmt->
1162       bind("type", type)->
1163       bind("domain", domain)->
1164       bind("masters", masters)->
1165       bind("account", account)->
1166       execute()->
1167       reset();
1168   }
1169   catch(SSqlException &e) {
1170     throw PDNSException("Database error trying to insert new domain '"+domain.toLogString()+"': "+ e.txtReason());
1171   }
1172   return true;
1173 }
1174
1175 bool GSQLBackend::createSlaveDomain(const string &ip, const DNSName &domain, const string &nameserver, const string &account)
1176 {
1177   string name;
1178   string masters(ip);
1179   try {
1180     if (!nameserver.empty()) {
1181       // figure out all IP addresses for the master
1182       reconnectIfNeeded();
1183
1184       d_GetSuperMasterIPs_stmt->
1185         bind("nameserver", nameserver)->
1186         bind("account", account)->
1187         execute()->
1188         getResult(d_result)->
1189         reset();
1190       if (!d_result.empty()) {
1191         // collect all IP addresses
1192         vector<string> tmp;
1193         for(const auto& row: d_result) {
1194           if (account == row[1])
1195             tmp.push_back(row[0]);
1196         }
1197         // set them as domain's masters, comma separated
1198         masters = boost::join(tmp, ", ");
1199       }
1200     }
1201     createDomain(domain, "SLAVE", masters, account);
1202   }
1203   catch(SSqlException &e) {
1204     throw PDNSException("Database error trying to insert new slave domain '"+domain.toLogString()+"': "+ e.txtReason());
1205   }
1206   return true;
1207 }
1208
1209 bool GSQLBackend::deleteDomain(const DNSName &domain)
1210 {
1211   DomainInfo di;
1212   if (!getDomainInfo(domain, di)) {
1213     return false;
1214   }
1215
1216   try {
1217     reconnectIfNeeded();
1218
1219     d_DeleteZoneQuery_stmt->
1220       bind("domain_id", di.id)->
1221       execute()->
1222       reset();
1223     d_ClearDomainAllMetadataQuery_stmt->
1224       bind("domain", domain)->
1225       execute()->
1226       reset();
1227     d_ClearDomainAllKeysQuery_stmt->
1228       bind("domain", domain)->
1229       execute()->
1230       reset();
1231     d_DeleteCommentsQuery_stmt->
1232       bind("domain_id", di.id)->
1233       execute()->
1234       reset();
1235     d_DeleteDomainQuery_stmt->
1236       bind("domain", domain)->
1237       execute()->
1238       reset();
1239   }
1240   catch(SSqlException &e) {
1241     throw PDNSException("Database error trying to delete domain '"+domain.toLogString()+"': "+ e.txtReason());
1242   }
1243   return true;
1244 }
1245
1246 void GSQLBackend::getAllDomains(vector<DomainInfo> *domains, bool include_disabled)
1247 {
1248   DLOG(g_log<<"GSQLBackend retrieving all domains."<<endl);
1249
1250   try {
1251     reconnectIfNeeded();
1252
1253     d_getAllDomainsQuery_stmt->
1254       bind("include_disabled", (int)include_disabled)->
1255       execute();
1256
1257     SSqlStatement::row_t row;
1258     while (d_getAllDomainsQuery_stmt->hasNextRow()) {
1259       d_getAllDomainsQuery_stmt->nextRow(row);
1260       ASSERT_ROW_COLUMNS("get-all-domains-query", row, 8);
1261       DomainInfo di;
1262       di.id = pdns_stou(row[0]);
1263       try {
1264         di.zone = DNSName(row[1]);
1265       } catch (...) {
1266         continue;
1267       }
1268
1269       if (!row[4].empty()) {
1270         vector<string> masters;
1271         stringtok(masters, row[4], " ,\t");
1272         for(const auto& m : masters)
1273           di.masters.emplace_back(m, 53);
1274       }
1275
1276       SOAData sd;
1277       fillSOAData(row[2], sd);
1278       di.serial = sd.serial;
1279       di.notified_serial = pdns_stou(row[5]);
1280       di.last_check = pdns_stou(row[6]);
1281       di.account = row[7];
1282
1283       if (pdns_iequals(row[3], "MASTER"))
1284         di.kind = DomainInfo::Master;
1285       else if (pdns_iequals(row[3], "SLAVE"))
1286         di.kind = DomainInfo::Slave;
1287       else
1288         di.kind = DomainInfo::Native;
1289
1290       di.backend = this;
1291
1292       domains->push_back(di);
1293     }
1294     d_getAllDomainsQuery_stmt->reset();
1295   }
1296   catch (SSqlException &e) {
1297     throw PDNSException("Database error trying to retrieve all domains:" + e.txtReason());
1298   }
1299 }
1300
1301 bool GSQLBackend::replaceRRSet(uint32_t domain_id, const DNSName& qname, const QType& qt, const vector<DNSResourceRecord>& rrset)
1302 {
1303   try {
1304     reconnectIfNeeded();
1305
1306     if (qt != QType::ANY) {
1307       d_DeleteRRSetQuery_stmt->
1308         bind("domain_id", domain_id)->
1309         bind("qname", qname)->
1310         bind("qtype", qt.getName())->
1311         execute()->
1312         reset();
1313     } else {
1314       d_DeleteNamesQuery_stmt->
1315         bind("domain_id", domain_id)->
1316         bind("qname", qname)->
1317         execute()->
1318         reset();
1319     }
1320   }
1321   catch (SSqlException &e) {
1322     throw PDNSException("GSQLBackend unable to delete RRSet " + qname.toLogString() + "|" + qt.getName() + ": "+e.txtReason());
1323   }
1324
1325   if (rrset.empty()) {
1326     try {
1327       reconnectIfNeeded();
1328
1329       d_DeleteCommentRRsetQuery_stmt->
1330         bind("domain_id", domain_id)->
1331         bind("qname", qname)->
1332         bind("qtype", qt.getName())->
1333         execute()->
1334         reset();
1335     }
1336     catch (SSqlException &e) {
1337       throw PDNSException("GSQLBackend unable to delete comment for RRSet " + qname.toLogString() + "|" + qt.getName() + ": "+e.txtReason());
1338     }
1339   }
1340   for(const auto& rr: rrset) {
1341     feedRecord(rr, DNSName());
1342   }
1343
1344   return true;
1345 }
1346
1347 bool GSQLBackend::feedRecord(const DNSResourceRecord &r, const DNSName &ordername, bool ordernameIsNSEC3)
1348 {
1349   int prio=0;
1350   string content(r.content);
1351   if (r.qtype == QType::MX || r.qtype == QType::SRV) {
1352     string::size_type pos = content.find_first_not_of("0123456789");
1353     if (pos != string::npos) {
1354       prio=pdns_stou(content.substr(0,pos));
1355       boost::erase_head(content, pos);
1356     }
1357     trim_left(content);
1358   }
1359
1360   try {
1361     reconnectIfNeeded();
1362
1363     d_InsertRecordQuery_stmt->
1364       bind("content",content)->
1365       bind("ttl",r.ttl)->
1366       bind("priority",prio)->
1367       bind("qtype",r.qtype.getName())->
1368       bind("domain_id",r.domain_id)->
1369       bind("disabled",r.disabled)->
1370       bind("qname",r.qname);
1371
1372     if (!ordername.empty())
1373       d_InsertRecordQuery_stmt->bind("ordername", ordername.labelReverse().makeLowerCase().toString(" ", false));
1374     else
1375       d_InsertRecordQuery_stmt->bindNull("ordername");
1376
1377     if (d_dnssecQueries)
1378       d_InsertRecordQuery_stmt->bind("auth", r.auth);
1379     else
1380       d_InsertRecordQuery_stmt->bind("auth", true);
1381
1382     d_InsertRecordQuery_stmt->
1383       execute()->
1384       reset();
1385   }
1386   catch (SSqlException &e) {
1387     throw PDNSException("GSQLBackend unable to feed record " + r.qname.toLogString() + "|" + r.qtype.getName() + ": "+e.txtReason());
1388   }
1389   return true; // XXX FIXME this API should not return 'true' I think -ahu
1390 }
1391
1392 bool GSQLBackend::feedEnts(int domain_id, map<DNSName,bool>& nonterm)
1393 {
1394   for(const auto& nt: nonterm) {
1395     try {
1396       reconnectIfNeeded();
1397
1398       d_InsertEmptyNonTerminalOrderQuery_stmt->
1399         bind("domain_id",domain_id)->
1400         bind("qname", nt.first)->
1401         bindNull("ordername")->
1402         bind("auth",(nt.second || !d_dnssecQueries))->
1403         execute()->
1404         reset();
1405     }
1406     catch (SSqlException &e) {
1407       throw PDNSException("GSQLBackend unable to feed empty non-terminal with name '" + nt.first.toLogString() + "': "+e.txtReason());
1408     }
1409   }
1410   return true;
1411 }
1412
1413 bool GSQLBackend::feedEnts3(int domain_id, const DNSName &domain, map<DNSName,bool> &nonterm, const NSEC3PARAMRecordContent& ns3prc, bool narrow)
1414 {
1415   if(!d_dnssecQueries)
1416       return false;
1417
1418   string ordername;
1419
1420   for(const auto& nt: nonterm) {
1421     try {
1422       reconnectIfNeeded();
1423
1424       d_InsertEmptyNonTerminalOrderQuery_stmt->
1425         bind("domain_id",domain_id)->
1426         bind("qname", nt.first);
1427       if (narrow || !nt.second) {
1428         d_InsertEmptyNonTerminalOrderQuery_stmt->
1429           bindNull("ordername");
1430       } else {
1431         ordername=toBase32Hex(hashQNameWithSalt(ns3prc, nt.first));
1432         d_InsertEmptyNonTerminalOrderQuery_stmt->
1433           bind("ordername", ordername);
1434       }
1435       d_InsertEmptyNonTerminalOrderQuery_stmt->
1436         bind("auth",nt.second)->
1437         execute()->
1438         reset();
1439     }
1440     catch (SSqlException &e) {
1441       throw PDNSException("GSQLBackend unable to feed empty non-terminal with name '" + nt.first.toLogString() + "' (hashed name '"+ toBase32Hex(hashQNameWithSalt(ns3prc, nt.first)) + "') : "+e.txtReason());
1442     }
1443   }
1444   return true;
1445 }
1446
1447 bool GSQLBackend::startTransaction(const DNSName &domain, int domain_id)
1448 {
1449   try {
1450     reconnectIfNeeded();
1451
1452     d_db->startTransaction();
1453     d_inTransaction = true;
1454     if(domain_id >= 0) {
1455       d_DeleteZoneQuery_stmt->
1456         bind("domain_id", domain_id)->
1457         execute()->
1458         reset();
1459     }
1460   }
1461   catch (SSqlException &e) {
1462     d_inTransaction = false;
1463     throw PDNSException("Database failed to start transaction for domain '" + domain.toLogString() + "': "+e.txtReason());
1464   }
1465
1466   return true;
1467 }
1468
1469 bool GSQLBackend::commitTransaction()
1470 {
1471   try {
1472     d_db->commit();
1473     d_inTransaction = false;
1474   }
1475   catch (SSqlException &e) {
1476     d_inTransaction = false;
1477     throw PDNSException("Database failed to commit transaction: "+e.txtReason());
1478   }
1479   return true;
1480 }
1481
1482 bool GSQLBackend::abortTransaction()
1483 {
1484   try {
1485     d_db->rollback();
1486     d_inTransaction = false;
1487   }
1488   catch(SSqlException &e) {
1489     d_inTransaction = false;
1490     throw PDNSException("Database failed to abort transaction: "+string(e.txtReason()));
1491   }
1492   return true;
1493 }
1494
1495 bool GSQLBackend::listComments(const uint32_t domain_id)
1496 {
1497   try {
1498     reconnectIfNeeded();
1499
1500     d_query_name = "list-comments-query";
1501     d_query_stmt = &d_ListCommentsQuery_stmt;
1502     (*d_query_stmt)->
1503       bind("domain_id", domain_id)->
1504       execute();
1505   }
1506   catch(SSqlException &e) {
1507     throw PDNSException("GSQLBackend unable to list comments for domain id " + std::to_string(domain_id) + ": "+e.txtReason());
1508   }
1509
1510   return true;
1511 }
1512
1513 bool GSQLBackend::getComment(Comment& comment)
1514 {
1515   SSqlStatement::row_t row;
1516
1517   for(;;) {
1518     if (!(*d_query_stmt)->hasNextRow()) {
1519       try {
1520         (*d_query_stmt)->reset();
1521       } catch(SSqlException &e) {
1522         throw PDNSException("GSQLBackend comment get: "+e.txtReason());
1523       }
1524       d_query_stmt = NULL;
1525       return false;
1526     }
1527
1528     try {
1529       (*d_query_stmt)->nextRow(row);
1530       ASSERT_ROW_COLUMNS(d_query_name, row, 6);
1531     } catch(SSqlException &e) {
1532       throw PDNSException("GSQLBackend comment get: "+e.txtReason());
1533     }
1534     try {
1535       extractComment(row, comment);
1536     } catch (...) {
1537       continue;
1538     }
1539     return true;
1540   }
1541 }
1542
1543 void GSQLBackend::feedComment(const Comment& comment)
1544 {
1545   try {
1546     reconnectIfNeeded();
1547
1548     d_InsertCommentQuery_stmt->
1549       bind("domain_id",comment.domain_id)->
1550       bind("qname",comment.qname)->
1551       bind("qtype",comment.qtype.getName())->
1552       bind("modified_at",comment.modified_at)->
1553       bind("account",comment.account)->
1554       bind("content",comment.content)->
1555       execute()->
1556       reset();
1557   }
1558   catch (SSqlException &e) {
1559     throw PDNSException("GSQLBackend unable to feed comment for RRSet '" + comment.qname.toLogString() + "|" + comment.qtype.getName() + "': "+e.txtReason());
1560   }
1561 }
1562
1563 bool GSQLBackend::replaceComments(const uint32_t domain_id, const DNSName& qname, const QType& qt, const vector<Comment>& comments)
1564 {
1565   try {
1566     reconnectIfNeeded();
1567
1568     d_DeleteCommentRRsetQuery_stmt->
1569       bind("domain_id",domain_id)->
1570       bind("qname", qname)->
1571       bind("qtype",qt.getName())->
1572       execute()->
1573       reset();
1574   }
1575   catch (SSqlException &e) {
1576     throw PDNSException("GSQLBackend unable to delete comment for RRSet '" + qname.toLogString() + "|" + qt.getName() + "': "+e.txtReason());
1577   }
1578
1579   for(const auto& comment: comments) {
1580     feedComment(comment);
1581   }
1582
1583   return true;
1584 }
1585
1586 string GSQLBackend::directBackendCmd(const string &query)
1587 {
1588  try {
1589    ostringstream out;
1590
1591    auto stmt = d_db->prepare(query,0);
1592
1593    reconnectIfNeeded();
1594
1595    stmt->execute();
1596
1597    SSqlStatement::row_t row;
1598
1599    while(stmt->hasNextRow()) {
1600      stmt->nextRow(row);
1601      for(const auto& col: row)
1602        out<<"\'"<<col<<"\'\t";
1603      out<<endl;
1604    }
1605
1606    return out.str();
1607  }
1608  catch (SSqlException &e) {
1609    throw PDNSException("GSQLBackend unable to execute direct command query '" + query + "': "+e.txtReason());
1610  }
1611 }
1612
1613 string GSQLBackend::pattern2SQLPattern(const string &pattern)
1614 {
1615   string escaped_pattern = boost::replace_all_copy(pattern,"\\","\\\\");
1616   boost::replace_all(escaped_pattern,"_","\\_");
1617   boost::replace_all(escaped_pattern,"%","\\%");
1618   boost::replace_all(escaped_pattern,"*","%");
1619   boost::replace_all(escaped_pattern,"?","_");
1620   return escaped_pattern;
1621 }
1622
1623 bool GSQLBackend::searchRecords(const string &pattern, int maxResults, vector<DNSResourceRecord>& result)
1624 {
1625   d_qname.clear();
1626   string escaped_pattern = pattern2SQLPattern(pattern);
1627   try {
1628     reconnectIfNeeded();
1629
1630     d_SearchRecordsQuery_stmt->
1631       bind("value", escaped_pattern)->
1632       bind("value2", escaped_pattern)->
1633       bind("limit", maxResults)->
1634       execute();
1635
1636     while(d_SearchRecordsQuery_stmt->hasNextRow())
1637     {
1638       SSqlStatement::row_t row;
1639       DNSResourceRecord r;
1640       d_SearchRecordsQuery_stmt->nextRow(row);
1641       ASSERT_ROW_COLUMNS("search-records-query", row, 8);
1642       try {
1643         extractRecord(row, r);
1644       } catch (...) {
1645         continue;
1646       }
1647       result.push_back(r);
1648     }
1649
1650     d_SearchRecordsQuery_stmt->reset();
1651
1652     return true;
1653   }
1654   catch (SSqlException &e) {
1655     throw PDNSException("GSQLBackend unable to search for records with pattern '" + pattern + "' (escaped pattern '" + escaped_pattern + "'): "+e.txtReason());
1656   }
1657
1658   return false;
1659 }
1660
1661 bool GSQLBackend::searchComments(const string &pattern, int maxResults, vector<Comment>& result)
1662 {
1663   Comment c;
1664   string escaped_pattern = pattern2SQLPattern(pattern);
1665   try {
1666     reconnectIfNeeded();
1667
1668     d_SearchCommentsQuery_stmt->
1669       bind("value", escaped_pattern)->
1670       bind("value2", escaped_pattern)->
1671       bind("limit", maxResults)->
1672       execute();
1673
1674     while(d_SearchCommentsQuery_stmt->hasNextRow()) {
1675       SSqlStatement::row_t row;
1676       d_SearchCommentsQuery_stmt->nextRow(row);
1677       ASSERT_ROW_COLUMNS("search-comments-query", row, 6);
1678       Comment comment;
1679       extractComment(row, comment);
1680       result.push_back(comment);
1681     }
1682
1683     d_SearchCommentsQuery_stmt->reset();
1684
1685     return true;
1686   }
1687   catch (SSqlException &e) {
1688     throw PDNSException("GSQLBackend unable to search for comments with pattern '" + pattern + "' (escaped pattern '" + escaped_pattern + "'): "+e.txtReason());
1689   }
1690
1691   return false;
1692 }
1693
1694 void GSQLBackend::extractRecord(const SSqlStatement::row_t& row, DNSResourceRecord& r)
1695 {
1696   if (row[1].empty())
1697       r.ttl = ::arg().asNum( "default-ttl" );
1698   else
1699       r.ttl=pdns_stou(row[1]);
1700   if(!d_qname.empty())
1701     r.qname=d_qname;
1702   else
1703     r.qname=DNSName(row[6]);
1704
1705   r.qtype=row[3];
1706
1707   if (r.qtype==QType::MX || r.qtype==QType::SRV)
1708     r.content=row[2]+" "+row[0];
1709   else
1710     r.content=row[0];
1711
1712   r.last_modified=0;
1713
1714   if(d_dnssecQueries)
1715     r.auth = !row[7].empty() && row[7][0]=='1';
1716   else
1717     r.auth = 1;
1718
1719   r.disabled = !row[5].empty() && row[5][0]=='1';
1720
1721   r.domain_id=pdns_stou(row[4]);
1722 }
1723
1724 void GSQLBackend::extractComment(const SSqlStatement::row_t& row, Comment& comment)
1725 {
1726   comment.domain_id = pdns_stou(row[0]);
1727   comment.qname = DNSName(row[1]);
1728   comment.qtype = row[2];
1729   comment.modified_at = pdns_stou(row[3]);
1730   comment.account = row[4];
1731   comment.content = row[5];
1732 }
1733
1734 SSqlStatement::~SSqlStatement() {
1735 // make sure vtable won't break
1736 }