components/password_manager/core/browser/login_database.cc

   1 // Copyright 2014 The Chromium Authors. All rights reserved.
   2 // Use of this source code is governed by a BSD-style license that can be
   3 // found in the LICENSE file.
   4
   5 #include "components/password_manager/core/browser/login_database.h"
   6
   7 #include <algorithm>
   8 #include <limits>
   9
  10 #include "base/bind.h"
  11 #include "base/files/file_path.h"
  12 #include "base/logging.h"
  13 #include "base/metrics/histogram_macros.h"
  14 #include "base/metrics/sparse_histogram.h"
  15 #include "base/pickle.h"
  16 #include "base/strings/string_util.h"
  17 #include "base/strings/stringprintf.h"
  18 #include "base/time/time.h"
  19 #include "components/autofill/core/common/password_form.h"
  20 #include "components/password_manager/core/browser/affiliation_utils.h"
  21 #include "components/password_manager/core/browser/password_manager_client.h"
  22 #include "components/password_manager/core/browser/password_manager_metrics_util.h"
  23 #include "google_apis/gaia/gaia_auth_util.h"
  24 #include "google_apis/gaia/gaia_urls.h"
  25 #include "sql/connection.h"
  26 #include "sql/statement.h"
  27 #include "sql/transaction.h"
  28 #include "url/url_constants.h"
  29
  30 using autofill::PasswordForm;
  31
  32 namespace password_manager {
  33
  34 // The current version number of the login database schema.
  35 const int kCurrentVersionNumber = 15;
  36 // The oldest version of the schema such that a legacy Chrome client using that
  37 // version can still read/write the current database.
  38 const int kCompatibleVersionNumber = 14;
  39
  40 base::Pickle SerializeVector(const std::vector<base::string16>& vec) {
  41   base::Pickle p;
  42   for (size_t i = 0; i < vec.size(); ++i) {
  43     p.WriteString16(vec[i]);
  44   }
  45   return p;
  46 }
  47
  48 std::vector<base::string16> DeserializeVector(const base::Pickle& p) {
  49   std::vector<base::string16> ret;
  50   base::string16 str;
  51
  52   base::PickleIterator iterator(p);
  53   while (iterator.ReadString16(&str)) {
  54     ret.push_back(str);
  55   }
  56   return ret;
  57 }
  58
  59 namespace {
  60
  61 // Convenience enum for interacting with SQL queries that use all the columns.
  62 enum LoginTableColumns {
  63   COLUMN_ORIGIN_URL = 0,
  64   COLUMN_ACTION_URL,
  65   COLUMN_USERNAME_ELEMENT,
  66   COLUMN_USERNAME_VALUE,
  67   COLUMN_PASSWORD_ELEMENT,
  68   COLUMN_PASSWORD_VALUE,
  69   COLUMN_SUBMIT_ELEMENT,
  70   COLUMN_SIGNON_REALM,
  71   COLUMN_SSL_VALID,
  72   COLUMN_PREFERRED,
  73   COLUMN_DATE_CREATED,
  74   COLUMN_BLACKLISTED_BY_USER,
  75   COLUMN_SCHEME,
  76   COLUMN_PASSWORD_TYPE,
  77   COLUMN_POSSIBLE_USERNAMES,
  78   COLUMN_TIMES_USED,
  79   COLUMN_FORM_DATA,
  80   COLUMN_DATE_SYNCED,
  81   COLUMN_DISPLAY_NAME,
  82   COLUMN_ICON_URL,
  83   COLUMN_FEDERATION_URL,
  84   COLUMN_SKIP_ZERO_CLICK,
  85   COLUMN_GENERATION_UPLOAD_STATUS,
  86 };
  87
  88 enum class HistogramSize { SMALL, LARGE };
  89
  90 // An enum for UMA reporting. Add values to the end only.
  91 enum DatabaseInitError {
  92   INIT_OK,
  93   OPEN_FILE_ERROR,
  94   START_TRANSACTION_ERROR,
  95   META_TABLE_INIT_ERROR,
  96   INCOMPATIBLE_VERSION,
  97   INIT_LOGINS_ERROR,
  98   INIT_STATS_ERROR,
  99   MIGRATION_ERROR,
 100   COMMIT_TRANSACTION_ERROR,
 101
 102   DATABASE_INIT_ERROR_COUNT,
 103 };
 104
 105 void BindAddStatement(const PasswordForm& form,
 106                       const std::string& encrypted_password,
 107                       sql::Statement* s) {
 108   s->BindString(COLUMN_ORIGIN_URL, form.origin.spec());
 109   s->BindString(COLUMN_ACTION_URL, form.action.spec());
 110   s->BindString16(COLUMN_USERNAME_ELEMENT, form.username_element);
 111   s->BindString16(COLUMN_USERNAME_VALUE, form.username_value);
 112   s->BindString16(COLUMN_PASSWORD_ELEMENT, form.password_element);
 113   s->BindBlob(COLUMN_PASSWORD_VALUE, encrypted_password.data(),
 114               static_cast<int>(encrypted_password.length()));
 115   s->BindString16(COLUMN_SUBMIT_ELEMENT, form.submit_element);
 116   s->BindString(COLUMN_SIGNON_REALM, form.signon_realm);
 117   s->BindInt(COLUMN_SSL_VALID, form.ssl_valid);
 118   s->BindInt(COLUMN_PREFERRED, form.preferred);
 119   s->BindInt64(COLUMN_DATE_CREATED, form.date_created.ToInternalValue());
 120   s->BindInt(COLUMN_BLACKLISTED_BY_USER, form.blacklisted_by_user);
 121   s->BindInt(COLUMN_SCHEME, form.scheme);
 122   s->BindInt(COLUMN_PASSWORD_TYPE, form.type);
 123   base::Pickle usernames_pickle =
 124       SerializeVector(form.other_possible_usernames);
 125   s->BindBlob(COLUMN_POSSIBLE_USERNAMES,
 126               usernames_pickle.data(),
 127               usernames_pickle.size());
 128   s->BindInt(COLUMN_TIMES_USED, form.times_used);
 129   base::Pickle form_data_pickle;
 130   autofill::SerializeFormData(form.form_data, &form_data_pickle);
 131   s->BindBlob(COLUMN_FORM_DATA,
 132               form_data_pickle.data(),
 133               form_data_pickle.size());
 134   s->BindInt64(COLUMN_DATE_SYNCED, form.date_synced.ToInternalValue());
 135   s->BindString16(COLUMN_DISPLAY_NAME, form.display_name);
 136   s->BindString(COLUMN_ICON_URL, form.icon_url.spec());
 137   s->BindString(COLUMN_FEDERATION_URL, form.federation_url.spec());
 138   s->BindInt(COLUMN_SKIP_ZERO_CLICK, form.skip_zero_click);
 139   s->BindInt(COLUMN_GENERATION_UPLOAD_STATUS, form.generation_upload_status);
 140 }
 141
 142 void AddCallback(int err, sql::Statement* /*stmt*/) {
 143   if (err == 19 /*SQLITE_CONSTRAINT*/)
 144     DLOG(WARNING) << "LoginDatabase::AddLogin updated an existing form";
 145 }
 146
 147 bool DoesMatchConstraints(const PasswordForm& form) {
 148   if (!IsValidAndroidFacetURI(form.signon_realm) && form.origin.is_empty()) {
 149     DLOG(ERROR) << "Constraint violation: form.origin is empty";
 150     return false;
 151   }
 152   if (form.signon_realm.empty()) {
 153     DLOG(ERROR) << "Constraint violation: form.signon_realm is empty";
 154     return false;
 155   }
 156   return true;
 157 }
 158
 159 void LogDatabaseInitError(DatabaseInitError error) {
 160   UMA_HISTOGRAM_ENUMERATION("PasswordManager.LoginDatabaseInit", error,
 161                             DATABASE_INIT_ERROR_COUNT);
 162 }
 163
 164 // UMA_* macros assume that the name never changes. This is a helper function
 165 // where this assumption doesn't hold.
 166 void LogDynamicUMAStat(const std::string& name,
 167                        int sample,
 168                        int min,
 169                        int max,
 170                        int bucket_count) {
 171   base::HistogramBase* counter = base::Histogram::FactoryGet(
 172       name, min, max, bucket_count,
 173       base::HistogramBase::kUmaTargetedHistogramFlag);
 174   counter->Add(sample);
 175 }
 176
 177 void LogAccountStat(const std::string& name, int sample) {
 178   LogDynamicUMAStat(name, sample, 0, 32, 6);
 179 }
 180
 181 void LogTimesUsedStat(const std::string& name, int sample) {
 182   LogDynamicUMAStat(name, sample, 0, 100, 10);
 183 }
 184
 185 void LogNumberOfAccountsForScheme(const std::string& scheme, int sample) {
 186   LogDynamicUMAStat("PasswordManager.TotalAccountsHiRes.WithScheme." + scheme,
 187                     sample, 1, 1000, 100);
 188 }
 189
 190 void LogNumberOfAccountsReusingPassword(const std::string& suffix,
 191                                         int sample,
 192                                         HistogramSize histogram_size) {
 193   int max = histogram_size == HistogramSize::LARGE ? 500 : 100;
 194   int bucket_count = histogram_size == HistogramSize::LARGE ? 50 : 20;
 195   LogDynamicUMAStat("PasswordManager.AccountsReusingPassword." + suffix, sample,
 196                     1, max, bucket_count);
 197 }
 198
 199 // Records password reuse metrics given the |signon_realms| corresponding to a
 200 // set of accounts that reuse the same password. See histograms.xml for details.
 201 void LogPasswordReuseMetrics(const std::vector<std::string>& signon_realms) {
 202   struct StatisticsPerScheme {
 203     StatisticsPerScheme() : num_total_accounts(0) {}
 204
 205     // The number of accounts for each registry controlled domain.
 206     std::map<std::string, int> num_accounts_per_registry_controlled_domain;
 207
 208     // The number of accounts for each domain.
 209     std::map<std::string, int> num_accounts_per_domain;
 210
 211     // Total number of accounts with this scheme. This equals the sum of counts
 212     // in either of the above maps.
 213     int num_total_accounts;
 214   };
 215
 216   // The scheme (i.e. protocol) of the origin, not PasswordForm::scheme.
 217   enum Scheme { SCHEME_HTTP, SCHEME_HTTPS };
 218   const Scheme kAllSchemes[] = {SCHEME_HTTP, SCHEME_HTTPS};
 219
 220   StatisticsPerScheme statistics[arraysize(kAllSchemes)];
 221   std::map<std::string, std::string> domain_to_registry_controlled_domain;
 222
 223   for (const std::string& signon_realm : signon_realms) {
 224     const GURL signon_realm_url(signon_realm);
 225     const std::string domain = signon_realm_url.host();
 226     if (domain.empty())
 227       continue;
 228
 229     if (!domain_to_registry_controlled_domain.count(domain)) {
 230       domain_to_registry_controlled_domain[domain] =
 231           GetRegistryControlledDomain(signon_realm_url);
 232       if (domain_to_registry_controlled_domain[domain].empty())
 233         domain_to_registry_controlled_domain[domain] = domain;
 234     }
 235     const std::string& registry_controlled_domain =
 236         domain_to_registry_controlled_domain[domain];
 237
 238     Scheme scheme = SCHEME_HTTP;
 239     COMPILE_ASSERT(arraysize(kAllSchemes) == 2, "Update this logic");
 240     if (signon_realm_url.SchemeIs(url::kHttpsScheme))
 241       scheme = SCHEME_HTTPS;
 242     else if (!signon_realm_url.SchemeIs(url::kHttpScheme))
 243       continue;
 244
 245     statistics[scheme].num_accounts_per_domain[domain]++;
 246     statistics[scheme].num_accounts_per_registry_controlled_domain
 247         [registry_controlled_domain]++;
 248     statistics[scheme].num_total_accounts++;
 249   }
 250
 251   // For each "source" account of either scheme, count the number of "target"
 252   // accounts reusing the same password (of either scheme).
 253   for (const Scheme scheme : kAllSchemes) {
 254     for (const auto& kv : statistics[scheme].num_accounts_per_domain) {
 255       const std::string& domain(kv.first);
 256       const int num_accounts_per_domain(kv.second);
 257       const std::string& registry_controlled_domain =
 258           domain_to_registry_controlled_domain[domain];
 259
 260       Scheme other_scheme = scheme == SCHEME_HTTP ? SCHEME_HTTPS : SCHEME_HTTP;
 261       COMPILE_ASSERT(arraysize(kAllSchemes) == 2, "Update |other_scheme|");
 262
 263       // Discount the account at hand from the number of accounts with the same
 264       // domain and scheme.
 265       int num_accounts_for_same_domain[arraysize(kAllSchemes)] = {};
 266       num_accounts_for_same_domain[scheme] =
 267           statistics[scheme].num_accounts_per_domain[domain] - 1;
 268       num_accounts_for_same_domain[other_scheme] =
 269           statistics[other_scheme].num_accounts_per_domain[domain];
 270
 271       // By definition, a PSL match requires the scheme to be the same.
 272       int num_psl_matching_accounts =
 273           statistics[scheme].num_accounts_per_registry_controlled_domain
 274               [registry_controlled_domain] -
 275           statistics[scheme].num_accounts_per_domain[domain];
 276
 277       // Discount PSL matches from the number of accounts with different domains
 278       // but the same scheme.
 279       int num_accounts_for_different_domain[arraysize(kAllSchemes)] = {};
 280       num_accounts_for_different_domain[scheme] =
 281           statistics[scheme].num_total_accounts -
 282           statistics[scheme].num_accounts_per_registry_controlled_domain
 283               [registry_controlled_domain];
 284       num_accounts_for_different_domain[other_scheme] =
 285           statistics[other_scheme].num_total_accounts -
 286           statistics[other_scheme].num_accounts_per_domain[domain];
 287
 288       std::string source_realm_kind =
 289           scheme == SCHEME_HTTP ? "FromHttpRealm" : "FromHttpsRealm";
 290       COMPILE_ASSERT(arraysize(kAllSchemes) == 2, "Update |source_realm_kind|");
 291
 292       // So far, the calculation has been carried out once per "source" domain,
 293       // but the metrics need to be recorded on a per-account basis. The set of
 294       // metrics are the same for all accounts for the same domain, so simply
 295       // report them as many times as accounts.
 296       for (int i = 0; i < num_accounts_per_domain; ++i) {
 297         LogNumberOfAccountsReusingPassword(
 298             source_realm_kind + ".OnHttpRealmWithSameHost",
 299             num_accounts_for_same_domain[SCHEME_HTTP], HistogramSize::SMALL);
 300         LogNumberOfAccountsReusingPassword(
 301             source_realm_kind + ".OnHttpsRealmWithSameHost",
 302             num_accounts_for_same_domain[SCHEME_HTTPS], HistogramSize::SMALL);
 303         LogNumberOfAccountsReusingPassword(
 304             source_realm_kind + ".OnPSLMatchingRealm",
 305             num_psl_matching_accounts, HistogramSize::SMALL);
 306
 307         LogNumberOfAccountsReusingPassword(
 308             source_realm_kind + ".OnHttpRealmWithDifferentHost",
 309             num_accounts_for_different_domain[SCHEME_HTTP],
 310             HistogramSize::LARGE);
 311         LogNumberOfAccountsReusingPassword(
 312             source_realm_kind + ".OnHttpsRealmWithDifferentHost",
 313             num_accounts_for_different_domain[SCHEME_HTTPS],
 314             HistogramSize::LARGE);
 315
 316         LogNumberOfAccountsReusingPassword(
 317             source_realm_kind + ".OnAnyRealmWithDifferentHost",
 318             num_accounts_for_different_domain[SCHEME_HTTP] +
 319                 num_accounts_for_different_domain[SCHEME_HTTPS],
 320             HistogramSize::LARGE);
 321       }
 322     }
 323   }
 324 }
 325
 326 // Creates a table named |table_name| using our current schema.
 327 bool CreateNewTable(sql::Connection* db,
 328                     const char* table_name,
 329                     const char* extra_columns) {
 330   std::string query = base::StringPrintf(
 331       "CREATE TABLE %s ("
 332       "origin_url VARCHAR NOT NULL, "
 333       "action_url VARCHAR, "
 334       "username_element VARCHAR, "
 335       "username_value VARCHAR, "
 336       "password_element VARCHAR, "
 337       "password_value BLOB, "
 338       "submit_element VARCHAR, "
 339       "signon_realm VARCHAR NOT NULL,"
 340       "ssl_valid INTEGER NOT NULL,"
 341       "preferred INTEGER NOT NULL,"
 342       "date_created INTEGER NOT NULL,"
 343       "blacklisted_by_user INTEGER NOT NULL,"
 344       "scheme INTEGER NOT NULL,"
 345       "password_type INTEGER,"
 346       "possible_usernames BLOB,"
 347       "times_used INTEGER,"
 348       "form_data BLOB,"
 349       "date_synced INTEGER,"
 350       "display_name VARCHAR,"
 351       "icon_url VARCHAR,"
 352       "federation_url VARCHAR,"
 353       "skip_zero_click INTEGER,"
 354       "%s"
 355       "UNIQUE (origin_url, username_element, username_value, "
 356       "password_element, signon_realm))",
 357       table_name, extra_columns);
 358   return db->Execute(query.c_str());
 359 }
 360
 361 bool CreateIndexOnSignonRealm(sql::Connection* db, const char* table_name) {
 362   std::string query = base::StringPrintf(
 363       "CREATE INDEX logins_signon ON %s (signon_realm)", table_name);
 364   return db->Execute(query.c_str());
 365 }
 366
 367 }  // namespace
 368
 369 LoginDatabase::LoginDatabase(const base::FilePath& db_path)
 370     : db_path_(db_path), clear_password_values_(false) {
 371 }
 372
 373 LoginDatabase::~LoginDatabase() {
 374 }
 375
 376 bool LoginDatabase::Init() {
 377   // Set pragmas for a small, private database (based on WebDatabase).
 378   db_.set_page_size(2048);
 379   db_.set_cache_size(32);
 380   db_.set_exclusive_locking();
 381   db_.set_restrict_to_user();
 382   db_.set_histogram_tag("Passwords");
 383
 384   if (!db_.Open(db_path_)) {
 385     LogDatabaseInitError(OPEN_FILE_ERROR);
 386     LOG(ERROR) << "Unable to open the password store database.";
 387     return false;
 388   }
 389
 390   sql::Transaction transaction(&db_);
 391   if (!transaction.Begin()) {
 392     LogDatabaseInitError(START_TRANSACTION_ERROR);
 393     LOG(ERROR) << "Unable to start a transaction.";
 394     db_.Close();
 395     return false;
 396   }
 397
 398   // Check the database version.
 399   if (!meta_table_.Init(&db_, kCurrentVersionNumber,
 400                         kCompatibleVersionNumber)) {
 401     LogDatabaseInitError(META_TABLE_INIT_ERROR);
 402     LOG(ERROR) << "Unable to create the meta table.";
 403     db_.Close();
 404     return false;
 405   }
 406   if (meta_table_.GetCompatibleVersionNumber() > kCurrentVersionNumber) {
 407     LogDatabaseInitError(INCOMPATIBLE_VERSION);
 408     LOG(ERROR) << "Password store database is too new, kCurrentVersionNumber="
 409                << kCurrentVersionNumber << ", GetCompatibleVersionNumber="
 410                << meta_table_.GetCompatibleVersionNumber();
 411     db_.Close();
 412     return false;
 413   }
 414
 415   // Initialize the tables.
 416   if (!InitLoginsTable()) {
 417     LogDatabaseInitError(INIT_LOGINS_ERROR);
 418     LOG(ERROR) << "Unable to initialize the logins table.";
 419     db_.Close();
 420     return false;
 421   }
 422
 423   if (!stats_table_.Init(&db_)) {
 424     LogDatabaseInitError(INIT_STATS_ERROR);
 425     LOG(ERROR) << "Unable to initialize the stats table.";
 426     db_.Close();
 427     return false;
 428   }
 429
 430   // If the file on disk is an older database version, bring it up to date.
 431   if (meta_table_.GetVersionNumber() < kCurrentVersionNumber &&
 432       !MigrateOldVersionsAsNeeded()) {
 433     LogDatabaseInitError(MIGRATION_ERROR);
 434     UMA_HISTOGRAM_SPARSE_SLOWLY("PasswordManager.LoginDatabaseFailedVersion",
 435                                 meta_table_.GetVersionNumber());
 436     LOG(ERROR) << "Unable to migrate database from "
 437                << meta_table_.GetVersionNumber() << " to "
 438                << kCurrentVersionNumber;
 439     db_.Close();
 440     return false;
 441   }
 442
 443   if (!transaction.Commit()) {
 444     LogDatabaseInitError(COMMIT_TRANSACTION_ERROR);
 445     LOG(ERROR) << "Unable to commit a transaction.";
 446     db_.Close();
 447     return false;
 448   }
 449
 450   LogDatabaseInitError(INIT_OK);
 451   return true;
 452 }
 453
 454 bool LoginDatabase::MigrateOldVersionsAsNeeded() {
 455   const int original_version = meta_table_.GetVersionNumber();
 456   switch (original_version) {
 457     case 1:
 458       if (!db_.Execute("ALTER TABLE logins "
 459                        "ADD COLUMN password_type INTEGER") ||
 460           !db_.Execute("ALTER TABLE logins "
 461                        "ADD COLUMN possible_usernames BLOB")) {
 462         return false;
 463       }
 464     // Fall through.
 465     case 2:
 466       if (!db_.Execute("ALTER TABLE logins ADD COLUMN times_used INTEGER")) {
 467         return false;
 468       }
 469     // Fall through.
 470     case 3:
 471       // We need to check if the column exists because of
 472       // https://crbug.com/295851
 473       if (!db_.DoesColumnExist("logins", "form_data") &&
 474           !db_.Execute("ALTER TABLE logins ADD COLUMN form_data BLOB")) {
 475         return false;
 476       }
 477     // Fall through.
 478     case 4:
 479       if (!db_.Execute(
 480               "ALTER TABLE logins ADD COLUMN use_additional_auth INTEGER")) {
 481         return false;
 482       }
 483     // Fall through.
 484     case 5:
 485       if (!db_.Execute("ALTER TABLE logins ADD COLUMN date_synced INTEGER")) {
 486         return false;
 487       }
 488     // Fall through.
 489     case 6:
 490       if (!db_.Execute("ALTER TABLE logins ADD COLUMN display_name VARCHAR") ||
 491           !db_.Execute("ALTER TABLE logins ADD COLUMN avatar_url VARCHAR") ||
 492           !db_.Execute("ALTER TABLE logins "
 493                        "ADD COLUMN federation_url VARCHAR") ||
 494           !db_.Execute("ALTER TABLE logins ADD COLUMN is_zero_click INTEGER")) {
 495         return false;
 496       }
 497     // Fall through.
 498     case 7: {
 499       // Keep version 8 around even though no changes are made. See
 500       // crbug.com/423716 for context.
 501       // Fall through.
 502     }
 503     case 8: {
 504       sql::Statement s;
 505       s.Assign(db_.GetCachedStatement(SQL_FROM_HERE,
 506                                       "UPDATE logins SET "
 507                                       "date_created = "
 508                                       "(date_created * ?) + ?"));
 509       s.BindInt64(0, base::Time::kMicrosecondsPerSecond);
 510       s.BindInt64(1, base::Time::kTimeTToMicrosecondsOffset);
 511       if (!s.Run())
 512         return false;
 513       // Fall through.
 514     }
 515     case 9: {
 516       // Remove use_additional_auth column from database schema
 517       // crbug.com/423716 for context.
 518       std::string fields_to_copy =
 519           "origin_url, action_url, username_element, username_value, "
 520           "password_element, password_value, submit_element, "
 521           "signon_realm, ssl_valid, preferred, date_created, "
 522           "blacklisted_by_user, scheme, password_type, possible_usernames, "
 523           "times_used, form_data, date_synced, display_name, avatar_url, "
 524           "federation_url, is_zero_click";
 525       auto copy_data_query =
 526           [&fields_to_copy](const std::string& from, const std::string& to) {
 527         return "INSERT INTO " + to + " SELECT " + fields_to_copy + " FROM " +
 528                from;
 529       };
 530
 531       if (!db_.Execute(("CREATE TEMPORARY TABLE logins_data(" + fields_to_copy +
 532                         ")").c_str()) ||
 533           !db_.Execute(copy_data_query("logins", "logins_data").c_str()) ||
 534           !db_.Execute("DROP TABLE logins") ||
 535           !db_.Execute(
 536               ("CREATE TABLE logins(" + fields_to_copy + ")").c_str()) ||
 537           !db_.Execute(copy_data_query("logins_data", "logins").c_str()) ||
 538           !db_.Execute("DROP TABLE logins_data") ||
 539           !CreateIndexOnSignonRealm(&db_, "logins")) {
 540         return false;
 541       }
 542       // Fall through.
 543     }
 544     case 10: {
 545       // Rename is_zero_click -> skip_zero_click. Note that previous versions
 546       // may have incorrectly used a 6-column key (origin_url, username_element,
 547       // username_value, password_element, signon_realm, submit_element).
 548       // In that case, this step also restores the correct 5-column key;
 549       // that is, the above without "submit_element".
 550       const char copy_query[] = "INSERT OR REPLACE INTO logins_new SELECT "
 551           "origin_url, action_url, username_element, username_value, "
 552           "password_element, password_value, submit_element, signon_realm, "
 553           "ssl_valid, preferred, date_created, blacklisted_by_user, scheme, "
 554           "password_type, possible_usernames, times_used, form_data, "
 555           "date_synced, display_name, avatar_url, federation_url, is_zero_click"
 556           " FROM logins";
 557       if (!CreateNewTable(&db_, "logins_new", "") ||
 558           !db_.Execute(copy_query) ||
 559           !db_.Execute("DROP TABLE logins") ||
 560           !db_.Execute("ALTER TABLE logins_new RENAME TO logins") ||
 561           !CreateIndexOnSignonRealm(&db_, "logins")) {
 562         return false;
 563       }
 564       // Fall through.
 565     }
 566     case 11:
 567       if (!db_.Execute(
 568               "ALTER TABLE logins ADD COLUMN "
 569               "generation_upload_status INTEGER"))
 570         return false;
 571       // Fall through.
 572     case 12:
 573       // The stats table was added. Nothing to do really.
 574       // Fall through.
 575     case 13: {
 576       // Rename avatar_url -> icon_url. Note that if the original version was
 577       // at most 10, this renaming would have already happened in step 10,
 578       // as |CreateNewTable| would create a table with the new column name.
 579       if (original_version > 10) {
 580         const char copy_query[] = "INSERT OR REPLACE INTO logins_new SELECT "
 581             "origin_url, action_url, username_element, username_value, "
 582             "password_element, password_value, submit_element, signon_realm, "
 583             "ssl_valid, preferred, date_created, blacklisted_by_user, scheme, "
 584             "password_type, possible_usernames, times_used, form_data, "
 585             "date_synced, display_name, avatar_url, federation_url, "
 586             "skip_zero_click, generation_upload_status FROM logins";
 587         if (!CreateNewTable(
 588                 &db_, "logins_new", "generation_upload_status INTEGER,") ||
 589             !db_.Execute(copy_query) ||
 590             !db_.Execute("DROP TABLE logins") ||
 591             !db_.Execute("ALTER TABLE logins_new RENAME TO logins") ||
 592             !CreateIndexOnSignonRealm(&db_, "logins")) {
 593           return false;
 594         }
 595       }
 596       // Fall through.
 597     }
 598     case 14:
 599       // No change of schema. Version 15 was introduced to force all databases
 600       // through an otherwise no-op migration process that will, however, now
 601       // correctly set the 'compatible version number'. Previously, it was
 602       // always being set to (and forever left at) version 1.
 603       // Fall through.
 604
 605     // -------------------------------------------------------------------------
 606     // DO NOT FORGET to update |kCompatibleVersionNumber| if you add a migration
 607     // step that is a breaking change. This is needed so that an older version
 608     // of the browser can fail with a meaningful error when opening a newer
 609     // database, as opposed to failing on the first database operation.
 610     // -------------------------------------------------------------------------
 611     case kCurrentVersionNumber:
 612       // Already up to date.
 613       meta_table_.SetVersionNumber(kCurrentVersionNumber);
 614       meta_table_.SetCompatibleVersionNumber(kCompatibleVersionNumber);
 615       return true;
 616     default:
 617       NOTREACHED();
 618       return false;
 619   }
 620 }
 621
 622 bool LoginDatabase::InitLoginsTable() {
 623   if (!db_.DoesTableExist("logins")) {
 624     if (!CreateNewTable(&db_, "logins", "generation_upload_status INTEGER,")) {
 625       NOTREACHED();
 626       return false;
 627     }
 628     if (!CreateIndexOnSignonRealm(&db_, "logins")) {
 629       NOTREACHED();
 630       return false;
 631     }
 632   }
 633   return true;
 634 }
 635
 636 void LoginDatabase::ReportMetrics(const std::string& sync_username,
 637                                   bool custom_passphrase_sync_enabled) {
 638   sql::Statement s(db_.GetCachedStatement(
 639       SQL_FROM_HERE,
 640       "SELECT signon_realm, password_type, blacklisted_by_user,"
 641       "COUNT(username_value) FROM logins GROUP BY "
 642       "signon_realm, password_type, blacklisted_by_user"));
 643
 644   if (!s.is_valid())
 645     return;
 646
 647   std::string custom_passphrase = "WithoutCustomPassphrase";
 648   if (custom_passphrase_sync_enabled) {
 649     custom_passphrase = "WithCustomPassphrase";
 650   }
 651
 652   int total_user_created_accounts = 0;
 653   int total_generated_accounts = 0;
 654   int blacklisted_sites = 0;
 655   while (s.Step()) {
 656     PasswordForm::Type password_type =
 657         static_cast<PasswordForm::Type>(s.ColumnInt(1));
 658     int blacklisted = s.ColumnInt(2);
 659     int accounts_per_site = s.ColumnInt(3);
 660     if (blacklisted) {
 661       ++blacklisted_sites;
 662     } else if (password_type == PasswordForm::TYPE_GENERATED) {
 663       total_generated_accounts += accounts_per_site;
 664       LogAccountStat(
 665           base::StringPrintf("PasswordManager.AccountsPerSite.AutoGenerated.%s",
 666                              custom_passphrase.c_str()),
 667           accounts_per_site);
 668     } else {
 669       total_user_created_accounts += accounts_per_site;
 670       LogAccountStat(
 671           base::StringPrintf("PasswordManager.AccountsPerSite.UserCreated.%s",
 672                              custom_passphrase.c_str()),
 673           accounts_per_site);
 674     }
 675   }
 676   LogAccountStat(
 677       base::StringPrintf("PasswordManager.TotalAccounts.UserCreated.%s",
 678                          custom_passphrase.c_str()),
 679       total_user_created_accounts);
 680   LogAccountStat(
 681       base::StringPrintf("PasswordManager.TotalAccounts.AutoGenerated.%s",
 682                          custom_passphrase.c_str()),
 683       total_generated_accounts);
 684   LogAccountStat(base::StringPrintf("PasswordManager.BlacklistedSites.%s",
 685                                     custom_passphrase.c_str()),
 686                  blacklisted_sites);
 687
 688   sql::Statement usage_statement(db_.GetCachedStatement(
 689       SQL_FROM_HERE, "SELECT password_type, times_used FROM logins"));
 690
 691   if (!usage_statement.is_valid())
 692     return;
 693
 694   while (usage_statement.Step()) {
 695     PasswordForm::Type type =
 696         static_cast<PasswordForm::Type>(usage_statement.ColumnInt(0));
 697
 698     if (type == PasswordForm::TYPE_GENERATED) {
 699       LogTimesUsedStat(base::StringPrintf(
 700                            "PasswordManager.TimesPasswordUsed.AutoGenerated.%s",
 701                            custom_passphrase.c_str()),
 702                        usage_statement.ColumnInt(1));
 703     } else {
 704       LogTimesUsedStat(
 705           base::StringPrintf("PasswordManager.TimesPasswordUsed.UserCreated.%s",
 706                              custom_passphrase.c_str()),
 707           usage_statement.ColumnInt(1));
 708     }
 709   }
 710
 711   bool syncing_account_saved = false;
 712   if (!sync_username.empty()) {
 713     sql::Statement sync_statement(db_.GetCachedStatement(
 714         SQL_FROM_HERE,
 715         "SELECT username_value FROM logins "
 716         "WHERE signon_realm == ?"));
 717     sync_statement.BindString(
 718         0, GaiaUrls::GetInstance()->gaia_url().GetOrigin().spec());
 719
 720     if (!sync_statement.is_valid())
 721       return;
 722
 723     while (sync_statement.Step()) {
 724       std::string username = sync_statement.ColumnString(0);
 725       if (gaia::AreEmailsSame(sync_username, username)) {
 726         syncing_account_saved = true;
 727         break;
 728       }
 729     }
 730   }
 731   UMA_HISTOGRAM_ENUMERATION("PasswordManager.SyncingAccountState",
 732                             2 * sync_username.empty() + syncing_account_saved,
 733                             4);
 734
 735   sql::Statement empty_usernames_statement(db_.GetCachedStatement(
 736       SQL_FROM_HERE, "SELECT COUNT(*) FROM logins "
 737                      "WHERE blacklisted_by_user=0 AND username_value=''"));
 738   if (empty_usernames_statement.Step()) {
 739     int empty_forms = empty_usernames_statement.ColumnInt(0);
 740     UMA_HISTOGRAM_COUNTS_100("PasswordManager.EmptyUsernames.CountInDatabase",
 741                              empty_forms);
 742   }
 743
 744   sql::Statement standalone_empty_usernames_statement(db_.GetCachedStatement(
 745       SQL_FROM_HERE, "SELECT COUNT(*) FROM logins a "
 746                      "WHERE a.blacklisted_by_user=0 AND a.username_value='' "
 747                      "AND NOT EXISTS (SELECT * FROM logins b "
 748                      "WHERE b.blacklisted_by_user=0 AND b.username_value!='' "
 749                      "AND a.signon_realm = b.signon_realm)"));
 750   if (standalone_empty_usernames_statement.Step()) {
 751     int num_entries = standalone_empty_usernames_statement.ColumnInt(0);
 752     UMA_HISTOGRAM_COUNTS_100(
 753         "PasswordManager.EmptyUsernames.WithoutCorrespondingNonempty",
 754         num_entries);
 755   }
 756
 757   sql::Statement logins_with_schemes_statement(db_.GetUniqueStatement(
 758       "SELECT signon_realm, origin_url, ssl_valid, blacklisted_by_user "
 759       "FROM logins;"));
 760
 761   if (!logins_with_schemes_statement.is_valid())
 762     return;
 763
 764   int android_logins = 0;
 765   int ftp_logins = 0;
 766   int http_logins = 0;
 767   int https_logins = 0;
 768   int other_logins = 0;
 769
 770   while (logins_with_schemes_statement.Step()) {
 771     std::string signon_realm = logins_with_schemes_statement.ColumnString(0);
 772     GURL origin_url = GURL(logins_with_schemes_statement.ColumnString(1));
 773     bool ssl_valid = !!logins_with_schemes_statement.ColumnInt(2);
 774     bool blacklisted_by_user = !!logins_with_schemes_statement.ColumnInt(3);
 775     if (blacklisted_by_user)
 776       continue;
 777
 778     if (IsValidAndroidFacetURI(signon_realm)) {
 779       ++android_logins;
 780     } else if (origin_url.SchemeIs(url::kHttpsScheme)) {
 781       ++https_logins;
 782       metrics_util::LogUMAHistogramBoolean(
 783           "PasswordManager.UserStoredPasswordWithInvalidSSLCert", !ssl_valid);
 784     } else if (origin_url.SchemeIs(url::kHttpScheme)) {
 785       ++http_logins;
 786     } else if (origin_url.SchemeIs(url::kFtpScheme)) {
 787       ++ftp_logins;
 788     } else {
 789       ++other_logins;
 790     }
 791   }
 792
 793   LogNumberOfAccountsForScheme("Android", android_logins);
 794   LogNumberOfAccountsForScheme("Ftp", ftp_logins);
 795   LogNumberOfAccountsForScheme("Http", http_logins);
 796   LogNumberOfAccountsForScheme("Https", https_logins);
 797   LogNumberOfAccountsForScheme("Other", other_logins);
 798
 799   sql::Statement form_based_passwords_statement(
 800       db_.GetUniqueStatement("SELECT signon_realm, password_value FROM logins "
 801                              "WHERE blacklisted_by_user = 0 AND scheme = 0"));
 802
 803   std::map<base::string16, std::vector<std::string>> passwords_to_realms;
 804   while (form_based_passwords_statement.Step()) {
 805     std::string signon_realm = form_based_passwords_statement.ColumnString(0);
 806     base::string16 decrypted_password;
 807     // Note that CryptProtectData() is non-deterministic, so passwords must be
 808     // decrypted before checking equality.
 809     if (!IsValidAndroidFacetURI(signon_realm) &&
 810         DecryptedString(form_based_passwords_statement.ColumnString(1),
 811                         &decrypted_password) == ENCRYPTION_RESULT_SUCCESS) {
 812       passwords_to_realms[decrypted_password].push_back(signon_realm);
 813     }
 814   }
 815
 816   for (const auto& password_to_realms : passwords_to_realms)
 817     LogPasswordReuseMetrics(password_to_realms.second);
 818 }
 819
 820 PasswordStoreChangeList LoginDatabase::AddLogin(const PasswordForm& form) {
 821   PasswordStoreChangeList list;
 822   if (!DoesMatchConstraints(form))
 823     return list;
 824   std::string encrypted_password;
 825   if (EncryptedString(
 826           clear_password_values_ ? base::string16() : form.password_value,
 827           &encrypted_password) != ENCRYPTION_RESULT_SUCCESS)
 828     return list;
 829
 830   // You *must* change LoginTableColumns if this query changes.
 831   sql::Statement s(db_.GetCachedStatement(
 832       SQL_FROM_HERE,
 833       "INSERT INTO logins "
 834       "(origin_url, action_url, username_element, username_value, "
 835       " password_element, password_value, submit_element, "
 836       " signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
 837       " scheme, password_type, possible_usernames, times_used, form_data, "
 838       " date_synced, display_name, icon_url,"
 839       " federation_url, skip_zero_click, generation_upload_status) VALUES "
 840       "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"));
 841   BindAddStatement(form, encrypted_password, &s);
 842   db_.set_error_callback(base::Bind(&AddCallback));
 843   const bool success = s.Run();
 844   db_.reset_error_callback();
 845   if (success) {
 846     list.push_back(PasswordStoreChange(PasswordStoreChange::ADD, form));
 847     return list;
 848   }
 849   // Repeat the same statement but with REPLACE semantic.
 850   s.Assign(db_.GetCachedStatement(
 851       SQL_FROM_HERE,
 852       "INSERT OR REPLACE INTO logins "
 853       "(origin_url, action_url, username_element, username_value, "
 854       " password_element, password_value, submit_element, "
 855       " signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
 856       " scheme, password_type, possible_usernames, times_used, form_data, "
 857       " date_synced, display_name, icon_url,"
 858       " federation_url, skip_zero_click, generation_upload_status) VALUES "
 859       "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"));
 860   BindAddStatement(form, encrypted_password, &s);
 861   if (s.Run()) {
 862     list.push_back(PasswordStoreChange(PasswordStoreChange::REMOVE, form));
 863     list.push_back(PasswordStoreChange(PasswordStoreChange::ADD, form));
 864   }
 865   return list;
 866 }
 867
 868 PasswordStoreChangeList LoginDatabase::UpdateLogin(const PasswordForm& form) {
 869   std::string encrypted_password;
 870   if (EncryptedString(
 871           clear_password_values_ ? base::string16() : form.password_value,
 872           &encrypted_password) != ENCRYPTION_RESULT_SUCCESS)
 873     return PasswordStoreChangeList();
 874
 875   // Replacement is necessary to deal with updating imported credentials. See
 876   // crbug.com/349138 for details.
 877   sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
 878                                           "UPDATE OR REPLACE logins SET "
 879                                           "action_url = ?, "
 880                                           "password_value = ?, "
 881                                           "ssl_valid = ?, "
 882                                           "preferred = ?, "
 883                                           "possible_usernames = ?, "
 884                                           "times_used = ?, "
 885                                           "submit_element = ?, "
 886                                           "date_synced = ?, "
 887                                           "date_created = ?, "
 888                                           "blacklisted_by_user = ?, "
 889                                           "scheme = ?, "
 890                                           "password_type = ?, "
 891                                           "display_name = ?, "
 892                                           "icon_url = ?, "
 893                                           "federation_url = ?, "
 894                                           "skip_zero_click = ?, "
 895                                           "generation_upload_status = ? "
 896                                           "WHERE origin_url = ? AND "
 897                                           "username_element = ? AND "
 898                                           "username_value = ? AND "
 899                                           "password_element = ? AND "
 900                                           "signon_realm = ?"));
 901   s.BindString(0, form.action.spec());
 902   s.BindBlob(1, encrypted_password.data(),
 903              static_cast<int>(encrypted_password.length()));
 904   s.BindInt(2, form.ssl_valid);
 905   s.BindInt(3, form.preferred);
 906   base::Pickle pickle = SerializeVector(form.other_possible_usernames);
 907   s.BindBlob(4, pickle.data(), pickle.size());
 908   s.BindInt(5, form.times_used);
 909   s.BindString16(6, form.submit_element);
 910   s.BindInt64(7, form.date_synced.ToInternalValue());
 911   s.BindInt64(8, form.date_created.ToInternalValue());
 912   s.BindInt(9, form.blacklisted_by_user);
 913   s.BindInt(10, form.scheme);
 914   s.BindInt(11, form.type);
 915   s.BindString16(12, form.display_name);
 916   s.BindString(13, form.icon_url.spec());
 917   s.BindString(14, form.federation_url.spec());
 918   s.BindInt(15, form.skip_zero_click);
 919   s.BindInt(16, form.generation_upload_status);
 920
 921   // WHERE starts here.
 922   s.BindString(17, form.origin.spec());
 923   s.BindString16(18, form.username_element);
 924   s.BindString16(19, form.username_value);
 925   s.BindString16(20, form.password_element);
 926   s.BindString(21, form.signon_realm);
 927
 928   if (!s.Run())
 929     return PasswordStoreChangeList();
 930
 931   PasswordStoreChangeList list;
 932   if (db_.GetLastChangeCount())
 933     list.push_back(PasswordStoreChange(PasswordStoreChange::UPDATE, form));
 934
 935   return list;
 936 }
 937
 938 bool LoginDatabase::RemoveLogin(const PasswordForm& form) {
 939   if (form.IsPublicSuffixMatch()) {
 940     // Do not try to remove |form|. It is a modified copy of a password stored
 941     // for a different origin, and it is not contained in the database.
 942     return false;
 943   }
 944   // Remove a login by UNIQUE-constrained fields.
 945   sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
 946                                           "DELETE FROM logins WHERE "
 947                                           "origin_url = ? AND "
 948                                           "username_element = ? AND "
 949                                           "username_value = ? AND "
 950                                           "password_element = ? AND "
 951                                           "submit_element = ? AND "
 952                                           "signon_realm = ? "));
 953   s.BindString(0, form.origin.spec());
 954   s.BindString16(1, form.username_element);
 955   s.BindString16(2, form.username_value);
 956   s.BindString16(3, form.password_element);
 957   s.BindString16(4, form.submit_element);
 958   s.BindString(5, form.signon_realm);
 959
 960   return s.Run() && db_.GetLastChangeCount() > 0;
 961 }
 962
 963 bool LoginDatabase::RemoveLoginsCreatedBetween(base::Time delete_begin,
 964                                                base::Time delete_end) {
 965   sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
 966       "DELETE FROM logins WHERE "
 967       "date_created >= ? AND date_created < ?"));
 968   s.BindInt64(0, delete_begin.ToInternalValue());
 969   s.BindInt64(1, delete_end.is_null() ? std::numeric_limits<int64>::max()
 970                                       : delete_end.ToInternalValue());
 971
 972   return s.Run();
 973 }
 974
 975 bool LoginDatabase::RemoveLoginsSyncedBetween(base::Time delete_begin,
 976                                               base::Time delete_end) {
 977   sql::Statement s(db_.GetCachedStatement(
 978       SQL_FROM_HERE,
 979       "DELETE FROM logins WHERE date_synced >= ? AND date_synced < ?"));
 980   s.BindInt64(0, delete_begin.ToInternalValue());
 981   s.BindInt64(1,
 982               delete_end.is_null() ? base::Time::Max().ToInternalValue()
 983                                    : delete_end.ToInternalValue());
 984
 985   return s.Run();
 986 }
 987
 988 // static
 989 LoginDatabase::EncryptionResult LoginDatabase::InitPasswordFormFromStatement(
 990     PasswordForm* form,
 991     sql::Statement& s) {
 992   std::string encrypted_password;
 993   s.ColumnBlobAsString(COLUMN_PASSWORD_VALUE, &encrypted_password);
 994   base::string16 decrypted_password;
 995   EncryptionResult encryption_result =
 996       DecryptedString(encrypted_password, &decrypted_password);
 997   if (encryption_result != ENCRYPTION_RESULT_SUCCESS)
 998     return encryption_result;
 999
1000   std::string tmp = s.ColumnString(COLUMN_ORIGIN_URL);
1001   form->origin = GURL(tmp);
1002   tmp = s.ColumnString(COLUMN_ACTION_URL);
1003   form->action = GURL(tmp);
1004   form->username_element = s.ColumnString16(COLUMN_USERNAME_ELEMENT);
1005   form->username_value = s.ColumnString16(COLUMN_USERNAME_VALUE);
1006   form->password_element = s.ColumnString16(COLUMN_PASSWORD_ELEMENT);
1007   form->password_value = decrypted_password;
1008   form->submit_element = s.ColumnString16(COLUMN_SUBMIT_ELEMENT);
1009   tmp = s.ColumnString(COLUMN_SIGNON_REALM);
1010   form->signon_realm = tmp;
1011   form->ssl_valid = (s.ColumnInt(COLUMN_SSL_VALID) > 0);
1012   form->preferred = (s.ColumnInt(COLUMN_PREFERRED) > 0);
1013   form->date_created =
1014       base::Time::FromInternalValue(s.ColumnInt64(COLUMN_DATE_CREATED));
1015   form->blacklisted_by_user = (s.ColumnInt(COLUMN_BLACKLISTED_BY_USER) > 0);
1016   int scheme_int = s.ColumnInt(COLUMN_SCHEME);
1017   DCHECK((scheme_int >= 0) && (scheme_int <= PasswordForm::SCHEME_OTHER));
1018   form->scheme = static_cast<PasswordForm::Scheme>(scheme_int);
1019   int type_int = s.ColumnInt(COLUMN_PASSWORD_TYPE);
1020   DCHECK(type_int >= 0 && type_int <= PasswordForm::TYPE_GENERATED);
1021   form->type = static_cast<PasswordForm::Type>(type_int);
1022   if (s.ColumnByteLength(COLUMN_POSSIBLE_USERNAMES)) {
1023     base::Pickle pickle(
1024         static_cast<const char*>(s.ColumnBlob(COLUMN_POSSIBLE_USERNAMES)),
1025         s.ColumnByteLength(COLUMN_POSSIBLE_USERNAMES));
1026     form->other_possible_usernames = DeserializeVector(pickle);
1027   }
1028   form->times_used = s.ColumnInt(COLUMN_TIMES_USED);
1029   if (s.ColumnByteLength(COLUMN_FORM_DATA)) {
1030     base::Pickle form_data_pickle(
1031         static_cast<const char*>(s.ColumnBlob(COLUMN_FORM_DATA)),
1032         s.ColumnByteLength(COLUMN_FORM_DATA));
1033     base::PickleIterator form_data_iter(form_data_pickle);
1034     bool success =
1035         autofill::DeserializeFormData(&form_data_iter, &form->form_data);
1036     metrics_util::FormDeserializationStatus status =
1037         success ? metrics_util::LOGIN_DATABASE_SUCCESS
1038                 : metrics_util::LOGIN_DATABASE_FAILURE;
1039     metrics_util::LogFormDataDeserializationStatus(status);
1040   }
1041   form->date_synced =
1042       base::Time::FromInternalValue(s.ColumnInt64(COLUMN_DATE_SYNCED));
1043   form->display_name = s.ColumnString16(COLUMN_DISPLAY_NAME);
1044   form->icon_url = GURL(s.ColumnString(COLUMN_ICON_URL));
1045   form->federation_url = GURL(s.ColumnString(COLUMN_FEDERATION_URL));
1046   form->skip_zero_click = (s.ColumnInt(COLUMN_SKIP_ZERO_CLICK) > 0);
1047   int generation_upload_status_int =
1048       s.ColumnInt(COLUMN_GENERATION_UPLOAD_STATUS);
1049   DCHECK(generation_upload_status_int >= 0 &&
1050          generation_upload_status_int <= PasswordForm::UNKNOWN_STATUS);
1051   form->generation_upload_status =
1052       static_cast<PasswordForm::GenerationUploadStatus>(
1053           generation_upload_status_int);
1054   return ENCRYPTION_RESULT_SUCCESS;
1055 }
1056
1057 bool LoginDatabase::GetLogins(
1058     const PasswordForm& form,
1059     ScopedVector<autofill::PasswordForm>* forms) const {
1060   DCHECK(forms);
1061   // You *must* change LoginTableColumns if this query changes.
1062   const std::string sql_query =
1063       "SELECT origin_url, action_url, "
1064       "username_element, username_value, "
1065       "password_element, password_value, submit_element, "
1066       "signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
1067       "scheme, password_type, possible_usernames, times_used, form_data, "
1068       "date_synced, display_name, icon_url, "
1069       "federation_url, skip_zero_click, generation_upload_status "
1070       "FROM logins WHERE signon_realm == ? ";
1071   sql::Statement s;
1072   const GURL signon_realm(form.signon_realm);
1073   std::string registered_domain = GetRegistryControlledDomain(signon_realm);
1074   const bool should_PSL_matching_apply =
1075       form.scheme == PasswordForm::SCHEME_HTML &&
1076       ShouldPSLDomainMatchingApply(registered_domain);
1077   // PSL matching only applies to HTML forms.
1078   if (should_PSL_matching_apply) {
1079     // We are extending the original SQL query with one that includes more
1080     // possible matches based on public suffix domain matching. Using a regexp
1081     // here is just an optimization to not have to parse all the stored entries
1082     // in the |logins| table. The result (scheme, domain and port) is verified
1083     // further down using GURL. See the functions SchemeMatches,
1084     // RegistryControlledDomainMatches and PortMatches.
1085     const std::string extended_sql_query =
1086         sql_query + "OR signon_realm REGEXP ? ";
1087     // TODO(nyquist) Re-enable usage of GetCachedStatement when
1088     // http://crbug.com/248608 is fixed.
1089     s.Assign(db_.GetUniqueStatement(extended_sql_query.c_str()));
1090     // We need to escape . in the domain. Since the domain has already been
1091     // sanitized using GURL, we do not need to escape any other characters.
1092     base::ReplaceChars(registered_domain, ".", "\\.", &registered_domain);
1093     std::string scheme = signon_realm.scheme();
1094     // We need to escape . in the scheme. Since the scheme has already been
1095     // sanitized using GURL, we do not need to escape any other characters.
1096     // The scheme soap.beep is an example with '.'.
1097     base::ReplaceChars(scheme, ".", "\\.", &scheme);
1098     const std::string port = signon_realm.port();
1099     // For a signon realm such as http://foo.bar/, this regexp will match
1100     // domains on the form http://foo.bar/, http://www.foo.bar/,
1101     // http://www.mobile.foo.bar/. It will not match http://notfoo.bar/.
1102     // The scheme and port has to be the same as the observed form.
1103     std::string regexp = "^(" + scheme + ":\\/\\/)([\\w-]+\\.)*" +
1104                          registered_domain + "(:" + port + ")?\\/$";
1105     s.BindString(0, form.signon_realm);
1106     s.BindString(1, regexp);
1107   } else {
1108     UMA_HISTOGRAM_ENUMERATION("PasswordManager.PslDomainMatchTriggering",
1109                               PSL_DOMAIN_MATCH_NOT_USED,
1110                               PSL_DOMAIN_MATCH_COUNT);
1111     s.Assign(db_.GetCachedStatement(SQL_FROM_HERE, sql_query.c_str()));
1112     s.BindString(0, form.signon_realm);
1113   }
1114
1115   return StatementToForms(&s, should_PSL_matching_apply ? &form : nullptr,
1116                           forms);
1117 }
1118
1119 bool LoginDatabase::GetLoginsCreatedBetween(
1120     const base::Time begin,
1121     const base::Time end,
1122     ScopedVector<autofill::PasswordForm>* forms) const {
1123   DCHECK(forms);
1124   sql::Statement s(db_.GetCachedStatement(
1125       SQL_FROM_HERE,
1126       "SELECT origin_url, action_url, "
1127       "username_element, username_value, "
1128       "password_element, password_value, submit_element, "
1129       "signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
1130       "scheme, password_type, possible_usernames, times_used, form_data, "
1131       "date_synced, display_name, icon_url, "
1132       "federation_url, skip_zero_click, generation_upload_status FROM logins "
1133       "WHERE date_created >= ? AND date_created < ?"
1134       "ORDER BY origin_url"));
1135   s.BindInt64(0, begin.ToInternalValue());
1136   s.BindInt64(1, end.is_null() ? std::numeric_limits<int64>::max()
1137                                : end.ToInternalValue());
1138
1139   return StatementToForms(&s, nullptr, forms);
1140 }
1141
1142 bool LoginDatabase::GetLoginsSyncedBetween(
1143     const base::Time begin,
1144     const base::Time end,
1145     ScopedVector<autofill::PasswordForm>* forms) const {
1146   DCHECK(forms);
1147   sql::Statement s(db_.GetCachedStatement(
1148       SQL_FROM_HERE,
1149       "SELECT origin_url, action_url, username_element, username_value, "
1150       "password_element, password_value, submit_element, signon_realm, "
1151       "ssl_valid, preferred, date_created, blacklisted_by_user, "
1152       "scheme, password_type, possible_usernames, times_used, form_data, "
1153       "date_synced, display_name, icon_url, "
1154       "federation_url, skip_zero_click, generation_upload_status FROM logins "
1155       "WHERE date_synced >= ? AND date_synced < ?"
1156       "ORDER BY origin_url"));
1157   s.BindInt64(0, begin.ToInternalValue());
1158   s.BindInt64(1,
1159               end.is_null() ? base::Time::Max().ToInternalValue()
1160                             : end.ToInternalValue());
1161
1162   return StatementToForms(&s, nullptr, forms);
1163 }
1164
1165 bool LoginDatabase::GetAutofillableLogins(
1166     ScopedVector<autofill::PasswordForm>* forms) const {
1167   return GetAllLoginsWithBlacklistSetting(false, forms);
1168 }
1169
1170 bool LoginDatabase::GetBlacklistLogins(
1171     ScopedVector<autofill::PasswordForm>* forms) const {
1172   return GetAllLoginsWithBlacklistSetting(true, forms);
1173 }
1174
1175 bool LoginDatabase::GetAllLoginsWithBlacklistSetting(
1176     bool blacklisted,
1177     ScopedVector<autofill::PasswordForm>* forms) const {
1178   DCHECK(forms);
1179   // You *must* change LoginTableColumns if this query changes.
1180   sql::Statement s(db_.GetCachedStatement(
1181       SQL_FROM_HERE,
1182       "SELECT origin_url, action_url, "
1183       "username_element, username_value, "
1184       "password_element, password_value, submit_element, "
1185       "signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
1186       "scheme, password_type, possible_usernames, times_used, form_data, "
1187       "date_synced, display_name, icon_url, "
1188       "federation_url, skip_zero_click, generation_upload_status FROM logins "
1189       "WHERE blacklisted_by_user == ? ORDER BY origin_url"));
1190   s.BindInt(0, blacklisted ? 1 : 0);
1191
1192   return StatementToForms(&s, nullptr, forms);
1193 }
1194
1195 bool LoginDatabase::DeleteAndRecreateDatabaseFile() {
1196   DCHECK(db_.is_open());
1197   meta_table_.Reset();
1198   db_.Close();
1199   sql::Connection::Delete(db_path_);
1200   return Init();
1201 }
1202
1203 // static
1204 bool LoginDatabase::StatementToForms(
1205     sql::Statement* statement,
1206     const autofill::PasswordForm* psl_match,
1207     ScopedVector<autofill::PasswordForm>* forms) {
1208   PSLDomainMatchMetric psl_domain_match_metric = PSL_DOMAIN_MATCH_NONE;
1209
1210   forms->clear();
1211   while (statement->Step()) {
1212     scoped_ptr<PasswordForm> new_form(new PasswordForm());
1213     EncryptionResult result =
1214         InitPasswordFormFromStatement(new_form.get(), *statement);
1215     if (result == ENCRYPTION_RESULT_SERVICE_FAILURE)
1216       return false;
1217     if (result == ENCRYPTION_RESULT_ITEM_FAILURE)
1218       continue;
1219     DCHECK(result == ENCRYPTION_RESULT_SUCCESS);
1220     if (psl_match && psl_match->signon_realm != new_form->signon_realm) {
1221       if (new_form->scheme != PasswordForm::SCHEME_HTML)
1222         continue;  // Ignore non-HTML matches.
1223
1224       if (!IsPublicSuffixDomainMatch(new_form->signon_realm,
1225                                      psl_match->signon_realm)) {
1226         continue;
1227       }
1228
1229       psl_domain_match_metric = PSL_DOMAIN_MATCH_FOUND;
1230       // This is not a perfect match, so we need to create a new valid result.
1231       // We do this by copying over origin, signon realm and action from the
1232       // observed form and setting the original signon realm to what we found
1233       // in the database. We use the fact that |original_signon_realm| is
1234       // non-empty to communicate that this match was found using public
1235       // suffix matching.
1236       new_form->original_signon_realm = new_form->signon_realm;
1237       new_form->origin = psl_match->origin;
1238       new_form->signon_realm = psl_match->signon_realm;
1239       new_form->action = psl_match->action;
1240     }
1241     forms->push_back(new_form.Pass());
1242   }
1243
1244   if (psl_match) {
1245     UMA_HISTOGRAM_ENUMERATION("PasswordManager.PslDomainMatchTriggering",
1246                               psl_domain_match_metric, PSL_DOMAIN_MATCH_COUNT);
1247   }
1248
1249   if (!statement->Succeeded())
1250     return false;
1251   return true;
1252 }
1253
1254 }  // namespace password_manager