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