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 19 using webkit_glue::PasswordForm; 20 21 static const int kCurrentVersionNumber = 1; 22 static const int kCompatibleVersionNumber = 1; 23 24 namespace { 25 26 // Convenience enum for interacting with SQL queries that use all the columns. 27 enum 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 45 LoginDatabase::LoginDatabase() { 46 } 47 48 LoginDatabase::~LoginDatabase() { 49 } 50 51 bool 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 97 void LoginDatabase::MigrateOldVersionsAsNeeded() { 98 switch (meta_table_.GetVersionNumber()) { 99 case kCurrentVersionNumber: 100 // No migration needed. 101 return; 102 } 103 } 104 105 bool 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 137 void 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 159 bool 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 196 bool 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 235 bool 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 264 bool 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 280 void 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 305 bool 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 332 bool 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 363 bool LoginDatabase::GetAutofillableLogins( 364 std::vector<PasswordForm*>* forms) const { 365 return GetAllLoginsWithBlacklistSetting(false, forms); 366 } 367 368 bool LoginDatabase::GetBlacklistLogins( 369 std::vector<PasswordForm*>* forms) const { 370 return GetAllLoginsWithBlacklistSetting(true, forms); 371 } 372 373 bool 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 401 bool 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
