src/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.h"
  14 #include "base/pickle.h"
  15 #include "base/strings/string_util.h"
  16 #include "base/time/time.h"
  17 #include "components/autofill/core/common/password_form.h"
  18 #include "google_apis/gaia/gaia_auth_util.h"
  19 #include "google_apis/gaia/gaia_urls.h"
  20 #include "sql/connection.h"
  21 #include "sql/statement.h"
  22 #include "sql/transaction.h"
  23
  24 using autofill::PasswordForm;
  25
  26 namespace password_manager {
  27
  28 static const int kCurrentVersionNumber = 7;
  29 static const int kCompatibleVersionNumber = 1;
  30
  31 Pickle SerializeVector(const std::vector<base::string16>& vec) {
  32   Pickle p;
  33   for (size_t i = 0; i < vec.size(); ++i) {
  34     p.WriteString16(vec[i]);
  35   }
  36   return p;
  37 }
  38
  39 std::vector<base::string16> DeserializeVector(const Pickle& p) {
  40   std::vector<base::string16> ret;
  41   base::string16 str;
  42
  43   PickleIterator iterator(p);
  44   while (iterator.ReadString16(&str)) {
  45     ret.push_back(str);
  46   }
  47   return ret;
  48 }
  49
  50 namespace {
  51
  52 // Convenience enum for interacting with SQL queries that use all the columns.
  53 enum LoginTableColumns {
  54   COLUMN_ORIGIN_URL = 0,
  55   COLUMN_ACTION_URL,
  56   COLUMN_USERNAME_ELEMENT,
  57   COLUMN_USERNAME_VALUE,
  58   COLUMN_PASSWORD_ELEMENT,
  59   COLUMN_PASSWORD_VALUE,
  60   COLUMN_SUBMIT_ELEMENT,
  61   COLUMN_SIGNON_REALM,
  62   COLUMN_SSL_VALID,
  63   COLUMN_PREFERRED,
  64   COLUMN_DATE_CREATED,
  65   COLUMN_BLACKLISTED_BY_USER,
  66   COLUMN_SCHEME,
  67   COLUMN_PASSWORD_TYPE,
  68   COLUMN_POSSIBLE_USERNAMES,
  69   COLUMN_TIMES_USED,
  70   COLUMN_FORM_DATA,
  71   COLUMN_USE_ADDITIONAL_AUTH,
  72   COLUMN_DATE_SYNCED,
  73   COLUMN_DISPLAY_NAME,
  74   COLUMN_AVATAR_URL,
  75   COLUMN_FEDERATION_URL,
  76   COLUMN_IS_ZERO_CLICK,
  77 };
  78
  79 void BindAddStatement(const PasswordForm& form,
  80                       const std::string& encrypted_password,
  81                       sql::Statement* s) {
  82   s->BindString(COLUMN_ORIGIN_URL, form.origin.spec());
  83   s->BindString(COLUMN_ACTION_URL, form.action.spec());
  84   s->BindString16(COLUMN_USERNAME_ELEMENT, form.username_element);
  85   s->BindString16(COLUMN_USERNAME_VALUE, form.username_value);
  86   s->BindString16(COLUMN_PASSWORD_ELEMENT, form.password_element);
  87   s->BindBlob(COLUMN_PASSWORD_VALUE, encrypted_password.data(),
  88               static_cast<int>(encrypted_password.length()));
  89   s->BindString16(COLUMN_SUBMIT_ELEMENT, form.submit_element);
  90   s->BindString(COLUMN_SIGNON_REALM, form.signon_realm);
  91   s->BindInt(COLUMN_SSL_VALID, form.ssl_valid);
  92   s->BindInt(COLUMN_PREFERRED, form.preferred);
  93   s->BindInt64(COLUMN_DATE_CREATED, form.date_created.ToTimeT());
  94   s->BindInt(COLUMN_BLACKLISTED_BY_USER, form.blacklisted_by_user);
  95   s->BindInt(COLUMN_SCHEME, form.scheme);
  96   s->BindInt(COLUMN_PASSWORD_TYPE, form.type);
  97   Pickle usernames_pickle = SerializeVector(form.other_possible_usernames);
  98   s->BindBlob(COLUMN_POSSIBLE_USERNAMES,
  99               usernames_pickle.data(),
 100               usernames_pickle.size());
 101   s->BindInt(COLUMN_TIMES_USED, form.times_used);
 102   Pickle form_data_pickle;
 103   autofill::SerializeFormData(form.form_data, &form_data_pickle);
 104   s->BindBlob(COLUMN_FORM_DATA,
 105               form_data_pickle.data(),
 106               form_data_pickle.size());
 107   s->BindInt(COLUMN_USE_ADDITIONAL_AUTH, form.use_additional_authentication);
 108   s->BindInt64(COLUMN_DATE_SYNCED, form.date_synced.ToInternalValue());
 109   s->BindString16(COLUMN_DISPLAY_NAME, form.display_name);
 110   s->BindString(COLUMN_AVATAR_URL, form.avatar_url.spec());
 111   s->BindString(COLUMN_FEDERATION_URL, form.federation_url.spec());
 112   s->BindInt(COLUMN_IS_ZERO_CLICK, form.is_zero_click);
 113 }
 114
 115 void AddCallback(int err, sql::Statement* /*stmt*/) {
 116   if (err == 19 /*SQLITE_CONSTRAINT*/)
 117     DLOG(WARNING) << "LoginDatabase::AddLogin updated an existing form";
 118 }
 119
 120 }  // namespace
 121
 122 LoginDatabase::LoginDatabase() {
 123 }
 124
 125 LoginDatabase::~LoginDatabase() {
 126 }
 127
 128 bool LoginDatabase::Init(const base::FilePath& db_path) {
 129   // Set pragmas for a small, private database (based on WebDatabase).
 130   db_.set_page_size(2048);
 131   db_.set_cache_size(32);
 132   db_.set_exclusive_locking();
 133   db_.set_restrict_to_user();
 134
 135   if (!db_.Open(db_path)) {
 136     LOG(WARNING) << "Unable to open the password store database.";
 137     return false;
 138   }
 139
 140   sql::Transaction transaction(&db_);
 141   transaction.Begin();
 142
 143   // Check the database version.
 144   if (!meta_table_.Init(&db_, kCurrentVersionNumber,
 145                         kCompatibleVersionNumber)) {
 146     db_.Close();
 147     return false;
 148   }
 149   if (meta_table_.GetCompatibleVersionNumber() > kCurrentVersionNumber) {
 150     LOG(WARNING) << "Password store database is too new.";
 151     db_.Close();
 152     return false;
 153   }
 154
 155   // Initialize the tables.
 156   if (!InitLoginsTable()) {
 157     LOG(WARNING) << "Unable to initialize the password store database.";
 158     db_.Close();
 159     return false;
 160   }
 161
 162   // Save the path for DeleteDatabaseFile().
 163   db_path_ = db_path;
 164
 165   // If the file on disk is an older database version, bring it up to date.
 166   if (!MigrateOldVersionsAsNeeded()) {
 167     LOG(WARNING) << "Unable to migrate database";
 168     db_.Close();
 169     return false;
 170   }
 171
 172   if (!transaction.Commit()) {
 173     db_.Close();
 174     return false;
 175   }
 176
 177   return true;
 178 }
 179
 180 bool LoginDatabase::MigrateOldVersionsAsNeeded() {
 181   switch (meta_table_.GetVersionNumber()) {
 182     case 1:
 183       if (!db_.Execute("ALTER TABLE logins "
 184                        "ADD COLUMN password_type INTEGER") ||
 185           !db_.Execute("ALTER TABLE logins "
 186                        "ADD COLUMN possible_usernames BLOB")) {
 187         return false;
 188       }
 189       meta_table_.SetVersionNumber(2);
 190       // Fall through.
 191     case 2:
 192       if (!db_.Execute("ALTER TABLE logins ADD COLUMN times_used INTEGER")) {
 193         return false;
 194       }
 195       meta_table_.SetVersionNumber(3);
 196       // Fall through.
 197     case 3:
 198       // We need to check if the column exists because of
 199       // https://crbug.com/295851
 200       if (!db_.DoesColumnExist("logins", "form_data") &&
 201           !db_.Execute("ALTER TABLE logins ADD COLUMN form_data BLOB")) {
 202         return false;
 203       }
 204       meta_table_.SetVersionNumber(4);
 205       // Fall through.
 206     case 4:
 207       if (!db_.Execute(
 208           "ALTER TABLE logins ADD COLUMN use_additional_auth INTEGER")) {
 209         return false;
 210       }
 211       meta_table_.SetVersionNumber(5);
 212       // Fall through.
 213     case 5:
 214       if (!db_.Execute("ALTER TABLE logins ADD COLUMN date_synced INTEGER")) {
 215         return false;
 216       }
 217       meta_table_.SetVersionNumber(6);
 218       // Fall through.
 219     case 6:
 220       if (!db_.Execute("ALTER TABLE logins ADD COLUMN display_name VARCHAR") ||
 221           !db_.Execute("ALTER TABLE logins ADD COLUMN avatar_url VARCHAR") ||
 222           !db_.Execute("ALTER TABLE logins "
 223                        "ADD COLUMN federation_url VARCHAR") ||
 224           !db_.Execute("ALTER TABLE logins ADD COLUMN is_zero_click INTEGER")) {
 225         return false;
 226       }
 227       meta_table_.SetVersionNumber(7);
 228       // Fall through.
 229     case kCurrentVersionNumber:
 230       // Already up to date
 231       return true;
 232     default:
 233       NOTREACHED();
 234       return false;
 235   }
 236 }
 237
 238 bool LoginDatabase::InitLoginsTable() {
 239   if (!db_.DoesTableExist("logins")) {
 240     if (!db_.Execute("CREATE TABLE logins ("
 241                      "origin_url VARCHAR NOT NULL, "
 242                      "action_url VARCHAR, "
 243                      "username_element VARCHAR, "
 244                      "username_value VARCHAR, "
 245                      "password_element VARCHAR, "
 246                      "password_value BLOB, "
 247                      "submit_element VARCHAR, "
 248                      "signon_realm VARCHAR NOT NULL,"
 249                      "ssl_valid INTEGER NOT NULL,"
 250                      "preferred INTEGER NOT NULL,"
 251                      "date_created INTEGER NOT NULL,"
 252                      "blacklisted_by_user INTEGER NOT NULL,"
 253                      "scheme INTEGER NOT NULL,"
 254                      "password_type INTEGER,"
 255                      "possible_usernames BLOB,"
 256                      "times_used INTEGER,"
 257                      "form_data BLOB,"
 258                      "use_additional_auth INTEGER,"
 259                      "date_synced INTEGER,"
 260                      "display_name VARCHAR,"
 261                      "avatar_url VARCHAR,"
 262                      "federation_url VARCHAR,"
 263                      "is_zero_click INTEGER,"
 264                      "UNIQUE "
 265                      "(origin_url, username_element, "
 266                      "username_value, password_element, "
 267                      "submit_element, signon_realm))")) {
 268       NOTREACHED();
 269       return false;
 270     }
 271     if (!db_.Execute("CREATE INDEX logins_signon ON "
 272                      "logins (signon_realm)")) {
 273       NOTREACHED();
 274       return false;
 275     }
 276   }
 277   return true;
 278 }
 279
 280 void LoginDatabase::ReportMetrics(const std::string& sync_username) {
 281   sql::Statement s(db_.GetCachedStatement(
 282       SQL_FROM_HERE,
 283       "SELECT signon_realm, blacklisted_by_user, COUNT(username_value) "
 284       "FROM logins GROUP BY signon_realm, blacklisted_by_user"));
 285
 286   if (!s.is_valid())
 287     return;
 288
 289   int total_accounts = 0;
 290   int blacklisted_sites = 0;
 291   while (s.Step()) {
 292     int blacklisted = s.ColumnInt(1);
 293     int accounts_per_site = s.ColumnInt(2);
 294     if (blacklisted) {
 295       ++blacklisted_sites;
 296     } else {
 297       total_accounts += accounts_per_site;
 298       UMA_HISTOGRAM_CUSTOM_COUNTS("PasswordManager.AccountsPerSite",
 299                                   accounts_per_site, 0, 32, 6);
 300     }
 301   }
 302   UMA_HISTOGRAM_CUSTOM_COUNTS("PasswordManager.TotalAccounts",
 303                               total_accounts, 0, 32, 6);
 304   UMA_HISTOGRAM_CUSTOM_COUNTS("PasswordManager.BlacklistedSites",
 305                               blacklisted_sites, 0, 32, 6);
 306
 307   sql::Statement usage_statement(db_.GetCachedStatement(
 308       SQL_FROM_HERE,
 309       "SELECT password_type, times_used FROM logins"));
 310
 311   if (!usage_statement.is_valid())
 312     return;
 313
 314   while (usage_statement.Step()) {
 315     PasswordForm::Type type = static_cast<PasswordForm::Type>(
 316         usage_statement.ColumnInt(0));
 317
 318     if (type == PasswordForm::TYPE_GENERATED) {
 319       UMA_HISTOGRAM_CUSTOM_COUNTS(
 320           "PasswordManager.TimesGeneratedPasswordUsed",
 321           usage_statement.ColumnInt(1), 0, 100, 10);
 322     } else {
 323       UMA_HISTOGRAM_CUSTOM_COUNTS(
 324           "PasswordManager.TimesPasswordUsed",
 325           usage_statement.ColumnInt(1), 0, 100, 10);
 326     }
 327   }
 328
 329   bool syncing_account_saved = false;
 330   if (!sync_username.empty()) {
 331     sql::Statement sync_statement(db_.GetCachedStatement(
 332         SQL_FROM_HERE,
 333         "SELECT username_value FROM logins "
 334         "WHERE signon_realm == ?"));
 335     sync_statement.BindString(
 336         0, GaiaUrls::GetInstance()->gaia_url().GetOrigin().spec());
 337
 338     if (!sync_statement.is_valid())
 339       return;
 340
 341     while (sync_statement.Step()) {
 342       std::string username = sync_statement.ColumnString(0);
 343       if (gaia::AreEmailsSame(sync_username, username)) {
 344         syncing_account_saved = true;
 345         break;
 346       }
 347     }
 348   }
 349   UMA_HISTOGRAM_ENUMERATION("PasswordManager.SyncingAccountState",
 350                             2 * sync_username.empty() + syncing_account_saved,
 351                             4);
 352 }
 353
 354 PasswordStoreChangeList LoginDatabase::AddLogin(const PasswordForm& form) {
 355   PasswordStoreChangeList list;
 356   std::string encrypted_password;
 357   if (EncryptedString(form.password_value, &encrypted_password) !=
 358           ENCRYPTION_RESULT_SUCCESS)
 359     return list;
 360
 361   // You *must* change LoginTableColumns if this query changes.
 362   sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
 363       "INSERT INTO logins "
 364       "(origin_url, action_url, username_element, username_value, "
 365       " password_element, password_value, submit_element, "
 366       " signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
 367       " scheme, password_type, possible_usernames, times_used, form_data, "
 368       " use_additional_auth, date_synced, display_name, avatar_url,"
 369       " federation_url, is_zero_click) VALUES "
 370       "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"));
 371   BindAddStatement(form, encrypted_password, &s);
 372   db_.set_error_callback(base::Bind(&AddCallback));
 373   const bool success = s.Run();
 374   db_.reset_error_callback();
 375   if (success) {
 376     list.push_back(PasswordStoreChange(PasswordStoreChange::ADD, form));
 377     return list;
 378   }
 379   // Repeat the same statement but with REPLACE semantic.
 380   s.Assign(db_.GetCachedStatement(SQL_FROM_HERE,
 381       "INSERT OR REPLACE INTO logins "
 382       "(origin_url, action_url, username_element, username_value, "
 383       " password_element, password_value, submit_element, "
 384       " signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
 385       " scheme, password_type, possible_usernames, times_used, form_data, "
 386       " use_additional_auth, date_synced, display_name, avatar_url,"
 387       " federation_url, is_zero_click) VALUES "
 388       "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"));
 389   BindAddStatement(form, encrypted_password, &s);
 390   if (s.Run()) {
 391     list.push_back(PasswordStoreChange(PasswordStoreChange::REMOVE, form));
 392     list.push_back(PasswordStoreChange(PasswordStoreChange::ADD, form));
 393   }
 394   return list;
 395 }
 396
 397 PasswordStoreChangeList LoginDatabase::UpdateLogin(const PasswordForm& form) {
 398   std::string encrypted_password;
 399   if (EncryptedString(form.password_value, &encrypted_password) !=
 400           ENCRYPTION_RESULT_SUCCESS)
 401     return PasswordStoreChangeList();
 402
 403   // Replacement is necessary to deal with updating imported credentials. See
 404   // crbug.com/349138 for details.
 405   sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
 406                                           "UPDATE OR REPLACE logins SET "
 407                                           "action_url = ?, "
 408                                           "password_value = ?, "
 409                                           "ssl_valid = ?, "
 410                                           "preferred = ?, "
 411                                           "possible_usernames = ?, "
 412                                           "times_used = ?, "
 413                                           "submit_element = ?, "
 414                                           "date_synced = ?, "
 415                                           "date_created = ?, "
 416                                           "blacklisted_by_user = ?, "
 417                                           "scheme = ?, "
 418                                           "password_type = ?, "
 419                                           "display_name = ?, "
 420                                           "avatar_url = ?, "
 421                                           "federation_url = ?, "
 422                                           "is_zero_click = ? "
 423                                           "WHERE origin_url = ? AND "
 424                                           "username_element = ? AND "
 425                                           "username_value = ? AND "
 426                                           "password_element = ? AND "
 427                                           "signon_realm = ?"));
 428   s.BindString(0, form.action.spec());
 429   s.BindBlob(1, encrypted_password.data(),
 430              static_cast<int>(encrypted_password.length()));
 431   s.BindInt(2, form.ssl_valid);
 432   s.BindInt(3, form.preferred);
 433   Pickle pickle = SerializeVector(form.other_possible_usernames);
 434   s.BindBlob(4, pickle.data(), pickle.size());
 435   s.BindInt(5, form.times_used);
 436   s.BindString16(6, form.submit_element);
 437   s.BindInt64(7, form.date_synced.ToInternalValue());
 438   s.BindInt64(8, form.date_created.ToTimeT());
 439   s.BindInt(9, form.blacklisted_by_user);
 440   s.BindInt(10, form.scheme);
 441   s.BindInt(11, form.type);
 442   s.BindString16(12, form.display_name);
 443   s.BindString(13, form.avatar_url.spec());
 444   s.BindString(14, form.federation_url.spec());
 445   s.BindInt(15, form.is_zero_click);
 446
 447   // WHERE starts here.
 448   s.BindString(16, form.origin.spec());
 449   s.BindString16(17, form.username_element);
 450   s.BindString16(18, form.username_value);
 451   s.BindString16(19, form.password_element);
 452   s.BindString(20, form.signon_realm);
 453
 454   if (!s.Run())
 455     return PasswordStoreChangeList();
 456
 457   PasswordStoreChangeList list;
 458   if (db_.GetLastChangeCount())
 459     list.push_back(PasswordStoreChange(PasswordStoreChange::UPDATE, form));
 460
 461   return list;
 462 }
 463
 464 bool LoginDatabase::RemoveLogin(const PasswordForm& form) {
 465   // Remove a login by UNIQUE-constrained fields.
 466   sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
 467       "DELETE FROM logins WHERE "
 468       "origin_url = ? AND "
 469       "username_element = ? AND "
 470       "username_value = ? AND "
 471       "password_element = ? AND "
 472       "submit_element = ? AND "
 473       "signon_realm = ? "));
 474   s.BindString(0, form.origin.spec());
 475   s.BindString16(1, form.username_element);
 476   s.BindString16(2, form.username_value);
 477   s.BindString16(3, form.password_element);
 478   s.BindString16(4, form.submit_element);
 479   s.BindString(5, form.signon_realm);
 480
 481   return s.Run();
 482 }
 483
 484 bool LoginDatabase::RemoveLoginsCreatedBetween(base::Time delete_begin,
 485                                                base::Time delete_end) {
 486   sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
 487       "DELETE FROM logins WHERE "
 488       "date_created >= ? AND date_created < ?"));
 489   s.BindInt64(0, delete_begin.ToTimeT());
 490   s.BindInt64(1, delete_end.is_null() ? std::numeric_limits<int64>::max()
 491                                       : delete_end.ToTimeT());
 492
 493   return s.Run();
 494 }
 495
 496 bool LoginDatabase::RemoveLoginsSyncedBetween(base::Time delete_begin,
 497                                               base::Time delete_end) {
 498   sql::Statement s(db_.GetCachedStatement(
 499       SQL_FROM_HERE,
 500       "DELETE FROM logins WHERE date_synced >= ? AND date_synced < ?"));
 501   s.BindInt64(0, delete_begin.ToInternalValue());
 502   s.BindInt64(1,
 503               delete_end.is_null() ? base::Time::Max().ToInternalValue()
 504                                    : delete_end.ToInternalValue());
 505
 506   return s.Run();
 507 }
 508
 509 LoginDatabase::EncryptionResult LoginDatabase::InitPasswordFormFromStatement(
 510     PasswordForm* form,
 511     sql::Statement& s) const {
 512   std::string encrypted_password;
 513   s.ColumnBlobAsString(COLUMN_PASSWORD_VALUE, &encrypted_password);
 514   base::string16 decrypted_password;
 515   EncryptionResult encryption_result =
 516       DecryptedString(encrypted_password, &decrypted_password);
 517   if (encryption_result != ENCRYPTION_RESULT_SUCCESS)
 518     return encryption_result;
 519
 520   std::string tmp = s.ColumnString(COLUMN_ORIGIN_URL);
 521   form->origin = GURL(tmp);
 522   tmp = s.ColumnString(COLUMN_ACTION_URL);
 523   form->action = GURL(tmp);
 524   form->username_element = s.ColumnString16(COLUMN_USERNAME_ELEMENT);
 525   form->username_value = s.ColumnString16(COLUMN_USERNAME_VALUE);
 526   form->password_element = s.ColumnString16(COLUMN_PASSWORD_ELEMENT);
 527   form->password_value = decrypted_password;
 528   form->submit_element = s.ColumnString16(COLUMN_SUBMIT_ELEMENT);
 529   tmp = s.ColumnString(COLUMN_SIGNON_REALM);
 530   form->signon_realm = tmp;
 531   form->ssl_valid = (s.ColumnInt(COLUMN_SSL_VALID) > 0);
 532   form->preferred = (s.ColumnInt(COLUMN_PREFERRED) > 0);
 533   form->date_created = base::Time::FromTimeT(
 534       s.ColumnInt64(COLUMN_DATE_CREATED));
 535   form->blacklisted_by_user = (s.ColumnInt(COLUMN_BLACKLISTED_BY_USER) > 0);
 536   int scheme_int = s.ColumnInt(COLUMN_SCHEME);
 537   DCHECK((scheme_int >= 0) && (scheme_int <= PasswordForm::SCHEME_OTHER));
 538   form->scheme = static_cast<PasswordForm::Scheme>(scheme_int);
 539   int type_int = s.ColumnInt(COLUMN_PASSWORD_TYPE);
 540   DCHECK(type_int >= 0 && type_int <= PasswordForm::TYPE_GENERATED);
 541   form->type = static_cast<PasswordForm::Type>(type_int);
 542   if (s.ColumnByteLength(COLUMN_POSSIBLE_USERNAMES)) {
 543     Pickle pickle(
 544         static_cast<const char*>(s.ColumnBlob(COLUMN_POSSIBLE_USERNAMES)),
 545         s.ColumnByteLength(COLUMN_POSSIBLE_USERNAMES));
 546     form->other_possible_usernames = DeserializeVector(pickle);
 547   }
 548   form->times_used = s.ColumnInt(COLUMN_TIMES_USED);
 549   if (s.ColumnByteLength(COLUMN_FORM_DATA)) {
 550     Pickle form_data_pickle(
 551         static_cast<const char*>(s.ColumnBlob(COLUMN_FORM_DATA)),
 552         s.ColumnByteLength(COLUMN_FORM_DATA));
 553     PickleIterator form_data_iter(form_data_pickle);
 554     autofill::DeserializeFormData(&form_data_iter, &form->form_data);
 555   }
 556   form->use_additional_authentication =
 557       (s.ColumnInt(COLUMN_USE_ADDITIONAL_AUTH) > 0);
 558   form->date_synced = base::Time::FromInternalValue(
 559       s.ColumnInt64(COLUMN_DATE_SYNCED));
 560   form->display_name = s.ColumnString16(COLUMN_DISPLAY_NAME);
 561   form->avatar_url = GURL(s.ColumnString(COLUMN_AVATAR_URL));
 562   form->federation_url = GURL(s.ColumnString(COLUMN_FEDERATION_URL));
 563   form->is_zero_click = (s.ColumnInt(COLUMN_IS_ZERO_CLICK) > 0);
 564   return ENCRYPTION_RESULT_SUCCESS;
 565 }
 566
 567 bool LoginDatabase::GetLogins(const PasswordForm& form,
 568                               std::vector<PasswordForm*>* forms) const {
 569   DCHECK(forms);
 570   // You *must* change LoginTableColumns if this query changes.
 571   const std::string sql_query = "SELECT origin_url, action_url, "
 572       "username_element, username_value, "
 573       "password_element, password_value, submit_element, "
 574       "signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
 575       "scheme, password_type, possible_usernames, times_used, form_data, "
 576       "use_additional_auth, date_synced, display_name, avatar_url, "
 577       "federation_url, is_zero_click FROM logins WHERE signon_realm == ? ";
 578   sql::Statement s;
 579   const GURL signon_realm(form.signon_realm);
 580   std::string registered_domain = GetRegistryControlledDomain(signon_realm);
 581   PSLDomainMatchMetric psl_domain_match_metric = PSL_DOMAIN_MATCH_NONE;
 582   const bool should_PSL_matching_apply =
 583       ShouldPSLDomainMatchingApply(registered_domain);
 584   // PSL matching only applies to HTML forms.
 585   if (form.scheme == PasswordForm::SCHEME_HTML && should_PSL_matching_apply) {
 586     // We are extending the original SQL query with one that includes more
 587     // possible matches based on public suffix domain matching. Using a regexp
 588     // here is just an optimization to not have to parse all the stored entries
 589     // in the |logins| table. The result (scheme, domain and port) is verified
 590     // further down using GURL. See the functions SchemeMatches,
 591     // RegistryControlledDomainMatches and PortMatches.
 592     const std::string extended_sql_query =
 593         sql_query + "OR signon_realm REGEXP ? ";
 594     // TODO(nyquist) Re-enable usage of GetCachedStatement when
 595     // http://crbug.com/248608 is fixed.
 596     s.Assign(db_.GetUniqueStatement(extended_sql_query.c_str()));
 597     // We need to escape . in the domain. Since the domain has already been
 598     // sanitized using GURL, we do not need to escape any other characters.
 599     base::ReplaceChars(registered_domain, ".", "\\.", &registered_domain);
 600     std::string scheme = signon_realm.scheme();
 601     // We need to escape . in the scheme. Since the scheme has already been
 602     // sanitized using GURL, we do not need to escape any other characters.
 603     // The scheme soap.beep is an example with '.'.
 604     base::ReplaceChars(scheme, ".", "\\.", &scheme);
 605     const std::string port = signon_realm.port();
 606     // For a signon realm such as http://foo.bar/, this regexp will match
 607     // domains on the form http://foo.bar/, http://www.foo.bar/,
 608     // http://www.mobile.foo.bar/. It will not match http://notfoo.bar/.
 609     // The scheme and port has to be the same as the observed form.
 610     std::string regexp = "^(" + scheme + ":\\/\\/)([\\w-]+\\.)*" +
 611                          registered_domain + "(:" + port + ")?\\/$";
 612     s.BindString(0, form.signon_realm);
 613     s.BindString(1, regexp);
 614   } else {
 615     psl_domain_match_metric = PSL_DOMAIN_MATCH_NOT_USED;
 616     s.Assign(db_.GetCachedStatement(SQL_FROM_HERE, sql_query.c_str()));
 617     s.BindString(0, form.signon_realm);
 618   }
 619
 620   while (s.Step()) {
 621     scoped_ptr<PasswordForm> new_form(new PasswordForm());
 622     EncryptionResult result = InitPasswordFormFromStatement(new_form.get(), s);
 623     if (result == ENCRYPTION_RESULT_SERVICE_FAILURE)
 624       return false;
 625     if (result == ENCRYPTION_RESULT_ITEM_FAILURE)
 626       continue;
 627     DCHECK(result == ENCRYPTION_RESULT_SUCCESS);
 628     if (should_PSL_matching_apply) {
 629       if (!IsPublicSuffixDomainMatch(new_form->signon_realm,
 630                                      form.signon_realm)) {
 631         // The database returned results that should not match. Skipping result.
 632         continue;
 633       }
 634       if (form.signon_realm != new_form->signon_realm) {
 635         // Ignore non-HTML matches.
 636         if (new_form->scheme != PasswordForm::SCHEME_HTML)
 637           continue;
 638
 639         psl_domain_match_metric = PSL_DOMAIN_MATCH_FOUND;
 640         // This is not a perfect match, so we need to create a new valid result.
 641         // We do this by copying over origin, signon realm and action from the
 642         // observed form and setting the original signon realm to what we found
 643         // in the database. We use the fact that |original_signon_realm| is
 644         // non-empty to communicate that this match was found using public
 645         // suffix matching.
 646         new_form->original_signon_realm = new_form->signon_realm;
 647         new_form->origin = form.origin;
 648         new_form->signon_realm = form.signon_realm;
 649         new_form->action = form.action;
 650       }
 651     }
 652     forms->push_back(new_form.release());
 653   }
 654   UMA_HISTOGRAM_ENUMERATION("PasswordManager.PslDomainMatchTriggering",
 655                             psl_domain_match_metric,
 656                             PSL_DOMAIN_MATCH_COUNT);
 657   return s.Succeeded();
 658 }
 659
 660 bool LoginDatabase::GetLoginsCreatedBetween(
 661     const base::Time begin,
 662     const base::Time end,
 663     std::vector<autofill::PasswordForm*>* forms) const {
 664   DCHECK(forms);
 665   sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
 666       "SELECT origin_url, action_url, "
 667       "username_element, username_value, "
 668       "password_element, password_value, submit_element, "
 669       "signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
 670       "scheme, password_type, possible_usernames, times_used, form_data, "
 671       "use_additional_auth, date_synced, display_name, avatar_url, "
 672       "federation_url, is_zero_click FROM logins "
 673       "WHERE date_created >= ? AND date_created < ?"
 674       "ORDER BY origin_url"));
 675   s.BindInt64(0, begin.ToTimeT());
 676   s.BindInt64(1, end.is_null() ? std::numeric_limits<int64>::max()
 677                                : end.ToTimeT());
 678
 679   while (s.Step()) {
 680     scoped_ptr<PasswordForm> new_form(new PasswordForm());
 681     EncryptionResult result = InitPasswordFormFromStatement(new_form.get(), s);
 682     if (result == ENCRYPTION_RESULT_SERVICE_FAILURE)
 683       return false;
 684     if (result == ENCRYPTION_RESULT_ITEM_FAILURE)
 685       continue;
 686     DCHECK(result == ENCRYPTION_RESULT_SUCCESS);
 687     forms->push_back(new_form.release());
 688   }
 689   return s.Succeeded();
 690 }
 691
 692 bool LoginDatabase::GetLoginsSyncedBetween(
 693     const base::Time begin,
 694     const base::Time end,
 695     std::vector<autofill::PasswordForm*>* forms) const {
 696   DCHECK(forms);
 697   sql::Statement s(db_.GetCachedStatement(
 698       SQL_FROM_HERE,
 699       "SELECT origin_url, action_url, username_element, username_value, "
 700       "password_element, password_value, submit_element, signon_realm, "
 701       "ssl_valid, preferred, date_created, blacklisted_by_user, "
 702       "scheme, password_type, possible_usernames, times_used, form_data, "
 703       "use_additional_auth, date_synced, display_name, avatar_url, "
 704       "federation_url, is_zero_click FROM logins "
 705       "WHERE date_synced >= ? AND date_synced < ?"
 706       "ORDER BY origin_url"));
 707   s.BindInt64(0, begin.ToInternalValue());
 708   s.BindInt64(1,
 709               end.is_null() ? base::Time::Max().ToInternalValue()
 710                             : end.ToInternalValue());
 711
 712   while (s.Step()) {
 713     scoped_ptr<PasswordForm> new_form(new PasswordForm());
 714     EncryptionResult result = InitPasswordFormFromStatement(new_form.get(), s);
 715     if (result == ENCRYPTION_RESULT_SERVICE_FAILURE)
 716       return false;
 717     if (result == ENCRYPTION_RESULT_ITEM_FAILURE)
 718       continue;
 719     DCHECK(result == ENCRYPTION_RESULT_SUCCESS);
 720     forms->push_back(new_form.release());
 721   }
 722   return s.Succeeded();
 723 }
 724
 725 bool LoginDatabase::GetAutofillableLogins(
 726     std::vector<PasswordForm*>* forms) const {
 727   return GetAllLoginsWithBlacklistSetting(false, forms);
 728 }
 729
 730 bool LoginDatabase::GetBlacklistLogins(
 731     std::vector<PasswordForm*>* forms) const {
 732   return GetAllLoginsWithBlacklistSetting(true, forms);
 733 }
 734
 735 bool LoginDatabase::GetAllLoginsWithBlacklistSetting(
 736     bool blacklisted, std::vector<PasswordForm*>* forms) const {
 737   DCHECK(forms);
 738   // You *must* change LoginTableColumns if this query changes.
 739   sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
 740       "SELECT origin_url, action_url, "
 741       "username_element, username_value, "
 742       "password_element, password_value, submit_element, "
 743       "signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
 744       "scheme, password_type, possible_usernames, times_used, form_data, "
 745       "use_additional_auth, date_synced, display_name, avatar_url, "
 746       "federation_url, is_zero_click FROM logins "
 747       "WHERE blacklisted_by_user == ? ORDER BY origin_url"));
 748   s.BindInt(0, blacklisted ? 1 : 0);
 749
 750   while (s.Step()) {
 751     scoped_ptr<PasswordForm> new_form(new PasswordForm());
 752     EncryptionResult result = InitPasswordFormFromStatement(new_form.get(), s);
 753     if (result == ENCRYPTION_RESULT_SERVICE_FAILURE)
 754       return false;
 755     if (result == ENCRYPTION_RESULT_ITEM_FAILURE)
 756       continue;
 757     DCHECK(result == ENCRYPTION_RESULT_SUCCESS);
 758     forms->push_back(new_form.release());
 759   }
 760   return s.Succeeded();
 761 }
 762
 763 bool LoginDatabase::DeleteAndRecreateDatabaseFile() {
 764   DCHECK(db_.is_open());
 765   meta_table_.Reset();
 766   db_.Close();
 767   sql::Connection::Delete(db_path_);
 768   return Init(db_path_);
 769 }
 770
 771 }  // namespace password_manager