1 // Copyright 2013 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/autofill/core/browser/webdata/autofill_table.h" 6 7 #include <algorithm> 8 #include <cmath> 9 #include <limits> 10 #include <map> 11 #include <set> 12 #include <string> 13 #include <vector> 14 15 #include "base/guid.h" 16 #include "base/i18n/case_conversion.h" 17 #include "base/logging.h" 18 #include "base/numerics/safe_conversions.h" 19 #include "base/strings/string_number_conversions.h" 20 #include "base/strings/utf_string_conversions.h" 21 #include "base/time/time.h" 22 #include "base/tuple.h" 23 #include "components/autofill/core/browser/autofill_country.h" 24 #include "components/autofill/core/browser/autofill_profile.h" 25 #include "components/autofill/core/browser/autofill_type.h" 26 #include "components/autofill/core/browser/credit_card.h" 27 #include "components/autofill/core/browser/personal_data_manager.h" 28 #include "components/autofill/core/browser/webdata/autofill_change.h" 29 #include "components/autofill/core/browser/webdata/autofill_entry.h" 30 #include "components/autofill/core/common/form_field_data.h" 31 #include "components/os_crypt/os_crypt.h" 32 #include "components/webdata/common/web_database.h" 33 #include "sql/statement.h" 34 #include "sql/transaction.h" 35 #include "ui/base/l10n/l10n_util.h" 36 #include "url/gurl.h" 37 38 using base::Time; 39 40 namespace autofill { 41 namespace { 42 43 // The period after which Autofill entries should expire in days. 44 const int64 kExpirationPeriodInDays = 60; 45 46 template<typename T> 47 T* address_of(T& v) { 48 return &v; 49 } 50 51 // Helper struct for AutofillTable::RemoveFormElementsAddedBetween(). 52 // Contains all the necessary fields to update a row in the 'autofill' table. 53 struct AutofillUpdate { 54 base::string16 name; 55 base::string16 value; 56 time_t date_created; 57 time_t date_last_used; 58 int count; 59 }; 60 61 // Rounds a positive floating point number to the nearest integer. 62 int Round(float f) { 63 DCHECK_GE(f, 0.f); 64 return base::checked_cast<int>(std::floor(f + 0.5f)); 65 } 66 67 // Returns the |data_model|'s value corresponding to the |type|, trimmed to the 68 // maximum length that can be stored in a column of the Autofill database. 69 base::string16 GetInfo(const AutofillDataModel& data_model, 70 ServerFieldType type) { 71 base::string16 data = data_model.GetRawInfo(type); 72 if (data.size() > AutofillTable::kMaxDataLength) 73 return data.substr(0, AutofillTable::kMaxDataLength); 74 75 return data; 76 } 77 78 void BindAutofillProfileToStatement(const AutofillProfile& profile, 79 sql::Statement* s) { 80 DCHECK(base::IsValidGUID(profile.guid())); 81 int index = 0; 82 s->BindString(index++, profile.guid()); 83 84 s->BindString16(index++, GetInfo(profile, COMPANY_NAME)); 85 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_STREET_ADDRESS)); 86 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_DEPENDENT_LOCALITY)); 87 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_CITY)); 88 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_STATE)); 89 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_ZIP)); 90 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_SORTING_CODE)); 91 s->BindString16(index++, GetInfo(profile, ADDRESS_HOME_COUNTRY)); 92 s->BindInt64(index++, Time::Now().ToTimeT()); 93 s->BindString(index++, profile.origin()); 94 s->BindString(index++, profile.language_code()); 95 } 96 97 scoped_ptr<AutofillProfile> AutofillProfileFromStatement( 98 const sql::Statement& s) { 99 scoped_ptr<AutofillProfile> profile(new AutofillProfile); 100 int index = 0; 101 profile->set_guid(s.ColumnString(index++)); 102 DCHECK(base::IsValidGUID(profile->guid())); 103 104 profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(index++)); 105 profile->SetRawInfo(ADDRESS_HOME_STREET_ADDRESS, s.ColumnString16(index++)); 106 profile->SetRawInfo(ADDRESS_HOME_DEPENDENT_LOCALITY, 107 s.ColumnString16(index++)); 108 profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++)); 109 profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++)); 110 profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++)); 111 profile->SetRawInfo(ADDRESS_HOME_SORTING_CODE, s.ColumnString16(index++)); 112 profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(index++)); 113 // Intentionally skip column 9, which stores the profile's modification date. 114 index++; 115 profile->set_origin(s.ColumnString(index++)); 116 profile->set_language_code(s.ColumnString(index++)); 117 118 return profile.Pass(); 119 } 120 121 void BindCreditCardToStatement(const CreditCard& credit_card, 122 sql::Statement* s) { 123 DCHECK(base::IsValidGUID(credit_card.guid())); 124 int index = 0; 125 s->BindString(index++, credit_card.guid()); 126 127 s->BindString16(index++, GetInfo(credit_card, CREDIT_CARD_NAME)); 128 s->BindString16(index++, GetInfo(credit_card, CREDIT_CARD_EXP_MONTH)); 129 s->BindString16(index++, GetInfo(credit_card, CREDIT_CARD_EXP_4_DIGIT_YEAR)); 130 131 std::string encrypted_data; 132 OSCrypt::EncryptString16(credit_card.GetRawInfo(CREDIT_CARD_NUMBER), 133 &encrypted_data); 134 s->BindBlob(index++, encrypted_data.data(), 135 static_cast<int>(encrypted_data.length())); 136 137 s->BindInt64(index++, Time::Now().ToTimeT()); 138 s->BindString(index++, credit_card.origin()); 139 } 140 141 scoped_ptr<CreditCard> CreditCardFromStatement(const sql::Statement& s) { 142 scoped_ptr<CreditCard> credit_card(new CreditCard); 143 144 int index = 0; 145 credit_card->set_guid(s.ColumnString(index++)); 146 DCHECK(base::IsValidGUID(credit_card->guid())); 147 148 credit_card->SetRawInfo(CREDIT_CARD_NAME, s.ColumnString16(index++)); 149 credit_card->SetRawInfo(CREDIT_CARD_EXP_MONTH, s.ColumnString16(index++)); 150 credit_card->SetRawInfo(CREDIT_CARD_EXP_4_DIGIT_YEAR, 151 s.ColumnString16(index++)); 152 int encrypted_number_len = s.ColumnByteLength(index); 153 base::string16 credit_card_number; 154 if (encrypted_number_len) { 155 std::string encrypted_number; 156 encrypted_number.resize(encrypted_number_len); 157 memcpy(&encrypted_number[0], s.ColumnBlob(index++), encrypted_number_len); 158 OSCrypt::DecryptString16(encrypted_number, &credit_card_number); 159 } else { 160 index++; 161 } 162 credit_card->SetRawInfo(CREDIT_CARD_NUMBER, credit_card_number); 163 // Intentionally skip column 5, which stores the modification date. 164 index++; 165 credit_card->set_origin(s.ColumnString(index++)); 166 167 return credit_card.Pass(); 168 } 169 170 // Obsolete version of AddAutofillProfileNamesToProfile, but still needed 171 // for MigrateToVersion37MergeAndCullOlderProfiles(). 172 bool AddAutofillProfileNamesToProfileForVersion37(sql::Connection* db, 173 AutofillProfile* profile) { 174 sql::Statement s(db->GetUniqueStatement( 175 "SELECT guid, first_name, middle_name, last_name " 176 "FROM autofill_profile_names " 177 "WHERE guid=?")); 178 s.BindString(0, profile->guid()); 179 180 if (!s.is_valid()) 181 return false; 182 183 std::vector<base::string16> first_names; 184 std::vector<base::string16> middle_names; 185 std::vector<base::string16> last_names; 186 while (s.Step()) { 187 DCHECK_EQ(profile->guid(), s.ColumnString(0)); 188 first_names.push_back(s.ColumnString16(1)); 189 middle_names.push_back(s.ColumnString16(2)); 190 last_names.push_back(s.ColumnString16(3)); 191 } 192 if (!s.Succeeded()) 193 return false; 194 195 profile->SetRawMultiInfo(NAME_FIRST, first_names); 196 profile->SetRawMultiInfo(NAME_MIDDLE, middle_names); 197 profile->SetRawMultiInfo(NAME_LAST, last_names); 198 return true; 199 } 200 201 bool AddAutofillProfileNamesToProfile(sql::Connection* db, 202 AutofillProfile* profile) { 203 sql::Statement s(db->GetUniqueStatement( 204 "SELECT guid, first_name, middle_name, last_name, full_name " 205 "FROM autofill_profile_names " 206 "WHERE guid=?")); 207 s.BindString(0, profile->guid()); 208 209 if (!s.is_valid()) 210 return false; 211 212 std::vector<base::string16> first_names; 213 std::vector<base::string16> middle_names; 214 std::vector<base::string16> last_names; 215 std::vector<base::string16> full_names; 216 while (s.Step()) { 217 DCHECK_EQ(profile->guid(), s.ColumnString(0)); 218 first_names.push_back(s.ColumnString16(1)); 219 middle_names.push_back(s.ColumnString16(2)); 220 last_names.push_back(s.ColumnString16(3)); 221 full_names.push_back(s.ColumnString16(4)); 222 } 223 if (!s.Succeeded()) 224 return false; 225 226 profile->SetRawMultiInfo(NAME_FIRST, first_names); 227 profile->SetRawMultiInfo(NAME_MIDDLE, middle_names); 228 profile->SetRawMultiInfo(NAME_LAST, last_names); 229 profile->SetRawMultiInfo(NAME_FULL, full_names); 230 return true; 231 } 232 233 bool AddAutofillProfileEmailsToProfile(sql::Connection* db, 234 AutofillProfile* profile) { 235 sql::Statement s(db->GetUniqueStatement( 236 "SELECT guid, email " 237 "FROM autofill_profile_emails " 238 "WHERE guid=?")); 239 s.BindString(0, profile->guid()); 240 241 if (!s.is_valid()) 242 return false; 243 244 std::vector<base::string16> emails; 245 while (s.Step()) { 246 DCHECK_EQ(profile->guid(), s.ColumnString(0)); 247 emails.push_back(s.ColumnString16(1)); 248 } 249 if (!s.Succeeded()) 250 return false; 251 252 profile->SetRawMultiInfo(EMAIL_ADDRESS, emails); 253 return true; 254 } 255 256 bool AddAutofillProfilePhonesToProfile(sql::Connection* db, 257 AutofillProfile* profile) { 258 sql::Statement s(db->GetUniqueStatement( 259 "SELECT guid, number " 260 "FROM autofill_profile_phones " 261 "WHERE guid=?")); 262 s.BindString(0, profile->guid()); 263 264 if (!s.is_valid()) 265 return false; 266 267 std::vector<base::string16> numbers; 268 while (s.Step()) { 269 DCHECK_EQ(profile->guid(), s.ColumnString(0)); 270 numbers.push_back(s.ColumnString16(1)); 271 } 272 if (!s.Succeeded()) 273 return false; 274 275 profile->SetRawMultiInfo(PHONE_HOME_WHOLE_NUMBER, numbers); 276 return true; 277 } 278 279 // Obsolete version of AddAutofillProfileNames needed for 280 // MigrateToVersion33ProfilesBasedOnFirstName() and 281 // MigrateToVersion37MergeAndCullOlderProfiles(). 282 bool AddAutofillProfileNamesForVersion3x( 283 const AutofillProfile& profile, 284 sql::Connection* db) { 285 std::vector<base::string16> first_names; 286 profile.GetRawMultiInfo(NAME_FIRST, &first_names); 287 std::vector<base::string16> middle_names; 288 profile.GetRawMultiInfo(NAME_MIDDLE, &middle_names); 289 std::vector<base::string16> last_names; 290 profile.GetRawMultiInfo(NAME_LAST, &last_names); 291 DCHECK_EQ(first_names.size(), middle_names.size()); 292 DCHECK_EQ(first_names.size(), last_names.size()); 293 294 for (size_t i = 0; i < first_names.size(); ++i) { 295 // Add the new name. 296 sql::Statement s(db->GetUniqueStatement( 297 "INSERT INTO autofill_profile_names" 298 " (guid, first_name, middle_name, last_name) " 299 "VALUES (?,?,?,?)")); 300 s.BindString(0, profile.guid()); 301 s.BindString16(1, first_names[i]); 302 s.BindString16(2, middle_names[i]); 303 s.BindString16(3, last_names[i]); 304 305 if (!s.Run()) 306 return false; 307 } 308 return true; 309 } 310 311 bool AddAutofillProfileNames(const AutofillProfile& profile, 312 sql::Connection* db) { 313 std::vector<base::string16> first_names; 314 profile.GetRawMultiInfo(NAME_FIRST, &first_names); 315 std::vector<base::string16> middle_names; 316 profile.GetRawMultiInfo(NAME_MIDDLE, &middle_names); 317 std::vector<base::string16> last_names; 318 profile.GetRawMultiInfo(NAME_LAST, &last_names); 319 std::vector<base::string16> full_names; 320 profile.GetRawMultiInfo(NAME_FULL, &full_names); 321 DCHECK_EQ(first_names.size(), middle_names.size()); 322 DCHECK_EQ(first_names.size(), last_names.size()); 323 DCHECK_EQ(first_names.size(), full_names.size()); 324 325 for (size_t i = 0; i < first_names.size(); ++i) { 326 // Add the new name. 327 sql::Statement s(db->GetUniqueStatement( 328 "INSERT INTO autofill_profile_names" 329 " (guid, first_name, middle_name, last_name, full_name) " 330 "VALUES (?,?,?,?,?)")); 331 s.BindString(0, profile.guid()); 332 s.BindString16(1, first_names[i]); 333 s.BindString16(2, middle_names[i]); 334 s.BindString16(3, last_names[i]); 335 s.BindString16(4, full_names[i]); 336 337 if (!s.Run()) 338 return false; 339 } 340 return true; 341 } 342 343 bool AddAutofillProfileEmails(const AutofillProfile& profile, 344 sql::Connection* db) { 345 std::vector<base::string16> emails; 346 profile.GetRawMultiInfo(EMAIL_ADDRESS, &emails); 347 348 for (size_t i = 0; i < emails.size(); ++i) { 349 // Add the new email. 350 sql::Statement s(db->GetUniqueStatement( 351 "INSERT INTO autofill_profile_emails" 352 " (guid, email) " 353 "VALUES (?,?)")); 354 s.BindString(0, profile.guid()); 355 s.BindString16(1, emails[i]); 356 357 if (!s.Run()) 358 return false; 359 } 360 361 return true; 362 } 363 364 bool AddAutofillProfilePhones(const AutofillProfile& profile, 365 sql::Connection* db) { 366 std::vector<base::string16> numbers; 367 profile.GetRawMultiInfo(PHONE_HOME_WHOLE_NUMBER, &numbers); 368 369 for (size_t i = 0; i < numbers.size(); ++i) { 370 // Add the new number. 371 sql::Statement s(db->GetUniqueStatement( 372 "INSERT INTO autofill_profile_phones" 373 " (guid, number) " 374 "VALUES (?,?)")); 375 s.BindString(0, profile.guid()); 376 s.BindString16(1, numbers[i]); 377 378 if (!s.Run()) 379 return false; 380 } 381 382 return true; 383 } 384 385 bool AddAutofillProfilePieces(const AutofillProfile& profile, 386 sql::Connection* db) { 387 if (!AddAutofillProfileNames(profile, db)) 388 return false; 389 390 if (!AddAutofillProfileEmails(profile, db)) 391 return false; 392 393 if (!AddAutofillProfilePhones(profile, db)) 394 return false; 395 396 return true; 397 } 398 399 bool RemoveAutofillProfilePieces(const std::string& guid, sql::Connection* db) { 400 sql::Statement s1(db->GetUniqueStatement( 401 "DELETE FROM autofill_profile_names WHERE guid = ?")); 402 s1.BindString(0, guid); 403 404 if (!s1.Run()) 405 return false; 406 407 sql::Statement s2(db->GetUniqueStatement( 408 "DELETE FROM autofill_profile_emails WHERE guid = ?")); 409 s2.BindString(0, guid); 410 411 if (!s2.Run()) 412 return false; 413 414 sql::Statement s3(db->GetUniqueStatement( 415 "DELETE FROM autofill_profile_phones WHERE guid = ?")); 416 s3.BindString(0, guid); 417 418 return s3.Run(); 419 } 420 421 WebDatabaseTable::TypeKey GetKey() { 422 // We just need a unique constant. Use the address of a static that 423 // COMDAT folding won't touch in an optimizing linker. 424 static int table_key = 0; 425 return reinterpret_cast<void*>(&table_key); 426 } 427 428 time_t GetEndTime(const base::Time& end) { 429 if (end.is_null() || end == base::Time::Max()) 430 return std::numeric_limits<time_t>::max(); 431 432 return end.ToTimeT(); 433 } 434 435 } // namespace 436 437 // The maximum length allowed for form data. 438 const size_t AutofillTable::kMaxDataLength = 1024; 439 440 AutofillTable::AutofillTable(const std::string& app_locale) 441 : app_locale_(app_locale) { 442 } 443 444 AutofillTable::~AutofillTable() { 445 } 446 447 AutofillTable* AutofillTable::FromWebDatabase(WebDatabase* db) { 448 return static_cast<AutofillTable*>(db->GetTable(GetKey())); 449 } 450 451 WebDatabaseTable::TypeKey AutofillTable::GetTypeKey() const { 452 return GetKey(); 453 } 454 455 bool AutofillTable::CreateTablesIfNecessary() { 456 return (InitMainTable() && InitCreditCardsTable() && InitProfilesTable() && 457 InitProfileNamesTable() && InitProfileEmailsTable() && 458 InitProfilePhonesTable() && InitProfileTrashTable()); 459 } 460 461 bool AutofillTable::IsSyncable() { 462 return true; 463 } 464 465 bool AutofillTable::MigrateToVersion(int version, 466 bool* update_compatible_version) { 467 // Migrate if necessary. 468 switch (version) { 469 case 22: 470 return MigrateToVersion22ClearAutofillEmptyValueElements(); 471 case 23: 472 return MigrateToVersion23AddCardNumberEncryptedColumn(); 473 case 24: 474 return MigrateToVersion24CleanupOversizedStringFields(); 475 case 27: 476 *update_compatible_version = true; 477 return MigrateToVersion27UpdateLegacyCreditCards(); 478 case 30: 479 *update_compatible_version = true; 480 return MigrateToVersion30AddDateModifed(); 481 case 31: 482 *update_compatible_version = true; 483 return MigrateToVersion31AddGUIDToCreditCardsAndProfiles(); 484 case 32: 485 *update_compatible_version = true; 486 return MigrateToVersion32UpdateProfilesAndCreditCards(); 487 case 33: 488 *update_compatible_version = true; 489 return MigrateToVersion33ProfilesBasedOnFirstName(); 490 case 34: 491 *update_compatible_version = true; 492 return MigrateToVersion34ProfilesBasedOnCountryCode(); 493 case 35: 494 *update_compatible_version = true; 495 return MigrateToVersion35GreatBritainCountryCodes(); 496 // Combine migrations 36 and 37. This is due to enhancements to the merge 497 // step when migrating profiles. The original migration from 35 to 36 did 498 // not merge profiles with identical addresses, but the migration from 36 to 499 // 37 does. The step from 35 to 36 should only happen on the Chrome 12 dev 500 // channel. Chrome 12 beta and release users will jump from 35 to 37 501 // directly getting the full benefits of the multi-valued merge as well as 502 // the culling of bad data. 503 case 37: 504 *update_compatible_version = true; 505 return MigrateToVersion37MergeAndCullOlderProfiles(); 506 case 51: 507 // Combine migrations 50 and 51. The migration code from version 49 to 50 508 // worked correctly for users with existing 'origin' columns, but failed 509 // to create these columns for new users. 510 return MigrateToVersion51AddOriginColumn(); 511 case 54: 512 *update_compatible_version = true; 513 return MigrateToVersion54AddI18nFieldsAndRemoveDeprecatedFields(); 514 case 55: 515 *update_compatible_version = true; 516 return MigrateToVersion55MergeAutofillDatesTable(); 517 case 56: 518 *update_compatible_version = true; 519 return MigrateToVersion56AddProfileLanguageCodeForFormatting(); 520 case 57: 521 *update_compatible_version = true; 522 return MigrateToVersion57AddFullNameField(); 523 } 524 return true; 525 } 526 527 bool AutofillTable::AddFormFieldValues( 528 const std::vector<FormFieldData>& elements, 529 std::vector<AutofillChange>* changes) { 530 return AddFormFieldValuesTime(elements, changes, Time::Now()); 531 } 532 533 bool AutofillTable::AddFormFieldValue(const FormFieldData& element, 534 std::vector<AutofillChange>* changes) { 535 return AddFormFieldValueTime(element, changes, Time::Now()); 536 } 537 538 bool AutofillTable::GetFormValuesForElementName( 539 const base::string16& name, 540 const base::string16& prefix, 541 std::vector<base::string16>* values, 542 int limit) { 543 DCHECK(values); 544 sql::Statement s; 545 546 if (prefix.empty()) { 547 s.Assign(db_->GetUniqueStatement( 548 "SELECT value FROM autofill " 549 "WHERE name = ? " 550 "ORDER BY count DESC " 551 "LIMIT ?")); 552 s.BindString16(0, name); 553 s.BindInt(1, limit); 554 } else { 555 base::string16 prefix_lower = base::i18n::ToLower(prefix); 556 base::string16 next_prefix = prefix_lower; 557 next_prefix[next_prefix.length() - 1]++; 558 559 s.Assign(db_->GetUniqueStatement( 560 "SELECT value FROM autofill " 561 "WHERE name = ? AND " 562 "value_lower >= ? AND " 563 "value_lower < ? " 564 "ORDER BY count DESC " 565 "LIMIT ?")); 566 s.BindString16(0, name); 567 s.BindString16(1, prefix_lower); 568 s.BindString16(2, next_prefix); 569 s.BindInt(3, limit); 570 } 571 572 values->clear(); 573 while (s.Step()) 574 values->push_back(s.ColumnString16(0)); 575 return s.Succeeded(); 576 } 577 578 bool AutofillTable::HasFormElements() { 579 sql::Statement s(db_->GetUniqueStatement("SELECT COUNT(*) FROM autofill")); 580 if (!s.Step()) { 581 NOTREACHED(); 582 return false; 583 } 584 return s.ColumnInt(0) > 0; 585 } 586 587 bool AutofillTable::RemoveFormElementsAddedBetween( 588 const Time& delete_begin, 589 const Time& delete_end, 590 std::vector<AutofillChange>* changes) { 591 const time_t delete_begin_time_t = delete_begin.ToTimeT(); 592 const time_t delete_end_time_t = GetEndTime(delete_end); 593 594 // Query for the name, value, count, and access dates of all form elements 595 // that were used between the given times. 596 sql::Statement s(db_->GetUniqueStatement( 597 "SELECT name, value, count, date_created, date_last_used FROM autofill " 598 "WHERE (date_created >= ? AND date_created < ?) OR " 599 " (date_last_used >= ? AND date_last_used < ?)")); 600 s.BindInt64(0, delete_begin_time_t); 601 s.BindInt64(1, delete_end_time_t); 602 s.BindInt64(2, delete_begin_time_t); 603 s.BindInt64(3, delete_end_time_t); 604 605 std::vector<AutofillUpdate> updates; 606 std::vector<AutofillChange> tentative_changes; 607 while (s.Step()) { 608 base::string16 name = s.ColumnString16(0); 609 base::string16 value = s.ColumnString16(1); 610 int count = s.ColumnInt(2); 611 time_t date_created_time_t = s.ColumnInt64(3); 612 time_t date_last_used_time_t = s.ColumnInt64(4); 613 614 // If *all* uses of the element were between |delete_begin| and 615 // |delete_end|, then delete the element. Otherwise, update the use 616 // timestamps and use count. 617 AutofillChange::Type change_type; 618 if (date_created_time_t >= delete_begin_time_t && 619 date_last_used_time_t < delete_end_time_t) { 620 change_type = AutofillChange::REMOVE; 621 } else { 622 change_type = AutofillChange::UPDATE; 623 624 // For all updated elements, set either date_created or date_last_used so 625 // that the range [date_created, date_last_used] no longer overlaps with 626 // [delete_begin, delete_end). Update the count by interpolating. 627 // Precisely, compute the average amount of time between increments to the 628 // count in the original range [date_created, date_last_used]: 629 // avg_delta = (date_last_used_orig - date_created_orig) / (count - 1) 630 // The count can be expressed as 631 // count = 1 + (date_last_used - date_created) / avg_delta 632 // Hence, update the count to 633 // count_new = 1 + (date_last_used_new - date_created_new) / avg_delta 634 // = 1 + ((count - 1) * 635 // (date_last_used_new - date_created_new) / 636 // (date_last_used_orig - date_created_orig)) 637 // Interpolating might not give a result that completely accurately 638 // reflects the user's history, but it's the best that can be done given 639 // the information in the database. 640 AutofillUpdate updated_entry; 641 updated_entry.name = name; 642 updated_entry.value = value; 643 updated_entry.date_created = 644 date_created_time_t < delete_begin_time_t ? 645 date_created_time_t : 646 delete_end_time_t; 647 updated_entry.date_last_used = 648 date_last_used_time_t >= delete_end_time_t ? 649 date_last_used_time_t : 650 delete_begin_time_t - 1; 651 updated_entry.count = 652 1 + 653 Round(1.0 * (count - 1) * 654 (updated_entry.date_last_used - updated_entry.date_created) / 655 (date_last_used_time_t - date_created_time_t)); 656 updates.push_back(updated_entry); 657 } 658 659 tentative_changes.push_back( 660 AutofillChange(change_type, AutofillKey(name, value))); 661 } 662 if (!s.Succeeded()) 663 return false; 664 665 // As a single transaction, remove or update the elements appropriately. 666 sql::Statement s_delete(db_->GetUniqueStatement( 667 "DELETE FROM autofill WHERE date_created >= ? AND date_last_used < ?")); 668 s_delete.BindInt64(0, delete_begin_time_t); 669 s_delete.BindInt64(1, delete_end_time_t); 670 sql::Transaction transaction(db_); 671 if (!transaction.Begin()) 672 return false; 673 if (!s_delete.Run()) 674 return false; 675 for (size_t i = 0; i < updates.size(); ++i) { 676 sql::Statement s_update(db_->GetUniqueStatement( 677 "UPDATE autofill SET date_created = ?, date_last_used = ?, count = ?" 678 "WHERE name = ? AND value = ?")); 679 s_update.BindInt64(0, updates[i].date_created); 680 s_update.BindInt64(1, updates[i].date_last_used); 681 s_update.BindInt(2, updates[i].count); 682 s_update.BindString16(3, updates[i].name); 683 s_update.BindString16(4, updates[i].value); 684 if (!s_update.Run()) 685 return false; 686 } 687 if (!transaction.Commit()) 688 return false; 689 690 *changes = tentative_changes; 691 return true; 692 } 693 694 bool AutofillTable::RemoveExpiredFormElements( 695 std::vector<AutofillChange>* changes) { 696 base::Time expiration_time = 697 base::Time::Now() - base::TimeDelta::FromDays(kExpirationPeriodInDays); 698 699 // Query for the name and value of all form elements that were last used 700 // before the |expiration_time|. 701 sql::Statement select_for_delete(db_->GetUniqueStatement( 702 "SELECT name, value FROM autofill WHERE date_last_used < ?")); 703 select_for_delete.BindInt64(0, expiration_time.ToTimeT()); 704 std::vector<AutofillChange> tentative_changes; 705 while (select_for_delete.Step()) { 706 base::string16 name = select_for_delete.ColumnString16(0); 707 base::string16 value = select_for_delete.ColumnString16(1); 708 tentative_changes.push_back( 709 AutofillChange(AutofillChange::REMOVE, AutofillKey(name, value))); 710 } 711 712 if (!select_for_delete.Succeeded()) 713 return false; 714 715 sql::Statement delete_data_statement(db_->GetUniqueStatement( 716 "DELETE FROM autofill WHERE date_last_used < ?")); 717 delete_data_statement.BindInt64(0, expiration_time.ToTimeT()); 718 if (!delete_data_statement.Run()) 719 return false; 720 721 *changes = tentative_changes; 722 return true; 723 } 724 725 bool AutofillTable::AddFormFieldValuesTime( 726 const std::vector<FormFieldData>& elements, 727 std::vector<AutofillChange>* changes, 728 Time time) { 729 // Only add one new entry for each unique element name. Use |seen_names| to 730 // track this. Add up to |kMaximumUniqueNames| unique entries per form. 731 const size_t kMaximumUniqueNames = 256; 732 std::set<base::string16> seen_names; 733 bool result = true; 734 for (std::vector<FormFieldData>::const_iterator itr = elements.begin(); 735 itr != elements.end(); ++itr) { 736 if (seen_names.size() >= kMaximumUniqueNames) 737 break; 738 if (seen_names.find(itr->name) != seen_names.end()) 739 continue; 740 result = result && AddFormFieldValueTime(*itr, changes, time); 741 seen_names.insert(itr->name); 742 } 743 return result; 744 } 745 746 bool AutofillTable::GetAllAutofillEntries(std::vector<AutofillEntry>* entries) { 747 sql::Statement s(db_->GetUniqueStatement( 748 "SELECT name, value, date_created, date_last_used FROM autofill")); 749 750 while (s.Step()) { 751 base::string16 name = s.ColumnString16(0); 752 base::string16 value = s.ColumnString16(1); 753 Time date_created = Time::FromTimeT(s.ColumnInt64(2)); 754 Time date_last_used = Time::FromTimeT(s.ColumnInt64(3)); 755 entries->push_back( 756 AutofillEntry(AutofillKey(name, value), date_created, date_last_used)); 757 } 758 759 return s.Succeeded(); 760 } 761 762 bool AutofillTable::GetAutofillTimestamps(const base::string16& name, 763 const base::string16& value, 764 Time* date_created, 765 Time* date_last_used) { 766 sql::Statement s(db_->GetUniqueStatement( 767 "SELECT date_created, date_last_used FROM autofill " 768 "WHERE name = ? AND value = ?")); 769 s.BindString16(0, name); 770 s.BindString16(1, value); 771 if (!s.Step()) 772 return false; 773 774 *date_created = Time::FromTimeT(s.ColumnInt64(0)); 775 *date_last_used = Time::FromTimeT(s.ColumnInt64(1)); 776 777 DCHECK(!s.Step()); 778 return true; 779 } 780 781 bool AutofillTable::UpdateAutofillEntries( 782 const std::vector<AutofillEntry>& entries) { 783 if (!entries.size()) 784 return true; 785 786 // Remove all existing entries. 787 for (size_t i = 0; i < entries.size(); ++i) { 788 sql::Statement s(db_->GetUniqueStatement( 789 "DELETE FROM autofill WHERE name = ? AND value = ?")); 790 s.BindString16(0, entries[i].key().name()); 791 s.BindString16(1, entries[i].key().value()); 792 if (!s.Run()) 793 return false; 794 } 795 796 // Insert all the supplied autofill entries. 797 for (size_t i = 0; i < entries.size(); ++i) { 798 if (!InsertAutofillEntry(entries[i])) 799 return false; 800 } 801 802 return true; 803 } 804 805 bool AutofillTable::InsertAutofillEntry(const AutofillEntry& entry) { 806 std::string sql = 807 "INSERT INTO autofill " 808 "(name, value, value_lower, date_created, date_last_used, count) " 809 "VALUES (?, ?, ?, ?, ?, ?)"; 810 sql::Statement s(db_->GetUniqueStatement(sql.c_str())); 811 s.BindString16(0, entry.key().name()); 812 s.BindString16(1, entry.key().value()); 813 s.BindString16(2, base::i18n::ToLower(entry.key().value())); 814 s.BindInt64(3, entry.date_created().ToTimeT()); 815 s.BindInt64(4, entry.date_last_used().ToTimeT()); 816 // TODO(isherman): The counts column is currently synced implicitly as the 817 // number of timestamps. Sync the value explicitly instead, since the DB now 818 // only saves the first and last timestamp, which makes counting timestamps 819 // completely meaningless as a way to track frequency of usage. 820 s.BindInt(5, entry.date_last_used() == entry.date_created() ? 1 : 2); 821 return s.Run(); 822 } 823 824 bool AutofillTable::AddFormFieldValueTime(const FormFieldData& element, 825 std::vector<AutofillChange>* changes, 826 Time time) { 827 sql::Statement s_exists(db_->GetUniqueStatement( 828 "SELECT COUNT(*) FROM autofill WHERE name = ? AND value = ?")); 829 s_exists.BindString16(0, element.name); 830 s_exists.BindString16(1, element.value); 831 if (!s_exists.Step()) 832 return false; 833 834 bool already_exists = s_exists.ColumnInt(0) > 0; 835 if (already_exists) { 836 sql::Statement s(db_->GetUniqueStatement( 837 "UPDATE autofill SET date_last_used = ?, count = count + 1 " 838 "WHERE name = ? AND value = ?")); 839 s.BindInt64(0, time.ToTimeT()); 840 s.BindString16(1, element.name); 841 s.BindString16(2, element.value); 842 if (!s.Run()) 843 return false; 844 } else { 845 time_t time_as_time_t = time.ToTimeT(); 846 sql::Statement s(db_->GetUniqueStatement( 847 "INSERT INTO autofill " 848 "(name, value, value_lower, date_created, date_last_used, count) " 849 "VALUES (?, ?, ?, ?, ?, ?)")); 850 s.BindString16(0, element.name); 851 s.BindString16(1, element.value); 852 s.BindString16(2, base::i18n::ToLower(element.value)); 853 s.BindInt64(3, time_as_time_t); 854 s.BindInt64(4, time_as_time_t); 855 s.BindInt(5, 1); 856 if (!s.Run()) 857 return false; 858 } 859 860 AutofillChange::Type change_type = 861 already_exists ? AutofillChange::UPDATE : AutofillChange::ADD; 862 changes->push_back( 863 AutofillChange(change_type, AutofillKey(element.name, element.value))); 864 return true; 865 } 866 867 868 bool AutofillTable::RemoveFormElement(const base::string16& name, 869 const base::string16& value) { 870 sql::Statement s(db_->GetUniqueStatement( 871 "DELETE FROM autofill WHERE name = ? AND value= ?")); 872 s.BindString16(0, name); 873 s.BindString16(1, value); 874 return s.Run(); 875 } 876 877 bool AutofillTable::AddAutofillProfile(const AutofillProfile& profile) { 878 if (IsAutofillGUIDInTrash(profile.guid())) 879 return true; 880 881 sql::Statement s(db_->GetUniqueStatement( 882 "INSERT INTO autofill_profiles" 883 "(guid, company_name, street_address, dependent_locality, city, state," 884 " zipcode, sorting_code, country_code, date_modified, origin," 885 " language_code)" 886 "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)")); 887 BindAutofillProfileToStatement(profile, &s); 888 889 if (!s.Run()) 890 return false; 891 892 return AddAutofillProfilePieces(profile, db_); 893 } 894 895 bool AutofillTable::GetAutofillProfile(const std::string& guid, 896 AutofillProfile** profile) { 897 DCHECK(base::IsValidGUID(guid)); 898 DCHECK(profile); 899 sql::Statement s(db_->GetUniqueStatement( 900 "SELECT guid, company_name, street_address, dependent_locality, city," 901 " state, zipcode, sorting_code, country_code, date_modified, origin," 902 " language_code " 903 "FROM autofill_profiles " 904 "WHERE guid=?")); 905 s.BindString(0, guid); 906 907 if (!s.Step()) 908 return false; 909 910 scoped_ptr<AutofillProfile> p = AutofillProfileFromStatement(s); 911 912 // Get associated name info. 913 AddAutofillProfileNamesToProfile(db_, p.get()); 914 915 // Get associated email info. 916 AddAutofillProfileEmailsToProfile(db_, p.get()); 917 918 // Get associated phone info. 919 AddAutofillProfilePhonesToProfile(db_, p.get()); 920 921 *profile = p.release(); 922 return true; 923 } 924 925 bool AutofillTable::GetAutofillProfiles( 926 std::vector<AutofillProfile*>* profiles) { 927 DCHECK(profiles); 928 profiles->clear(); 929 930 sql::Statement s(db_->GetUniqueStatement( 931 "SELECT guid " 932 "FROM autofill_profiles")); 933 934 while (s.Step()) { 935 std::string guid = s.ColumnString(0); 936 AutofillProfile* profile = NULL; 937 if (!GetAutofillProfile(guid, &profile)) 938 return false; 939 profiles->push_back(profile); 940 } 941 942 return s.Succeeded(); 943 } 944 945 bool AutofillTable::UpdateAutofillProfile(const AutofillProfile& profile) { 946 DCHECK(base::IsValidGUID(profile.guid())); 947 948 // Don't update anything until the trash has been emptied. There may be 949 // pending modifications to process. 950 if (!IsAutofillProfilesTrashEmpty()) 951 return true; 952 953 AutofillProfile* tmp_profile = NULL; 954 if (!GetAutofillProfile(profile.guid(), &tmp_profile)) 955 return false; 956 957 // Preserve appropriate modification dates by not updating unchanged profiles. 958 scoped_ptr<AutofillProfile> old_profile(tmp_profile); 959 if (*old_profile == profile) 960 return true; 961 962 sql::Statement s(db_->GetUniqueStatement( 963 "UPDATE autofill_profiles " 964 "SET guid=?, company_name=?, street_address=?, dependent_locality=?, " 965 " city=?, state=?, zipcode=?, sorting_code=?, country_code=?, " 966 " date_modified=?, origin=?, language_code=? " 967 "WHERE guid=?")); 968 BindAutofillProfileToStatement(profile, &s); 969 s.BindString(12, profile.guid()); 970 971 bool result = s.Run(); 972 DCHECK_GT(db_->GetLastChangeCount(), 0); 973 if (!result) 974 return result; 975 976 // Remove the old names, emails, and phone numbers. 977 if (!RemoveAutofillProfilePieces(profile.guid(), db_)) 978 return false; 979 980 return AddAutofillProfilePieces(profile, db_); 981 } 982 983 bool AutofillTable::RemoveAutofillProfile(const std::string& guid) { 984 DCHECK(base::IsValidGUID(guid)); 985 986 if (IsAutofillGUIDInTrash(guid)) { 987 sql::Statement s_trash(db_->GetUniqueStatement( 988 "DELETE FROM autofill_profiles_trash WHERE guid = ?")); 989 s_trash.BindString(0, guid); 990 991 bool success = s_trash.Run(); 992 DCHECK_GT(db_->GetLastChangeCount(), 0) << "Expected item in trash"; 993 return success; 994 } 995 996 sql::Statement s(db_->GetUniqueStatement( 997 "DELETE FROM autofill_profiles WHERE guid = ?")); 998 s.BindString(0, guid); 999 1000 if (!s.Run()) 1001 return false; 1002 1003 return RemoveAutofillProfilePieces(guid, db_); 1004 } 1005 1006 bool AutofillTable::ClearAutofillProfiles() { 1007 sql::Statement s1(db_->GetUniqueStatement( 1008 "DELETE FROM autofill_profiles")); 1009 1010 if (!s1.Run()) 1011 return false; 1012 1013 sql::Statement s2(db_->GetUniqueStatement( 1014 "DELETE FROM autofill_profile_names")); 1015 1016 if (!s2.Run()) 1017 return false; 1018 1019 sql::Statement s3(db_->GetUniqueStatement( 1020 "DELETE FROM autofill_profile_emails")); 1021 1022 if (!s3.Run()) 1023 return false; 1024 1025 sql::Statement s4(db_->GetUniqueStatement( 1026 "DELETE FROM autofill_profile_phones")); 1027 1028 return s4.Run(); 1029 } 1030 1031 bool AutofillTable::AddCreditCard(const CreditCard& credit_card) { 1032 sql::Statement s(db_->GetUniqueStatement( 1033 "INSERT INTO credit_cards" 1034 "(guid, name_on_card, expiration_month, expiration_year, " 1035 " card_number_encrypted, date_modified, origin)" 1036 "VALUES (?,?,?,?,?,?,?)")); 1037 BindCreditCardToStatement(credit_card, &s); 1038 1039 if (!s.Run()) 1040 return false; 1041 1042 DCHECK_GT(db_->GetLastChangeCount(), 0); 1043 return true; 1044 } 1045 1046 bool AutofillTable::GetCreditCard(const std::string& guid, 1047 CreditCard** credit_card) { 1048 DCHECK(base::IsValidGUID(guid)); 1049 sql::Statement s(db_->GetUniqueStatement( 1050 "SELECT guid, name_on_card, expiration_month, expiration_year, " 1051 " card_number_encrypted, date_modified, origin " 1052 "FROM credit_cards " 1053 "WHERE guid = ?")); 1054 s.BindString(0, guid); 1055 1056 if (!s.Step()) 1057 return false; 1058 1059 *credit_card = CreditCardFromStatement(s).release(); 1060 return true; 1061 } 1062 1063 bool AutofillTable::GetCreditCards( 1064 std::vector<CreditCard*>* credit_cards) { 1065 DCHECK(credit_cards); 1066 credit_cards->clear(); 1067 1068 sql::Statement s(db_->GetUniqueStatement( 1069 "SELECT guid " 1070 "FROM credit_cards")); 1071 1072 while (s.Step()) { 1073 std::string guid = s.ColumnString(0); 1074 CreditCard* credit_card = NULL; 1075 if (!GetCreditCard(guid, &credit_card)) 1076 return false; 1077 credit_cards->push_back(credit_card); 1078 } 1079 1080 return s.Succeeded(); 1081 } 1082 1083 bool AutofillTable::UpdateCreditCard(const CreditCard& credit_card) { 1084 DCHECK(base::IsValidGUID(credit_card.guid())); 1085 1086 CreditCard* tmp_credit_card = NULL; 1087 if (!GetCreditCard(credit_card.guid(), &tmp_credit_card)) 1088 return false; 1089 1090 // Preserve appropriate modification dates by not updating unchanged cards. 1091 scoped_ptr<CreditCard> old_credit_card(tmp_credit_card); 1092 if (*old_credit_card == credit_card) 1093 return true; 1094 1095 sql::Statement s(db_->GetUniqueStatement( 1096 "UPDATE credit_cards " 1097 "SET guid=?, name_on_card=?, expiration_month=?, " 1098 " expiration_year=?, card_number_encrypted=?, date_modified=?, " 1099 " origin=? " 1100 "WHERE guid=?")); 1101 BindCreditCardToStatement(credit_card, &s); 1102 s.BindString(7, credit_card.guid()); 1103 1104 bool result = s.Run(); 1105 DCHECK_GT(db_->GetLastChangeCount(), 0); 1106 return result; 1107 } 1108 1109 bool AutofillTable::RemoveCreditCard(const std::string& guid) { 1110 DCHECK(base::IsValidGUID(guid)); 1111 sql::Statement s(db_->GetUniqueStatement( 1112 "DELETE FROM credit_cards WHERE guid = ?")); 1113 s.BindString(0, guid); 1114 1115 return s.Run(); 1116 } 1117 1118 bool AutofillTable::RemoveAutofillDataModifiedBetween( 1119 const Time& delete_begin, 1120 const Time& delete_end, 1121 std::vector<std::string>* profile_guids, 1122 std::vector<std::string>* credit_card_guids) { 1123 DCHECK(delete_end.is_null() || delete_begin < delete_end); 1124 1125 time_t delete_begin_t = delete_begin.ToTimeT(); 1126 time_t delete_end_t = GetEndTime(delete_end); 1127 1128 // Remember Autofill profiles in the time range. 1129 sql::Statement s_profiles_get(db_->GetUniqueStatement( 1130 "SELECT guid FROM autofill_profiles " 1131 "WHERE date_modified >= ? AND date_modified < ?")); 1132 s_profiles_get.BindInt64(0, delete_begin_t); 1133 s_profiles_get.BindInt64(1, delete_end_t); 1134 1135 profile_guids->clear(); 1136 while (s_profiles_get.Step()) { 1137 std::string guid = s_profiles_get.ColumnString(0); 1138 profile_guids->push_back(guid); 1139 } 1140 if (!s_profiles_get.Succeeded()) 1141 return false; 1142 1143 // Remove Autofill profiles in the time range. 1144 sql::Statement s_profiles(db_->GetUniqueStatement( 1145 "DELETE FROM autofill_profiles " 1146 "WHERE date_modified >= ? AND date_modified < ?")); 1147 s_profiles.BindInt64(0, delete_begin_t); 1148 s_profiles.BindInt64(1, delete_end_t); 1149 1150 if (!s_profiles.Run()) 1151 return false; 1152 1153 // Remember Autofill credit cards in the time range. 1154 sql::Statement s_credit_cards_get(db_->GetUniqueStatement( 1155 "SELECT guid FROM credit_cards " 1156 "WHERE date_modified >= ? AND date_modified < ?")); 1157 s_credit_cards_get.BindInt64(0, delete_begin_t); 1158 s_credit_cards_get.BindInt64(1, delete_end_t); 1159 1160 credit_card_guids->clear(); 1161 while (s_credit_cards_get.Step()) { 1162 std::string guid = s_credit_cards_get.ColumnString(0); 1163 credit_card_guids->push_back(guid); 1164 } 1165 if (!s_credit_cards_get.Succeeded()) 1166 return false; 1167 1168 // Remove Autofill credit cards in the time range. 1169 sql::Statement s_credit_cards(db_->GetUniqueStatement( 1170 "DELETE FROM credit_cards " 1171 "WHERE date_modified >= ? AND date_modified < ?")); 1172 s_credit_cards.BindInt64(0, delete_begin_t); 1173 s_credit_cards.BindInt64(1, delete_end_t); 1174 1175 return s_credit_cards.Run(); 1176 } 1177 1178 bool AutofillTable::RemoveOriginURLsModifiedBetween( 1179 const Time& delete_begin, 1180 const Time& delete_end, 1181 ScopedVector<AutofillProfile>* profiles) { 1182 DCHECK(delete_end.is_null() || delete_begin < delete_end); 1183 1184 time_t delete_begin_t = delete_begin.ToTimeT(); 1185 time_t delete_end_t = GetEndTime(delete_end); 1186 1187 // Remember Autofill profiles with URL origins in the time range. 1188 sql::Statement s_profiles_get(db_->GetUniqueStatement( 1189 "SELECT guid, origin FROM autofill_profiles " 1190 "WHERE date_modified >= ? AND date_modified < ?")); 1191 s_profiles_get.BindInt64(0, delete_begin_t); 1192 s_profiles_get.BindInt64(1, delete_end_t); 1193 1194 std::vector<std::string> profile_guids; 1195 while (s_profiles_get.Step()) { 1196 std::string guid = s_profiles_get.ColumnString(0); 1197 std::string origin = s_profiles_get.ColumnString(1); 1198 if (GURL(origin).is_valid()) 1199 profile_guids.push_back(guid); 1200 } 1201 if (!s_profiles_get.Succeeded()) 1202 return false; 1203 1204 // Clear out the origins for the found Autofill profiles. 1205 for (std::vector<std::string>::const_iterator it = profile_guids.begin(); 1206 it != profile_guids.end(); ++it) { 1207 sql::Statement s_profile(db_->GetUniqueStatement( 1208 "UPDATE autofill_profiles SET origin='' WHERE guid=?")); 1209 s_profile.BindString(0, *it); 1210 if (!s_profile.Run()) 1211 return false; 1212 1213 AutofillProfile* profile; 1214 if (!GetAutofillProfile(*it, &profile)) 1215 return false; 1216 1217 profiles->push_back(profile); 1218 } 1219 1220 // Remember Autofill credit cards with URL origins in the time range. 1221 sql::Statement s_credit_cards_get(db_->GetUniqueStatement( 1222 "SELECT guid, origin FROM credit_cards " 1223 "WHERE date_modified >= ? AND date_modified < ?")); 1224 s_credit_cards_get.BindInt64(0, delete_begin_t); 1225 s_credit_cards_get.BindInt64(1, delete_end_t); 1226 1227 std::vector<std::string> credit_card_guids; 1228 while (s_credit_cards_get.Step()) { 1229 std::string guid = s_credit_cards_get.ColumnString(0); 1230 std::string origin = s_credit_cards_get.ColumnString(1); 1231 if (GURL(origin).is_valid()) 1232 credit_card_guids.push_back(guid); 1233 } 1234 if (!s_credit_cards_get.Succeeded()) 1235 return false; 1236 1237 // Clear out the origins for the found credit cards. 1238 for (std::vector<std::string>::const_iterator it = credit_card_guids.begin(); 1239 it != credit_card_guids.end(); ++it) { 1240 sql::Statement s_credit_card(db_->GetUniqueStatement( 1241 "UPDATE credit_cards SET origin='' WHERE guid=?")); 1242 s_credit_card.BindString(0, *it); 1243 if (!s_credit_card.Run()) 1244 return false; 1245 } 1246 1247 return true; 1248 } 1249 1250 bool AutofillTable::GetAutofillProfilesInTrash( 1251 std::vector<std::string>* guids) { 1252 guids->clear(); 1253 1254 sql::Statement s(db_->GetUniqueStatement( 1255 "SELECT guid " 1256 "FROM autofill_profiles_trash")); 1257 1258 while (s.Step()) { 1259 std::string guid = s.ColumnString(0); 1260 guids->push_back(guid); 1261 } 1262 1263 return s.Succeeded(); 1264 } 1265 1266 bool AutofillTable::EmptyAutofillProfilesTrash() { 1267 sql::Statement s(db_->GetUniqueStatement( 1268 "DELETE FROM autofill_profiles_trash")); 1269 1270 return s.Run(); 1271 } 1272 1273 1274 bool AutofillTable::AddAutofillGUIDToTrash(const std::string& guid) { 1275 sql::Statement s(db_->GetUniqueStatement( 1276 "INSERT INTO autofill_profiles_trash" 1277 " (guid) " 1278 "VALUES (?)")); 1279 s.BindString(0, guid); 1280 1281 return s.Run(); 1282 } 1283 1284 bool AutofillTable::IsAutofillProfilesTrashEmpty() { 1285 sql::Statement s(db_->GetUniqueStatement( 1286 "SELECT guid " 1287 "FROM autofill_profiles_trash")); 1288 1289 return !s.Step(); 1290 } 1291 1292 bool AutofillTable::IsAutofillGUIDInTrash(const std::string& guid) { 1293 sql::Statement s(db_->GetUniqueStatement( 1294 "SELECT guid " 1295 "FROM autofill_profiles_trash " 1296 "WHERE guid = ?")); 1297 s.BindString(0, guid); 1298 1299 return s.Step(); 1300 } 1301 1302 bool AutofillTable::InitMainTable() { 1303 if (!db_->DoesTableExist("autofill")) { 1304 if (!db_->Execute("CREATE TABLE autofill (" 1305 "name VARCHAR, " 1306 "value VARCHAR, " 1307 "value_lower VARCHAR, " 1308 "date_created INTEGER DEFAULT 0, " 1309 "date_last_used INTEGER DEFAULT 0, " 1310 "count INTEGER DEFAULT 1, " 1311 "PRIMARY KEY (name, value))") || 1312 !db_->Execute("CREATE INDEX autofill_name ON autofill (name)") || 1313 !db_->Execute("CREATE INDEX autofill_name_value_lower ON " 1314 "autofill (name, value_lower)")) { 1315 NOTREACHED(); 1316 return false; 1317 } 1318 } 1319 return true; 1320 } 1321 1322 bool AutofillTable::InitCreditCardsTable() { 1323 if (!db_->DoesTableExist("credit_cards")) { 1324 if (!db_->Execute("CREATE TABLE credit_cards ( " 1325 "guid VARCHAR PRIMARY KEY, " 1326 "name_on_card VARCHAR, " 1327 "expiration_month INTEGER, " 1328 "expiration_year INTEGER, " 1329 "card_number_encrypted BLOB, " 1330 "date_modified INTEGER NOT NULL DEFAULT 0, " 1331 "origin VARCHAR DEFAULT '')")) { 1332 NOTREACHED(); 1333 return false; 1334 } 1335 } 1336 1337 return true; 1338 } 1339 1340 bool AutofillTable::InitProfilesTable() { 1341 if (!db_->DoesTableExist("autofill_profiles")) { 1342 if (!db_->Execute("CREATE TABLE autofill_profiles ( " 1343 "guid VARCHAR PRIMARY KEY, " 1344 "company_name VARCHAR, " 1345 "street_address VARCHAR, " 1346 "dependent_locality VARCHAR, " 1347 "city VARCHAR, " 1348 "state VARCHAR, " 1349 "zipcode VARCHAR, " 1350 "sorting_code VARCHAR, " 1351 "country_code VARCHAR, " 1352 "date_modified INTEGER NOT NULL DEFAULT 0, " 1353 "origin VARCHAR DEFAULT '', " 1354 "language_code VARCHAR)")) { 1355 NOTREACHED(); 1356 return false; 1357 } 1358 } 1359 return true; 1360 } 1361 1362 bool AutofillTable::InitProfileNamesTable() { 1363 if (!db_->DoesTableExist("autofill_profile_names")) { 1364 if (!db_->Execute("CREATE TABLE autofill_profile_names ( " 1365 "guid VARCHAR, " 1366 "first_name VARCHAR, " 1367 "middle_name VARCHAR, " 1368 "last_name VARCHAR, " 1369 "full_name VARCHAR)")) { 1370 NOTREACHED(); 1371 return false; 1372 } 1373 } 1374 return true; 1375 } 1376 1377 bool AutofillTable::InitProfileEmailsTable() { 1378 if (!db_->DoesTableExist("autofill_profile_emails")) { 1379 if (!db_->Execute("CREATE TABLE autofill_profile_emails ( " 1380 "guid VARCHAR, " 1381 "email VARCHAR)")) { 1382 NOTREACHED(); 1383 return false; 1384 } 1385 } 1386 return true; 1387 } 1388 1389 bool AutofillTable::InitProfilePhonesTable() { 1390 if (!db_->DoesTableExist("autofill_profile_phones")) { 1391 if (!db_->Execute("CREATE TABLE autofill_profile_phones ( " 1392 "guid VARCHAR, " 1393 "number VARCHAR)")) { 1394 NOTREACHED(); 1395 return false; 1396 } 1397 } 1398 return true; 1399 } 1400 1401 bool AutofillTable::InitProfileTrashTable() { 1402 if (!db_->DoesTableExist("autofill_profiles_trash")) { 1403 if (!db_->Execute("CREATE TABLE autofill_profiles_trash ( " 1404 "guid VARCHAR)")) { 1405 NOTREACHED(); 1406 return false; 1407 } 1408 } 1409 return true; 1410 } 1411 1412 bool AutofillTable::MigrateToVersion22ClearAutofillEmptyValueElements() { 1413 if (!db_->DoesTableExist("autofill") && 1414 (!db_->Execute("CREATE TABLE autofill (" 1415 " name VARCHAR," 1416 " value VARCHAR," 1417 " value_lower VARCHAR," 1418 " pair_id INTEGER PRIMARY KEY," 1419 " count INTEGER DEFAULT 1)") || 1420 !db_->Execute("CREATE INDEX autofill_name ON autofill (name)") || 1421 !db_->Execute("CREATE INDEX autofill_name_value_lower ON" 1422 " autofill (name, value_lower)") || 1423 !db_->Execute("CREATE TABLE autofill_dates (" 1424 " pair_id INTEGER DEFAULT 0," 1425 " date_created INTEGER DEFAULT 0)") || 1426 !db_->Execute("CREATE INDEX autofill_dates_pair_id ON" 1427 " autofill (pair_id)"))) 1428 return false; 1429 1430 1431 sql::Statement s(db_->GetUniqueStatement( 1432 "SELECT pair_id FROM autofill WHERE TRIM(value) = \"\"")); 1433 if (!s.is_valid()) 1434 return false; 1435 1436 std::set<int64> ids; 1437 while (s.Step()) 1438 ids.insert(s.ColumnInt64(0)); 1439 if (!s.Succeeded()) 1440 return false; 1441 1442 if (!db_->Execute("DELETE FROM autofill WHERE TRIM(value) = \"\"")) 1443 return false; 1444 1445 for (std::set<int64>::const_iterator it = ids.begin(); it != ids.end(); 1446 ++it) { 1447 sql::Statement s(db_->GetUniqueStatement( 1448 "DELETE FROM autofill_dates WHERE pair_id = ?")); 1449 s.BindInt64(0, *it); 1450 if (!s.Run()) 1451 return false; 1452 } 1453 1454 return true; 1455 } 1456 1457 // Add the card_number_encrypted column if credit card table was not 1458 // created in this build (otherwise the column already exists). 1459 // WARNING: Do not change the order of the execution of the SQL 1460 // statements in this case! Profile corruption and data migration 1461 // issues WILL OCCUR. See http://crbug.com/10913 1462 // 1463 // The problem is that if a user has a profile which was created before 1464 // r37036, when the credit_cards table was added, and then failed to 1465 // update this profile between the credit card addition and the addition 1466 // of the "encrypted" columns (44963), the next data migration will put 1467 // the user's profile in an incoherent state: The user will update from 1468 // a data profile set to be earlier than 22, and therefore pass through 1469 // this update case. But because the user did not have a credit_cards 1470 // table before starting Chrome, it will have just been initialized 1471 // above, and so already have these columns -- and thus this data 1472 // update step will have failed. 1473 // 1474 // The false assumption in this case is that at this step in the 1475 // migration, the user has a credit card table, and that this 1476 // table does not include encrypted columns! 1477 // Because this case does not roll back the complete set of SQL 1478 // transactions properly in case of failure (that is, it does not 1479 // roll back the table initialization done above), the incoherent 1480 // profile will now see itself as being at version 22 -- but include a 1481 // fully initialized credit_cards table. Every time Chrome runs, it 1482 // will try to update the web database and fail at this step, unless 1483 // we allow for the faulty assumption described above by checking for 1484 // the existence of the columns only AFTER we've executed the commands 1485 // to add them. 1486 bool AutofillTable::MigrateToVersion23AddCardNumberEncryptedColumn() { 1487 if (!db_->DoesTableExist("autofill_profiles") && 1488 (!db_->Execute("CREATE TABLE autofill_profiles ( " 1489 "label VARCHAR, " 1490 "unique_id INTEGER PRIMARY KEY, " 1491 "first_name VARCHAR, " 1492 "middle_name VARCHAR, " 1493 "last_name VARCHAR, " 1494 "email VARCHAR, " 1495 "company_name VARCHAR, " 1496 "address_line_1 VARCHAR, " 1497 "address_line_2 VARCHAR, " 1498 "city VARCHAR, " 1499 "state VARCHAR, " 1500 "zipcode VARCHAR, " 1501 "country VARCHAR, " 1502 "phone VARCHAR, " 1503 "fax VARCHAR)") || 1504 !db_->Execute("CREATE INDEX autofill_profiles_label_index" 1505 " ON autofill_profiles (label)"))) 1506 return false; 1507 1508 if (!db_->DoesTableExist("credit_cards") && 1509 (!db_->Execute("CREATE TABLE credit_cards ( " 1510 "label VARCHAR, " 1511 "unique_id INTEGER PRIMARY KEY, " 1512 "name_on_card VARCHAR, " 1513 "type VARCHAR, " 1514 "card_number VARCHAR, " 1515 "expiration_month INTEGER, " 1516 "expiration_year INTEGER, " 1517 "verification_code VARCHAR, " 1518 "billing_address VARCHAR, " 1519 "shipping_address VARCHAR)") || 1520 !db_->Execute("CREATE INDEX credit_cards_label_index" 1521 " ON credit_cards (label)"))) 1522 return false; 1523 1524 if (!db_->DoesColumnExist("credit_cards", "card_number_encrypted")) { 1525 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN " 1526 "card_number_encrypted BLOB DEFAULT NULL")) { 1527 return false; 1528 } 1529 } 1530 1531 if (!db_->DoesColumnExist("credit_cards", "verification_code_encrypted")) { 1532 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN " 1533 "verification_code_encrypted BLOB DEFAULT NULL")) { 1534 return false; 1535 } 1536 } 1537 1538 return true; 1539 } 1540 1541 // One-time cleanup for http://crbug.com/38364 - In the presence of 1542 // multi-byte UTF-8 characters, that bug could cause Autofill strings 1543 // to grow larger and more corrupt with each save. The cleanup removes 1544 // any row with a string field larger than a reasonable size. The string 1545 // fields examined here are precisely the ones that were subject to 1546 // corruption by the original bug. 1547 bool AutofillTable::MigrateToVersion24CleanupOversizedStringFields() { 1548 const std::string autofill_is_too_big = 1549 "max(length(name), length(value)) > 500"; 1550 1551 const std::string credit_cards_is_too_big = 1552 "max(length(label), length(name_on_card), length(type), " 1553 " length(expiration_month), length(expiration_year), " 1554 " length(billing_address), length(shipping_address) " 1555 ") > 500"; 1556 1557 const std::string autofill_profiles_is_too_big = 1558 "max(length(label), length(first_name), " 1559 " length(middle_name), length(last_name), length(email), " 1560 " length(company_name), length(address_line_1), " 1561 " length(address_line_2), length(city), length(state), " 1562 " length(zipcode), length(country), length(phone)) > 500"; 1563 1564 std::string query = "DELETE FROM autofill_dates WHERE pair_id IN (" 1565 "SELECT pair_id FROM autofill WHERE " + autofill_is_too_big + ")"; 1566 1567 if (!db_->Execute(query.c_str())) 1568 return false; 1569 1570 query = "DELETE FROM autofill WHERE " + autofill_is_too_big; 1571 1572 if (!db_->Execute(query.c_str())) 1573 return false; 1574 1575 // Only delete from legacy credit card tables where specific columns exist. 1576 if (db_->DoesColumnExist("credit_cards", "label") && 1577 db_->DoesColumnExist("credit_cards", "name_on_card") && 1578 db_->DoesColumnExist("credit_cards", "type") && 1579 db_->DoesColumnExist("credit_cards", "expiration_month") && 1580 db_->DoesColumnExist("credit_cards", "expiration_year") && 1581 db_->DoesColumnExist("credit_cards", "billing_address") && 1582 db_->DoesColumnExist("credit_cards", "shipping_address") && 1583 db_->DoesColumnExist("autofill_profiles", "label")) { 1584 query = "DELETE FROM credit_cards WHERE (" + credit_cards_is_too_big + 1585 ") OR label IN (SELECT label FROM autofill_profiles WHERE " + 1586 autofill_profiles_is_too_big + ")"; 1587 1588 if (!db_->Execute(query.c_str())) 1589 return false; 1590 } 1591 1592 if (db_->DoesColumnExist("autofill_profiles", "label")) { 1593 query = "DELETE FROM autofill_profiles WHERE " + 1594 autofill_profiles_is_too_big; 1595 1596 if (!db_->Execute(query.c_str())) 1597 return false; 1598 } 1599 1600 return true; 1601 } 1602 1603 // Change the credit_cards.billing_address column from a string to an 1604 // int. The stored string is the label of an address, so we have to 1605 // select the unique ID of this address using the label as a foreign 1606 // key into the |autofill_profiles| table. 1607 bool AutofillTable::MigrateToVersion27UpdateLegacyCreditCards() { 1608 // Only migrate from legacy credit card tables where specific columns 1609 // exist. 1610 if (!(db_->DoesColumnExist("credit_cards", "unique_id") && 1611 db_->DoesColumnExist("credit_cards", "billing_address") && 1612 db_->DoesColumnExist("autofill_profiles", "unique_id"))) { 1613 return true; 1614 } 1615 1616 std::string stmt = 1617 "SELECT credit_cards.unique_id, autofill_profiles.unique_id " 1618 "FROM autofill_profiles, credit_cards " 1619 "WHERE credit_cards.billing_address = autofill_profiles.label"; 1620 sql::Statement s(db_->GetUniqueStatement(stmt.c_str())); 1621 1622 std::map<int, int> cc_billing_map; 1623 while (s.Step()) 1624 cc_billing_map[s.ColumnInt(0)] = s.ColumnInt(1); 1625 if (!s.Succeeded()) 1626 return false; 1627 1628 // Windows already stores the IDs as strings in |billing_address|. Try 1629 // to convert those. 1630 if (cc_billing_map.empty()) { 1631 std::string stmt = "SELECT unique_id,billing_address FROM credit_cards"; 1632 sql::Statement s(db_->GetUniqueStatement(stmt.c_str())); 1633 1634 while (s.Step()) { 1635 int id = 0; 1636 if (base::StringToInt(s.ColumnString(1), &id)) 1637 cc_billing_map[s.ColumnInt(0)] = id; 1638 } 1639 if (!s.Succeeded()) 1640 return false; 1641 } 1642 1643 if (!db_->Execute("CREATE TABLE credit_cards_temp ( " 1644 "label VARCHAR, " 1645 "unique_id INTEGER PRIMARY KEY, " 1646 "name_on_card VARCHAR, " 1647 "type VARCHAR, " 1648 "card_number VARCHAR, " 1649 "expiration_month INTEGER, " 1650 "expiration_year INTEGER, " 1651 "verification_code VARCHAR, " 1652 "billing_address INTEGER, " 1653 "shipping_address VARCHAR, " 1654 "card_number_encrypted BLOB, " 1655 "verification_code_encrypted BLOB)")) { 1656 return false; 1657 } 1658 1659 if (!db_->Execute( 1660 "INSERT INTO credit_cards_temp " 1661 "SELECT label,unique_id,name_on_card,type,card_number," 1662 "expiration_month,expiration_year,verification_code,0," 1663 "shipping_address,card_number_encrypted," 1664 "verification_code_encrypted FROM credit_cards")) { 1665 return false; 1666 } 1667 1668 if (!db_->Execute("DROP TABLE credit_cards")) 1669 return false; 1670 1671 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards")) 1672 return false; 1673 1674 for (std::map<int, int>::const_iterator iter = cc_billing_map.begin(); 1675 iter != cc_billing_map.end(); ++iter) { 1676 sql::Statement s(db_->GetCachedStatement( 1677 SQL_FROM_HERE, 1678 "UPDATE credit_cards SET billing_address=? WHERE unique_id=?")); 1679 s.BindInt(0, (*iter).second); 1680 s.BindInt(1, (*iter).first); 1681 1682 if (!s.Run()) 1683 return false; 1684 } 1685 1686 return true; 1687 } 1688 1689 bool AutofillTable::MigrateToVersion30AddDateModifed() { 1690 // Add date_modified to autofill_profiles. 1691 if (!db_->DoesColumnExist("autofill_profiles", "date_modified")) { 1692 if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN " 1693 "date_modified INTEGER NON NULL DEFAULT 0")) { 1694 return false; 1695 } 1696 1697 sql::Statement s(db_->GetUniqueStatement( 1698 "UPDATE autofill_profiles SET date_modified=?")); 1699 s.BindInt64(0, Time::Now().ToTimeT()); 1700 1701 if (!s.Run()) 1702 return false; 1703 } 1704 1705 // Add date_modified to credit_cards. 1706 if (!db_->DoesColumnExist("credit_cards", "date_modified")) { 1707 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN " 1708 "date_modified INTEGER NON NULL DEFAULT 0")) { 1709 return false; 1710 } 1711 1712 sql::Statement s(db_->GetUniqueStatement( 1713 "UPDATE credit_cards SET date_modified=?")); 1714 s.BindInt64(0, Time::Now().ToTimeT()); 1715 1716 if (!s.Run()) 1717 return false; 1718 } 1719 1720 return true; 1721 } 1722 1723 bool AutofillTable::MigrateToVersion31AddGUIDToCreditCardsAndProfiles() { 1724 // Note that we need to check for the guid column's existence due to the 1725 // fact that for a version 22 database the |autofill_profiles| table 1726 // gets created fresh with |InitAutofillProfilesTable|. 1727 if (!db_->DoesColumnExist("autofill_profiles", "guid")) { 1728 if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN " 1729 "guid VARCHAR NOT NULL DEFAULT \"\"")) { 1730 return false; 1731 } 1732 1733 // Set all the |guid| fields to valid values. 1734 1735 sql::Statement s(db_->GetUniqueStatement("SELECT unique_id " 1736 "FROM autofill_profiles")); 1737 1738 while (s.Step()) { 1739 sql::Statement update_s( 1740 db_->GetUniqueStatement("UPDATE autofill_profiles " 1741 "SET guid=? WHERE unique_id=?")); 1742 update_s.BindString(0, base::GenerateGUID()); 1743 update_s.BindInt(1, s.ColumnInt(0)); 1744 1745 if (!update_s.Run()) 1746 return false; 1747 } 1748 if (!s.Succeeded()) 1749 return false; 1750 } 1751 1752 // Note that we need to check for the guid column's existence due to the 1753 // fact that for a version 22 database the |autofill_profiles| table 1754 // gets created fresh with |InitAutofillProfilesTable|. 1755 if (!db_->DoesColumnExist("credit_cards", "guid")) { 1756 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN " 1757 "guid VARCHAR NOT NULL DEFAULT \"\"")) { 1758 return false; 1759 } 1760 1761 // Set all the |guid| fields to valid values. 1762 1763 sql::Statement s(db_->GetUniqueStatement("SELECT unique_id " 1764 "FROM credit_cards")); 1765 1766 while (s.Step()) { 1767 sql::Statement update_s( 1768 db_->GetUniqueStatement("UPDATE credit_cards " 1769 "set guid=? WHERE unique_id=?")); 1770 update_s.BindString(0, base::GenerateGUID()); 1771 update_s.BindInt(1, s.ColumnInt(0)); 1772 1773 if (!update_s.Run()) 1774 return false; 1775 } 1776 if (!s.Succeeded()) 1777 return false; 1778 } 1779 1780 return true; 1781 } 1782 1783 bool AutofillTable::MigrateToVersion32UpdateProfilesAndCreditCards() { 1784 if (db_->DoesColumnExist("autofill_profiles", "unique_id")) { 1785 if (!db_->Execute("CREATE TABLE autofill_profiles_temp ( " 1786 "guid VARCHAR PRIMARY KEY, " 1787 "label VARCHAR, " 1788 "first_name VARCHAR, " 1789 "middle_name VARCHAR, " 1790 "last_name VARCHAR, " 1791 "email VARCHAR, " 1792 "company_name VARCHAR, " 1793 "address_line_1 VARCHAR, " 1794 "address_line_2 VARCHAR, " 1795 "city VARCHAR, " 1796 "state VARCHAR, " 1797 "zipcode VARCHAR, " 1798 "country VARCHAR, " 1799 "phone VARCHAR, " 1800 "date_modified INTEGER NOT NULL DEFAULT 0)")) { 1801 return false; 1802 } 1803 1804 if (!db_->Execute( 1805 "INSERT INTO autofill_profiles_temp " 1806 "SELECT guid, label, first_name, middle_name, last_name, email, " 1807 "company_name, address_line_1, address_line_2, city, state, " 1808 "zipcode, country, phone, date_modified " 1809 "FROM autofill_profiles")) { 1810 return false; 1811 } 1812 1813 if (!db_->Execute("DROP TABLE autofill_profiles")) 1814 return false; 1815 1816 if (!db_->Execute( 1817 "ALTER TABLE autofill_profiles_temp RENAME TO autofill_profiles")) { 1818 return false; 1819 } 1820 } 1821 1822 if (db_->DoesColumnExist("credit_cards", "unique_id")) { 1823 if (!db_->Execute("CREATE TABLE credit_cards_temp ( " 1824 "guid VARCHAR PRIMARY KEY, " 1825 "label VARCHAR, " 1826 "name_on_card VARCHAR, " 1827 "expiration_month INTEGER, " 1828 "expiration_year INTEGER, " 1829 "card_number_encrypted BLOB, " 1830 "date_modified INTEGER NOT NULL DEFAULT 0)")) { 1831 return false; 1832 } 1833 1834 if (!db_->Execute( 1835 "INSERT INTO credit_cards_temp " 1836 "SELECT guid, label, name_on_card, expiration_month, " 1837 "expiration_year, card_number_encrypted, date_modified " 1838 "FROM credit_cards")) { 1839 return false; 1840 } 1841 1842 if (!db_->Execute("DROP TABLE credit_cards")) 1843 return false; 1844 1845 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards")) 1846 return false; 1847 } 1848 1849 return true; 1850 } 1851 1852 // Test the existence of the |first_name| column as an indication that 1853 // we need a migration. It is possible that the new |autofill_profiles| 1854 // schema is in place because the table was newly created when migrating 1855 // from a pre-version-22 database. 1856 bool AutofillTable::MigrateToVersion33ProfilesBasedOnFirstName() { 1857 if (!db_->DoesTableExist("autofill_profile_names") && 1858 !db_->Execute("CREATE TABLE autofill_profile_names ( " 1859 "guid VARCHAR, " 1860 "first_name VARCHAR, " 1861 "middle_name VARCHAR, " 1862 "last_name VARCHAR)")) 1863 return false; 1864 1865 if (!db_->DoesTableExist("autofill_profile_emails") && 1866 !db_->Execute("CREATE TABLE autofill_profile_emails ( " 1867 "guid VARCHAR, " 1868 "email VARCHAR)")) 1869 return false; 1870 1871 if (!db_->DoesTableExist("autofill_profile_phones") && 1872 !db_->Execute("CREATE TABLE autofill_profile_phones ( " 1873 "guid VARCHAR, " 1874 "type INTEGER DEFAULT 0, " 1875 "number VARCHAR)")) 1876 return false; 1877 1878 if (db_->DoesColumnExist("autofill_profiles", "first_name")) { 1879 // Create autofill_profiles_temp table that will receive the data. 1880 if (!db_->DoesTableExist("autofill_profiles_temp")) { 1881 if (!db_->Execute("CREATE TABLE autofill_profiles_temp ( " 1882 "guid VARCHAR PRIMARY KEY, " 1883 "company_name VARCHAR, " 1884 "address_line_1 VARCHAR, " 1885 "address_line_2 VARCHAR, " 1886 "city VARCHAR, " 1887 "state VARCHAR, " 1888 "zipcode VARCHAR, " 1889 "country VARCHAR, " 1890 "date_modified INTEGER NOT NULL DEFAULT 0)")) { 1891 return false; 1892 } 1893 } 1894 1895 sql::Statement s(db_->GetUniqueStatement( 1896 "SELECT guid, first_name, middle_name, last_name, email, " 1897 "company_name, address_line_1, address_line_2, city, state, " 1898 "zipcode, country, phone, date_modified " 1899 "FROM autofill_profiles")); 1900 1901 while (s.Step()) { 1902 AutofillProfile profile; 1903 int index = 0; 1904 profile.set_guid(s.ColumnString(index++)); 1905 DCHECK(base::IsValidGUID(profile.guid())); 1906 1907 profile.SetRawInfo(NAME_FIRST, s.ColumnString16(index++)); 1908 profile.SetRawInfo(NAME_MIDDLE, s.ColumnString16(index++)); 1909 profile.SetRawInfo(NAME_LAST, s.ColumnString16(index++)); 1910 profile.SetRawInfo(EMAIL_ADDRESS, s.ColumnString16(index++)); 1911 profile.SetRawInfo(COMPANY_NAME, s.ColumnString16(index++)); 1912 profile.SetRawInfo(ADDRESS_HOME_LINE1, s.ColumnString16(index++)); 1913 profile.SetRawInfo(ADDRESS_HOME_LINE2, s.ColumnString16(index++)); 1914 profile.SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++)); 1915 profile.SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++)); 1916 profile.SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++)); 1917 profile.SetInfo(AutofillType(ADDRESS_HOME_COUNTRY), 1918 s.ColumnString16(index++), app_locale_); 1919 profile.SetRawInfo(PHONE_HOME_WHOLE_NUMBER, s.ColumnString16(index++)); 1920 int64 date_modified = s.ColumnInt64(index++); 1921 1922 sql::Statement s_insert(db_->GetUniqueStatement( 1923 "INSERT INTO autofill_profiles_temp" 1924 "(guid, company_name, address_line_1, address_line_2, city," 1925 " state, zipcode, country, date_modified)" 1926 "VALUES (?,?,?,?,?,?,?,?,?)")); 1927 index = 0; 1928 s_insert.BindString(index++, profile.guid()); 1929 s_insert.BindString16(index++, profile.GetRawInfo(COMPANY_NAME)); 1930 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_LINE1)); 1931 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_LINE2)); 1932 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_CITY)); 1933 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_STATE)); 1934 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_ZIP)); 1935 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_COUNTRY)); 1936 s_insert.BindInt64(index++, date_modified); 1937 1938 if (!s_insert.Run()) 1939 return false; 1940 1941 // Add the other bits: names, emails, and phone numbers. 1942 if (!AddAutofillProfileNamesForVersion3x(profile, db_) || 1943 !AddAutofillProfileEmails(profile, db_) || 1944 !AddAutofillProfilePhones(profile, db_)) { 1945 return false; 1946 } 1947 } // endwhile 1948 if (!s.Succeeded()) 1949 return false; 1950 1951 if (!db_->Execute("DROP TABLE autofill_profiles")) 1952 return false; 1953 1954 if (!db_->Execute( 1955 "ALTER TABLE autofill_profiles_temp RENAME TO autofill_profiles")) { 1956 return false; 1957 } 1958 } 1959 1960 // Remove the labels column from the credit_cards table. 1961 if (db_->DoesColumnExist("credit_cards", "label")) { 1962 if (!db_->Execute("CREATE TABLE credit_cards_temp ( " 1963 "guid VARCHAR PRIMARY KEY, " 1964 "name_on_card VARCHAR, " 1965 "expiration_month INTEGER, " 1966 "expiration_year INTEGER, " 1967 "card_number_encrypted BLOB, " 1968 "date_modified INTEGER NOT NULL DEFAULT 0)")) { 1969 return false; 1970 } 1971 1972 if (!db_->Execute( 1973 "INSERT INTO credit_cards_temp " 1974 "SELECT guid, name_on_card, expiration_month, " 1975 "expiration_year, card_number_encrypted, date_modified " 1976 "FROM credit_cards")) { 1977 return false; 1978 } 1979 1980 if (!db_->Execute("DROP TABLE credit_cards")) 1981 return false; 1982 1983 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards")) 1984 return false; 1985 } 1986 1987 return true; 1988 } 1989 1990 // Test the existence of the |country_code| column as an indication that 1991 // we need a migration. It is possible that the new |autofill_profiles| 1992 // schema is in place because the table was newly created when migrating 1993 // from a pre-version-22 database. 1994 bool AutofillTable::MigrateToVersion34ProfilesBasedOnCountryCode() { 1995 if (!db_->DoesColumnExist("autofill_profiles", "country_code")) { 1996 if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN " 1997 "country_code VARCHAR")) { 1998 return false; 1999 } 2000 2001 // Set all the |country_code| fields to match existing |country| values. 2002 sql::Statement s(db_->GetUniqueStatement("SELECT guid, country " 2003 "FROM autofill_profiles")); 2004 2005 while (s.Step()) { 2006 sql::Statement update_s( 2007 db_->GetUniqueStatement("UPDATE autofill_profiles " 2008 "SET country_code=? WHERE guid=?")); 2009 2010 base::string16 country = s.ColumnString16(1); 2011 update_s.BindString(0, AutofillCountry::GetCountryCode(country, 2012 app_locale_)); 2013 update_s.BindString(1, s.ColumnString(0)); 2014 2015 if (!update_s.Run()) 2016 return false; 2017 } 2018 if (!s.Succeeded()) 2019 return false; 2020 } 2021 2022 return true; 2023 } 2024 2025 // Correct all country codes with value "UK" to be "GB". This data 2026 // was mistakenly introduced in build 686.0. This migration is to clean 2027 // it up. See http://crbug.com/74511 for details. 2028 bool AutofillTable::MigrateToVersion35GreatBritainCountryCodes() { 2029 sql::Statement s(db_->GetUniqueStatement( 2030 "UPDATE autofill_profiles SET country_code=\"GB\" " 2031 "WHERE country_code=\"UK\"")); 2032 2033 return s.Run(); 2034 } 2035 2036 // Merge and cull older profiles where possible. 2037 bool AutofillTable::MigrateToVersion37MergeAndCullOlderProfiles() { 2038 if (!db_->DoesTableExist("autofill_profiles_trash") && 2039 !db_->Execute("CREATE TABLE autofill_profiles_trash (guid VARCHAR)")) 2040 return false; 2041 2042 sql::Statement s(db_->GetUniqueStatement( 2043 "SELECT guid, date_modified FROM autofill_profiles")); 2044 2045 // Accumulate the good profiles. 2046 std::vector<AutofillProfile> accumulated_profiles; 2047 std::vector<AutofillProfile*> accumulated_profiles_p; 2048 std::map<std::string, int64> modification_map; 2049 while (s.Step()) { 2050 std::string guid = s.ColumnString(0); 2051 int64 date_modified = s.ColumnInt64(1); 2052 modification_map.insert( 2053 std::pair<std::string, int64>(guid, date_modified)); 2054 2055 sql::Statement s(db_->GetUniqueStatement( 2056 "SELECT guid, company_name, address_line_1, address_line_2, city, " 2057 " state, zipcode, country, country_code, date_modified " 2058 "FROM autofill_profiles " 2059 "WHERE guid=?")); 2060 s.BindString(0, guid); 2061 2062 if (!s.Step()) 2063 return false; 2064 2065 scoped_ptr<AutofillProfile> profile(new AutofillProfile); 2066 int index = 0; 2067 profile->set_guid(s.ColumnString(index++)); 2068 DCHECK(base::IsValidGUID(profile->guid())); 2069 2070 profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(index++)); 2071 profile->SetRawInfo(ADDRESS_HOME_LINE1, s.ColumnString16(index++)); 2072 profile->SetRawInfo(ADDRESS_HOME_LINE2, s.ColumnString16(index++)); 2073 profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++)); 2074 profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++)); 2075 profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++)); 2076 // Intentionally skip column 7, which stores the localized country name. 2077 index++; 2078 profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(index++)); 2079 // Intentionally skip column 9, which stores the profile's modification 2080 // date. 2081 index++; 2082 profile->set_origin(s.ColumnString(index++)); 2083 2084 // Get associated name info. 2085 AddAutofillProfileNamesToProfileForVersion37(db_, profile.get()); 2086 2087 // Get associated email info. 2088 AddAutofillProfileEmailsToProfile(db_, profile.get()); 2089 2090 // Get associated phone info. 2091 AddAutofillProfilePhonesToProfile(db_, profile.get()); 2092 2093 if (PersonalDataManager::IsValidLearnableProfile(*profile, app_locale_)) { 2094 std::vector<AutofillProfile> merged_profiles; 2095 std::string merged_guid = PersonalDataManager::MergeProfile( 2096 *profile, accumulated_profiles_p, app_locale_, &merged_profiles); 2097 2098 std::swap(accumulated_profiles, merged_profiles); 2099 2100 accumulated_profiles_p.clear(); 2101 accumulated_profiles_p.resize(accumulated_profiles.size()); 2102 std::transform(accumulated_profiles.begin(), 2103 accumulated_profiles.end(), 2104 accumulated_profiles_p.begin(), 2105 address_of<AutofillProfile>); 2106 2107 // If the profile got merged trash the original. 2108 if (merged_guid != profile->guid()) 2109 AddAutofillGUIDToTrash(profile->guid()); 2110 } else { 2111 // An invalid profile, so trash it. 2112 AddAutofillGUIDToTrash(profile->guid()); 2113 } 2114 } // endwhile 2115 if (!s.Succeeded()) 2116 return false; 2117 2118 // Drop the current profiles. 2119 if (!ClearAutofillProfiles()) 2120 return false; 2121 2122 // Add the newly merged profiles back in. 2123 for (std::vector<AutofillProfile>::const_iterator 2124 iter = accumulated_profiles.begin(); 2125 iter != accumulated_profiles.end(); 2126 ++iter) { 2127 // Save the profile with its original modification date. 2128 std::map<std::string, int64>::const_iterator date_item = 2129 modification_map.find(iter->guid()); 2130 if (date_item == modification_map.end()) 2131 return false; 2132 2133 sql::Statement s(db_->GetUniqueStatement( 2134 "INSERT INTO autofill_profiles" 2135 "(guid, company_name, address_line_1, address_line_2, city, state," 2136 " zipcode, country, country_code, date_modified)" 2137 "VALUES (?,?,?,?,?,?,?,?,?,?)")); 2138 int index = 0; 2139 s.BindString(index++, iter->guid()); 2140 s.BindString16(index++, GetInfo(*iter, COMPANY_NAME)); 2141 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_LINE1)); 2142 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_LINE2)); 2143 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_CITY)); 2144 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_STATE)); 2145 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_ZIP)); 2146 s.BindString16(index++, base::string16()); // This column is deprecated. 2147 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_COUNTRY)); 2148 s.BindInt64(index++, date_item->second); 2149 2150 if (!s.Run()) 2151 return false; 2152 2153 if (!AddAutofillProfileNamesForVersion3x(*iter, db_) || 2154 !AddAutofillProfileEmails(*iter, db_) || 2155 !AddAutofillProfilePhones(*iter, db_)) { 2156 return false; 2157 } 2158 } 2159 2160 return true; 2161 } 2162 2163 bool AutofillTable::MigrateToVersion51AddOriginColumn() { 2164 sql::Transaction transaction(db_); 2165 if (!transaction.Begin()) 2166 return false; 2167 2168 // Add origin to autofill_profiles. 2169 if (!db_->DoesColumnExist("autofill_profiles", "origin") && 2170 !db_->Execute("ALTER TABLE autofill_profiles " 2171 "ADD COLUMN origin VARCHAR DEFAULT ''")) { 2172 return false; 2173 } 2174 2175 // Add origin to credit_cards. 2176 if (!db_->DoesColumnExist("credit_cards", "origin") && 2177 !db_->Execute("ALTER TABLE credit_cards " 2178 "ADD COLUMN origin VARCHAR DEFAULT ''")) { 2179 return false; 2180 } 2181 2182 return transaction.Commit(); 2183 } 2184 2185 bool AutofillTable::MigrateToVersion54AddI18nFieldsAndRemoveDeprecatedFields() { 2186 sql::Transaction transaction(db_); 2187 if (!transaction.Begin()) 2188 return false; 2189 2190 // Test the existence of the |address_line_1| column as an indication that a 2191 // migration is needed. It is possible that the new |autofill_profile_phones| 2192 // schema is in place because the table was newly created when migrating from 2193 // a pre-version-23 database. 2194 if (db_->DoesColumnExist("autofill_profiles", "address_line_1")) { 2195 // Create a temporary copy of the autofill_profiles table in the (newer) 2196 // version 54 format. This table 2197 // (a) adds columns for street_address, dependent_locality, and 2198 // sorting_code, 2199 // (b) removes the address_line_1 and address_line_2 columns, which are 2200 // replaced by the street_address column, and 2201 // (c) removes the country column, which was long deprecated. 2202 if (db_->DoesTableExist("autofill_profiles_temp") || 2203 !db_->Execute("CREATE TABLE autofill_profiles_temp ( " 2204 "guid VARCHAR PRIMARY KEY, " 2205 "company_name VARCHAR, " 2206 "street_address VARCHAR, " 2207 "dependent_locality VARCHAR, " 2208 "city VARCHAR, " 2209 "state VARCHAR, " 2210 "zipcode VARCHAR, " 2211 "sorting_code VARCHAR, " 2212 "country_code VARCHAR, " 2213 "date_modified INTEGER NOT NULL DEFAULT 0, " 2214 "origin VARCHAR DEFAULT '')")) { 2215 return false; 2216 } 2217 2218 // Copy over the data from the autofill_profiles table, taking care to merge 2219 // the address lines 1 and 2 into the new street_address column. 2220 if (!db_->Execute("INSERT INTO autofill_profiles_temp " 2221 "SELECT guid, company_name, '', '', city, state, zipcode," 2222 " '', country_code, date_modified, origin " 2223 "FROM autofill_profiles")) { 2224 return false; 2225 } 2226 sql::Statement s(db_->GetUniqueStatement( 2227 "SELECT guid, address_line_1, address_line_2 FROM autofill_profiles")); 2228 while (s.Step()) { 2229 std::string guid = s.ColumnString(0); 2230 base::string16 line1 = s.ColumnString16(1); 2231 base::string16 line2 = s.ColumnString16(2); 2232 base::string16 street_address = line1; 2233 if (!line2.empty()) 2234 street_address += base::ASCIIToUTF16("\n") + line2; 2235 2236 sql::Statement s_update(db_->GetUniqueStatement( 2237 "UPDATE autofill_profiles_temp SET street_address=? WHERE guid=?")); 2238 s_update.BindString16(0, street_address); 2239 s_update.BindString(1, guid); 2240 if (!s_update.Run()) 2241 return false; 2242 } 2243 if (!s.Succeeded()) 2244 return false; 2245 2246 // Delete the existing (version 53) table and replace it with the contents 2247 // of the temporary table. 2248 if (!db_->Execute("DROP TABLE autofill_profiles") || 2249 !db_->Execute("ALTER TABLE autofill_profiles_temp " 2250 "RENAME TO autofill_profiles")) { 2251 return false; 2252 } 2253 } 2254 2255 // Test the existence of the |type| column as an indication that a migration 2256 // is needed. It is possible that the new |autofill_profile_phones| schema is 2257 // in place because the table was newly created when migrating from a 2258 // pre-version-23 database. 2259 if (db_->DoesColumnExist("autofill_profile_phones", "type")) { 2260 // Create a temporary copy of the autofill_profile_phones table in the 2261 // (newer) version 54 format. This table removes the deprecated |type| 2262 // column. 2263 if (db_->DoesTableExist("autofill_profile_phones_temp") || 2264 !db_->Execute("CREATE TABLE autofill_profile_phones_temp ( " 2265 "guid VARCHAR, " 2266 "number VARCHAR)")) { 2267 return false; 2268 } 2269 2270 // Copy over the data from the autofill_profile_phones table. 2271 if (!db_->Execute("INSERT INTO autofill_profile_phones_temp " 2272 "SELECT guid, number FROM autofill_profile_phones")) { 2273 return false; 2274 } 2275 2276 // Delete the existing (version 53) table and replace it with the contents 2277 // of the temporary table. 2278 if (!db_->Execute("DROP TABLE autofill_profile_phones")) 2279 return false; 2280 if (!db_->Execute("ALTER TABLE autofill_profile_phones_temp " 2281 "RENAME TO autofill_profile_phones")) { 2282 return false; 2283 } 2284 } 2285 2286 return transaction.Commit(); 2287 } 2288 2289 bool AutofillTable::MigrateToVersion55MergeAutofillDatesTable() { 2290 sql::Transaction transaction(db_); 2291 if (!transaction.Begin()) 2292 return false; 2293 2294 if (db_->DoesTableExist("autofill_temp") || 2295 !db_->Execute("CREATE TABLE autofill_temp (" 2296 "name VARCHAR, " 2297 "value VARCHAR, " 2298 "value_lower VARCHAR, " 2299 "date_created INTEGER DEFAULT 0, " 2300 "date_last_used INTEGER DEFAULT 0, " 2301 "count INTEGER DEFAULT 1, " 2302 "PRIMARY KEY (name, value))")) { 2303 return false; 2304 } 2305 2306 // Slurp up the data from the existing table and write it to the new table. 2307 sql::Statement s(db_->GetUniqueStatement( 2308 "SELECT name, value, value_lower, count, MIN(date_created)," 2309 " MAX(date_created) " 2310 "FROM autofill a JOIN autofill_dates ad ON a.pair_id=ad.pair_id " 2311 "GROUP BY name, value, value_lower, count")); 2312 while (s.Step()) { 2313 sql::Statement s_insert(db_->GetUniqueStatement( 2314 "INSERT INTO autofill_temp " 2315 "(name, value, value_lower, count, date_created, date_last_used) " 2316 "VALUES (?, ?, ?, ?, ?, ?)")); 2317 s_insert.BindString16(0, s.ColumnString16(0)); 2318 s_insert.BindString16(1, s.ColumnString16(1)); 2319 s_insert.BindString16(2, s.ColumnString16(2)); 2320 s_insert.BindInt(3, s.ColumnInt(3)); 2321 s_insert.BindInt64(4, s.ColumnInt64(4)); 2322 s_insert.BindInt64(5, s.ColumnInt64(5)); 2323 if (!s_insert.Run()) 2324 return false; 2325 } 2326 2327 if (!s.Succeeded()) 2328 return false; 2329 2330 // Delete the existing (version 54) tables and replace them with the contents 2331 // of the temporary table. 2332 if (!db_->Execute("DROP TABLE autofill") || 2333 !db_->Execute("DROP TABLE autofill_dates") || 2334 !db_->Execute("ALTER TABLE autofill_temp " 2335 "RENAME TO autofill")) { 2336 return false; 2337 } 2338 2339 // Create indices on the new table, for fast lookups. 2340 if (!db_->Execute("CREATE INDEX autofill_name ON autofill (name)") || 2341 !db_->Execute("CREATE INDEX autofill_name_value_lower ON " 2342 "autofill (name, value_lower)")) { 2343 return false; 2344 } 2345 2346 2347 return transaction.Commit(); 2348 } 2349 2350 bool AutofillTable::MigrateToVersion56AddProfileLanguageCodeForFormatting() { 2351 return db_->Execute("ALTER TABLE autofill_profiles " 2352 "ADD COLUMN language_code VARCHAR"); 2353 } 2354 2355 bool AutofillTable::MigrateToVersion57AddFullNameField() { 2356 return db_->Execute("ALTER TABLE autofill_profile_names " 2357 "ADD COLUMN full_name VARCHAR"); 2358 } 2359 2360 } // namespace autofill 2361