Home | History | Annotate | Download | only in browser 
      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
    772