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