1// Copyright (c) 2010 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 "app/sql/statement.h"
11#include "app/sql/transaction.h"
12#include "base/file_path.h"
13#include "base/file_util.h"
14#include "base/logging.h"
15#include "base/metrics/histogram.h"
16#include "base/time.h"
17#include "base/utf_string_conversions.h"
18
19using webkit_glue::PasswordForm;
20
21static const int kCurrentVersionNumber = 1;
22static const int kCompatibleVersionNumber = 1;
23
24namespace {
25
26// Convenience enum for interacting with SQL queries that use all the columns.
27enum LoginTableColumns {
28  COLUMN_ORIGIN_URL = 0,
29  COLUMN_ACTION_URL,
30  COLUMN_USERNAME_ELEMENT,
31  COLUMN_USERNAME_VALUE,
32  COLUMN_PASSWORD_ELEMENT,
33  COLUMN_PASSWORD_VALUE,
34  COLUMN_SUBMIT_ELEMENT,
35  COLUMN_SIGNON_REALM,
36  COLUMN_SSL_VALID,
37  COLUMN_PREFERRED,
38  COLUMN_DATE_CREATED,
39  COLUMN_BLACKLISTED_BY_USER,
40  COLUMN_SCHEME
41};
42
43}  // namespace
44
45LoginDatabase::LoginDatabase() {
46}
47
48LoginDatabase::~LoginDatabase() {
49}
50
51bool LoginDatabase::Init(const FilePath& db_path) {
52  // Set pragmas for a small, private database (based on WebDatabase).
53  db_.set_page_size(2048);
54  db_.set_cache_size(32);
55  db_.set_exclusive_locking();
56
57  if (!db_.Open(db_path)) {
58    LOG(WARNING) << "Unable to open the password store database.";
59    return false;
60  }
61
62  sql::Transaction transaction(&db_);
63  transaction.Begin();
64
65  // Check the database version.
66  if (!meta_table_.Init(&db_, kCurrentVersionNumber,
67                        kCompatibleVersionNumber)) {
68    db_.Close();
69    return false;
70  }
71  if (meta_table_.GetCompatibleVersionNumber() > kCurrentVersionNumber) {
72    LOG(WARNING) << "Password store database is too new.";
73    db_.Close();
74    return false;
75  }
76
77  // Initialize the tables.
78  if (!InitLoginsTable()) {
79    LOG(WARNING) << "Unable to initialize the password store database.";
80    db_.Close();
81    return false;
82  }
83
84  // Save the path for DeleteDatabaseFile().
85  db_path_ = db_path;
86
87  // If the file on disk is an older database version, bring it up to date.
88  MigrateOldVersionsAsNeeded();
89
90  if (!transaction.Commit()) {
91    db_.Close();
92    return false;
93  }
94  return true;
95}
96
97void LoginDatabase::MigrateOldVersionsAsNeeded() {
98  switch (meta_table_.GetVersionNumber()) {
99    case kCurrentVersionNumber:
100      // No migration needed.
101      return;
102  }
103}
104
105bool LoginDatabase::InitLoginsTable() {
106  if (!db_.DoesTableExist("logins")) {
107    if (!db_.Execute("CREATE TABLE logins ("
108                     "origin_url VARCHAR NOT NULL, "
109                     "action_url VARCHAR, "
110                     "username_element VARCHAR, "
111                     "username_value VARCHAR, "
112                     "password_element VARCHAR, "
113                     "password_value BLOB, "
114                     "submit_element VARCHAR, "
115                     "signon_realm VARCHAR NOT NULL,"
116                     "ssl_valid INTEGER NOT NULL,"
117                     "preferred INTEGER NOT NULL,"
118                     "date_created INTEGER NOT NULL,"
119                     "blacklisted_by_user INTEGER NOT NULL,"
120                     "scheme INTEGER NOT NULL,"
121                     "UNIQUE "
122                     "(origin_url, username_element, "
123                     "username_value, password_element, "
124                     "submit_element, signon_realm))")) {
125      NOTREACHED();
126      return false;
127    }
128    if (!db_.Execute("CREATE INDEX logins_signon ON "
129                     "logins (signon_realm)")) {
130      NOTREACHED();
131      return false;
132    }
133  }
134  return true;
135}
136
137void LoginDatabase::ReportMetrics() {
138  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
139      "SELECT signon_realm, COUNT(username_value) FROM logins "
140      "GROUP BY signon_realm"));
141  if (!s) {
142    NOTREACHED() << "Statement prepare failed";
143    return;
144  }
145
146  int total_accounts = 0;
147  while (s.Step()) {
148    int accounts_per_site = s.ColumnInt(1);
149    total_accounts += accounts_per_site;
150    UMA_HISTOGRAM_CUSTOM_COUNTS("PasswordManager.AccountsPerSite",
151                                accounts_per_site, 0, 32, 6);
152  }
153  UMA_HISTOGRAM_CUSTOM_COUNTS("PasswordManager.TotalAccounts",
154                              total_accounts, 0, 32, 6);
155
156  return;
157}
158
159bool LoginDatabase::AddLogin(const PasswordForm& form) {
160  // You *must* change LoginTableColumns if this query changes.
161  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
162      "INSERT OR REPLACE INTO logins "
163      "(origin_url, action_url, username_element, username_value, "
164      " password_element, password_value, submit_element, "
165      " signon_realm, ssl_valid, preferred, date_created, "
166      " blacklisted_by_user, scheme) "
167      "VALUES "
168      "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"));
169  if (!s) {
170    NOTREACHED() << "Statement prepare failed";
171    return false;
172  }
173
174  s.BindString(COLUMN_ORIGIN_URL, form.origin.spec());
175  s.BindString(COLUMN_ACTION_URL, form.action.spec());
176  s.BindString16(COLUMN_USERNAME_ELEMENT, form.username_element);
177  s.BindString16(COLUMN_USERNAME_VALUE, form.username_value);
178  s.BindString16(COLUMN_PASSWORD_ELEMENT, form.password_element);
179  std::string encrypted_password = EncryptedString(form.password_value);
180  s.BindBlob(COLUMN_PASSWORD_VALUE, encrypted_password.data(),
181              static_cast<int>(encrypted_password.length()));
182  s.BindString16(COLUMN_SUBMIT_ELEMENT, form.submit_element);
183  s.BindString(COLUMN_SIGNON_REALM, form.signon_realm);
184  s.BindInt(COLUMN_SSL_VALID, form.ssl_valid);
185  s.BindInt(COLUMN_PREFERRED, form.preferred);
186  s.BindInt64(COLUMN_DATE_CREATED, form.date_created.ToTimeT());
187  s.BindInt(COLUMN_BLACKLISTED_BY_USER, form.blacklisted_by_user);
188  s.BindInt(COLUMN_SCHEME, form.scheme);
189  if (!s.Run()) {
190    NOTREACHED();
191    return false;
192  }
193  return true;
194}
195
196bool LoginDatabase::UpdateLogin(const PasswordForm& form, int* items_changed) {
197  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
198      "UPDATE logins SET "
199      "action_url = ?, "
200      "password_value = ?, "
201      "ssl_valid = ?, "
202      "preferred = ? "
203      "WHERE origin_url = ? AND "
204      "username_element = ? AND "
205      "username_value = ? AND "
206      "password_element = ? AND "
207      "signon_realm = ?"));
208  if (!s) {
209    NOTREACHED() << "Statement prepare failed";
210    return false;
211  }
212
213  s.BindString(0, form.action.spec());
214  std::string encrypted_password = EncryptedString(form.password_value);
215  s.BindBlob(1, encrypted_password.data(),
216             static_cast<int>(encrypted_password.length()));
217  s.BindInt(2, form.ssl_valid);
218  s.BindInt(3, form.preferred);
219  s.BindString(4, form.origin.spec());
220  s.BindString16(5, form.username_element);
221  s.BindString16(6, form.username_value);
222  s.BindString16(7, form.password_element);
223  s.BindString(8, form.signon_realm);
224
225  if (!s.Run()) {
226    NOTREACHED();
227    return false;
228  }
229  if (items_changed) {
230    *items_changed = db_.GetLastChangeCount();
231  }
232  return true;
233}
234
235bool LoginDatabase::RemoveLogin(const PasswordForm& form) {
236  // Remove a login by UNIQUE-constrained fields.
237  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
238      "DELETE FROM logins WHERE "
239      "origin_url = ? AND "
240      "username_element = ? AND "
241      "username_value = ? AND "
242      "password_element = ? AND "
243      "submit_element = ? AND "
244      "signon_realm = ? "));
245  if (!s) {
246    NOTREACHED() << "Statement prepare failed";
247    return false;
248  }
249
250  s.BindString(0, form.origin.spec());
251  s.BindString16(1, form.username_element);
252  s.BindString16(2, form.username_value);
253  s.BindString16(3, form.password_element);
254  s.BindString16(4, form.submit_element);
255  s.BindString(5, form.signon_realm);
256
257  if (!s.Run()) {
258    NOTREACHED();
259    return false;
260  }
261  return true;
262}
263
264bool LoginDatabase::RemoveLoginsCreatedBetween(const base::Time delete_begin,
265                                               const base::Time delete_end) {
266  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
267      "DELETE FROM logins WHERE "
268      "date_created >= ? AND date_created < ?"));
269  if (!s) {
270    NOTREACHED() << "Statement prepare failed";
271    return false;
272  }
273  s.BindInt64(0, delete_begin.ToTimeT());
274  s.BindInt64(1, delete_end.is_null() ? std::numeric_limits<int64>::max()
275                                      : delete_end.ToTimeT());
276
277  return s.Run();
278}
279
280void LoginDatabase::InitPasswordFormFromStatement(PasswordForm* form,
281                                                  sql::Statement& s) const {
282  std::string tmp = s.ColumnString(COLUMN_ORIGIN_URL);
283  form->origin = GURL(tmp);
284  tmp = s.ColumnString(COLUMN_ACTION_URL);
285  form->action = GURL(tmp);
286  form->username_element = s.ColumnString16(COLUMN_USERNAME_ELEMENT);
287  form->username_value = s.ColumnString16(COLUMN_USERNAME_VALUE);
288  form->password_element = s.ColumnString16(COLUMN_PASSWORD_ELEMENT);
289  std::string encrypted_password;
290  s.ColumnBlobAsString(COLUMN_PASSWORD_VALUE, &encrypted_password);
291  form->password_value = DecryptedString(encrypted_password);
292  form->submit_element = s.ColumnString16(COLUMN_SUBMIT_ELEMENT);
293  tmp = s.ColumnString(COLUMN_SIGNON_REALM);
294  form->signon_realm = tmp;
295  form->ssl_valid = (s.ColumnInt(COLUMN_SSL_VALID) > 0);
296  form->preferred = (s.ColumnInt(COLUMN_PREFERRED) > 0);
297  form->date_created = base::Time::FromTimeT(
298      s.ColumnInt64(COLUMN_DATE_CREATED));
299  form->blacklisted_by_user = (s.ColumnInt(COLUMN_BLACKLISTED_BY_USER) > 0);
300  int scheme_int = s.ColumnInt(COLUMN_SCHEME);
301  DCHECK((scheme_int >= 0) && (scheme_int <= PasswordForm::SCHEME_OTHER));
302  form->scheme = static_cast<PasswordForm::Scheme>(scheme_int);
303}
304
305bool LoginDatabase::GetLogins(const PasswordForm& form,
306                              std::vector<PasswordForm*>* forms) const {
307  DCHECK(forms);
308  // You *must* change LoginTableColumns if this query changes.
309  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
310      "SELECT origin_url, action_url, "
311      "username_element, username_value, "
312      "password_element, password_value, "
313      "submit_element, signon_realm, ssl_valid, preferred, "
314      "date_created, blacklisted_by_user, scheme FROM logins "
315      "WHERE signon_realm == ? "));
316  if (!s) {
317    NOTREACHED() << "Statement prepare failed";
318    return false;
319  }
320
321  s.BindString(0, form.signon_realm);
322
323  while (s.Step()) {
324    PasswordForm* new_form = new PasswordForm();
325    InitPasswordFormFromStatement(new_form, s);
326
327    forms->push_back(new_form);
328  }
329  return s.Succeeded();
330}
331
332bool LoginDatabase::GetLoginsCreatedBetween(
333    const base::Time begin,
334    const base::Time end,
335    std::vector<webkit_glue::PasswordForm*>* forms) const {
336  DCHECK(forms);
337  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
338      "SELECT origin_url, action_url, "
339      "username_element, username_value, "
340      "password_element, password_value, "
341      "submit_element, signon_realm, ssl_valid, preferred, "
342      "date_created, blacklisted_by_user, scheme FROM logins "
343      "WHERE date_created >= ? AND date_created < ?"
344      "ORDER BY origin_url"));
345
346  if (!s) {
347    NOTREACHED() << "Statement prepare failed";
348    return false;
349  }
350  s.BindInt64(0, begin.ToTimeT());
351  s.BindInt64(1, end.is_null() ? std::numeric_limits<int64>::max()
352                               : end.ToTimeT());
353
354  while (s.Step()) {
355    PasswordForm* new_form = new PasswordForm();
356    InitPasswordFormFromStatement(new_form, s);
357
358    forms->push_back(new_form);
359  }
360  return s.Succeeded();
361}
362
363bool LoginDatabase::GetAutofillableLogins(
364    std::vector<PasswordForm*>* forms) const {
365  return GetAllLoginsWithBlacklistSetting(false, forms);
366}
367
368bool LoginDatabase::GetBlacklistLogins(
369    std::vector<PasswordForm*>* forms) const {
370  return GetAllLoginsWithBlacklistSetting(true, forms);
371}
372
373bool LoginDatabase::GetAllLoginsWithBlacklistSetting(
374    bool blacklisted, std::vector<PasswordForm*>* forms) const {
375  DCHECK(forms);
376  // You *must* change LoginTableColumns if this query changes.
377  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
378      "SELECT origin_url, action_url, "
379      "username_element, username_value, "
380      "password_element, password_value, "
381      "submit_element, signon_realm, ssl_valid, preferred, "
382      "date_created, blacklisted_by_user, scheme FROM logins "
383      "WHERE blacklisted_by_user == ? "
384      "ORDER BY origin_url"));
385
386  if (!s) {
387    NOTREACHED() << "Statement prepare failed";
388    return false;
389  }
390  s.BindInt(0, blacklisted ? 1 : 0);
391
392  while (s.Step()) {
393    PasswordForm* new_form = new PasswordForm();
394    InitPasswordFormFromStatement(new_form, s);
395
396    forms->push_back(new_form);
397  }
398  return s.Succeeded();
399}
400
401bool LoginDatabase::DeleteAndRecreateDatabaseFile() {
402  DCHECK(db_.is_open());
403  meta_table_.Reset();
404  db_.Close();
405  file_util::Delete(db_path_, false);
406  return Init(db_path_);
407}
408