1// Copyright (c) 2012 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 "chrome/browser/password_manager/login_database.h"
6
7#include <algorithm>
8#include <limits>
9
10#include "base/command_line.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 "chrome/common/chrome_switches.h"
18#include "net/base/registry_controlled_domains/registry_controlled_domain.h"
19#include "sql/connection.h"
20#include "sql/statement.h"
21#include "sql/transaction.h"
22
23using content::PasswordForm;
24
25static const int kCurrentVersionNumber = 3;
26static const int kCompatibleVersionNumber = 1;
27
28namespace {
29
30// Convenience enum for interacting with SQL queries that use all the columns.
31enum LoginTableColumns {
32  COLUMN_ORIGIN_URL = 0,
33  COLUMN_ACTION_URL,
34  COLUMN_USERNAME_ELEMENT,
35  COLUMN_USERNAME_VALUE,
36  COLUMN_PASSWORD_ELEMENT,
37  COLUMN_PASSWORD_VALUE,
38  COLUMN_SUBMIT_ELEMENT,
39  COLUMN_SIGNON_REALM,
40  COLUMN_SSL_VALID,
41  COLUMN_PREFERRED,
42  COLUMN_DATE_CREATED,
43  COLUMN_BLACKLISTED_BY_USER,
44  COLUMN_SCHEME,
45  COLUMN_PASSWORD_TYPE,
46  COLUMN_POSSIBLE_USERNAMES,
47  COLUMN_TIMES_USED
48};
49
50// Using the public suffix list for matching the origin is only needed for
51// websites that do not have a single hostname for entering credentials. It
52// would be better for their users if they did, but until then we help them find
53// credentials across different hostnames. We know that accounts.google.com is
54// the only hostname we should be accepting credentials on for any domain under
55// google.com, so we can apply a tighter policy for that domain.
56// For owners of domains where a single hostname is always used when your
57// users are entering their credentials, please contact palmer@chromium.org,
58// nyquist@chromium.org or file a bug at http://crbug.com/ to be added here.
59bool ShouldPSLDomainMatchingApply(
60      const std::string& registry_controlled_domain) {
61  return registry_controlled_domain != "google.com";
62}
63
64std::string GetRegistryControlledDomain(const GURL& signon_realm) {
65  return net::registry_controlled_domains::GetDomainAndRegistry(
66      signon_realm,
67      net::registry_controlled_domains::INCLUDE_PRIVATE_REGISTRIES);
68}
69
70std::string GetRegistryControlledDomain(const std::string& signon_realm_str) {
71  GURL signon_realm(signon_realm_str);
72  return net::registry_controlled_domains::GetDomainAndRegistry(
73      signon_realm,
74      net::registry_controlled_domains::INCLUDE_PRIVATE_REGISTRIES);
75}
76
77bool RegistryControlledDomainMatches(const scoped_ptr<PasswordForm>& found,
78                                     const PasswordForm current) {
79  const std::string found_registry_controlled_domain =
80      GetRegistryControlledDomain(found->signon_realm);
81  const std::string form_registry_controlled_domain =
82      GetRegistryControlledDomain(current.signon_realm);
83  return found_registry_controlled_domain == form_registry_controlled_domain;
84}
85
86bool SchemeMatches(const scoped_ptr<PasswordForm>& found,
87                   const PasswordForm current) {
88  const std::string found_scheme = GURL(found->signon_realm).scheme();
89  const std::string form_scheme = GURL(current.signon_realm).scheme();
90  return found_scheme == form_scheme;
91}
92
93bool PortMatches(const scoped_ptr<PasswordForm>& found,
94                   const PasswordForm current) {
95  const std::string found_port = GURL(found->signon_realm).port();
96  const std::string form_port = GURL(current.signon_realm).port();
97  return found_port == form_port;
98}
99
100bool IsPublicSuffixDomainMatchingEnabled() {
101#if defined(OS_ANDROID)
102  if (CommandLine::ForCurrentProcess()->HasSwitch(
103          switches::kEnablePasswordAutofillPublicSuffixDomainMatching)) {
104    return true;
105  }
106  if (CommandLine::ForCurrentProcess()->HasSwitch(
107          switches::kDisablePasswordAutofillPublicSuffixDomainMatching)) {
108    return false;
109  }
110  return true;
111#else
112  return false;
113#endif
114}
115
116}  // namespace
117
118LoginDatabase::LoginDatabase() : public_suffix_domain_matching_(false) {
119}
120
121LoginDatabase::~LoginDatabase() {
122}
123
124bool LoginDatabase::Init(const base::FilePath& db_path) {
125  // Set pragmas for a small, private database (based on WebDatabase).
126  db_.set_page_size(2048);
127  db_.set_cache_size(32);
128  db_.set_exclusive_locking();
129  db_.set_restrict_to_user();
130
131  if (!db_.Open(db_path)) {
132    LOG(WARNING) << "Unable to open the password store database.";
133    return false;
134  }
135
136  sql::Transaction transaction(&db_);
137  transaction.Begin();
138
139  // Check the database version.
140  if (!meta_table_.Init(&db_, kCurrentVersionNumber,
141                        kCompatibleVersionNumber)) {
142    db_.Close();
143    return false;
144  }
145  if (meta_table_.GetCompatibleVersionNumber() > kCurrentVersionNumber) {
146    LOG(WARNING) << "Password store database is too new.";
147    db_.Close();
148    return false;
149  }
150
151  // Initialize the tables.
152  if (!InitLoginsTable()) {
153    LOG(WARNING) << "Unable to initialize the password store database.";
154    db_.Close();
155    return false;
156  }
157
158  // Save the path for DeleteDatabaseFile().
159  db_path_ = db_path;
160
161  // If the file on disk is an older database version, bring it up to date.
162  if (!MigrateOldVersionsAsNeeded()) {
163    LOG(WARNING) << "Unable to migrate database";
164    db_.Close();
165    return false;
166  }
167
168  if (!transaction.Commit()) {
169    db_.Close();
170    return false;
171  }
172
173  public_suffix_domain_matching_ = IsPublicSuffixDomainMatchingEnabled();
174
175  return true;
176}
177
178bool LoginDatabase::MigrateOldVersionsAsNeeded() {
179  switch (meta_table_.GetVersionNumber()) {
180    case 1:
181      if (!db_.Execute("ALTER TABLE logins "
182                       "ADD COLUMN password_type INTEGER") ||
183          !db_.Execute("ALTER TABLE logins "
184                       "ADD COLUMN possible_usernames BLOB")) {
185        return false;
186      }
187    case 2:
188      if (!db_.Execute("ALTER TABLE logins "
189                       "ADD COLUMN times_used INTEGER")) {
190        return false;
191      }
192      break;
193    case kCurrentVersionNumber:
194      // Already up to date
195      return true;
196      break;
197    default:
198      NOTREACHED();
199      return false;
200  }
201  meta_table_.SetVersionNumber(kCurrentVersionNumber);
202  return true;
203}
204
205bool LoginDatabase::InitLoginsTable() {
206  if (!db_.DoesTableExist("logins")) {
207    if (!db_.Execute("CREATE TABLE logins ("
208                     "origin_url VARCHAR NOT NULL, "
209                     "action_url VARCHAR, "
210                     "username_element VARCHAR, "
211                     "username_value VARCHAR, "
212                     "password_element VARCHAR, "
213                     "password_value BLOB, "
214                     "submit_element VARCHAR, "
215                     "signon_realm VARCHAR NOT NULL,"
216                     "ssl_valid INTEGER NOT NULL,"
217                     "preferred INTEGER NOT NULL,"
218                     "date_created INTEGER NOT NULL,"
219                     "blacklisted_by_user INTEGER NOT NULL,"
220                     "scheme INTEGER NOT NULL,"
221                     "password_type INTEGER,"
222                     "possible_usernames BLOB,"
223                     "times_used INTEGER,"
224                     "UNIQUE "
225                     "(origin_url, username_element, "
226                     "username_value, password_element, "
227                     "submit_element, signon_realm))")) {
228      NOTREACHED();
229      return false;
230    }
231    if (!db_.Execute("CREATE INDEX logins_signon ON "
232                     "logins (signon_realm)")) {
233      NOTREACHED();
234      return false;
235    }
236  }
237  return true;
238}
239
240void LoginDatabase::ReportMetrics() {
241  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
242      "SELECT signon_realm, COUNT(username_value) FROM logins "
243      "GROUP BY signon_realm"));
244
245  if (!s.is_valid())
246    return;
247
248  int total_accounts = 0;
249  while (s.Step()) {
250    int accounts_per_site = s.ColumnInt(1);
251    total_accounts += accounts_per_site;
252    UMA_HISTOGRAM_CUSTOM_COUNTS("PasswordManager.AccountsPerSite",
253                                accounts_per_site, 0, 32, 6);
254  }
255  UMA_HISTOGRAM_CUSTOM_COUNTS("PasswordManager.TotalAccounts",
256                              total_accounts, 0, 32, 6);
257
258  sql::Statement usage_statement(db_.GetCachedStatement(
259      SQL_FROM_HERE,
260      "SELECT password_type, times_used FROM logins"));
261
262  if (!usage_statement.is_valid())
263    return;
264
265  while (usage_statement.Step()) {
266    PasswordForm::Type type = static_cast<PasswordForm::Type>(
267        usage_statement.ColumnInt(0));
268
269    if (type == PasswordForm::TYPE_GENERATED) {
270      UMA_HISTOGRAM_CUSTOM_COUNTS(
271          "PasswordManager.TimesGeneratedPasswordUsed",
272          usage_statement.ColumnInt(1), 0, 100, 10);
273    } else {
274      UMA_HISTOGRAM_CUSTOM_COUNTS(
275          "PasswordManager.TimesPasswordUsed",
276          usage_statement.ColumnInt(1), 0, 100, 10);
277    }
278  }
279}
280
281bool LoginDatabase::AddLogin(const PasswordForm& form) {
282  std::string encrypted_password;
283  if (!EncryptedString(form.password_value, &encrypted_password))
284    return false;
285
286  // You *must* change LoginTableColumns if this query changes.
287  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
288      "INSERT OR REPLACE INTO logins "
289      "(origin_url, action_url, username_element, username_value, "
290      " password_element, password_value, submit_element, "
291      " signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
292      " scheme, password_type, possible_usernames, times_used) "
293      "VALUES "
294      "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"));
295  s.BindString(COLUMN_ORIGIN_URL, form.origin.spec());
296  s.BindString(COLUMN_ACTION_URL, form.action.spec());
297  s.BindString16(COLUMN_USERNAME_ELEMENT, form.username_element);
298  s.BindString16(COLUMN_USERNAME_VALUE, form.username_value);
299  s.BindString16(COLUMN_PASSWORD_ELEMENT, form.password_element);
300  s.BindBlob(COLUMN_PASSWORD_VALUE, encrypted_password.data(),
301              static_cast<int>(encrypted_password.length()));
302  s.BindString16(COLUMN_SUBMIT_ELEMENT, form.submit_element);
303  s.BindString(COLUMN_SIGNON_REALM, form.signon_realm);
304  s.BindInt(COLUMN_SSL_VALID, form.ssl_valid);
305  s.BindInt(COLUMN_PREFERRED, form.preferred);
306  s.BindInt64(COLUMN_DATE_CREATED, form.date_created.ToTimeT());
307  s.BindInt(COLUMN_BLACKLISTED_BY_USER, form.blacklisted_by_user);
308  s.BindInt(COLUMN_SCHEME, form.scheme);
309  s.BindInt(COLUMN_PASSWORD_TYPE, form.type);
310  Pickle pickle = SerializeVector(form.other_possible_usernames);
311  s.BindBlob(COLUMN_POSSIBLE_USERNAMES, pickle.data(), pickle.size());
312  s.BindInt(COLUMN_TIMES_USED, form.times_used);
313
314  return s.Run();
315}
316
317bool LoginDatabase::UpdateLogin(const PasswordForm& form, int* items_changed) {
318  std::string encrypted_password;
319  if (!EncryptedString(form.password_value, &encrypted_password))
320    return false;
321
322  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
323      "UPDATE logins SET "
324      "action_url = ?, "
325      "password_value = ?, "
326      "ssl_valid = ?, "
327      "preferred = ?, "
328      "possible_usernames = ?, "
329      "times_used = ? "
330      "WHERE origin_url = ? AND "
331      "username_element = ? AND "
332      "username_value = ? AND "
333      "password_element = ? AND "
334      "signon_realm = ?"));
335  s.BindString(0, form.action.spec());
336  s.BindBlob(1, encrypted_password.data(),
337             static_cast<int>(encrypted_password.length()));
338  s.BindInt(2, form.ssl_valid);
339  s.BindInt(3, form.preferred);
340  Pickle pickle = SerializeVector(form.other_possible_usernames);
341  s.BindBlob(4, pickle.data(), pickle.size());
342  s.BindInt(5, form.times_used);
343  s.BindString(6, form.origin.spec());
344  s.BindString16(7, form.username_element);
345  s.BindString16(8, form.username_value);
346  s.BindString16(9, form.password_element);
347  s.BindString(10, form.signon_realm);
348
349  if (!s.Run())
350    return false;
351
352  if (items_changed)
353    *items_changed = db_.GetLastChangeCount();
354
355  return true;
356}
357
358bool LoginDatabase::RemoveLogin(const PasswordForm& form) {
359  // Remove a login by UNIQUE-constrained fields.
360  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
361      "DELETE FROM logins WHERE "
362      "origin_url = ? AND "
363      "username_element = ? AND "
364      "username_value = ? AND "
365      "password_element = ? AND "
366      "submit_element = ? AND "
367      "signon_realm = ? "));
368  s.BindString(0, form.origin.spec());
369  s.BindString16(1, form.username_element);
370  s.BindString16(2, form.username_value);
371  s.BindString16(3, form.password_element);
372  s.BindString16(4, form.submit_element);
373  s.BindString(5, form.signon_realm);
374
375  return s.Run();
376}
377
378bool LoginDatabase::RemoveLoginsCreatedBetween(const base::Time delete_begin,
379                                               const base::Time delete_end) {
380  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
381      "DELETE FROM logins WHERE "
382      "date_created >= ? AND date_created < ?"));
383  s.BindInt64(0, delete_begin.ToTimeT());
384  s.BindInt64(1, delete_end.is_null() ? std::numeric_limits<int64>::max()
385                                      : delete_end.ToTimeT());
386
387  return s.Run();
388}
389
390bool LoginDatabase::InitPasswordFormFromStatement(PasswordForm* form,
391                                                  sql::Statement& s) const {
392  std::string encrypted_password;
393  s.ColumnBlobAsString(COLUMN_PASSWORD_VALUE, &encrypted_password);
394  string16 decrypted_password;
395  if (!DecryptedString(encrypted_password, &decrypted_password))
396    return false;
397
398  std::string tmp = s.ColumnString(COLUMN_ORIGIN_URL);
399  form->origin = GURL(tmp);
400  tmp = s.ColumnString(COLUMN_ACTION_URL);
401  form->action = GURL(tmp);
402  form->username_element = s.ColumnString16(COLUMN_USERNAME_ELEMENT);
403  form->username_value = s.ColumnString16(COLUMN_USERNAME_VALUE);
404  form->password_element = s.ColumnString16(COLUMN_PASSWORD_ELEMENT);
405  form->password_value = decrypted_password;
406  form->submit_element = s.ColumnString16(COLUMN_SUBMIT_ELEMENT);
407  tmp = s.ColumnString(COLUMN_SIGNON_REALM);
408  form->signon_realm = tmp;
409  form->ssl_valid = (s.ColumnInt(COLUMN_SSL_VALID) > 0);
410  form->preferred = (s.ColumnInt(COLUMN_PREFERRED) > 0);
411  form->date_created = base::Time::FromTimeT(
412      s.ColumnInt64(COLUMN_DATE_CREATED));
413  form->blacklisted_by_user = (s.ColumnInt(COLUMN_BLACKLISTED_BY_USER) > 0);
414  int scheme_int = s.ColumnInt(COLUMN_SCHEME);
415  DCHECK((scheme_int >= 0) && (scheme_int <= PasswordForm::SCHEME_OTHER));
416  form->scheme = static_cast<PasswordForm::Scheme>(scheme_int);
417  int type_int = s.ColumnInt(COLUMN_PASSWORD_TYPE);
418  DCHECK(type_int >= 0 && type_int <= PasswordForm::TYPE_GENERATED);
419  form->type = static_cast<PasswordForm::Type>(type_int);
420  Pickle pickle(
421      static_cast<const char*>(s.ColumnBlob(COLUMN_POSSIBLE_USERNAMES)),
422      s.ColumnByteLength(COLUMN_POSSIBLE_USERNAMES));
423  form->other_possible_usernames = DeserializeVector(pickle);
424  form->times_used = s.ColumnInt(COLUMN_TIMES_USED);
425  return true;
426}
427
428bool LoginDatabase::GetLogins(const PasswordForm& form,
429                              std::vector<PasswordForm*>* forms) const {
430  DCHECK(forms);
431  // You *must* change LoginTableColumns if this query changes.
432  const std::string sql_query = "SELECT origin_url, action_url, "
433      "username_element, username_value, "
434      "password_element, password_value, submit_element, "
435      "signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
436      "scheme, password_type, possible_usernames, times_used "
437      "FROM logins WHERE signon_realm == ? ";
438  sql::Statement s;
439  const GURL signon_realm(form.signon_realm);
440  std::string registered_domain = GetRegistryControlledDomain(signon_realm);
441  if (public_suffix_domain_matching_ &&
442      ShouldPSLDomainMatchingApply(registered_domain)) {
443    // We are extending the original SQL query with one that includes more
444    // possible matches based on public suffix domain matching. Using a regexp
445    // here is just an optimization to not have to parse all the stored entries
446    // in the |logins| table. The result (scheme, domain and port) is verified
447    // further down using GURL. See the functions SchemeMatches,
448    // RegistryControlledDomainMatches and PortMatches.
449    const std::string extended_sql_query =
450        sql_query + "OR signon_realm REGEXP ? ";
451    // TODO(nyquist) Re-enable usage of GetCachedStatement when
452    // http://crbug.com/248608 is fixed.
453    s.Assign(db_.GetUniqueStatement(extended_sql_query.c_str()));
454    // We need to escape . in the domain. Since the domain has already been
455    // sanitized using GURL, we do not need to escape any other characters.
456    ReplaceChars(registered_domain, ".", "\\.", &registered_domain);
457    std::string scheme = signon_realm.scheme();
458    // We need to escape . in the scheme. Since the scheme has already been
459    // sanitized using GURL, we do not need to escape any other characters.
460    // The scheme soap.beep is an example with '.'.
461    ReplaceChars(scheme, ".", "\\.", &scheme);
462    const std::string port = signon_realm.port();
463    // For a signon realm such as http://foo.bar/, this regexp will match
464    // domains on the form http://foo.bar/, http://www.foo.bar/,
465    // http://www.mobile.foo.bar/. It will not match http://notfoo.bar/.
466    // The scheme and port has to be the same as the observed form.
467    std::string regexp = "^(" + scheme + ":\\/\\/)([\\w-]+\\.)*" +
468                         registered_domain + "(:" + port + ")?\\/$";
469    s.BindString(0, form.signon_realm);
470    s.BindString(1, regexp);
471  } else {
472    s.Assign(db_.GetCachedStatement(SQL_FROM_HERE, sql_query.c_str()));
473    s.BindString(0, form.signon_realm);
474  }
475
476  while (s.Step()) {
477    scoped_ptr<PasswordForm> new_form(new PasswordForm());
478    if (!InitPasswordFormFromStatement(new_form.get(), s))
479      return false;
480    if (public_suffix_domain_matching_) {
481      if (!SchemeMatches(new_form, form) ||
482          !RegistryControlledDomainMatches(new_form, form) ||
483          !PortMatches(new_form, form)) {
484        // The database returned results that should not match. Skipping result.
485        continue;
486      }
487      if (form.signon_realm != new_form->signon_realm) {
488        // This is not a perfect match, so we need to create a new valid result.
489        // We do this by copying over origin, signon realm and action from the
490        // observed form and setting the original signon realm to what we found
491        // in the database. We use the fact that |original_signon_realm| is
492        // non-empty to communicate that this match was found using public
493        // suffix matching.
494        new_form->original_signon_realm = new_form->signon_realm;
495        new_form->origin = form.origin;
496        new_form->signon_realm = form.signon_realm;
497        new_form->action = form.action;
498      }
499    }
500    forms->push_back(new_form.release());
501  }
502  return s.Succeeded();
503}
504
505bool LoginDatabase::GetLoginsCreatedBetween(
506    const base::Time begin,
507    const base::Time end,
508    std::vector<content::PasswordForm*>* forms) const {
509  DCHECK(forms);
510  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
511      "SELECT origin_url, action_url, "
512      "username_element, username_value, "
513      "password_element, password_value, submit_element, "
514      "signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
515      "scheme, password_type, possible_usernames, times_used "
516      "FROM logins WHERE date_created >= ? AND date_created < ?"
517      "ORDER BY origin_url"));
518  s.BindInt64(0, begin.ToTimeT());
519  s.BindInt64(1, end.is_null() ? std::numeric_limits<int64>::max()
520                               : end.ToTimeT());
521
522  while (s.Step()) {
523    scoped_ptr<PasswordForm> new_form(new PasswordForm());
524    if (!InitPasswordFormFromStatement(new_form.get(), s))
525      return false;
526    forms->push_back(new_form.release());
527  }
528  return s.Succeeded();
529}
530
531bool LoginDatabase::GetAutofillableLogins(
532    std::vector<PasswordForm*>* forms) const {
533  return GetAllLoginsWithBlacklistSetting(false, forms);
534}
535
536bool LoginDatabase::GetBlacklistLogins(
537    std::vector<PasswordForm*>* forms) const {
538  return GetAllLoginsWithBlacklistSetting(true, forms);
539}
540
541bool LoginDatabase::GetAllLoginsWithBlacklistSetting(
542    bool blacklisted, std::vector<PasswordForm*>* forms) const {
543  DCHECK(forms);
544  // You *must* change LoginTableColumns if this query changes.
545  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
546      "SELECT origin_url, action_url, "
547      "username_element, username_value, "
548      "password_element, password_value, submit_element, "
549      "signon_realm, ssl_valid, preferred, date_created, blacklisted_by_user, "
550      "scheme, password_type, possible_usernames, times_used "
551      "FROM logins WHERE blacklisted_by_user == ? "
552      "ORDER BY origin_url"));
553  s.BindInt(0, blacklisted ? 1 : 0);
554
555  while (s.Step()) {
556    scoped_ptr<PasswordForm> new_form(new PasswordForm());
557    if (!InitPasswordFormFromStatement(new_form.get(), s))
558      return false;
559    forms->push_back(new_form.release());
560  }
561  return s.Succeeded();
562}
563
564bool LoginDatabase::DeleteAndRecreateDatabaseFile() {
565  DCHECK(db_.is_open());
566  meta_table_.Reset();
567  db_.Close();
568  sql::Connection::Delete(db_path_);
569  return Init(db_path_);
570}
571
572Pickle LoginDatabase::SerializeVector(const std::vector<string16>& vec) const {
573  Pickle p;
574  for (size_t i = 0; i < vec.size(); ++i) {
575    p.WriteString16(vec[i]);
576  }
577  return p;
578}
579
580std::vector<string16> LoginDatabase::DeserializeVector(const Pickle& p) const {
581  std::vector<string16> ret;
582  string16 str;
583
584  PickleIterator iterator(p);
585  while (iterator.ReadString16(&str)) {
586    ret.push_back(str);
587  }
588  return ret;
589}
590