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