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
24using autofill::PasswordForm;
25
26namespace password_manager {
27
28static const int kCurrentVersionNumber = 7;
29static const int kCompatibleVersionNumber = 1;
30
31Pickle 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
39std::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
50namespace {
51
52// Convenience enum for interacting with SQL queries that use all the columns.
53enum 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
79void 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
115void 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
122LoginDatabase::LoginDatabase() {
123}
124
125LoginDatabase::~LoginDatabase() {
126}
127
128bool 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
180bool 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
238bool 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
280void 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
354PasswordStoreChangeList 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
397PasswordStoreChangeList 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
464bool 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
484bool 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
496bool 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
509LoginDatabase::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
567bool 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
660bool 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
692bool 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
725bool LoginDatabase::GetAutofillableLogins(
726    std::vector<PasswordForm*>* forms) const {
727  return GetAllLoginsWithBlacklistSetting(false, forms);
728}
729
730bool LoginDatabase::GetBlacklistLogins(
731    std::vector<PasswordForm*>* forms) const {
732  return GetAllLoginsWithBlacklistSetting(true, forms);
733}
734
735bool 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
763bool 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