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 domain '" + domain.toLogString() + "': "+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   string type=d_result[0][5];
 296   di.account=d_result[0][6];
 297   di.kind = DomainInfo::stringToKind(type);
 298
 299   vector<string> masters;
 300   stringtok(masters, d_result[0][2], " ,\t");
 301   for(const auto& m : masters)
 302     di.masters.emplace_back(m, 53);
 303   di.last_check=pdns_stou(d_result[0][3]);
 304   di.notified_serial = pdns_stou(d_result[0][4]);
 305   di.backend=this;
 306
 307   di.serial = 0;
 308   if(getSerial) {
 309     try {
 310       SOAData sd;
 311       if(!getSOA(domain, sd))
 312         g_log<<Logger::Notice<<"No serial for '"<<domain<<"' found - zone is missing?"<<endl;
 313       else
 314         di.serial = sd.serial;
 315     }
 316     catch(PDNSException &ae){
 317       g_log<<Logger::Error<<"Error retrieving serial for '"<<domain<<"': "<<ae.reason<<endl;
 318     }
 319   }
 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   bool loggedAssertRowColumns = false;
 343   for(const auto& row : d_result) { // id,name,master,last_check
 344     DomainInfo sd;
 345     try {
 346       ASSERT_ROW_COLUMNS("info-all-slaves-query", row, 4);
 347     } catch(const PDNSException &e) {
 348       if (!loggedAssertRowColumns) {
 349         g_log<<Logger::Warning<<e.reason<<endl;
 350       }
 351       loggedAssertRowColumns = true;
 352       continue;
 353     }
 354
 355     try {
 356       sd.zone = DNSName(row[1]);
 357     } catch(const std::runtime_error &e) {
 358       g_log<<Logger::Warning<<"Domain name '"<<row[1]<<"' is not a valid DNS name: "<<e.what()<<endl;
 359       continue;
 360     }
 361
 362     try {
 363       sd.id=pdns_stou(row[0]);
 364     } catch (const std::exception &e) {
 365       g_log<<Logger::Warning<<"Could not convert id ("<<row[0]<<") for domain '"<<sd.zone<<"' into an integer: "<<e.what()<<endl;
 366       continue;
 367     }
 368
 369     vector<string> masters;
 370     stringtok(masters, row[2], ", \t");
 371     for(const auto& m : masters) {
 372       try {
 373         sd.masters.emplace_back(m, 53);
 374       } catch(const PDNSException &e) {
 375         g_log<<Logger::Warning<<"Could not parse master address ("<<m<<") for zone '"<<sd.zone<<"': "<<e.reason<<endl;
 376       }
 377     }
 378     if (sd.masters.empty()) {
 379       g_log<<Logger::Warning<<"No masters for slave zone '"<<sd.zone<<"' found in the database"<<endl;
 380       continue;
 381     }
 382
 383     try {
 384       sd.last_check=pdns_stou(row[3]);
 385     } catch (const std::exception &e) {
 386       g_log<<Logger::Warning<<"Could not convert last_check ("<<row[3]<<") for domain '"<<sd.zone<<"' into an integer: "<<e.what()<<endl;
 387       continue;
 388     }
 389
 390     sd.backend=this;
 391     sd.kind=DomainInfo::Slave;
 392     allSlaves.push_back(sd);
 393   }
 394
 395   for (auto& slave : allSlaves) {
 396     try {
 397       SOAData sdata;
 398       sdata.serial=0;
 399       sdata.refresh=0;
 400       getSOA(slave.zone, sdata);
 401       if(static_cast<time_t>(slave.last_check + sdata.refresh) < time(nullptr)) {
 402         slave.serial=sdata.serial;
 403         unfreshDomains->push_back(slave);
 404       }
 405     }
 406     catch(const std::exception& exp) {
 407       g_log<<Logger::Warning<<"Error while parsing SOA data for slave zone '"<<slave.zone.toLogString()<<"': "<<exp.what()<<endl;
 408       continue;
 409     }
 410     catch(...) {
 411       g_log<<Logger::Warning<<"Error while parsing SOA data for slave zone '"<<slave.zone.toLogString()<<"', skipping"<<endl;
 412       continue;
 413     }
 414   }
 415 }
 416
 417 void GSQLBackend::getUpdatedMasters(vector<DomainInfo> *updatedDomains)
 418 {
 419   /* list all domains that need notifications for which we are master, and insert into updatedDomains
 420      id, name, notified_serial, serial */
 421   try {
 422     reconnectIfNeeded();
 423
 424     d_InfoOfAllMasterDomainsQuery_stmt->
 425       execute()->
 426       getResult(d_result)->
 427       reset();
 428   }
 429   catch(SSqlException &e) {
 430     throw PDNSException("GSQLBackend unable to retrieve list of master domains: "+e.txtReason());
 431   }
 432
 433   size_t numanswers=d_result.size();
 434   vector<string>parts;
 435   DomainInfo di;
 436
 437   di.backend = this;
 438   di.kind = DomainInfo::Master;
 439
 440   for( size_t n = 0; n < numanswers; ++n ) { // id, name, notified_serial, content
 441     ASSERT_ROW_COLUMNS( "info-all-master-query", d_result[n], 4 );
 442
 443     parts.clear();
 444     stringtok( parts, d_result[n][3] );
 445
 446     try {
 447       uint32_t serial = parts.size() > 2 ? pdns_stou(parts[2]) : 0;
 448       uint32_t notified_serial = pdns_stou( d_result[n][2] );
 449
 450       if( serial != notified_serial ) {
 451         di.id = pdns_stou( d_result[n][0] );
 452         di.zone = DNSName( d_result[n][1] );
 453         di.serial = serial;
 454         di.notified_serial = notified_serial;
 455
 456         updatedDomains->emplace_back(di);
 457       }
 458     } catch ( ... ) {
 459       continue;
 460     }
 461   }
 462 }
 463
 464 bool GSQLBackend::updateDNSSECOrderNameAndAuth(uint32_t domain_id, const DNSName& qname, const DNSName& ordername, bool auth, const uint16_t qtype)
 465 {
 466   if(!d_dnssecQueries)
 467     return false;
 468
 469   if (!ordername.empty()) {
 470     if (qtype == QType::ANY) {
 471       try {
 472         reconnectIfNeeded();
 473
 474         d_updateOrderNameAndAuthQuery_stmt->
 475           bind("ordername", ordername.labelReverse().toString(" ", false))->
 476           bind("auth", auth)->
 477           bind("domain_id", domain_id)->
 478           bind("qname", qname)->
 479           execute()->
 480           reset();
 481       }
 482       catch(SSqlException &e) {
 483         throw PDNSException("GSQLBackend unable to update ordername and auth for " + qname.toLogString() + " for domain_id "+itoa(domain_id)+", domain name '" + qname.toLogString() + "': "+e.txtReason());
 484       }
 485     } else {
 486       try {
 487         reconnectIfNeeded();
 488
 489         d_updateOrderNameAndAuthTypeQuery_stmt->
 490           bind("ordername", ordername.labelReverse().toString(" ", false))->
 491           bind("auth", auth)->
 492           bind("domain_id", domain_id)->
 493           bind("qname", qname)->
 494           bind("qtype", QType(qtype).getName())->
 495           execute()->
 496           reset();
 497       }
 498       catch(SSqlException &e) {
 499         throw PDNSException("GSQLBackend unable to update ordername and auth for " + qname.toLogString() + "|" + QType(qtype).getName() + " for domain_id "+itoa(domain_id)+": "+e.txtReason());
 500       }
 501     }
 502   } else {
 503     if (qtype == QType::ANY) {
 504       reconnectIfNeeded();
 505
 506       try {
 507         d_nullifyOrderNameAndUpdateAuthQuery_stmt->
 508           bind("auth", auth)->
 509           bind("domain_id", domain_id)->
 510           bind("qname", qname)->
 511           execute()->
 512           reset();
 513       }
 514       catch(SSqlException &e) {
 515         throw PDNSException("GSQLBackend unable to nullify ordername and update auth for " + qname.toLogString() + " for domain_id "+itoa(domain_id)+": "+e.txtReason());
 516       }
 517     } else {
 518       try {
 519         reconnectIfNeeded();
 520
 521         d_nullifyOrderNameAndUpdateAuthTypeQuery_stmt->
 522           bind("auth", auth)->
 523           bind("domain_id", domain_id)->
 524           bind("qname", qname)->
 525           bind("qtype", QType(qtype).getName())->
 526           execute()->
 527           reset();
 528       }
 529       catch(SSqlException &e) {
 530         throw PDNSException("GSQLBackend unable to nullify ordername and update auth for " + qname.toLogString() + "|" + QType(qtype).getName() + " for domain_id "+itoa(domain_id)+": "+e.txtReason());
 531       }
 532     }
 533   }
 534   return true;
 535 }
 536
 537 bool GSQLBackend::updateEmptyNonTerminals(uint32_t domain_id, set<DNSName>& insert, set<DNSName>& erase, bool remove)
 538 {
 539   if(remove) {
 540     try {
 541       reconnectIfNeeded();
 542
 543       d_RemoveEmptyNonTerminalsFromZoneQuery_stmt->
 544         bind("domain_id", domain_id)->
 545         execute()->
 546         reset();
 547     }
 548     catch (SSqlException &e) {
 549       throw PDNSException("GSQLBackend unable to delete empty non-terminal records from domain_id "+itoa(domain_id)+": "+e.txtReason());
 550       return false;
 551     }
 552   }
 553   else
 554   {
 555     for(const auto& qname: erase) {
 556       try {
 557         reconnectIfNeeded();
 558
 559         d_DeleteEmptyNonTerminalQuery_stmt->
 560           bind("domain_id", domain_id)->
 561           bind("qname", qname)->
 562           execute()->
 563           reset();
 564       }
 565       catch (SSqlException &e) {
 566         throw PDNSException("GSQLBackend unable to delete empty non-terminal rr '"+qname.toLogString()+"' from domain_id "+itoa(domain_id)+": "+e.txtReason());
 567         return false;
 568       }
 569     }
 570   }
 571
 572   for(const auto& qname: insert) {
 573     try {
 574       reconnectIfNeeded();
 575
 576       d_InsertEmptyNonTerminalOrderQuery_stmt->
 577         bind("domain_id", domain_id)->
 578         bind("qname", qname)->
 579         bindNull("ordername")->
 580         bind("auth", true)->
 581         execute()->
 582         reset();
 583     }
 584     catch (SSqlException &e) {
 585       throw PDNSException("GSQLBackend unable to insert empty non-terminal rr '"+qname.toLogString()+"' in domain_id "+itoa(domain_id)+": "+e.txtReason());
 586       return false;
 587     }
 588   }
 589
 590   return true;
 591 }
 592
 593 bool GSQLBackend::doesDNSSEC()
 594 {
 595     return d_dnssecQueries;
 596 }
 597
 598 bool GSQLBackend::getBeforeAndAfterNamesAbsolute(uint32_t id, const DNSName& qname, DNSName& unhashed, DNSName& before, DNSName& after)
 599 {
 600   if(!d_dnssecQueries)
 601     return false;
 602   after.clear();
 603
 604   SSqlStatement::row_t row;
 605   try {
 606     reconnectIfNeeded();
 607
 608     d_afterOrderQuery_stmt->
 609       bind("ordername", qname.labelReverse().toString(" ", false))->
 610       bind("domain_id", id)->
 611       execute();
 612     while(d_afterOrderQuery_stmt->hasNextRow()) {
 613       d_afterOrderQuery_stmt->nextRow(row);
 614       ASSERT_ROW_COLUMNS("get-order-after-query", row, 1);
 615       if(! row[0].empty()) { // Hack because NULL values are passed on as empty strings
 616         after=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 617       }
 618     }
 619     d_afterOrderQuery_stmt->reset();
 620   }
 621   catch(SSqlException &e) {
 622     throw PDNSException("GSQLBackend unable to find before/after (after) for domain_id "+itoa(id)+" and qname '"+ qname.toLogString() +"': "+e.txtReason());
 623   }
 624
 625   if(after.empty()) {
 626     try {
 627       reconnectIfNeeded();
 628
 629       d_firstOrderQuery_stmt->
 630         bind("domain_id", id)->
 631         execute();
 632       while(d_firstOrderQuery_stmt->hasNextRow()) {
 633         d_firstOrderQuery_stmt->nextRow(row);
 634         ASSERT_ROW_COLUMNS("get-order-first-query", row, 1);
 635         after=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 636       }
 637       d_firstOrderQuery_stmt->reset();
 638     }
 639     catch(SSqlException &e) {
 640       throw PDNSException("GSQLBackend unable to find before/after (first) for domain_id "+itoa(id)+" and qname '"+ qname.toLogString() + "': "+e.txtReason());
 641     }
 642   }
 643
 644   if (before.empty()) {
 645     unhashed.clear();
 646
 647     try {
 648       reconnectIfNeeded();
 649
 650       d_beforeOrderQuery_stmt->
 651         bind("ordername", qname.labelReverse().toString(" ", false))->
 652         bind("domain_id", id)->
 653         execute();
 654       while(d_beforeOrderQuery_stmt->hasNextRow()) {
 655         d_beforeOrderQuery_stmt->nextRow(row);
 656         ASSERT_ROW_COLUMNS("get-order-before-query", row, 2);
 657         before=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 658         try {
 659           unhashed=DNSName(row[1]);
 660         } catch (...) {
 661           continue;
 662         }
 663       }
 664       d_beforeOrderQuery_stmt->reset();
 665     }
 666     catch(SSqlException &e) {
 667       throw PDNSException("GSQLBackend unable to find before/after (before) for domain_id "+itoa(id)+" and qname '"+ qname.toLogString() + ": "+e.txtReason());
 668     }
 669
 670     if(! unhashed.empty())
 671     {
 672       // cerr<<"unhashed="<<unhashed<<",before="<<before<<", after="<<after<<endl;
 673       return true;
 674     }
 675
 676     try {
 677       reconnectIfNeeded();
 678
 679       d_lastOrderQuery_stmt->
 680         bind("domain_id", id)->
 681         execute();
 682       while(d_lastOrderQuery_stmt->hasNextRow()) {
 683         d_lastOrderQuery_stmt->nextRow(row);
 684         ASSERT_ROW_COLUMNS("get-order-last-query", row, 2);
 685         before=DNSName(boost::replace_all_copy(row[0]," ",".")).labelReverse();
 686         try {
 687           unhashed=DNSName(row[1]);
 688         } catch (...) {
 689           continue;
 690         }
 691       }
 692       d_lastOrderQuery_stmt->reset();
 693     }
 694     catch(SSqlException &e) {
 695       throw PDNSException("GSQLBackend unable to find before/after (last) for domain_id "+itoa(id)+" and qname '"+ qname.toLogString() + ": "+e.txtReason());
 696     }
 697   } else {
 698     before=qname;
 699   }
 700
 701   return true;
 702 }
 703
 704 bool GSQLBackend::addDomainKey(const DNSName& name, const KeyData& key, int64_t& id)
 705 {
 706   if(!d_dnssecQueries)
 707     return false;
 708
 709   try {
 710     reconnectIfNeeded();
 711
 712     d_AddDomainKeyQuery_stmt->
 713       bind("flags", key.flags)->
 714       bind("active", key.active)->
 715       bind("content", key.content)->
 716       bind("domain", name)->
 717       execute()->
 718       reset();
 719   }
 720   catch (SSqlException &e) {
 721     throw PDNSException("GSQLBackend unable to store key for domain '"+ name.toLogString() + "': "+e.txtReason());
 722   }
 723
 724   try {
 725     reconnectIfNeeded();
 726
 727     d_GetLastInsertedKeyIdQuery_stmt->execute();
 728     if (!d_GetLastInsertedKeyIdQuery_stmt->hasNextRow()) {
 729       id = -2;
 730       return true;
 731     }
 732     SSqlStatement::row_t row;
 733     d_GetLastInsertedKeyIdQuery_stmt->nextRow(row);
 734     ASSERT_ROW_COLUMNS("get-last-inserted-key-id-query", row, 1);
 735     id = std::stoi(row[0]);
 736     d_GetLastInsertedKeyIdQuery_stmt->reset();
 737     return true;
 738   }
 739   catch (SSqlException &e) {
 740     id = -2;
 741     return true;
 742   }
 743
 744   return false;
 745 }
 746
 747 bool GSQLBackend::activateDomainKey(const DNSName& name, unsigned int id)
 748 {
 749   if(!d_dnssecQueries)
 750     return false;
 751
 752   try {
 753     reconnectIfNeeded();
 754
 755     d_ActivateDomainKeyQuery_stmt->
 756       bind("domain", name)->
 757       bind("key_id", id)->
 758       execute()->
 759       reset();
 760   }
 761   catch (SSqlException &e) {
 762     throw PDNSException("GSQLBackend unable to activate key with id "+ std::to_string(id) + " for domain '" + name.toLogString() + "': "+e.txtReason());
 763   }
 764   return true;
 765 }
 766
 767 bool GSQLBackend::deactivateDomainKey(const DNSName& name, unsigned int id)
 768 {
 769   if(!d_dnssecQueries)
 770     return false;
 771
 772   try {
 773     reconnectIfNeeded();
 774
 775     d_DeactivateDomainKeyQuery_stmt->
 776       bind("domain", name)->
 777       bind("key_id", id)->
 778       execute()->
 779       reset();
 780   }
 781   catch (SSqlException &e) {
 782     throw PDNSException("GSQLBackend unable to deactivate key with id "+ std::to_string(id) + " for domain '" + name.toLogString() + "': "+e.txtReason());
 783   }
 784   return true;
 785 }
 786
 787 bool GSQLBackend::removeDomainKey(const DNSName& name, unsigned int id)
 788 {
 789   if(!d_dnssecQueries)
 790     return false;
 791
 792   try {
 793     reconnectIfNeeded();
 794
 795     d_RemoveDomainKeyQuery_stmt->
 796       bind("domain", name)->
 797       bind("key_id", id)->
 798       execute()->
 799       reset();
 800   }
 801   catch (SSqlException &e) {
 802     throw PDNSException("GSQLBackend unable to remove key with id "+ std::to_string(id) + " for domain '" + name.toLogString() + "': "+e.txtReason());
 803   }
 804   return true;
 805 }
 806
 807 bool GSQLBackend::getTSIGKey(const DNSName& name, DNSName* algorithm, string* content)
 808 {
 809   try {
 810     reconnectIfNeeded();
 811
 812     d_getTSIGKeyQuery_stmt->
 813       bind("key_name", name)->
 814       execute();
 815
 816     SSqlStatement::row_t row;
 817
 818     content->clear();
 819     while(d_getTSIGKeyQuery_stmt->hasNextRow()) {
 820       d_getTSIGKeyQuery_stmt->nextRow(row);
 821       ASSERT_ROW_COLUMNS("get-tsig-key-query", row, 2);
 822       try{
 823         if(algorithm->empty() || *algorithm==DNSName(row[0])) {
 824           *algorithm = DNSName(row[0]);
 825           *content = row[1];
 826         }
 827       } catch (...) {}
 828     }
 829
 830     d_getTSIGKeyQuery_stmt->reset();
 831   }
 832   catch (SSqlException &e) {
 833     throw PDNSException("GSQLBackend unable to retrieve TSIG key with name '" + name.toLogString() + "': "+e.txtReason());
 834   }
 835
 836   return !content->empty();
 837 }
 838
 839 bool GSQLBackend::setTSIGKey(const DNSName& name, const DNSName& algorithm, const string& content)
 840 {
 841   try {
 842     reconnectIfNeeded();
 843
 844     d_setTSIGKeyQuery_stmt->
 845       bind("key_name", name)->
 846       bind("algorithm", algorithm)->
 847       bind("content", content)->
 848       execute()->
 849       reset();
 850   }
 851   catch (SSqlException &e) {
 852     throw PDNSException("GSQLBackend unable to store TSIG key with name '" + name.toLogString() + "' and algorithm '" + algorithm.toString() + "': "+e.txtReason());
 853   }
 854   return true;
 855 }
 856
 857 bool GSQLBackend::deleteTSIGKey(const DNSName& name)
 858 {
 859   try {
 860     reconnectIfNeeded();
 861
 862     d_deleteTSIGKeyQuery_stmt->
 863       bind("key_name", name)->
 864       execute()->
 865       reset();
 866   }
 867   catch (SSqlException &e) {
 868     throw PDNSException("GSQLBackend unable to delete TSIG key with name '" + name.toLogString() + "': "+e.txtReason());
 869   }
 870   return true;
 871 }
 872
 873 bool GSQLBackend::getTSIGKeys(std::vector< struct TSIGKey > &keys)
 874 {
 875   try {
 876     reconnectIfNeeded();
 877
 878     d_getTSIGKeysQuery_stmt->
 879       execute();
 880
 881     SSqlStatement::row_t row;
 882
 883     while(d_getTSIGKeysQuery_stmt->hasNextRow()) {
 884       d_getTSIGKeysQuery_stmt->nextRow(row);
 885       ASSERT_ROW_COLUMNS("get-tsig-keys-query", row, 3);
 886       struct TSIGKey key;
 887       try {
 888         key.name = DNSName(row[0]);
 889         key.algorithm = DNSName(row[1]);
 890       } catch (...) {
 891         continue;
 892       }
 893       key.key = row[2];
 894       keys.push_back(key);
 895     }
 896
 897     d_getTSIGKeysQuery_stmt->reset();
 898   }
 899   catch (SSqlException &e) {
 900     throw PDNSException("GSQLBackend unable to retrieve TSIG keys: "+e.txtReason());
 901   }
 902
 903   return keys.empty();
 904 }
 905
 906 bool GSQLBackend::getDomainKeys(const DNSName& name, std::vector<KeyData>& keys)
 907 {
 908   if(!d_dnssecQueries)
 909     return false;
 910
 911   try {
 912     reconnectIfNeeded();
 913
 914     d_ListDomainKeysQuery_stmt->
 915       bind("domain", name)->
 916       execute();
 917
 918     SSqlStatement::row_t row;
 919     KeyData kd;
 920     while(d_ListDomainKeysQuery_stmt->hasNextRow()) {
 921       d_ListDomainKeysQuery_stmt->nextRow(row);
 922       ASSERT_ROW_COLUMNS("list-domain-keys-query", row, 4);
 923       //~ for(const auto& val: row) {
 924         //~ cerr<<"'"<<val<<"'"<<endl;
 925       //~ }
 926       kd.id = pdns_stou(row[0]);
 927       kd.flags = pdns_stou(row[1]);
 928       kd.active = row[2] == "1";
 929       kd.content = row[3];
 930       keys.push_back(kd);
 931     }
 932
 933     d_ListDomainKeysQuery_stmt->reset();
 934   }
 935   catch (SSqlException &e) {
 936     throw PDNSException("GSQLBackend unable to list keys: "+e.txtReason());
 937   }
 938
 939   return true;
 940 }
 941
 942 void GSQLBackend::alsoNotifies(const DNSName &domain, set<string> *ips)
 943 {
 944   vector<string> meta;
 945   getDomainMetadata(domain, "ALSO-NOTIFY", meta);
 946   for(const auto& str: meta) {
 947     ips->insert(str);
 948   }
 949 }
 950
 951 bool GSQLBackend::getAllDomainMetadata(const DNSName& name, std::map<std::string, std::vector<std::string> >& meta)
 952 {
 953   try {
 954     reconnectIfNeeded();
 955
 956     d_GetAllDomainMetadataQuery_stmt->
 957       bind("domain", name)->
 958       execute();
 959
 960     SSqlStatement::row_t row;
 961
 962     while(d_GetAllDomainMetadataQuery_stmt->hasNextRow()) {
 963       d_GetAllDomainMetadataQuery_stmt->nextRow(row);
 964       ASSERT_ROW_COLUMNS("get-all-domain-metadata-query", row, 2);
 965
 966       if (d_dnssecQueries || !isDnssecDomainMetadata(row[0]))
 967         meta[row[0]].push_back(row[1]);
 968     }
 969
 970     d_GetAllDomainMetadataQuery_stmt->reset();
 971   }
 972   catch (SSqlException &e) {
 973     throw PDNSException("GSQLBackend unable to list metadata for domain '" + name.toLogString() + "': "+e.txtReason());
 974   }
 975
 976   return true;
 977 }
 978
 979
 980 bool GSQLBackend::getDomainMetadata(const DNSName& name, const std::string& kind, std::vector<std::string>& meta)
 981 {
 982   if(!d_dnssecQueries && isDnssecDomainMetadata(kind))
 983     return false;
 984
 985   try {
 986     reconnectIfNeeded();
 987
 988     d_GetDomainMetadataQuery_stmt->
 989       bind("domain", name)->
 990       bind("kind", kind)->
 991       execute();
 992
 993     SSqlStatement::row_t row;
 994
 995     while(d_GetDomainMetadataQuery_stmt->hasNextRow()) {
 996       d_GetDomainMetadataQuery_stmt->nextRow(row);
 997       ASSERT_ROW_COLUMNS("get-domain-metadata-query", row, 1);
 998       meta.push_back(row[0]);
 999     }
1000
1001     d_GetDomainMetadataQuery_stmt->reset();
1002   }
1003   catch (SSqlException &e) {
1004     throw PDNSException("GSQLBackend unable to get metadata kind '" + kind + "' for domain '" + name.toLogString() + "': "+e.txtReason());
1005   }
1006
1007   return true;
1008 }
1009
1010 bool GSQLBackend::setDomainMetadata(const DNSName& name, const std::string& kind, const std::vector<std::string>& meta)
1011 {
1012   if(!d_dnssecQueries && isDnssecDomainMetadata(kind))
1013     return false;
1014
1015   try {
1016     reconnectIfNeeded();
1017
1018     d_ClearDomainMetadataQuery_stmt->
1019       bind("domain", name)->
1020       bind("kind", kind)->
1021       execute()->
1022       reset();
1023     if(!meta.empty()) {
1024       for(const auto& value: meta) {
1025          d_SetDomainMetadataQuery_stmt->
1026            bind("kind", kind)->
1027            bind("content", value)->
1028            bind("domain", name)->
1029            execute()->
1030            reset();
1031       }
1032     }
1033   }
1034   catch (SSqlException &e) {
1035     throw PDNSException("GSQLBackend unable to set metadata kind '" + kind + "' for domain '" + name.toLogString() + "': "+e.txtReason());
1036   }
1037
1038   return true;
1039 }
1040
1041 void GSQLBackend::lookup(const QType &qtype,const DNSName &qname, DNSPacket *pkt_p, int domain_id)
1042 {
1043   try {
1044     reconnectIfNeeded();
1045
1046     if(qtype.getCode()!=QType::ANY) {
1047       if(domain_id < 0) {
1048         d_query_name = "basic-query";
1049         d_query_stmt = &d_NoIdQuery_stmt;
1050         (*d_query_stmt)->
1051           bind("qtype", qtype.getName())->
1052           bind("qname", qname);
1053       } else {
1054         d_query_name = "id-query";
1055         d_query_stmt = &d_IdQuery_stmt;
1056         (*d_query_stmt)->
1057           bind("qtype", qtype.getName())->
1058           bind("qname", qname)->
1059           bind("domain_id", domain_id);
1060       }
1061     } else {
1062       // qtype==ANY
1063       if(domain_id < 0) {
1064         d_query_name = "any-query";
1065         d_query_stmt = &d_ANYNoIdQuery_stmt;
1066         (*d_query_stmt)->
1067           bind("qname", qname);
1068       } else {
1069         d_query_name = "any-id-query";
1070         d_query_stmt = &d_ANYIdQuery_stmt;
1071         (*d_query_stmt)->
1072           bind("qname", qname)->
1073           bind("domain_id", domain_id);
1074       }
1075     }
1076
1077     (*d_query_stmt)->
1078       execute();
1079   }
1080   catch(SSqlException &e) {
1081     throw PDNSException("GSQLBackend unable to lookup '" + qname.toLogString() + "|" + qtype.getName() + "':"+e.txtReason());
1082   }
1083
1084   d_qname=qname;
1085 }
1086
1087 bool GSQLBackend::list(const DNSName &target, int domain_id, bool include_disabled)
1088 {
1089   DLOG(g_log<<"GSQLBackend constructing handle for list of domain id '"<<domain_id<<"'"<<endl);
1090
1091   try {
1092     reconnectIfNeeded();
1093
1094     d_query_name = "list-query";
1095     d_query_stmt = &d_listQuery_stmt;
1096     (*d_query_stmt)->
1097       bind("include_disabled", (int)include_disabled)->
1098       bind("domain_id", domain_id)->
1099       execute();
1100   }
1101   catch(SSqlException &e) {
1102     throw PDNSException("GSQLBackend unable to list domain '" + target.toLogString() + "': "+e.txtReason());
1103   }
1104
1105   d_qname.clear();
1106   return true;
1107 }
1108
1109 bool GSQLBackend::listSubZone(const DNSName &zone, int domain_id) {
1110
1111   string wildzone = "%." + zone.makeLowerCase().toStringNoDot();
1112
1113   try {
1114     reconnectIfNeeded();
1115
1116     d_query_name = "list-subzone-query";
1117     d_query_stmt = &d_listSubZoneQuery_stmt;
1118     (*d_query_stmt)->
1119       bind("zone", zone)->
1120       bind("wildzone", wildzone)->
1121       bind("domain_id", domain_id)->
1122       execute();
1123   }
1124   catch(SSqlException &e) {
1125     throw PDNSException("GSQLBackend unable to list SubZones for domain '" + zone.toLogString() + "': "+e.txtReason());
1126   }
1127   d_qname.clear();
1128   return true;
1129 }
1130
1131 bool GSQLBackend::get(DNSResourceRecord &r)
1132 {
1133   // g_log << "GSQLBackend get() was called for "<<qtype.getName() << " record: ";
1134   SSqlStatement::row_t row;
1135
1136 skiprow:
1137   if((*d_query_stmt)->hasNextRow()) {
1138     try {
1139       (*d_query_stmt)->nextRow(row);
1140       ASSERT_ROW_COLUMNS(d_query_name, row, 8);
1141     } catch (SSqlException &e) {
1142       throw PDNSException("GSQLBackend get: "+e.txtReason());
1143     }
1144     try {
1145       extractRecord(row, r);
1146     } catch (...) {
1147       goto skiprow;
1148     }
1149     return true;
1150   }
1151
1152   try {
1153     (*d_query_stmt)->reset();
1154   } catch (SSqlException &e) {
1155       throw PDNSException("GSQLBackend get: "+e.txtReason());
1156   }
1157   d_query_stmt = NULL;
1158   return false;
1159 }
1160
1161 bool GSQLBackend::superMasterBackend(const string &ip, const DNSName &domain, const vector<DNSResourceRecord>&nsset, string *nameserver, string *account, DNSBackend **ddb)
1162 {
1163   // check if we know the ip/ns couple in the database
1164   for(vector<DNSResourceRecord>::const_iterator i=nsset.begin();i!=nsset.end();++i) {
1165     try {
1166       reconnectIfNeeded();
1167
1168       d_SuperMasterInfoQuery_stmt->
1169         bind("ip", ip)->
1170         bind("nameserver", i->content)->
1171         execute()->
1172         getResult(d_result)->
1173         reset();
1174     }
1175     catch (SSqlException &e) {
1176       throw PDNSException("GSQLBackend unable to search for a supermaster with IP " + ip + " and nameserver name '" + i->content + "' for domain '" + domain.toLogString() + "': "+e.txtReason());
1177     }
1178     if(!d_result.empty()) {
1179       ASSERT_ROW_COLUMNS("supermaster-query", d_result[0], 1);
1180       *nameserver=i->content;
1181       *account=d_result[0][0];
1182       *ddb=this;
1183       return true;
1184     }
1185   }
1186   return false;
1187 }
1188
1189 bool GSQLBackend::createDomain(const DNSName &domain, const string &type, const string &masters, const string &account)
1190 {
1191   try {
1192     reconnectIfNeeded();
1193
1194     d_InsertZoneQuery_stmt->
1195       bind("type", type)->
1196       bind("domain", domain)->
1197       bind("masters", masters)->
1198       bind("account", account)->
1199       execute()->
1200       reset();
1201   }
1202   catch(SSqlException &e) {
1203     throw PDNSException("Database error trying to insert new domain '"+domain.toLogString()+"': "+ e.txtReason());
1204   }
1205   return true;
1206 }
1207
1208 bool GSQLBackend::createSlaveDomain(const string &ip, const DNSName &domain, const string &nameserver, const string &account)
1209 {
1210   string name;
1211   string masters(ip);
1212   try {
1213     if (!nameserver.empty()) {
1214       // figure out all IP addresses for the master
1215       reconnectIfNeeded();
1216
1217       d_GetSuperMasterIPs_stmt->
1218         bind("nameserver", nameserver)->
1219         bind("account", account)->
1220         execute()->
1221         getResult(d_result)->
1222         reset();
1223       if (!d_result.empty()) {
1224         // collect all IP addresses
1225         vector<string> tmp;
1226         for(const auto& row: d_result) {
1227           if (account == row[1])
1228             tmp.push_back(row[0]);
1229         }
1230         // set them as domain's masters, comma separated
1231         masters = boost::join(tmp, ", ");
1232       }
1233     }
1234     createDomain(domain, "SLAVE", masters, account);
1235   }
1236   catch(SSqlException &e) {
1237     throw PDNSException("Database error trying to insert new slave domain '"+domain.toLogString()+"': "+ e.txtReason());
1238   }
1239   return true;
1240 }
1241
1242 bool GSQLBackend::deleteDomain(const DNSName &domain)
1243 {
1244   DomainInfo di;
1245   if (!getDomainInfo(domain, di)) {
1246     return false;
1247   }
1248
1249   try {
1250     reconnectIfNeeded();
1251
1252     d_DeleteZoneQuery_stmt->
1253       bind("domain_id", di.id)->
1254       execute()->
1255       reset();
1256     d_ClearDomainAllMetadataQuery_stmt->
1257       bind("domain", domain)->
1258       execute()->
1259       reset();
1260     d_ClearDomainAllKeysQuery_stmt->
1261       bind("domain", domain)->
1262       execute()->
1263       reset();
1264     d_DeleteCommentsQuery_stmt->
1265       bind("domain_id", di.id)->
1266       execute()->
1267       reset();
1268     d_DeleteDomainQuery_stmt->
1269       bind("domain", domain)->
1270       execute()->
1271       reset();
1272   }
1273   catch(SSqlException &e) {
1274     throw PDNSException("Database error trying to delete domain '"+domain.toLogString()+"': "+ e.txtReason());
1275   }
1276   return true;
1277 }
1278
1279 void GSQLBackend::getAllDomains(vector<DomainInfo> *domains, bool include_disabled)
1280 {
1281   DLOG(g_log<<"GSQLBackend retrieving all domains."<<endl);
1282
1283   try {
1284     reconnectIfNeeded();
1285
1286     d_getAllDomainsQuery_stmt->
1287       bind("include_disabled", (int)include_disabled)->
1288       execute();
1289
1290     SSqlStatement::row_t row;
1291     while (d_getAllDomainsQuery_stmt->hasNextRow()) {
1292       d_getAllDomainsQuery_stmt->nextRow(row);
1293       ASSERT_ROW_COLUMNS("get-all-domains-query", row, 8);
1294       DomainInfo di;
1295       di.id = pdns_stou(row[0]);
1296       try {
1297         di.zone = DNSName(row[1]);
1298       } catch (...) {
1299         continue;
1300       }
1301
1302       if (pdns_iequals(row[3], "MASTER")) {
1303         di.kind = DomainInfo::Master;
1304       } else if (pdns_iequals(row[3], "SLAVE")) {
1305         di.kind = DomainInfo::Slave;
1306       } else if (pdns_iequals(row[3], "NATIVE")) {
1307         di.kind = DomainInfo::Native;
1308       } else {
1309         g_log<<Logger::Warning<<"Could not parse domain kind '"<<row[3]<<"' as one of 'MASTER', 'SLAVE' or 'NATIVE'. Setting zone kind to 'NATIVE'"<<endl;
1310         di.kind = DomainInfo::Native;
1311       }
1312
1313       if (!row[4].empty()) {
1314         vector<string> masters;
1315         stringtok(masters, row[4], " ,\t");
1316         for(const auto& m : masters) {
1317           try {
1318             di.masters.emplace_back(m, 53);
1319           } catch(const PDNSException &e) {
1320             g_log<<Logger::Warning<<"Could not parse master address ("<<m<<") for zone '"<<di.zone<<"': "<<e.reason;
1321           }
1322         }
1323       }
1324
1325       SOAData sd;
1326       fillSOAData(row[2], sd);
1327       di.serial = sd.serial;
1328       try {
1329         di.notified_serial = pdns_stou(row[5]);
1330         di.last_check = pdns_stou(row[6]);
1331       } catch(...) {
1332         continue;
1333       }
1334       di.account = row[7];
1335
1336       di.backend = this;
1337
1338       domains->push_back(di);
1339     }
1340     d_getAllDomainsQuery_stmt->reset();
1341   }
1342   catch (SSqlException &e) {
1343     throw PDNSException("Database error trying to retrieve all domains:" + e.txtReason());
1344   }
1345 }
1346
1347 bool GSQLBackend::replaceRRSet(uint32_t domain_id, const DNSName& qname, const QType& qt, const vector<DNSResourceRecord>& rrset)
1348 {
1349   try {
1350     reconnectIfNeeded();
1351
1352     if (qt != QType::ANY) {
1353       d_DeleteRRSetQuery_stmt->
1354         bind("domain_id", domain_id)->
1355         bind("qname", qname)->
1356         bind("qtype", qt.getName())->
1357         execute()->
1358         reset();
1359     } else {
1360       d_DeleteNamesQuery_stmt->
1361         bind("domain_id", domain_id)->
1362         bind("qname", qname)->
1363         execute()->
1364         reset();
1365     }
1366   }
1367   catch (SSqlException &e) {
1368     throw PDNSException("GSQLBackend unable to delete RRSet " + qname.toLogString() + "|" + qt.getName() + ": "+e.txtReason());
1369   }
1370
1371   if (rrset.empty()) {
1372     try {
1373       reconnectIfNeeded();
1374
1375       d_DeleteCommentRRsetQuery_stmt->
1376         bind("domain_id", domain_id)->
1377         bind("qname", qname)->
1378         bind("qtype", qt.getName())->
1379         execute()->
1380         reset();
1381     }
1382     catch (SSqlException &e) {
1383       throw PDNSException("GSQLBackend unable to delete comment for RRSet " + qname.toLogString() + "|" + qt.getName() + ": "+e.txtReason());
1384     }
1385   }
1386   for(const auto& rr: rrset) {
1387     feedRecord(rr, DNSName());
1388   }
1389
1390   return true;
1391 }
1392
1393 bool GSQLBackend::feedRecord(const DNSResourceRecord &r, const DNSName &ordername, bool ordernameIsNSEC3)
1394 {
1395   int prio=0;
1396   string content(r.content);
1397   if (r.qtype == QType::MX || r.qtype == QType::SRV) {
1398     string::size_type pos = content.find_first_not_of("0123456789");
1399     if (pos != string::npos) {
1400       prio=pdns_stou(content.substr(0,pos));
1401       boost::erase_head(content, pos);
1402     }
1403     trim_left(content);
1404   }
1405
1406   try {
1407     reconnectIfNeeded();
1408
1409     d_InsertRecordQuery_stmt->
1410       bind("content",content)->
1411       bind("ttl",r.ttl)->
1412       bind("priority",prio)->
1413       bind("qtype",r.qtype.getName())->
1414       bind("domain_id",r.domain_id)->
1415       bind("disabled",r.disabled)->
1416       bind("qname",r.qname);
1417
1418     if (!ordername.empty())
1419       d_InsertRecordQuery_stmt->bind("ordername", ordername.labelReverse().makeLowerCase().toString(" ", false));
1420     else
1421       d_InsertRecordQuery_stmt->bindNull("ordername");
1422
1423     if (d_dnssecQueries)
1424       d_InsertRecordQuery_stmt->bind("auth", r.auth);
1425     else
1426       d_InsertRecordQuery_stmt->bind("auth", true);
1427
1428     d_InsertRecordQuery_stmt->
1429       execute()->
1430       reset();
1431   }
1432   catch (SSqlException &e) {
1433     throw PDNSException("GSQLBackend unable to feed record " + r.qname.toLogString() + "|" + r.qtype.getName() + ": "+e.txtReason());
1434   }
1435   return true; // XXX FIXME this API should not return 'true' I think -ahu
1436 }
1437
1438 bool GSQLBackend::feedEnts(int domain_id, map<DNSName,bool>& nonterm)
1439 {
1440   for(const auto& nt: nonterm) {
1441     try {
1442       reconnectIfNeeded();
1443
1444       d_InsertEmptyNonTerminalOrderQuery_stmt->
1445         bind("domain_id",domain_id)->
1446         bind("qname", nt.first)->
1447         bindNull("ordername")->
1448         bind("auth",(nt.second || !d_dnssecQueries))->
1449         execute()->
1450         reset();
1451     }
1452     catch (SSqlException &e) {
1453       throw PDNSException("GSQLBackend unable to feed empty non-terminal with name '" + nt.first.toLogString() + "': "+e.txtReason());
1454     }
1455   }
1456   return true;
1457 }
1458
1459 bool GSQLBackend::feedEnts3(int domain_id, const DNSName &domain, map<DNSName,bool> &nonterm, const NSEC3PARAMRecordContent& ns3prc, bool narrow)
1460 {
1461   if(!d_dnssecQueries)
1462       return false;
1463
1464   string ordername;
1465
1466   for(const auto& nt: nonterm) {
1467     try {
1468       reconnectIfNeeded();
1469
1470       d_InsertEmptyNonTerminalOrderQuery_stmt->
1471         bind("domain_id",domain_id)->
1472         bind("qname", nt.first);
1473       if (narrow || !nt.second) {
1474         d_InsertEmptyNonTerminalOrderQuery_stmt->
1475           bindNull("ordername");
1476       } else {
1477         ordername=toBase32Hex(hashQNameWithSalt(ns3prc, nt.first));
1478         d_InsertEmptyNonTerminalOrderQuery_stmt->
1479           bind("ordername", ordername);
1480       }
1481       d_InsertEmptyNonTerminalOrderQuery_stmt->
1482         bind("auth",nt.second)->
1483         execute()->
1484         reset();
1485     }
1486     catch (SSqlException &e) {
1487       throw PDNSException("GSQLBackend unable to feed empty non-terminal with name '" + nt.first.toLogString() + "' (hashed name '"+ toBase32Hex(hashQNameWithSalt(ns3prc, nt.first)) + "') : "+e.txtReason());
1488     }
1489   }
1490   return true;
1491 }
1492
1493 bool GSQLBackend::startTransaction(const DNSName &domain, int domain_id)
1494 {
1495   try {
1496     reconnectIfNeeded();
1497
1498     d_db->startTransaction();
1499     d_inTransaction = true;
1500     if(domain_id >= 0) {
1501       d_DeleteZoneQuery_stmt->
1502         bind("domain_id", domain_id)->
1503         execute()->
1504         reset();
1505     }
1506   }
1507   catch (SSqlException &e) {
1508     d_inTransaction = false;
1509     throw PDNSException("Database failed to start transaction for domain '" + domain.toLogString() + "': "+e.txtReason());
1510   }
1511
1512   return true;
1513 }
1514
1515 bool GSQLBackend::commitTransaction()
1516 {
1517   try {
1518     d_db->commit();
1519     d_inTransaction = false;
1520   }
1521   catch (SSqlException &e) {
1522     d_inTransaction = false;
1523     throw PDNSException("Database failed to commit transaction: "+e.txtReason());
1524   }
1525   return true;
1526 }
1527
1528 bool GSQLBackend::abortTransaction()
1529 {
1530   try {
1531     d_db->rollback();
1532     d_inTransaction = false;
1533   }
1534   catch(SSqlException &e) {
1535     d_inTransaction = false;
1536     throw PDNSException("Database failed to abort transaction: "+string(e.txtReason()));
1537   }
1538   return true;
1539 }
1540
1541 bool GSQLBackend::listComments(const uint32_t domain_id)
1542 {
1543   try {
1544     reconnectIfNeeded();
1545
1546     d_query_name = "list-comments-query";
1547     d_query_stmt = &d_ListCommentsQuery_stmt;
1548     (*d_query_stmt)->
1549       bind("domain_id", domain_id)->
1550       execute();
1551   }
1552   catch(SSqlException &e) {
1553     throw PDNSException("GSQLBackend unable to list comments for domain id " + std::to_string(domain_id) + ": "+e.txtReason());
1554   }
1555
1556   return true;
1557 }
1558
1559 bool GSQLBackend::getComment(Comment& comment)
1560 {
1561   SSqlStatement::row_t row;
1562
1563   for(;;) {
1564     if (!(*d_query_stmt)->hasNextRow()) {
1565       try {
1566         (*d_query_stmt)->reset();
1567       } catch(SSqlException &e) {
1568         throw PDNSException("GSQLBackend comment get: "+e.txtReason());
1569       }
1570       d_query_stmt = NULL;
1571       return false;
1572     }
1573
1574     try {
1575       (*d_query_stmt)->nextRow(row);
1576       ASSERT_ROW_COLUMNS(d_query_name, row, 6);
1577     } catch(SSqlException &e) {
1578       throw PDNSException("GSQLBackend comment get: "+e.txtReason());
1579     }
1580     try {
1581       extractComment(row, comment);
1582     } catch (...) {
1583       continue;
1584     }
1585     return true;
1586   }
1587 }
1588
1589 void GSQLBackend::feedComment(const Comment& comment)
1590 {
1591   try {
1592     reconnectIfNeeded();
1593
1594     d_InsertCommentQuery_stmt->
1595       bind("domain_id",comment.domain_id)->
1596       bind("qname",comment.qname)->
1597       bind("qtype",comment.qtype.getName())->
1598       bind("modified_at",comment.modified_at)->
1599       bind("account",comment.account)->
1600       bind("content",comment.content)->
1601       execute()->
1602       reset();
1603   }
1604   catch (SSqlException &e) {
1605     throw PDNSException("GSQLBackend unable to feed comment for RRSet '" + comment.qname.toLogString() + "|" + comment.qtype.getName() + "': "+e.txtReason());
1606   }
1607 }
1608
1609 bool GSQLBackend::replaceComments(const uint32_t domain_id, const DNSName& qname, const QType& qt, const vector<Comment>& comments)
1610 {
1611   try {
1612     reconnectIfNeeded();
1613
1614     d_DeleteCommentRRsetQuery_stmt->
1615       bind("domain_id",domain_id)->
1616       bind("qname", qname)->
1617       bind("qtype",qt.getName())->
1618       execute()->
1619       reset();
1620   }
1621   catch (SSqlException &e) {
1622     throw PDNSException("GSQLBackend unable to delete comment for RRSet '" + qname.toLogString() + "|" + qt.getName() + "': "+e.txtReason());
1623   }
1624
1625   for(const auto& comment: comments) {
1626     feedComment(comment);
1627   }
1628
1629   return true;
1630 }
1631
1632 string GSQLBackend::directBackendCmd(const string &query)
1633 {
1634  try {
1635    ostringstream out;
1636
1637    auto stmt = d_db->prepare(query,0);
1638
1639    reconnectIfNeeded();
1640
1641    stmt->execute();
1642
1643    SSqlStatement::row_t row;
1644
1645    while(stmt->hasNextRow()) {
1646      stmt->nextRow(row);
1647      for(const auto& col: row)
1648        out<<"\'"<<col<<"\'\t";
1649      out<<endl;
1650    }
1651
1652    return out.str();
1653  }
1654  catch (SSqlException &e) {
1655    throw PDNSException("GSQLBackend unable to execute direct command query '" + query + "': "+e.txtReason());
1656  }
1657 }
1658
1659 string GSQLBackend::pattern2SQLPattern(const string &pattern)
1660 {
1661   string escaped_pattern = boost::replace_all_copy(pattern,"\\","\\\\");
1662   boost::replace_all(escaped_pattern,"_","\\_");
1663   boost::replace_all(escaped_pattern,"%","\\%");
1664   boost::replace_all(escaped_pattern,"*","%");
1665   boost::replace_all(escaped_pattern,"?","_");
1666   return escaped_pattern;
1667 }
1668
1669 bool GSQLBackend::searchRecords(const string &pattern, int maxResults, vector<DNSResourceRecord>& result)
1670 {
1671   d_qname.clear();
1672   string escaped_pattern = pattern2SQLPattern(pattern);
1673   try {
1674     reconnectIfNeeded();
1675
1676     d_SearchRecordsQuery_stmt->
1677       bind("value", escaped_pattern)->
1678       bind("value2", escaped_pattern)->
1679       bind("limit", maxResults)->
1680       execute();
1681
1682     while(d_SearchRecordsQuery_stmt->hasNextRow())
1683     {
1684       SSqlStatement::row_t row;
1685       DNSResourceRecord r;
1686       d_SearchRecordsQuery_stmt->nextRow(row);
1687       ASSERT_ROW_COLUMNS("search-records-query", row, 8);
1688       try {
1689         extractRecord(row, r);
1690       } catch (...) {
1691         continue;
1692       }
1693       result.push_back(r);
1694     }
1695
1696     d_SearchRecordsQuery_stmt->reset();
1697
1698     return true;
1699   }
1700   catch (SSqlException &e) {
1701     throw PDNSException("GSQLBackend unable to search for records with pattern '" + pattern + "' (escaped pattern '" + escaped_pattern + "'): "+e.txtReason());
1702   }
1703
1704   return false;
1705 }
1706
1707 bool GSQLBackend::searchComments(const string &pattern, int maxResults, vector<Comment>& result)
1708 {
1709   Comment c;
1710   string escaped_pattern = pattern2SQLPattern(pattern);
1711   try {
1712     reconnectIfNeeded();
1713
1714     d_SearchCommentsQuery_stmt->
1715       bind("value", escaped_pattern)->
1716       bind("value2", escaped_pattern)->
1717       bind("limit", maxResults)->
1718       execute();
1719
1720     while(d_SearchCommentsQuery_stmt->hasNextRow()) {
1721       SSqlStatement::row_t row;
1722       d_SearchCommentsQuery_stmt->nextRow(row);
1723       ASSERT_ROW_COLUMNS("search-comments-query", row, 6);
1724       Comment comment;
1725       extractComment(row, comment);
1726       result.push_back(comment);
1727     }
1728
1729     d_SearchCommentsQuery_stmt->reset();
1730
1731     return true;
1732   }
1733   catch (SSqlException &e) {
1734     throw PDNSException("GSQLBackend unable to search for comments with pattern '" + pattern + "' (escaped pattern '" + escaped_pattern + "'): "+e.txtReason());
1735   }
1736
1737   return false;
1738 }
1739
1740 void GSQLBackend::extractRecord(const SSqlStatement::row_t& row, DNSResourceRecord& r)
1741 {
1742   if (row[1].empty())
1743       r.ttl = ::arg().asNum( "default-ttl" );
1744   else
1745       r.ttl=pdns_stou(row[1]);
1746   if(!d_qname.empty())
1747     r.qname=d_qname;
1748   else
1749     r.qname=DNSName(row[6]);
1750
1751   r.qtype=row[3];
1752
1753   if (r.qtype==QType::MX || r.qtype==QType::SRV)
1754     r.content=row[2]+" "+row[0];
1755   else
1756     r.content=row[0];
1757
1758   r.last_modified=0;
1759
1760   if(d_dnssecQueries)
1761     r.auth = !row[7].empty() && row[7][0]=='1';
1762   else
1763     r.auth = 1;
1764
1765   r.disabled = !row[5].empty() && row[5][0]=='1';
1766
1767   r.domain_id=pdns_stou(row[4]);
1768 }
1769
1770 void GSQLBackend::extractComment(const SSqlStatement::row_t& row, Comment& comment)
1771 {
1772   comment.domain_id = pdns_stou(row[0]);
1773   comment.qname = DNSName(row[1]);
1774   comment.qtype = row[2];
1775   comment.modified_at = pdns_stou(row[3]);
1776   comment.account = row[4];
1777   comment.content = row[5];
1778 }
1779
1780 SSqlStatement::~SSqlStatement() {
1781 // make sure vtable won't break
1782 }