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 38using base::Time; 39 40namespace autofill { 41namespace { 42 43// The period after which Autofill entries should expire in days. 44const int64 kExpirationPeriodInDays = 60; 45 46template<typename T> 47T* 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. 53struct 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. 62int 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. 69base::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 78void 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 97scoped_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 121void 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 141scoped_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(). 172bool 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 201bool 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 233bool 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 256bool 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(). 282bool 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 311bool 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 343bool 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 364bool 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 385bool 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 399bool 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 421WebDatabaseTable::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 428time_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. 438const size_t AutofillTable::kMaxDataLength = 1024; 439 440AutofillTable::AutofillTable(const std::string& app_locale) 441 : app_locale_(app_locale) { 442} 443 444AutofillTable::~AutofillTable() { 445} 446 447AutofillTable* AutofillTable::FromWebDatabase(WebDatabase* db) { 448 return static_cast<AutofillTable*>(db->GetTable(GetKey())); 449} 450 451WebDatabaseTable::TypeKey AutofillTable::GetTypeKey() const { 452 return GetKey(); 453} 454 455bool AutofillTable::CreateTablesIfNecessary() { 456 return (InitMainTable() && InitCreditCardsTable() && InitProfilesTable() && 457 InitProfileNamesTable() && InitProfileEmailsTable() && 458 InitProfilePhonesTable() && InitProfileTrashTable()); 459} 460 461bool AutofillTable::IsSyncable() { 462 return true; 463} 464 465bool 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 527bool AutofillTable::AddFormFieldValues( 528 const std::vector<FormFieldData>& elements, 529 std::vector<AutofillChange>* changes) { 530 return AddFormFieldValuesTime(elements, changes, Time::Now()); 531} 532 533bool AutofillTable::AddFormFieldValue(const FormFieldData& element, 534 std::vector<AutofillChange>* changes) { 535 return AddFormFieldValueTime(element, changes, Time::Now()); 536} 537 538bool 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 578bool 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 587bool 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 694bool 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 725bool 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 746bool 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 762bool 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 781bool AutofillTable::UpdateAutofillEntries( 782 const std::vector<AutofillEntry>& entries) { 783 if (entries.empty()) 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 805bool 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 824bool 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 868bool 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 877bool 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 895bool 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 925bool 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 "ORDER BY date_modified DESC, guid")); 934 935 while (s.Step()) { 936 std::string guid = s.ColumnString(0); 937 AutofillProfile* profile = NULL; 938 if (!GetAutofillProfile(guid, &profile)) 939 return false; 940 profiles->push_back(profile); 941 } 942 943 return s.Succeeded(); 944} 945 946bool AutofillTable::UpdateAutofillProfile(const AutofillProfile& profile) { 947 DCHECK(base::IsValidGUID(profile.guid())); 948 949 // Don't update anything until the trash has been emptied. There may be 950 // pending modifications to process. 951 if (!IsAutofillProfilesTrashEmpty()) 952 return true; 953 954 AutofillProfile* tmp_profile = NULL; 955 if (!GetAutofillProfile(profile.guid(), &tmp_profile)) 956 return false; 957 958 // Preserve appropriate modification dates by not updating unchanged profiles. 959 scoped_ptr<AutofillProfile> old_profile(tmp_profile); 960 if (*old_profile == profile) 961 return true; 962 963 sql::Statement s(db_->GetUniqueStatement( 964 "UPDATE autofill_profiles " 965 "SET guid=?, company_name=?, street_address=?, dependent_locality=?, " 966 " city=?, state=?, zipcode=?, sorting_code=?, country_code=?, " 967 " date_modified=?, origin=?, language_code=? " 968 "WHERE guid=?")); 969 BindAutofillProfileToStatement(profile, &s); 970 s.BindString(12, profile.guid()); 971 972 bool result = s.Run(); 973 DCHECK_GT(db_->GetLastChangeCount(), 0); 974 if (!result) 975 return result; 976 977 // Remove the old names, emails, and phone numbers. 978 if (!RemoveAutofillProfilePieces(profile.guid(), db_)) 979 return false; 980 981 return AddAutofillProfilePieces(profile, db_); 982} 983 984bool AutofillTable::RemoveAutofillProfile(const std::string& guid) { 985 DCHECK(base::IsValidGUID(guid)); 986 987 if (IsAutofillGUIDInTrash(guid)) { 988 sql::Statement s_trash(db_->GetUniqueStatement( 989 "DELETE FROM autofill_profiles_trash WHERE guid = ?")); 990 s_trash.BindString(0, guid); 991 992 bool success = s_trash.Run(); 993 DCHECK_GT(db_->GetLastChangeCount(), 0) << "Expected item in trash"; 994 return success; 995 } 996 997 sql::Statement s(db_->GetUniqueStatement( 998 "DELETE FROM autofill_profiles WHERE guid = ?")); 999 s.BindString(0, guid); 1000 1001 if (!s.Run()) 1002 return false; 1003 1004 return RemoveAutofillProfilePieces(guid, db_); 1005} 1006 1007bool AutofillTable::ClearAutofillProfiles() { 1008 sql::Statement s1(db_->GetUniqueStatement( 1009 "DELETE FROM autofill_profiles")); 1010 1011 if (!s1.Run()) 1012 return false; 1013 1014 sql::Statement s2(db_->GetUniqueStatement( 1015 "DELETE FROM autofill_profile_names")); 1016 1017 if (!s2.Run()) 1018 return false; 1019 1020 sql::Statement s3(db_->GetUniqueStatement( 1021 "DELETE FROM autofill_profile_emails")); 1022 1023 if (!s3.Run()) 1024 return false; 1025 1026 sql::Statement s4(db_->GetUniqueStatement( 1027 "DELETE FROM autofill_profile_phones")); 1028 1029 return s4.Run(); 1030} 1031 1032bool AutofillTable::AddCreditCard(const CreditCard& credit_card) { 1033 sql::Statement s(db_->GetUniqueStatement( 1034 "INSERT INTO credit_cards" 1035 "(guid, name_on_card, expiration_month, expiration_year, " 1036 " card_number_encrypted, date_modified, origin)" 1037 "VALUES (?,?,?,?,?,?,?)")); 1038 BindCreditCardToStatement(credit_card, &s); 1039 1040 if (!s.Run()) 1041 return false; 1042 1043 DCHECK_GT(db_->GetLastChangeCount(), 0); 1044 return true; 1045} 1046 1047bool AutofillTable::GetCreditCard(const std::string& guid, 1048 CreditCard** credit_card) { 1049 DCHECK(base::IsValidGUID(guid)); 1050 sql::Statement s(db_->GetUniqueStatement( 1051 "SELECT guid, name_on_card, expiration_month, expiration_year, " 1052 " card_number_encrypted, date_modified, origin " 1053 "FROM credit_cards " 1054 "WHERE guid = ?")); 1055 s.BindString(0, guid); 1056 1057 if (!s.Step()) 1058 return false; 1059 1060 *credit_card = CreditCardFromStatement(s).release(); 1061 return true; 1062} 1063 1064bool AutofillTable::GetCreditCards( 1065 std::vector<CreditCard*>* credit_cards) { 1066 DCHECK(credit_cards); 1067 credit_cards->clear(); 1068 1069 sql::Statement s(db_->GetUniqueStatement( 1070 "SELECT guid " 1071 "FROM credit_cards " 1072 "ORDER BY date_modified DESC, guid")); 1073 1074 while (s.Step()) { 1075 std::string guid = s.ColumnString(0); 1076 CreditCard* credit_card = NULL; 1077 if (!GetCreditCard(guid, &credit_card)) 1078 return false; 1079 credit_cards->push_back(credit_card); 1080 } 1081 1082 return s.Succeeded(); 1083} 1084 1085bool AutofillTable::UpdateCreditCard(const CreditCard& credit_card) { 1086 DCHECK(base::IsValidGUID(credit_card.guid())); 1087 1088 CreditCard* tmp_credit_card = NULL; 1089 if (!GetCreditCard(credit_card.guid(), &tmp_credit_card)) 1090 return false; 1091 1092 // Preserve appropriate modification dates by not updating unchanged cards. 1093 scoped_ptr<CreditCard> old_credit_card(tmp_credit_card); 1094 if (*old_credit_card == credit_card) 1095 return true; 1096 1097 sql::Statement s(db_->GetUniqueStatement( 1098 "UPDATE credit_cards " 1099 "SET guid=?, name_on_card=?, expiration_month=?, " 1100 " expiration_year=?, card_number_encrypted=?, date_modified=?, " 1101 " origin=? " 1102 "WHERE guid=?")); 1103 BindCreditCardToStatement(credit_card, &s); 1104 s.BindString(7, credit_card.guid()); 1105 1106 bool result = s.Run(); 1107 DCHECK_GT(db_->GetLastChangeCount(), 0); 1108 return result; 1109} 1110 1111bool AutofillTable::RemoveCreditCard(const std::string& guid) { 1112 DCHECK(base::IsValidGUID(guid)); 1113 sql::Statement s(db_->GetUniqueStatement( 1114 "DELETE FROM credit_cards WHERE guid = ?")); 1115 s.BindString(0, guid); 1116 1117 return s.Run(); 1118} 1119 1120bool AutofillTable::RemoveAutofillDataModifiedBetween( 1121 const Time& delete_begin, 1122 const Time& delete_end, 1123 std::vector<std::string>* profile_guids, 1124 std::vector<std::string>* credit_card_guids) { 1125 DCHECK(delete_end.is_null() || delete_begin < delete_end); 1126 1127 time_t delete_begin_t = delete_begin.ToTimeT(); 1128 time_t delete_end_t = GetEndTime(delete_end); 1129 1130 // Remember Autofill profiles in the time range. 1131 sql::Statement s_profiles_get(db_->GetUniqueStatement( 1132 "SELECT guid FROM autofill_profiles " 1133 "WHERE date_modified >= ? AND date_modified < ?")); 1134 s_profiles_get.BindInt64(0, delete_begin_t); 1135 s_profiles_get.BindInt64(1, delete_end_t); 1136 1137 profile_guids->clear(); 1138 while (s_profiles_get.Step()) { 1139 std::string guid = s_profiles_get.ColumnString(0); 1140 profile_guids->push_back(guid); 1141 } 1142 if (!s_profiles_get.Succeeded()) 1143 return false; 1144 1145 // Remove Autofill profiles in the time range. 1146 sql::Statement s_profiles(db_->GetUniqueStatement( 1147 "DELETE FROM autofill_profiles " 1148 "WHERE date_modified >= ? AND date_modified < ?")); 1149 s_profiles.BindInt64(0, delete_begin_t); 1150 s_profiles.BindInt64(1, delete_end_t); 1151 1152 if (!s_profiles.Run()) 1153 return false; 1154 1155 // Remember Autofill credit cards in the time range. 1156 sql::Statement s_credit_cards_get(db_->GetUniqueStatement( 1157 "SELECT guid FROM credit_cards " 1158 "WHERE date_modified >= ? AND date_modified < ?")); 1159 s_credit_cards_get.BindInt64(0, delete_begin_t); 1160 s_credit_cards_get.BindInt64(1, delete_end_t); 1161 1162 credit_card_guids->clear(); 1163 while (s_credit_cards_get.Step()) { 1164 std::string guid = s_credit_cards_get.ColumnString(0); 1165 credit_card_guids->push_back(guid); 1166 } 1167 if (!s_credit_cards_get.Succeeded()) 1168 return false; 1169 1170 // Remove Autofill credit cards in the time range. 1171 sql::Statement s_credit_cards(db_->GetUniqueStatement( 1172 "DELETE FROM credit_cards " 1173 "WHERE date_modified >= ? AND date_modified < ?")); 1174 s_credit_cards.BindInt64(0, delete_begin_t); 1175 s_credit_cards.BindInt64(1, delete_end_t); 1176 1177 return s_credit_cards.Run(); 1178} 1179 1180bool AutofillTable::RemoveOriginURLsModifiedBetween( 1181 const Time& delete_begin, 1182 const Time& delete_end, 1183 ScopedVector<AutofillProfile>* profiles) { 1184 DCHECK(delete_end.is_null() || delete_begin < delete_end); 1185 1186 time_t delete_begin_t = delete_begin.ToTimeT(); 1187 time_t delete_end_t = GetEndTime(delete_end); 1188 1189 // Remember Autofill profiles with URL origins in the time range. 1190 sql::Statement s_profiles_get(db_->GetUniqueStatement( 1191 "SELECT guid, origin FROM autofill_profiles " 1192 "WHERE date_modified >= ? AND date_modified < ?")); 1193 s_profiles_get.BindInt64(0, delete_begin_t); 1194 s_profiles_get.BindInt64(1, delete_end_t); 1195 1196 std::vector<std::string> profile_guids; 1197 while (s_profiles_get.Step()) { 1198 std::string guid = s_profiles_get.ColumnString(0); 1199 std::string origin = s_profiles_get.ColumnString(1); 1200 if (GURL(origin).is_valid()) 1201 profile_guids.push_back(guid); 1202 } 1203 if (!s_profiles_get.Succeeded()) 1204 return false; 1205 1206 // Clear out the origins for the found Autofill profiles. 1207 for (std::vector<std::string>::const_iterator it = profile_guids.begin(); 1208 it != profile_guids.end(); ++it) { 1209 sql::Statement s_profile(db_->GetUniqueStatement( 1210 "UPDATE autofill_profiles SET origin='' WHERE guid=?")); 1211 s_profile.BindString(0, *it); 1212 if (!s_profile.Run()) 1213 return false; 1214 1215 AutofillProfile* profile; 1216 if (!GetAutofillProfile(*it, &profile)) 1217 return false; 1218 1219 profiles->push_back(profile); 1220 } 1221 1222 // Remember Autofill credit cards with URL origins in the time range. 1223 sql::Statement s_credit_cards_get(db_->GetUniqueStatement( 1224 "SELECT guid, origin FROM credit_cards " 1225 "WHERE date_modified >= ? AND date_modified < ?")); 1226 s_credit_cards_get.BindInt64(0, delete_begin_t); 1227 s_credit_cards_get.BindInt64(1, delete_end_t); 1228 1229 std::vector<std::string> credit_card_guids; 1230 while (s_credit_cards_get.Step()) { 1231 std::string guid = s_credit_cards_get.ColumnString(0); 1232 std::string origin = s_credit_cards_get.ColumnString(1); 1233 if (GURL(origin).is_valid()) 1234 credit_card_guids.push_back(guid); 1235 } 1236 if (!s_credit_cards_get.Succeeded()) 1237 return false; 1238 1239 // Clear out the origins for the found credit cards. 1240 for (std::vector<std::string>::const_iterator it = credit_card_guids.begin(); 1241 it != credit_card_guids.end(); ++it) { 1242 sql::Statement s_credit_card(db_->GetUniqueStatement( 1243 "UPDATE credit_cards SET origin='' WHERE guid=?")); 1244 s_credit_card.BindString(0, *it); 1245 if (!s_credit_card.Run()) 1246 return false; 1247 } 1248 1249 return true; 1250} 1251 1252bool AutofillTable::GetAutofillProfilesInTrash( 1253 std::vector<std::string>* guids) { 1254 guids->clear(); 1255 1256 sql::Statement s(db_->GetUniqueStatement( 1257 "SELECT guid " 1258 "FROM autofill_profiles_trash")); 1259 1260 while (s.Step()) { 1261 std::string guid = s.ColumnString(0); 1262 guids->push_back(guid); 1263 } 1264 1265 return s.Succeeded(); 1266} 1267 1268bool AutofillTable::EmptyAutofillProfilesTrash() { 1269 sql::Statement s(db_->GetUniqueStatement( 1270 "DELETE FROM autofill_profiles_trash")); 1271 1272 return s.Run(); 1273} 1274 1275 1276bool AutofillTable::AddAutofillGUIDToTrash(const std::string& guid) { 1277 sql::Statement s(db_->GetUniqueStatement( 1278 "INSERT INTO autofill_profiles_trash" 1279 " (guid) " 1280 "VALUES (?)")); 1281 s.BindString(0, guid); 1282 1283 return s.Run(); 1284} 1285 1286bool AutofillTable::IsAutofillProfilesTrashEmpty() { 1287 sql::Statement s(db_->GetUniqueStatement( 1288 "SELECT guid " 1289 "FROM autofill_profiles_trash")); 1290 1291 return !s.Step(); 1292} 1293 1294bool AutofillTable::IsAutofillGUIDInTrash(const std::string& guid) { 1295 sql::Statement s(db_->GetUniqueStatement( 1296 "SELECT guid " 1297 "FROM autofill_profiles_trash " 1298 "WHERE guid = ?")); 1299 s.BindString(0, guid); 1300 1301 return s.Step(); 1302} 1303 1304bool AutofillTable::InitMainTable() { 1305 if (!db_->DoesTableExist("autofill")) { 1306 if (!db_->Execute("CREATE TABLE autofill (" 1307 "name VARCHAR, " 1308 "value VARCHAR, " 1309 "value_lower VARCHAR, " 1310 "date_created INTEGER DEFAULT 0, " 1311 "date_last_used INTEGER DEFAULT 0, " 1312 "count INTEGER DEFAULT 1, " 1313 "PRIMARY KEY (name, value))") || 1314 !db_->Execute("CREATE INDEX autofill_name ON autofill (name)") || 1315 !db_->Execute("CREATE INDEX autofill_name_value_lower ON " 1316 "autofill (name, value_lower)")) { 1317 NOTREACHED(); 1318 return false; 1319 } 1320 } 1321 return true; 1322} 1323 1324bool AutofillTable::InitCreditCardsTable() { 1325 if (!db_->DoesTableExist("credit_cards")) { 1326 if (!db_->Execute("CREATE TABLE credit_cards ( " 1327 "guid VARCHAR PRIMARY KEY, " 1328 "name_on_card VARCHAR, " 1329 "expiration_month INTEGER, " 1330 "expiration_year INTEGER, " 1331 "card_number_encrypted BLOB, " 1332 "date_modified INTEGER NOT NULL DEFAULT 0, " 1333 "origin VARCHAR DEFAULT '')")) { 1334 NOTREACHED(); 1335 return false; 1336 } 1337 } 1338 1339 return true; 1340} 1341 1342bool AutofillTable::InitProfilesTable() { 1343 if (!db_->DoesTableExist("autofill_profiles")) { 1344 if (!db_->Execute("CREATE TABLE autofill_profiles ( " 1345 "guid VARCHAR PRIMARY KEY, " 1346 "company_name VARCHAR, " 1347 "street_address VARCHAR, " 1348 "dependent_locality VARCHAR, " 1349 "city VARCHAR, " 1350 "state VARCHAR, " 1351 "zipcode VARCHAR, " 1352 "sorting_code VARCHAR, " 1353 "country_code VARCHAR, " 1354 "date_modified INTEGER NOT NULL DEFAULT 0, " 1355 "origin VARCHAR DEFAULT '', " 1356 "language_code VARCHAR)")) { 1357 NOTREACHED(); 1358 return false; 1359 } 1360 } 1361 return true; 1362} 1363 1364bool AutofillTable::InitProfileNamesTable() { 1365 if (!db_->DoesTableExist("autofill_profile_names")) { 1366 if (!db_->Execute("CREATE TABLE autofill_profile_names ( " 1367 "guid VARCHAR, " 1368 "first_name VARCHAR, " 1369 "middle_name VARCHAR, " 1370 "last_name VARCHAR, " 1371 "full_name VARCHAR)")) { 1372 NOTREACHED(); 1373 return false; 1374 } 1375 } 1376 return true; 1377} 1378 1379bool AutofillTable::InitProfileEmailsTable() { 1380 if (!db_->DoesTableExist("autofill_profile_emails")) { 1381 if (!db_->Execute("CREATE TABLE autofill_profile_emails ( " 1382 "guid VARCHAR, " 1383 "email VARCHAR)")) { 1384 NOTREACHED(); 1385 return false; 1386 } 1387 } 1388 return true; 1389} 1390 1391bool AutofillTable::InitProfilePhonesTable() { 1392 if (!db_->DoesTableExist("autofill_profile_phones")) { 1393 if (!db_->Execute("CREATE TABLE autofill_profile_phones ( " 1394 "guid VARCHAR, " 1395 "number VARCHAR)")) { 1396 NOTREACHED(); 1397 return false; 1398 } 1399 } 1400 return true; 1401} 1402 1403bool AutofillTable::InitProfileTrashTable() { 1404 if (!db_->DoesTableExist("autofill_profiles_trash")) { 1405 if (!db_->Execute("CREATE TABLE autofill_profiles_trash ( " 1406 "guid VARCHAR)")) { 1407 NOTREACHED(); 1408 return false; 1409 } 1410 } 1411 return true; 1412} 1413 1414bool AutofillTable::MigrateToVersion22ClearAutofillEmptyValueElements() { 1415 if (!db_->DoesTableExist("autofill") && 1416 (!db_->Execute("CREATE TABLE autofill (" 1417 " name VARCHAR," 1418 " value VARCHAR," 1419 " value_lower VARCHAR," 1420 " pair_id INTEGER PRIMARY KEY," 1421 " count INTEGER DEFAULT 1)") || 1422 !db_->Execute("CREATE INDEX autofill_name ON autofill (name)") || 1423 !db_->Execute("CREATE INDEX autofill_name_value_lower ON" 1424 " autofill (name, value_lower)") || 1425 !db_->Execute("CREATE TABLE autofill_dates (" 1426 " pair_id INTEGER DEFAULT 0," 1427 " date_created INTEGER DEFAULT 0)") || 1428 !db_->Execute("CREATE INDEX autofill_dates_pair_id ON" 1429 " autofill (pair_id)"))) 1430 return false; 1431 1432 1433 sql::Statement s(db_->GetUniqueStatement( 1434 "SELECT pair_id FROM autofill WHERE TRIM(value) = \"\"")); 1435 if (!s.is_valid()) 1436 return false; 1437 1438 std::set<int64> ids; 1439 while (s.Step()) 1440 ids.insert(s.ColumnInt64(0)); 1441 if (!s.Succeeded()) 1442 return false; 1443 1444 if (!db_->Execute("DELETE FROM autofill WHERE TRIM(value) = \"\"")) 1445 return false; 1446 1447 for (std::set<int64>::const_iterator it = ids.begin(); it != ids.end(); 1448 ++it) { 1449 sql::Statement s(db_->GetUniqueStatement( 1450 "DELETE FROM autofill_dates WHERE pair_id = ?")); 1451 s.BindInt64(0, *it); 1452 if (!s.Run()) 1453 return false; 1454 } 1455 1456 return true; 1457} 1458 1459// Add the card_number_encrypted column if credit card table was not 1460// created in this build (otherwise the column already exists). 1461// WARNING: Do not change the order of the execution of the SQL 1462// statements in this case! Profile corruption and data migration 1463// issues WILL OCCUR. See http://crbug.com/10913 1464// 1465// The problem is that if a user has a profile which was created before 1466// r37036, when the credit_cards table was added, and then failed to 1467// update this profile between the credit card addition and the addition 1468// of the "encrypted" columns (44963), the next data migration will put 1469// the user's profile in an incoherent state: The user will update from 1470// a data profile set to be earlier than 22, and therefore pass through 1471// this update case. But because the user did not have a credit_cards 1472// table before starting Chrome, it will have just been initialized 1473// above, and so already have these columns -- and thus this data 1474// update step will have failed. 1475// 1476// The false assumption in this case is that at this step in the 1477// migration, the user has a credit card table, and that this 1478// table does not include encrypted columns! 1479// Because this case does not roll back the complete set of SQL 1480// transactions properly in case of failure (that is, it does not 1481// roll back the table initialization done above), the incoherent 1482// profile will now see itself as being at version 22 -- but include a 1483// fully initialized credit_cards table. Every time Chrome runs, it 1484// will try to update the web database and fail at this step, unless 1485// we allow for the faulty assumption described above by checking for 1486// the existence of the columns only AFTER we've executed the commands 1487// to add them. 1488bool AutofillTable::MigrateToVersion23AddCardNumberEncryptedColumn() { 1489 if (!db_->DoesTableExist("autofill_profiles") && 1490 (!db_->Execute("CREATE TABLE autofill_profiles ( " 1491 "label VARCHAR, " 1492 "unique_id INTEGER PRIMARY KEY, " 1493 "first_name VARCHAR, " 1494 "middle_name VARCHAR, " 1495 "last_name VARCHAR, " 1496 "email VARCHAR, " 1497 "company_name VARCHAR, " 1498 "address_line_1 VARCHAR, " 1499 "address_line_2 VARCHAR, " 1500 "city VARCHAR, " 1501 "state VARCHAR, " 1502 "zipcode VARCHAR, " 1503 "country VARCHAR, " 1504 "phone VARCHAR, " 1505 "fax VARCHAR)") || 1506 !db_->Execute("CREATE INDEX autofill_profiles_label_index" 1507 " ON autofill_profiles (label)"))) 1508 return false; 1509 1510 if (!db_->DoesTableExist("credit_cards") && 1511 (!db_->Execute("CREATE TABLE credit_cards ( " 1512 "label VARCHAR, " 1513 "unique_id INTEGER PRIMARY KEY, " 1514 "name_on_card VARCHAR, " 1515 "type VARCHAR, " 1516 "card_number VARCHAR, " 1517 "expiration_month INTEGER, " 1518 "expiration_year INTEGER, " 1519 "verification_code VARCHAR, " 1520 "billing_address VARCHAR, " 1521 "shipping_address VARCHAR)") || 1522 !db_->Execute("CREATE INDEX credit_cards_label_index" 1523 " ON credit_cards (label)"))) 1524 return false; 1525 1526 if (!db_->DoesColumnExist("credit_cards", "card_number_encrypted")) { 1527 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN " 1528 "card_number_encrypted BLOB DEFAULT NULL")) { 1529 return false; 1530 } 1531 } 1532 1533 if (!db_->DoesColumnExist("credit_cards", "verification_code_encrypted")) { 1534 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN " 1535 "verification_code_encrypted BLOB DEFAULT NULL")) { 1536 return false; 1537 } 1538 } 1539 1540 return true; 1541} 1542 1543// One-time cleanup for http://crbug.com/38364 - In the presence of 1544// multi-byte UTF-8 characters, that bug could cause Autofill strings 1545// to grow larger and more corrupt with each save. The cleanup removes 1546// any row with a string field larger than a reasonable size. The string 1547// fields examined here are precisely the ones that were subject to 1548// corruption by the original bug. 1549bool AutofillTable::MigrateToVersion24CleanupOversizedStringFields() { 1550 const std::string autofill_is_too_big = 1551 "max(length(name), length(value)) > 500"; 1552 1553 const std::string credit_cards_is_too_big = 1554 "max(length(label), length(name_on_card), length(type), " 1555 " length(expiration_month), length(expiration_year), " 1556 " length(billing_address), length(shipping_address) " 1557 ") > 500"; 1558 1559 const std::string autofill_profiles_is_too_big = 1560 "max(length(label), length(first_name), " 1561 " length(middle_name), length(last_name), length(email), " 1562 " length(company_name), length(address_line_1), " 1563 " length(address_line_2), length(city), length(state), " 1564 " length(zipcode), length(country), length(phone)) > 500"; 1565 1566 std::string query = "DELETE FROM autofill_dates WHERE pair_id IN (" 1567 "SELECT pair_id FROM autofill WHERE " + autofill_is_too_big + ")"; 1568 1569 if (!db_->Execute(query.c_str())) 1570 return false; 1571 1572 query = "DELETE FROM autofill WHERE " + autofill_is_too_big; 1573 1574 if (!db_->Execute(query.c_str())) 1575 return false; 1576 1577 // Only delete from legacy credit card tables where specific columns exist. 1578 if (db_->DoesColumnExist("credit_cards", "label") && 1579 db_->DoesColumnExist("credit_cards", "name_on_card") && 1580 db_->DoesColumnExist("credit_cards", "type") && 1581 db_->DoesColumnExist("credit_cards", "expiration_month") && 1582 db_->DoesColumnExist("credit_cards", "expiration_year") && 1583 db_->DoesColumnExist("credit_cards", "billing_address") && 1584 db_->DoesColumnExist("credit_cards", "shipping_address") && 1585 db_->DoesColumnExist("autofill_profiles", "label")) { 1586 query = "DELETE FROM credit_cards WHERE (" + credit_cards_is_too_big + 1587 ") OR label IN (SELECT label FROM autofill_profiles WHERE " + 1588 autofill_profiles_is_too_big + ")"; 1589 1590 if (!db_->Execute(query.c_str())) 1591 return false; 1592 } 1593 1594 if (db_->DoesColumnExist("autofill_profiles", "label")) { 1595 query = "DELETE FROM autofill_profiles WHERE " + 1596 autofill_profiles_is_too_big; 1597 1598 if (!db_->Execute(query.c_str())) 1599 return false; 1600 } 1601 1602 return true; 1603} 1604 1605// Change the credit_cards.billing_address column from a string to an 1606// int. The stored string is the label of an address, so we have to 1607// select the unique ID of this address using the label as a foreign 1608// key into the |autofill_profiles| table. 1609bool AutofillTable::MigrateToVersion27UpdateLegacyCreditCards() { 1610 // Only migrate from legacy credit card tables where specific columns 1611 // exist. 1612 if (!(db_->DoesColumnExist("credit_cards", "unique_id") && 1613 db_->DoesColumnExist("credit_cards", "billing_address") && 1614 db_->DoesColumnExist("autofill_profiles", "unique_id"))) { 1615 return true; 1616 } 1617 1618 std::string stmt = 1619 "SELECT credit_cards.unique_id, autofill_profiles.unique_id " 1620 "FROM autofill_profiles, credit_cards " 1621 "WHERE credit_cards.billing_address = autofill_profiles.label"; 1622 sql::Statement s(db_->GetUniqueStatement(stmt.c_str())); 1623 1624 std::map<int, int> cc_billing_map; 1625 while (s.Step()) 1626 cc_billing_map[s.ColumnInt(0)] = s.ColumnInt(1); 1627 if (!s.Succeeded()) 1628 return false; 1629 1630 // Windows already stores the IDs as strings in |billing_address|. Try 1631 // to convert those. 1632 if (cc_billing_map.empty()) { 1633 std::string stmt = "SELECT unique_id,billing_address FROM credit_cards"; 1634 sql::Statement s(db_->GetUniqueStatement(stmt.c_str())); 1635 1636 while (s.Step()) { 1637 int id = 0; 1638 if (base::StringToInt(s.ColumnString(1), &id)) 1639 cc_billing_map[s.ColumnInt(0)] = id; 1640 } 1641 if (!s.Succeeded()) 1642 return false; 1643 } 1644 1645 if (!db_->Execute("CREATE TABLE credit_cards_temp ( " 1646 "label VARCHAR, " 1647 "unique_id INTEGER PRIMARY KEY, " 1648 "name_on_card VARCHAR, " 1649 "type VARCHAR, " 1650 "card_number VARCHAR, " 1651 "expiration_month INTEGER, " 1652 "expiration_year INTEGER, " 1653 "verification_code VARCHAR, " 1654 "billing_address INTEGER, " 1655 "shipping_address VARCHAR, " 1656 "card_number_encrypted BLOB, " 1657 "verification_code_encrypted BLOB)")) { 1658 return false; 1659 } 1660 1661 if (!db_->Execute( 1662 "INSERT INTO credit_cards_temp " 1663 "SELECT label,unique_id,name_on_card,type,card_number," 1664 "expiration_month,expiration_year,verification_code,0," 1665 "shipping_address,card_number_encrypted," 1666 "verification_code_encrypted FROM credit_cards")) { 1667 return false; 1668 } 1669 1670 if (!db_->Execute("DROP TABLE credit_cards")) 1671 return false; 1672 1673 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards")) 1674 return false; 1675 1676 for (std::map<int, int>::const_iterator iter = cc_billing_map.begin(); 1677 iter != cc_billing_map.end(); ++iter) { 1678 sql::Statement s(db_->GetCachedStatement( 1679 SQL_FROM_HERE, 1680 "UPDATE credit_cards SET billing_address=? WHERE unique_id=?")); 1681 s.BindInt(0, (*iter).second); 1682 s.BindInt(1, (*iter).first); 1683 1684 if (!s.Run()) 1685 return false; 1686 } 1687 1688 return true; 1689} 1690 1691bool AutofillTable::MigrateToVersion30AddDateModifed() { 1692 // Add date_modified to autofill_profiles. 1693 if (!db_->DoesColumnExist("autofill_profiles", "date_modified")) { 1694 if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN " 1695 "date_modified INTEGER NON NULL DEFAULT 0")) { 1696 return false; 1697 } 1698 1699 sql::Statement s(db_->GetUniqueStatement( 1700 "UPDATE autofill_profiles SET date_modified=?")); 1701 s.BindInt64(0, Time::Now().ToTimeT()); 1702 1703 if (!s.Run()) 1704 return false; 1705 } 1706 1707 // Add date_modified to credit_cards. 1708 if (!db_->DoesColumnExist("credit_cards", "date_modified")) { 1709 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN " 1710 "date_modified INTEGER NON NULL DEFAULT 0")) { 1711 return false; 1712 } 1713 1714 sql::Statement s(db_->GetUniqueStatement( 1715 "UPDATE credit_cards SET date_modified=?")); 1716 s.BindInt64(0, Time::Now().ToTimeT()); 1717 1718 if (!s.Run()) 1719 return false; 1720 } 1721 1722 return true; 1723} 1724 1725bool AutofillTable::MigrateToVersion31AddGUIDToCreditCardsAndProfiles() { 1726 // Note that we need to check for the guid column's existence due to the 1727 // fact that for a version 22 database the |autofill_profiles| table 1728 // gets created fresh with |InitAutofillProfilesTable|. 1729 if (!db_->DoesColumnExist("autofill_profiles", "guid")) { 1730 if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN " 1731 "guid VARCHAR NOT NULL DEFAULT \"\"")) { 1732 return false; 1733 } 1734 1735 // Set all the |guid| fields to valid values. 1736 1737 sql::Statement s(db_->GetUniqueStatement("SELECT unique_id " 1738 "FROM autofill_profiles")); 1739 1740 while (s.Step()) { 1741 sql::Statement update_s( 1742 db_->GetUniqueStatement("UPDATE autofill_profiles " 1743 "SET guid=? WHERE unique_id=?")); 1744 update_s.BindString(0, base::GenerateGUID()); 1745 update_s.BindInt(1, s.ColumnInt(0)); 1746 1747 if (!update_s.Run()) 1748 return false; 1749 } 1750 if (!s.Succeeded()) 1751 return false; 1752 } 1753 1754 // Note that we need to check for the guid column's existence due to the 1755 // fact that for a version 22 database the |autofill_profiles| table 1756 // gets created fresh with |InitAutofillProfilesTable|. 1757 if (!db_->DoesColumnExist("credit_cards", "guid")) { 1758 if (!db_->Execute("ALTER TABLE credit_cards ADD COLUMN " 1759 "guid VARCHAR NOT NULL DEFAULT \"\"")) { 1760 return false; 1761 } 1762 1763 // Set all the |guid| fields to valid values. 1764 1765 sql::Statement s(db_->GetUniqueStatement("SELECT unique_id " 1766 "FROM credit_cards")); 1767 1768 while (s.Step()) { 1769 sql::Statement update_s( 1770 db_->GetUniqueStatement("UPDATE credit_cards " 1771 "set guid=? WHERE unique_id=?")); 1772 update_s.BindString(0, base::GenerateGUID()); 1773 update_s.BindInt(1, s.ColumnInt(0)); 1774 1775 if (!update_s.Run()) 1776 return false; 1777 } 1778 if (!s.Succeeded()) 1779 return false; 1780 } 1781 1782 return true; 1783} 1784 1785bool AutofillTable::MigrateToVersion32UpdateProfilesAndCreditCards() { 1786 if (db_->DoesColumnExist("autofill_profiles", "unique_id")) { 1787 if (!db_->Execute("CREATE TABLE autofill_profiles_temp ( " 1788 "guid VARCHAR PRIMARY KEY, " 1789 "label VARCHAR, " 1790 "first_name VARCHAR, " 1791 "middle_name VARCHAR, " 1792 "last_name VARCHAR, " 1793 "email VARCHAR, " 1794 "company_name VARCHAR, " 1795 "address_line_1 VARCHAR, " 1796 "address_line_2 VARCHAR, " 1797 "city VARCHAR, " 1798 "state VARCHAR, " 1799 "zipcode VARCHAR, " 1800 "country VARCHAR, " 1801 "phone VARCHAR, " 1802 "date_modified INTEGER NOT NULL DEFAULT 0)")) { 1803 return false; 1804 } 1805 1806 if (!db_->Execute( 1807 "INSERT INTO autofill_profiles_temp " 1808 "SELECT guid, label, first_name, middle_name, last_name, email, " 1809 "company_name, address_line_1, address_line_2, city, state, " 1810 "zipcode, country, phone, date_modified " 1811 "FROM autofill_profiles")) { 1812 return false; 1813 } 1814 1815 if (!db_->Execute("DROP TABLE autofill_profiles")) 1816 return false; 1817 1818 if (!db_->Execute( 1819 "ALTER TABLE autofill_profiles_temp RENAME TO autofill_profiles")) { 1820 return false; 1821 } 1822 } 1823 1824 if (db_->DoesColumnExist("credit_cards", "unique_id")) { 1825 if (!db_->Execute("CREATE TABLE credit_cards_temp ( " 1826 "guid VARCHAR PRIMARY KEY, " 1827 "label VARCHAR, " 1828 "name_on_card VARCHAR, " 1829 "expiration_month INTEGER, " 1830 "expiration_year INTEGER, " 1831 "card_number_encrypted BLOB, " 1832 "date_modified INTEGER NOT NULL DEFAULT 0)")) { 1833 return false; 1834 } 1835 1836 if (!db_->Execute( 1837 "INSERT INTO credit_cards_temp " 1838 "SELECT guid, label, name_on_card, expiration_month, " 1839 "expiration_year, card_number_encrypted, date_modified " 1840 "FROM credit_cards")) { 1841 return false; 1842 } 1843 1844 if (!db_->Execute("DROP TABLE credit_cards")) 1845 return false; 1846 1847 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards")) 1848 return false; 1849 } 1850 1851 return true; 1852} 1853 1854// Test the existence of the |first_name| column as an indication that 1855// we need a migration. It is possible that the new |autofill_profiles| 1856// schema is in place because the table was newly created when migrating 1857// from a pre-version-22 database. 1858bool AutofillTable::MigrateToVersion33ProfilesBasedOnFirstName() { 1859 if (!db_->DoesTableExist("autofill_profile_names") && 1860 !db_->Execute("CREATE TABLE autofill_profile_names ( " 1861 "guid VARCHAR, " 1862 "first_name VARCHAR, " 1863 "middle_name VARCHAR, " 1864 "last_name VARCHAR)")) 1865 return false; 1866 1867 if (!db_->DoesTableExist("autofill_profile_emails") && 1868 !db_->Execute("CREATE TABLE autofill_profile_emails ( " 1869 "guid VARCHAR, " 1870 "email VARCHAR)")) 1871 return false; 1872 1873 if (!db_->DoesTableExist("autofill_profile_phones") && 1874 !db_->Execute("CREATE TABLE autofill_profile_phones ( " 1875 "guid VARCHAR, " 1876 "type INTEGER DEFAULT 0, " 1877 "number VARCHAR)")) 1878 return false; 1879 1880 if (db_->DoesColumnExist("autofill_profiles", "first_name")) { 1881 // Create autofill_profiles_temp table that will receive the data. 1882 if (!db_->DoesTableExist("autofill_profiles_temp")) { 1883 if (!db_->Execute("CREATE TABLE autofill_profiles_temp ( " 1884 "guid VARCHAR PRIMARY KEY, " 1885 "company_name VARCHAR, " 1886 "address_line_1 VARCHAR, " 1887 "address_line_2 VARCHAR, " 1888 "city VARCHAR, " 1889 "state VARCHAR, " 1890 "zipcode VARCHAR, " 1891 "country VARCHAR, " 1892 "date_modified INTEGER NOT NULL DEFAULT 0)")) { 1893 return false; 1894 } 1895 } 1896 1897 sql::Statement s(db_->GetUniqueStatement( 1898 "SELECT guid, first_name, middle_name, last_name, email, " 1899 "company_name, address_line_1, address_line_2, city, state, " 1900 "zipcode, country, phone, date_modified " 1901 "FROM autofill_profiles")); 1902 1903 while (s.Step()) { 1904 AutofillProfile profile; 1905 int index = 0; 1906 profile.set_guid(s.ColumnString(index++)); 1907 DCHECK(base::IsValidGUID(profile.guid())); 1908 1909 profile.SetRawInfo(NAME_FIRST, s.ColumnString16(index++)); 1910 profile.SetRawInfo(NAME_MIDDLE, s.ColumnString16(index++)); 1911 profile.SetRawInfo(NAME_LAST, s.ColumnString16(index++)); 1912 profile.SetRawInfo(EMAIL_ADDRESS, s.ColumnString16(index++)); 1913 profile.SetRawInfo(COMPANY_NAME, s.ColumnString16(index++)); 1914 profile.SetRawInfo(ADDRESS_HOME_LINE1, s.ColumnString16(index++)); 1915 profile.SetRawInfo(ADDRESS_HOME_LINE2, s.ColumnString16(index++)); 1916 profile.SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++)); 1917 profile.SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++)); 1918 profile.SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++)); 1919 profile.SetInfo(AutofillType(ADDRESS_HOME_COUNTRY), 1920 s.ColumnString16(index++), app_locale_); 1921 profile.SetRawInfo(PHONE_HOME_WHOLE_NUMBER, s.ColumnString16(index++)); 1922 int64 date_modified = s.ColumnInt64(index++); 1923 1924 sql::Statement s_insert(db_->GetUniqueStatement( 1925 "INSERT INTO autofill_profiles_temp" 1926 "(guid, company_name, address_line_1, address_line_2, city," 1927 " state, zipcode, country, date_modified)" 1928 "VALUES (?,?,?,?,?,?,?,?,?)")); 1929 index = 0; 1930 s_insert.BindString(index++, profile.guid()); 1931 s_insert.BindString16(index++, profile.GetRawInfo(COMPANY_NAME)); 1932 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_LINE1)); 1933 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_LINE2)); 1934 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_CITY)); 1935 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_STATE)); 1936 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_ZIP)); 1937 s_insert.BindString16(index++, profile.GetRawInfo(ADDRESS_HOME_COUNTRY)); 1938 s_insert.BindInt64(index++, date_modified); 1939 1940 if (!s_insert.Run()) 1941 return false; 1942 1943 // Add the other bits: names, emails, and phone numbers. 1944 if (!AddAutofillProfileNamesForVersion3x(profile, db_) || 1945 !AddAutofillProfileEmails(profile, db_) || 1946 !AddAutofillProfilePhones(profile, db_)) { 1947 return false; 1948 } 1949 } // endwhile 1950 if (!s.Succeeded()) 1951 return false; 1952 1953 if (!db_->Execute("DROP TABLE autofill_profiles")) 1954 return false; 1955 1956 if (!db_->Execute( 1957 "ALTER TABLE autofill_profiles_temp RENAME TO autofill_profiles")) { 1958 return false; 1959 } 1960 } 1961 1962 // Remove the labels column from the credit_cards table. 1963 if (db_->DoesColumnExist("credit_cards", "label")) { 1964 if (!db_->Execute("CREATE TABLE credit_cards_temp ( " 1965 "guid VARCHAR PRIMARY KEY, " 1966 "name_on_card VARCHAR, " 1967 "expiration_month INTEGER, " 1968 "expiration_year INTEGER, " 1969 "card_number_encrypted BLOB, " 1970 "date_modified INTEGER NOT NULL DEFAULT 0)")) { 1971 return false; 1972 } 1973 1974 if (!db_->Execute( 1975 "INSERT INTO credit_cards_temp " 1976 "SELECT guid, name_on_card, expiration_month, " 1977 "expiration_year, card_number_encrypted, date_modified " 1978 "FROM credit_cards")) { 1979 return false; 1980 } 1981 1982 if (!db_->Execute("DROP TABLE credit_cards")) 1983 return false; 1984 1985 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards")) 1986 return false; 1987 } 1988 1989 return true; 1990} 1991 1992// Test the existence of the |country_code| column as an indication that 1993// we need a migration. It is possible that the new |autofill_profiles| 1994// schema is in place because the table was newly created when migrating 1995// from a pre-version-22 database. 1996bool AutofillTable::MigrateToVersion34ProfilesBasedOnCountryCode() { 1997 if (!db_->DoesColumnExist("autofill_profiles", "country_code")) { 1998 if (!db_->Execute("ALTER TABLE autofill_profiles ADD COLUMN " 1999 "country_code VARCHAR")) { 2000 return false; 2001 } 2002 2003 // Set all the |country_code| fields to match existing |country| values. 2004 sql::Statement s(db_->GetUniqueStatement("SELECT guid, country " 2005 "FROM autofill_profiles")); 2006 2007 while (s.Step()) { 2008 sql::Statement update_s( 2009 db_->GetUniqueStatement("UPDATE autofill_profiles " 2010 "SET country_code=? WHERE guid=?")); 2011 2012 base::string16 country = s.ColumnString16(1); 2013 update_s.BindString(0, AutofillCountry::GetCountryCode(country, 2014 app_locale_)); 2015 update_s.BindString(1, s.ColumnString(0)); 2016 2017 if (!update_s.Run()) 2018 return false; 2019 } 2020 if (!s.Succeeded()) 2021 return false; 2022 } 2023 2024 return true; 2025} 2026 2027// Correct all country codes with value "UK" to be "GB". This data 2028// was mistakenly introduced in build 686.0. This migration is to clean 2029// it up. See http://crbug.com/74511 for details. 2030bool AutofillTable::MigrateToVersion35GreatBritainCountryCodes() { 2031 sql::Statement s(db_->GetUniqueStatement( 2032 "UPDATE autofill_profiles SET country_code=\"GB\" " 2033 "WHERE country_code=\"UK\"")); 2034 2035 return s.Run(); 2036} 2037 2038// Merge and cull older profiles where possible. 2039bool AutofillTable::MigrateToVersion37MergeAndCullOlderProfiles() { 2040 if (!db_->DoesTableExist("autofill_profiles_trash") && 2041 !db_->Execute("CREATE TABLE autofill_profiles_trash (guid VARCHAR)")) 2042 return false; 2043 2044 sql::Statement s(db_->GetUniqueStatement( 2045 "SELECT guid, date_modified FROM autofill_profiles")); 2046 2047 // Accumulate the good profiles. 2048 std::vector<AutofillProfile> accumulated_profiles; 2049 std::vector<AutofillProfile*> accumulated_profiles_p; 2050 std::map<std::string, int64> modification_map; 2051 while (s.Step()) { 2052 std::string guid = s.ColumnString(0); 2053 int64 date_modified = s.ColumnInt64(1); 2054 modification_map.insert( 2055 std::pair<std::string, int64>(guid, date_modified)); 2056 2057 sql::Statement s(db_->GetUniqueStatement( 2058 "SELECT guid, company_name, address_line_1, address_line_2, city, " 2059 " state, zipcode, country, country_code, date_modified " 2060 "FROM autofill_profiles " 2061 "WHERE guid=?")); 2062 s.BindString(0, guid); 2063 2064 if (!s.Step()) 2065 return false; 2066 2067 scoped_ptr<AutofillProfile> profile(new AutofillProfile); 2068 int index = 0; 2069 profile->set_guid(s.ColumnString(index++)); 2070 DCHECK(base::IsValidGUID(profile->guid())); 2071 2072 profile->SetRawInfo(COMPANY_NAME, s.ColumnString16(index++)); 2073 profile->SetRawInfo(ADDRESS_HOME_LINE1, s.ColumnString16(index++)); 2074 profile->SetRawInfo(ADDRESS_HOME_LINE2, s.ColumnString16(index++)); 2075 profile->SetRawInfo(ADDRESS_HOME_CITY, s.ColumnString16(index++)); 2076 profile->SetRawInfo(ADDRESS_HOME_STATE, s.ColumnString16(index++)); 2077 profile->SetRawInfo(ADDRESS_HOME_ZIP, s.ColumnString16(index++)); 2078 // Intentionally skip column 7, which stores the localized country name. 2079 index++; 2080 profile->SetRawInfo(ADDRESS_HOME_COUNTRY, s.ColumnString16(index++)); 2081 // Intentionally skip column 9, which stores the profile's modification 2082 // date. 2083 index++; 2084 profile->set_origin(s.ColumnString(index++)); 2085 2086 // Get associated name info. 2087 AddAutofillProfileNamesToProfileForVersion37(db_, profile.get()); 2088 2089 // Get associated email info. 2090 AddAutofillProfileEmailsToProfile(db_, profile.get()); 2091 2092 // Get associated phone info. 2093 AddAutofillProfilePhonesToProfile(db_, profile.get()); 2094 2095 if (PersonalDataManager::IsValidLearnableProfile(*profile, app_locale_)) { 2096 std::vector<AutofillProfile> merged_profiles; 2097 std::string merged_guid = PersonalDataManager::MergeProfile( 2098 *profile, accumulated_profiles_p, app_locale_, &merged_profiles); 2099 2100 std::swap(accumulated_profiles, merged_profiles); 2101 2102 accumulated_profiles_p.clear(); 2103 accumulated_profiles_p.resize(accumulated_profiles.size()); 2104 std::transform(accumulated_profiles.begin(), 2105 accumulated_profiles.end(), 2106 accumulated_profiles_p.begin(), 2107 address_of<AutofillProfile>); 2108 2109 // If the profile got merged trash the original. 2110 if (merged_guid != profile->guid()) 2111 AddAutofillGUIDToTrash(profile->guid()); 2112 } else { 2113 // An invalid profile, so trash it. 2114 AddAutofillGUIDToTrash(profile->guid()); 2115 } 2116 } // endwhile 2117 if (!s.Succeeded()) 2118 return false; 2119 2120 // Drop the current profiles. 2121 if (!ClearAutofillProfiles()) 2122 return false; 2123 2124 // Add the newly merged profiles back in. 2125 for (std::vector<AutofillProfile>::const_iterator 2126 iter = accumulated_profiles.begin(); 2127 iter != accumulated_profiles.end(); 2128 ++iter) { 2129 // Save the profile with its original modification date. 2130 std::map<std::string, int64>::const_iterator date_item = 2131 modification_map.find(iter->guid()); 2132 if (date_item == modification_map.end()) 2133 return false; 2134 2135 sql::Statement s(db_->GetUniqueStatement( 2136 "INSERT INTO autofill_profiles" 2137 "(guid, company_name, address_line_1, address_line_2, city, state," 2138 " zipcode, country, country_code, date_modified)" 2139 "VALUES (?,?,?,?,?,?,?,?,?,?)")); 2140 int index = 0; 2141 s.BindString(index++, iter->guid()); 2142 s.BindString16(index++, GetInfo(*iter, COMPANY_NAME)); 2143 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_LINE1)); 2144 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_LINE2)); 2145 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_CITY)); 2146 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_STATE)); 2147 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_ZIP)); 2148 s.BindString16(index++, base::string16()); // This column is deprecated. 2149 s.BindString16(index++, GetInfo(*iter, ADDRESS_HOME_COUNTRY)); 2150 s.BindInt64(index++, date_item->second); 2151 2152 if (!s.Run()) 2153 return false; 2154 2155 if (!AddAutofillProfileNamesForVersion3x(*iter, db_) || 2156 !AddAutofillProfileEmails(*iter, db_) || 2157 !AddAutofillProfilePhones(*iter, db_)) { 2158 return false; 2159 } 2160 } 2161 2162 return true; 2163} 2164 2165bool AutofillTable::MigrateToVersion51AddOriginColumn() { 2166 sql::Transaction transaction(db_); 2167 if (!transaction.Begin()) 2168 return false; 2169 2170 // Add origin to autofill_profiles. 2171 if (!db_->DoesColumnExist("autofill_profiles", "origin") && 2172 !db_->Execute("ALTER TABLE autofill_profiles " 2173 "ADD COLUMN origin VARCHAR DEFAULT ''")) { 2174 return false; 2175 } 2176 2177 // Add origin to credit_cards. 2178 if (!db_->DoesColumnExist("credit_cards", "origin") && 2179 !db_->Execute("ALTER TABLE credit_cards " 2180 "ADD COLUMN origin VARCHAR DEFAULT ''")) { 2181 return false; 2182 } 2183 2184 return transaction.Commit(); 2185} 2186 2187bool AutofillTable::MigrateToVersion54AddI18nFieldsAndRemoveDeprecatedFields() { 2188 sql::Transaction transaction(db_); 2189 if (!transaction.Begin()) 2190 return false; 2191 2192 // Test the existence of the |address_line_1| column as an indication that a 2193 // migration is needed. It is possible that the new |autofill_profile_phones| 2194 // schema is in place because the table was newly created when migrating from 2195 // a pre-version-23 database. 2196 if (db_->DoesColumnExist("autofill_profiles", "address_line_1")) { 2197 // Create a temporary copy of the autofill_profiles table in the (newer) 2198 // version 54 format. This table 2199 // (a) adds columns for street_address, dependent_locality, and 2200 // sorting_code, 2201 // (b) removes the address_line_1 and address_line_2 columns, which are 2202 // replaced by the street_address column, and 2203 // (c) removes the country column, which was long deprecated. 2204 if (db_->DoesTableExist("autofill_profiles_temp") || 2205 !db_->Execute("CREATE TABLE autofill_profiles_temp ( " 2206 "guid VARCHAR PRIMARY KEY, " 2207 "company_name VARCHAR, " 2208 "street_address VARCHAR, " 2209 "dependent_locality VARCHAR, " 2210 "city VARCHAR, " 2211 "state VARCHAR, " 2212 "zipcode VARCHAR, " 2213 "sorting_code VARCHAR, " 2214 "country_code VARCHAR, " 2215 "date_modified INTEGER NOT NULL DEFAULT 0, " 2216 "origin VARCHAR DEFAULT '')")) { 2217 return false; 2218 } 2219 2220 // Copy over the data from the autofill_profiles table, taking care to merge 2221 // the address lines 1 and 2 into the new street_address column. 2222 if (!db_->Execute("INSERT INTO autofill_profiles_temp " 2223 "SELECT guid, company_name, '', '', city, state, zipcode," 2224 " '', country_code, date_modified, origin " 2225 "FROM autofill_profiles")) { 2226 return false; 2227 } 2228 sql::Statement s(db_->GetUniqueStatement( 2229 "SELECT guid, address_line_1, address_line_2 FROM autofill_profiles")); 2230 while (s.Step()) { 2231 std::string guid = s.ColumnString(0); 2232 base::string16 line1 = s.ColumnString16(1); 2233 base::string16 line2 = s.ColumnString16(2); 2234 base::string16 street_address = line1; 2235 if (!line2.empty()) 2236 street_address += base::ASCIIToUTF16("\n") + line2; 2237 2238 sql::Statement s_update(db_->GetUniqueStatement( 2239 "UPDATE autofill_profiles_temp SET street_address=? WHERE guid=?")); 2240 s_update.BindString16(0, street_address); 2241 s_update.BindString(1, guid); 2242 if (!s_update.Run()) 2243 return false; 2244 } 2245 if (!s.Succeeded()) 2246 return false; 2247 2248 // Delete the existing (version 53) table and replace it with the contents 2249 // of the temporary table. 2250 if (!db_->Execute("DROP TABLE autofill_profiles") || 2251 !db_->Execute("ALTER TABLE autofill_profiles_temp " 2252 "RENAME TO autofill_profiles")) { 2253 return false; 2254 } 2255 } 2256 2257 // Test the existence of the |type| column as an indication that a migration 2258 // is needed. It is possible that the new |autofill_profile_phones| schema is 2259 // in place because the table was newly created when migrating from a 2260 // pre-version-23 database. 2261 if (db_->DoesColumnExist("autofill_profile_phones", "type")) { 2262 // Create a temporary copy of the autofill_profile_phones table in the 2263 // (newer) version 54 format. This table removes the deprecated |type| 2264 // column. 2265 if (db_->DoesTableExist("autofill_profile_phones_temp") || 2266 !db_->Execute("CREATE TABLE autofill_profile_phones_temp ( " 2267 "guid VARCHAR, " 2268 "number VARCHAR)")) { 2269 return false; 2270 } 2271 2272 // Copy over the data from the autofill_profile_phones table. 2273 if (!db_->Execute("INSERT INTO autofill_profile_phones_temp " 2274 "SELECT guid, number FROM autofill_profile_phones")) { 2275 return false; 2276 } 2277 2278 // Delete the existing (version 53) table and replace it with the contents 2279 // of the temporary table. 2280 if (!db_->Execute("DROP TABLE autofill_profile_phones")) 2281 return false; 2282 if (!db_->Execute("ALTER TABLE autofill_profile_phones_temp " 2283 "RENAME TO autofill_profile_phones")) { 2284 return false; 2285 } 2286 } 2287 2288 return transaction.Commit(); 2289} 2290 2291bool AutofillTable::MigrateToVersion55MergeAutofillDatesTable() { 2292 sql::Transaction transaction(db_); 2293 if (!transaction.Begin()) 2294 return false; 2295 2296 if (db_->DoesTableExist("autofill_temp") || 2297 !db_->Execute("CREATE TABLE autofill_temp (" 2298 "name VARCHAR, " 2299 "value VARCHAR, " 2300 "value_lower VARCHAR, " 2301 "date_created INTEGER DEFAULT 0, " 2302 "date_last_used INTEGER DEFAULT 0, " 2303 "count INTEGER DEFAULT 1, " 2304 "PRIMARY KEY (name, value))")) { 2305 return false; 2306 } 2307 2308 // Slurp up the data from the existing table and write it to the new table. 2309 sql::Statement s(db_->GetUniqueStatement( 2310 "SELECT name, value, value_lower, count, MIN(date_created)," 2311 " MAX(date_created) " 2312 "FROM autofill a JOIN autofill_dates ad ON a.pair_id=ad.pair_id " 2313 "GROUP BY name, value, value_lower, count")); 2314 while (s.Step()) { 2315 sql::Statement s_insert(db_->GetUniqueStatement( 2316 "INSERT INTO autofill_temp " 2317 "(name, value, value_lower, count, date_created, date_last_used) " 2318 "VALUES (?, ?, ?, ?, ?, ?)")); 2319 s_insert.BindString16(0, s.ColumnString16(0)); 2320 s_insert.BindString16(1, s.ColumnString16(1)); 2321 s_insert.BindString16(2, s.ColumnString16(2)); 2322 s_insert.BindInt(3, s.ColumnInt(3)); 2323 s_insert.BindInt64(4, s.ColumnInt64(4)); 2324 s_insert.BindInt64(5, s.ColumnInt64(5)); 2325 if (!s_insert.Run()) 2326 return false; 2327 } 2328 2329 if (!s.Succeeded()) 2330 return false; 2331 2332 // Delete the existing (version 54) tables and replace them with the contents 2333 // of the temporary table. 2334 if (!db_->Execute("DROP TABLE autofill") || 2335 !db_->Execute("DROP TABLE autofill_dates") || 2336 !db_->Execute("ALTER TABLE autofill_temp " 2337 "RENAME TO autofill")) { 2338 return false; 2339 } 2340 2341 // Create indices on the new table, for fast lookups. 2342 if (!db_->Execute("CREATE INDEX autofill_name ON autofill (name)") || 2343 !db_->Execute("CREATE INDEX autofill_name_value_lower ON " 2344 "autofill (name, value_lower)")) { 2345 return false; 2346 } 2347 2348 2349 return transaction.Commit(); 2350} 2351 2352bool AutofillTable::MigrateToVersion56AddProfileLanguageCodeForFormatting() { 2353 return db_->Execute("ALTER TABLE autofill_profiles " 2354 "ADD COLUMN language_code VARCHAR"); 2355} 2356 2357bool AutofillTable::MigrateToVersion57AddFullNameField() { 2358 return db_->Execute("ALTER TABLE autofill_profile_names " 2359 "ADD COLUMN full_name VARCHAR"); 2360} 2361 2362} // namespace autofill 2363