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.
5 #include "components/autofill/core/browser/webdata/autofill_table.h"
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"
40 typedef std::vector<Tuple3<int64, base::string16, base::string16> >
43 // TODO(dhollowa): Find a common place for this. It is duplicated in
44 // personal_data_manager.cc.
50 base::string16 LimitDataSize(const base::string16& data) {
51 if (data.size() > AutofillTable::kMaxDataLength)
52 return data.substr(0, AutofillTable::kMaxDataLength);
57 void BindAutofillProfileToStatement(const AutofillProfile& profile,
59 const std::string& app_locale) {
60 DCHECK(base::IsValidGUID(profile.guid()));
61 s->BindString(0, profile.guid());
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());
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()));
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));
103 void BindCreditCardToStatement(const CreditCard& credit_card,
105 DCHECK(base::IsValidGUID(credit_card.guid()));
106 s->BindString(0, credit_card.guid());
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());
123 CreditCard* CreditCardFromStatement(const sql::Statement& s) {
124 CreditCard* credit_card = new CreditCard;
126 credit_card->set_guid(s.ColumnString(0));
127 DCHECK(base::IsValidGUID(credit_card->guid()));
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);
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));
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 "
153 s.BindString(0, profile->guid());
158 std::vector<base::string16> first_names;
159 std::vector<base::string16> middle_names;
160 std::vector<base::string16> last_names;
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));
170 profile->SetRawMultiInfo(NAME_FIRST, first_names);
171 profile->SetRawMultiInfo(NAME_MIDDLE, middle_names);
172 profile->SetRawMultiInfo(NAME_LAST, last_names);
176 bool AddAutofillProfileEmailsToProfile(sql::Connection* db,
177 AutofillProfile* profile) {
178 sql::Statement s(db->GetUniqueStatement(
179 "SELECT guid, email "
180 "FROM autofill_profile_emails "
182 s.BindString(0, profile->guid());
187 std::vector<base::string16> emails;
189 DCHECK_EQ(profile->guid(), s.ColumnString(0));
190 emails.push_back(s.ColumnString16(1));
195 profile->SetRawMultiInfo(EMAIL_ADDRESS, emails);
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=?"));
206 // Value used to be either [(0, phone), (1, fax)] but fax has been removed.
207 s.BindString(0, profile->guid());
213 std::vector<base::string16> numbers;
215 DCHECK_EQ(profile->guid(), s.ColumnString(0));
216 numbers.push_back(s.ColumnString16(2));
221 profile->SetRawMultiInfo(PHONE_HOME_WHOLE_NUMBER, numbers);
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());
236 for (size_t i = 0; i < first_names.size(); ++i) {
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]);
253 bool AddAutofillProfileEmails(const AutofillProfile& profile,
254 sql::Connection* db) {
255 std::vector<base::string16> emails;
256 profile.GetRawMultiInfo(EMAIL_ADDRESS, &emails);
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"
264 s.BindString(0, profile.guid());
265 s.BindString16(1, emails[i]);
274 bool AddAutofillProfilePhones(const AutofillProfile& profile,
275 sql::Connection* db) {
276 std::vector<base::string16> numbers;
277 profile.GetRawMultiInfo(PHONE_HOME_WHOLE_NUMBER, &numbers);
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) "
285 s.BindString(0, profile.guid());
286 // Value used to be either [(0, phone), (1, fax)] but fax has been removed.
288 s.BindString16(2, numbers[i]);
297 bool AddAutofillProfilePieces(const AutofillProfile& profile,
298 sql::Connection* db) {
299 if (!AddAutofillProfileNames(profile, db))
302 if (!AddAutofillProfileEmails(profile, db))
305 if (!AddAutofillProfilePhones(profile, db))
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);
319 sql::Statement s2(db->GetUniqueStatement(
320 "DELETE FROM autofill_profile_emails WHERE guid = ?"));
321 s2.BindString(0, guid);
326 sql::Statement s3(db->GetUniqueStatement(
327 "DELETE FROM autofill_profile_phones WHERE guid = ?"));
328 s3.BindString(0, guid);
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);
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();
344 return end.ToTimeT();
349 // The maximum length allowed for form data.
350 const size_t AutofillTable::kMaxDataLength = 1024;
352 AutofillTable::AutofillTable(const std::string& app_locale)
353 : app_locale_(app_locale) {
356 AutofillTable::~AutofillTable() {
359 AutofillTable* AutofillTable::FromWebDatabase(WebDatabase* db) {
360 return static_cast<AutofillTable*>(db->GetTable(GetKey()));
363 WebDatabaseTable::TypeKey AutofillTable::GetTypeKey() const {
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());
375 bool AutofillTable::IsSyncable() {
379 bool AutofillTable::MigrateToVersion(int version,
380 bool* update_compatible_version) {
381 // Migrate if necessary.
384 return ClearAutofillEmptyValueElements();
386 return MigrateToVersion23AddCardNumberEncryptedColumn();
388 return MigrateToVersion24CleanupOversizedStringFields();
390 *update_compatible_version = true;
391 return MigrateToVersion27UpdateLegacyCreditCards();
393 *update_compatible_version = true;
394 return MigrateToVersion30AddDateModifed();
396 *update_compatible_version = true;
397 return MigrateToVersion31AddGUIDToCreditCardsAndProfiles();
399 *update_compatible_version = true;
400 return MigrateToVersion32UpdateProfilesAndCreditCards();
402 *update_compatible_version = true;
403 return MigrateToVersion33ProfilesBasedOnFirstName();
405 *update_compatible_version = true;
406 return MigrateToVersion34ProfilesBasedOnCountryCode();
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.
418 *update_compatible_version = true;
419 return MigrateToVersion37MergeAndCullOlderProfiles();
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();
429 bool AutofillTable::AddFormFieldValues(
430 const std::vector<FormFieldData>& elements,
431 std::vector<AutofillChange>* changes) {
432 return AddFormFieldValuesTime(elements, changes, Time::Now());
435 bool AutofillTable::AddFormFieldValue(const FormFieldData& element,
436 std::vector<AutofillChange>* changes) {
437 return AddFormFieldValueTime(element, changes, Time::Now());
440 bool AutofillTable::GetFormValuesForElementName(
441 const base::string16& name,
442 const base::string16& prefix,
443 std::vector<base::string16>* values,
448 if (prefix.empty()) {
449 s.Assign(db_->GetUniqueStatement(
450 "SELECT value FROM autofill "
452 "ORDER BY count DESC "
454 s.BindString16(0, name);
457 base::string16 prefix_lower = base::i18n::ToLower(prefix);
458 base::string16 next_prefix = prefix_lower;
459 next_prefix[next_prefix.length() - 1]++;
461 s.Assign(db_->GetUniqueStatement(
462 "SELECT value FROM autofill "
463 "WHERE name = ? AND "
464 "value_lower >= ? AND "
466 "ORDER BY count DESC "
468 s.BindString16(0, name);
469 s.BindString16(1, prefix_lower);
470 s.BindString16(2, next_prefix);
476 values->push_back(s.ColumnString16(0));
477 return s.Succeeded();
480 bool AutofillTable::HasFormElements() {
481 sql::Statement s(db_->GetUniqueStatement(
482 "SELECT COUNT(*) FROM autofill"));
487 return s.ColumnInt(0) > 0;
490 bool AutofillTable::RemoveFormElementsAddedBetween(
491 const Time& delete_begin,
492 const Time& delete_end,
493 std::vector<AutofillChange>* 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());
503 (delete_end.is_null() || delete_end == base::Time::Max()) ?
504 std::numeric_limits<int64>::max() :
505 delete_end.ToTimeT());
507 AutofillElementList elements;
509 elements.push_back(MakeTuple(s.ColumnInt64(0),
511 s.ColumnString16(2)));
516 for (AutofillElementList::iterator itr = elements.begin();
517 itr != elements.end(); ++itr) {
519 if (!RemoveFormElementForTimeRange(itr->a, delete_begin, delete_end,
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);
528 if (!RemoveFormElementForID(itr->a))
531 if (!AddToCountOfFormElement(itr->a, -how_many))
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)));
543 bool AutofillTable::RemoveExpiredFormElements(
544 std::vector<AutofillChange>* changes) {
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)));
563 if (!select_for_delete.Succeeded())
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())
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())
580 // Cull remaining entries' timestamps.
581 std::vector<AutofillEntry> entries;
582 if (!GetAllAutofillEntries(&entries))
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())
600 cull_date_entry.Reset(true);
604 changes->reserve(entries_to_delete.size());
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)));
614 bool AutofillTable::RemoveFormElementForTimeRange(int64 pair_id,
615 const Time& delete_begin,
616 const Time& delete_end,
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());
626 bool result = s.Run();
628 *how_many = db_->GetLastChangeCount();
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);
641 return s.ColumnInt(0);
645 bool AutofillTable::AddToCountOfFormElement(int64 pair_id,
649 if (!GetCountOfFormElement(pair_id, &count))
652 if (count + delta == 0) {
653 // Should remove the element earlier in the code.
657 if (!SetCountOfFormElement(pair_id, count + delta))
663 bool AutofillTable::GetIDAndCountOfFormElement(
664 const FormFieldData& element,
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);
683 *pair_id = s.ColumnInt64(0);
684 *count = s.ColumnInt(1);
690 bool AutofillTable::GetCountOfFormElement(int64 pair_id, int* count) {
692 sql::Statement s(db_->GetUniqueStatement(
693 "SELECT count FROM autofill WHERE pair_id = ?"));
694 s.BindInt64(0, pair_id);
697 *count = s.ColumnInt(0);
703 bool AutofillTable::SetCountOfFormElement(int64 pair_id, int count) {
704 sql::Statement s(db_->GetUniqueStatement(
705 "UPDATE autofill SET count = ? WHERE pair_id = ?"));
707 s.BindInt64(1, pair_id);
712 bool AutofillTable::InsertFormElement(const FormFieldData& element,
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));
724 *pair_id = db_->GetLastInsertRowId();
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());
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);
752 bool AutofillTable::AddFormFieldValuesTime(
753 const std::vector<FormFieldData>& elements,
754 std::vector<AutofillChange>* changes,
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;
761 for (std::vector<FormFieldData>::const_iterator itr = elements.begin();
762 itr != elements.end(); ++itr) {
763 if (seen_names.size() >= kMaximumUniqueNames)
765 if (seen_names.find(itr->name) != seen_names.end())
767 result = result && AddFormFieldValueTime(*itr, changes, time);
768 seen_names.insert(itr->name);
773 bool AutofillTable::ClearAutofillEmptyValueElements() {
774 sql::Statement s(db_->GetUniqueStatement(
775 "SELECT pair_id FROM autofill WHERE TRIM(value)= \"\""));
781 ids.insert(s.ColumnInt64(0));
786 for (std::set<int64>::const_iterator iter = ids.begin(); iter != ids.end();
788 if (!RemoveFormElementForID(*iter))
795 bool AutofillTable::GetAllAutofillEntries(std::vector<AutofillEntry>* 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"));
801 bool first_entry = true;
802 AutofillKey* current_key_ptr = NULL;
803 std::vector<Time>* timestamps_ptr = NULL;
804 base::string16 name, value;
807 name = s.ColumnString16(0);
808 value = s.ColumnString16(1);
809 time = Time::FromTimeT(s.ColumnInt64(2));
812 current_key_ptr = new AutofillKey(name, value);
814 timestamps_ptr = new std::vector<Time>;
815 timestamps_ptr->push_back(time);
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);
825 delete current_key_ptr;
826 delete timestamps_ptr;
828 current_key_ptr = new AutofillKey(name, value);
829 timestamps_ptr = new std::vector<Time>;
831 timestamps_ptr->push_back(time);
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.
838 AutofillEntry entry(*current_key_ptr, *timestamps_ptr);
839 entries->push_back(entry);
840 delete current_key_ptr;
841 delete timestamps_ptr;
844 return s.Succeeded();
847 bool AutofillTable::GetAutofillTimestamps(const base::string16& name,
848 const base::string16& value,
849 std::vector<Time>* 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);
859 timestamps->push_back(Time::FromTimeT(s.ColumnInt64(0)));
861 return s.Succeeded();
864 bool AutofillTable::UpdateAutofillEntries(
865 const std::vector<AutofillEntry>& entries) {
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());
881 if (!RemoveFormElementForID(s.ColumnInt64(0)))
886 // Insert all the supplied autofill entries.
887 for (size_t i = 0; i < entries.size(); i++) {
888 if (!InsertAutofillEntry(entries[i]))
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());
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]))
916 bool AutofillTable::AddFormFieldValueTime(const FormFieldData& element,
917 std::vector<AutofillChange>* changes,
922 if (!GetIDAndCountOfFormElement(element, &pair_id, &count))
925 if (count == 0 && !InsertFormElement(element, &pair_id))
928 if (!SetCountOfFormElement(pair_id, count + 1))
931 // If we already have more than 2 times delete last one, before adding new
933 if (count >= 2 && !DeleteLastAccess(pair_id))
936 if (!InsertPairIDAndDate(pair_id, time))
939 AutofillChange::Type change_type =
940 count == 0 ? AutofillChange::ADD : AutofillChange::UPDATE;
942 AutofillChange(change_type,
943 AutofillKey(element.name, element.value)));
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);
957 return RemoveFormElementForID(s.ColumnInt64(0));
961 bool AutofillTable::AddAutofillProfile(const AutofillProfile& profile) {
962 if (IsAutofillGUIDInTrash(profile.guid()))
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_);
975 return AddAutofillProfilePieces(profile, db_);
978 bool AutofillTable::GetAutofillProfile(const std::string& guid,
979 AutofillProfile** profile) {
980 DCHECK(base::IsValidGUID(guid));
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 "
987 s.BindString(0, guid);
992 scoped_ptr<AutofillProfile> p(AutofillProfileFromStatement(s, app_locale_));
994 // Get associated name info.
995 AddAutofillProfileNamesToProfile(db_, p.get());
997 // Get associated email info.
998 AddAutofillProfileEmailsToProfile(db_, p.get());
1000 // Get associated phone info.
1001 AddAutofillProfilePhonesToProfile(db_, p.get());
1003 *profile = p.release();
1007 bool AutofillTable::GetAutofillProfiles(
1008 std::vector<AutofillProfile*>* profiles) {
1012 sql::Statement s(db_->GetUniqueStatement(
1014 "FROM autofill_profiles"));
1017 std::string guid = s.ColumnString(0);
1018 AutofillProfile* profile = NULL;
1019 if (!GetAutofillProfile(guid, &profile))
1021 profiles->push_back(profile);
1024 return s.Succeeded();
1027 bool AutofillTable::UpdateAutofillProfile(const AutofillProfile& profile) {
1028 DCHECK(base::IsValidGUID(profile.guid()));
1030 // Don't update anything until the trash has been emptied. There may be
1031 // pending modifications to process.
1032 if (!IsAutofillProfilesTrashEmpty())
1035 AutofillProfile* tmp_profile = NULL;
1036 if (!GetAutofillProfile(profile.guid(), &tmp_profile))
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())
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=? "
1051 BindAutofillProfileToStatement(profile, &s, app_locale_);
1052 s.BindString(11, profile.guid());
1054 bool result = s.Run();
1055 DCHECK_GT(db_->GetLastChangeCount(), 0);
1059 // Remove the old names, emails, and phone numbers.
1060 if (!RemoveAutofillProfilePieces(profile.guid(), db_))
1063 return AddAutofillProfilePieces(profile, db_);
1066 bool AutofillTable::RemoveAutofillProfile(const std::string& guid) {
1067 DCHECK(base::IsValidGUID(guid));
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);
1074 bool success = s_trash.Run();
1075 DCHECK_GT(db_->GetLastChangeCount(), 0) << "Expected item in trash";
1079 sql::Statement s(db_->GetUniqueStatement(
1080 "DELETE FROM autofill_profiles WHERE guid = ?"));
1081 s.BindString(0, guid);
1086 return RemoveAutofillProfilePieces(guid, db_);
1089 bool AutofillTable::ClearAutofillProfiles() {
1090 sql::Statement s1(db_->GetUniqueStatement(
1091 "DELETE FROM autofill_profiles"));
1096 sql::Statement s2(db_->GetUniqueStatement(
1097 "DELETE FROM autofill_profile_names"));
1102 sql::Statement s3(db_->GetUniqueStatement(
1103 "DELETE FROM autofill_profile_emails"));
1108 sql::Statement s4(db_->GetUniqueStatement(
1109 "DELETE FROM autofill_profile_phones"));
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);
1125 DCHECK_GT(db_->GetLastChangeCount(), 0);
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 "
1137 s.BindString(0, guid);
1142 *credit_card = CreditCardFromStatement(s);
1146 bool AutofillTable::GetCreditCards(
1147 std::vector<CreditCard*>* credit_cards) {
1148 DCHECK(credit_cards);
1149 credit_cards->clear();
1151 sql::Statement s(db_->GetUniqueStatement(
1153 "FROM credit_cards"));
1156 std::string guid = s.ColumnString(0);
1157 CreditCard* credit_card = NULL;
1158 if (!GetCreditCard(guid, &credit_card))
1160 credit_cards->push_back(credit_card);
1163 return s.Succeeded();
1166 bool AutofillTable::UpdateCreditCard(const CreditCard& credit_card) {
1167 DCHECK(base::IsValidGUID(credit_card.guid()));
1169 CreditCard* tmp_credit_card = NULL;
1170 if (!GetCreditCard(credit_card.guid(), &tmp_credit_card))
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)
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=?, "
1184 BindCreditCardToStatement(credit_card, &s);
1185 s.BindString(7, credit_card.guid());
1187 bool result = s.Run();
1188 DCHECK_GT(db_->GetLastChangeCount(), 0);
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);
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);
1208 time_t delete_begin_t = delete_begin.ToTimeT();
1209 time_t delete_end_t = GetEndTime(delete_end);
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);
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);
1223 if (!s_profiles_get.Succeeded())
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);
1233 if (!s_profiles.Run())
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);
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);
1248 if (!s_credit_cards_get.Succeeded())
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);
1258 return s_credit_cards.Run();
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);
1267 time_t delete_begin_t = delete_begin.ToTimeT();
1268 time_t delete_end_t = GetEndTime(delete_end);
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);
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);
1284 if (!s_profiles_get.Succeeded())
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())
1296 AutofillProfile* profile;
1297 if (!GetAutofillProfile(*it, &profile))
1300 profiles->push_back(profile);
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);
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);
1317 if (!s_credit_cards_get.Succeeded())
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())
1333 bool AutofillTable::GetAutofillProfilesInTrash(
1334 std::vector<std::string>* guids) {
1337 sql::Statement s(db_->GetUniqueStatement(
1339 "FROM autofill_profiles_trash"));
1342 std::string guid = s.ColumnString(0);
1343 guids->push_back(guid);
1346 return s.Succeeded();
1349 bool AutofillTable::EmptyAutofillProfilesTrash() {
1350 sql::Statement s(db_->GetUniqueStatement(
1351 "DELETE FROM autofill_profiles_trash"));
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);
1363 return RemoveFormElementForTimeRange(pair_id, Time(), Time(), NULL);
1368 bool AutofillTable::AddAutofillGUIDToTrash(const std::string& guid) {
1369 sql::Statement s(db_->GetUniqueStatement(
1370 "INSERT INTO autofill_profiles_trash"
1373 s.BindString(0, guid);
1378 bool AutofillTable::IsAutofillProfilesTrashEmpty() {
1379 sql::Statement s(db_->GetUniqueStatement(
1381 "FROM autofill_profiles_trash"));
1386 bool AutofillTable::IsAutofillGUIDInTrash(const std::string& guid) {
1387 sql::Statement s(db_->GetUniqueStatement(
1389 "FROM autofill_profiles_trash "
1391 s.BindString(0, guid);
1396 bool AutofillTable::InitMainTable() {
1397 if (!db_->DoesTableExist("autofill")) {
1398 if (!db_->Execute("CREATE TABLE autofill ("
1401 "value_lower VARCHAR, "
1402 "pair_id INTEGER PRIMARY KEY, "
1403 "count INTEGER DEFAULT 1)")) {
1407 if (!db_->Execute("CREATE INDEX autofill_name ON autofill (name)")) {
1411 if (!db_->Execute("CREATE INDEX autofill_name_value_lower ON "
1412 "autofill (name, value_lower)")) {
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 '')")) {
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)")) {
1446 if (!db_->Execute("CREATE INDEX autofill_dates_pair_id ON "
1447 "autofill_dates (pair_id)")) {
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, "
1466 "country_code VARCHAR, "
1467 "date_modified INTEGER NOT NULL DEFAULT 0, "
1468 "origin VARCHAR DEFAULT '')")) {
1476 bool AutofillTable::InitProfileNamesTable() {
1477 if (!db_->DoesTableExist("autofill_profile_names")) {
1478 if (!db_->Execute("CREATE TABLE autofill_profile_names ( "
1480 "first_name VARCHAR, "
1481 "middle_name VARCHAR, "
1482 "last_name VARCHAR)")) {
1490 bool AutofillTable::InitProfileEmailsTable() {
1491 if (!db_->DoesTableExist("autofill_profile_emails")) {
1492 if (!db_->Execute("CREATE TABLE autofill_profile_emails ( "
1494 "email VARCHAR)")) {
1502 bool AutofillTable::InitProfilePhonesTable() {
1503 if (!db_->DoesTableExist("autofill_profile_phones")) {
1504 if (!db_->Execute("CREATE TABLE autofill_profile_phones ( "
1506 "type INTEGER DEFAULT 0, "
1507 "number VARCHAR)")) {
1515 bool AutofillTable::InitProfileTrashTable() {
1516 if (!db_->DoesTableExist("autofill_profiles_trash")) {
1517 if (!db_->Execute("CREATE TABLE autofill_profiles_trash ( "
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
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.
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
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.";
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.";
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";
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) "
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";
1600 std::string query = "DELETE FROM autofill_dates WHERE pair_id IN ("
1601 "SELECT pair_id FROM autofill WHERE " + autofill_is_too_big + ")";
1603 if (!db_->Execute(query.c_str()))
1606 query = "DELETE FROM autofill WHERE " + autofill_is_too_big;
1608 if (!db_->Execute(query.c_str()))
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 + ")";
1624 if (!db_->Execute(query.c_str()))
1628 if (db_->DoesColumnExist("autofill_profiles", "label")) {
1629 query = "DELETE FROM autofill_profiles WHERE " +
1630 autofill_profiles_is_too_big;
1632 if (!db_->Execute(query.c_str()))
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
1646 if (!(db_->DoesColumnExist("credit_cards", "unique_id") &&
1647 db_->DoesColumnExist("credit_cards", "billing_address") &&
1648 db_->DoesColumnExist("autofill_profiles", "unique_id"))) {
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()));
1658 std::map<int, int> cc_billing_map;
1660 cc_billing_map[s.ColumnInt(0)] = s.ColumnInt(1);
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()));
1672 if (base::StringToInt(s.ColumnString(1), &id))
1673 cc_billing_map[s.ColumnInt(0)] = id;
1679 if (!db_->Execute("CREATE TABLE credit_cards_temp ( "
1681 "unique_id INTEGER PRIMARY KEY, "
1682 "name_on_card 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)")) {
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")) {
1704 if (!db_->Execute("DROP TABLE credit_cards"))
1707 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards"))
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(
1714 "UPDATE credit_cards SET billing_address=? WHERE unique_id=?"));
1715 s.BindInt(0, (*iter).second);
1716 s.BindInt(1, (*iter).first);
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")) {
1733 sql::Statement s(db_->GetUniqueStatement(
1734 "UPDATE autofill_profiles SET date_modified=?"));
1735 s.BindInt64(0, Time::Now().ToTimeT());
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")) {
1748 sql::Statement s(db_->GetUniqueStatement(
1749 "UPDATE credit_cards SET date_modified=?"));
1750 s.BindInt64(0, Time::Now().ToTimeT());
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 \"\"")) {
1769 // Set all the |guid| fields to valid values.
1771 sql::Statement s(db_->GetUniqueStatement("SELECT unique_id "
1772 "FROM autofill_profiles"));
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));
1781 if (!update_s.Run())
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 \"\"")) {
1797 // Set all the |guid| fields to valid values.
1799 sql::Statement s(db_->GetUniqueStatement("SELECT unique_id "
1800 "FROM credit_cards"));
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));
1809 if (!update_s.Run())
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, "
1824 "first_name VARCHAR, "
1825 "middle_name VARCHAR, "
1826 "last_name VARCHAR, "
1828 "company_name VARCHAR, "
1829 "address_line_1 VARCHAR, "
1830 "address_line_2 VARCHAR, "
1836 "date_modified INTEGER NOT NULL DEFAULT 0)")) {
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")) {
1849 if (!db_->Execute("DROP TABLE autofill_profiles"))
1853 "ALTER TABLE autofill_profiles_temp RENAME TO autofill_profiles")) {
1858 if (db_->DoesColumnExist("credit_cards", "unique_id")) {
1859 if (!db_->Execute("CREATE TABLE credit_cards_temp ( "
1860 "guid VARCHAR PRIMARY KEY, "
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)")) {
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")) {
1878 if (!db_->Execute("DROP TABLE credit_cards"))
1881 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards"))
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, "
1905 "date_modified INTEGER NOT NULL DEFAULT 0)")) {
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"));
1917 AutofillProfile profile;
1918 profile.set_guid(s.ColumnString(0));
1919 DCHECK(base::IsValidGUID(profile.guid()));
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),
1933 profile.SetRawInfo(PHONE_HOME_WHOLE_NUMBER, s.ColumnString16(12));
1934 int64 date_modified = s.ColumnInt64(13);
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);
1951 if (!s_insert.Run())
1954 // Add the other bits: names, emails, and phone numbers.
1955 if (!AddAutofillProfilePieces(profile, db_))
1961 if (!db_->Execute("DROP TABLE autofill_profiles"))
1965 "ALTER TABLE autofill_profiles_temp RENAME TO autofill_profiles")) {
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)")) {
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")) {
1990 if (!db_->Execute("DROP TABLE credit_cards"))
1993 if (!db_->Execute("ALTER TABLE credit_cards_temp RENAME TO credit_cards"))
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")) {
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"));
2016 sql::Statement update_s(
2017 db_->GetUniqueStatement("UPDATE autofill_profiles "
2018 "SET country_code=? WHERE guid=?"));
2020 base::string16 country = s.ColumnString16(1);
2021 update_s.BindString(0, AutofillCountry::GetCountryCode(country,
2023 update_s.BindString(1, s.ColumnString(0));
2025 if (!update_s.Run())
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\""));
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"));
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;
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));
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 "
2066 s.BindString(0, guid);
2071 scoped_ptr<AutofillProfile> profile(new AutofillProfile);
2072 profile->set_guid(s.ColumnString(0));
2073 DCHECK(base::IsValidGUID(profile->guid()));
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
2085 profile->set_origin(s.ColumnString(10));
2087 // Get associated name info.
2088 AddAutofillProfileNamesToProfile(db_, profile.get());
2090 // Get associated email info.
2091 AddAutofillProfileEmailsToProfile(db_, profile.get());
2093 // Get associated phone info.
2094 AddAutofillProfilePhonesToProfile(db_, profile.get());
2096 if (PersonalDataManager::IsValidLearnableProfile(*profile, app_locale_)) {
2097 std::vector<AutofillProfile> merged_profiles;
2098 std::string merged_guid = PersonalDataManager::MergeProfile(
2099 *profile, accumulated_profiles_p, app_locale_, &merged_profiles);
2101 std::swap(accumulated_profiles, merged_profiles);
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>);
2110 // If the profile got merged trash the original.
2111 if (merged_guid != profile->guid())
2112 AddAutofillGUIDToTrash(profile->guid());
2114 // An invalid profile, so trash it.
2115 AddAutofillGUIDToTrash(profile->guid());
2121 // Drop the current profiles.
2122 if (!ClearAutofillProfiles())
2125 // Add the newly merged profiles back in.
2126 for (std::vector<AutofillProfile>::const_iterator
2127 iter = accumulated_profiles.begin();
2128 iter != accumulated_profiles.end();
2130 // Save the profile with its original modification date.
2131 std::map<std::string, int64>::const_iterator date_item =
2132 modification_map.find(iter->guid());
2133 if (date_item == modification_map.end())
2136 sql::Statement s(db_->GetUniqueStatement(
2137 "INSERT INTO autofill_profiles"
2138 "(guid, company_name, address_line_1, address_line_2, city, state,"
2139 " zipcode, country, country_code, date_modified)"
2140 "VALUES (?,?,?,?,?,?,?,?,?,?)"));
2141 s.BindString(0, iter->guid());
2142 base::string16 text = iter->GetRawInfo(COMPANY_NAME);
2143 s.BindString16(1, LimitDataSize(text));
2144 text = iter->GetRawInfo(ADDRESS_HOME_LINE1);
2145 s.BindString16(2, LimitDataSize(text));
2146 text = iter->GetRawInfo(ADDRESS_HOME_LINE2);
2147 s.BindString16(3, LimitDataSize(text));
2148 text = iter->GetRawInfo(ADDRESS_HOME_CITY);
2149 s.BindString16(4, LimitDataSize(text));
2150 text = iter->GetRawInfo(ADDRESS_HOME_STATE);
2151 s.BindString16(5, LimitDataSize(text));
2152 text = iter->GetRawInfo(ADDRESS_HOME_ZIP);
2153 s.BindString16(6, LimitDataSize(text));
2154 text = iter->GetInfo(AutofillType(ADDRESS_HOME_COUNTRY), app_locale_);
2155 s.BindString16(7, LimitDataSize(text));
2156 text = iter->GetRawInfo(ADDRESS_HOME_COUNTRY);
2157 s.BindString16(8, LimitDataSize(text));
2158 s.BindInt64(9, date_item->second);
2163 if (!AddAutofillProfilePieces(*iter, db_))
2170 bool AutofillTable::MigrateToVersion51AddOriginColumn() {
2171 sql::Transaction transaction(db_);
2172 if (!transaction.Begin())
2175 // Add origin to autofill_profiles.
2176 if (!db_->DoesColumnExist("autofill_profiles", "origin") &&
2177 !db_->Execute("ALTER TABLE autofill_profiles "
2178 "ADD COLUMN origin VARCHAR DEFAULT ''")) {
2182 // Add origin to credit_cards.
2183 if (!db_->DoesColumnExist("credit_cards", "origin") &&
2184 !db_->Execute("ALTER TABLE credit_cards "
2185 "ADD COLUMN origin VARCHAR DEFAULT ''")) {
2189 return transaction.Commit();
2192 } // namespace autofill