web_database.cc revision 3345a6884c488ff3a535c2c9acdd33d74b37e311 
1// Copyright (c) 2010 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/web_database.h"
6
7#include <algorithm>
8#include <limits>
9#include <set>
10#include <string>
11
12#include "app/l10n_util.h"
13#include "app/sql/statement.h"
14#include "app/sql/transaction.h"
15#include "base/string_number_conversions.h"
16#include "base/string_split.h"
17#include "base/string_util.h"
18#include "base/tuple.h"
19#include "base/utf_string_conversions.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/diagnostics/sqlite_diagnostics.h"
24#include "chrome/browser/history/history_database.h"
25#include "chrome/browser/password_manager/encryptor.h"
26#include "chrome/browser/search_engines/template_url.h"
27#include "chrome/browser/webdata/autofill_change.h"
28#include "chrome/common/notification_service.h"
29#include "gfx/codec/png_codec.h"
30#include "third_party/skia/include/core/SkBitmap.h"
31#include "webkit/glue/form_field.h"
32#include "webkit/glue/password_form.h"
33
34// Encryptor is now in place for Windows and Mac.  The Linux implementation
35// currently obfuscates only.  Mac Encryptor implementation can block the
36// active thread while presenting UI to the user.  See |encryptor_mac.mm| for
37// details.
38// For details on the Linux work see:
39//   http://crbug.com/25404
40
41using webkit_glue::FormField;
42using webkit_glue::PasswordForm;
43
44////////////////////////////////////////////////////////////////////////////////
45//
46// Schema
47//   Note: The database stores time in seconds, UTC.
48//
49// keywords                 Most of the columns mirror that of a field in
50//                          TemplateURL. See TemplateURL for more details.
51//   id
52//   short_name
53//   keyword
54//   favicon_url
55//   url
56//   show_in_default_list
57//   safe_for_autoreplace
58//   originating_url
59//   date_created           This column was added after we allowed keywords.
60//                          Keywords created before we started tracking
61//                          creation date have a value of 0 for this.
62//   usage_count
63//   input_encodings        Semicolon separated list of supported input
64//                          encodings, may be empty.
65//   suggest_url
66//   prepopulate_id         See TemplateURL::prepopulate_id.
67//   autogenerate_keyword
68//   logo_id                See TemplateURL::logo_id
69//   created_by_policy      See TemplateURL::created_by_policy.  This was added
70//                          in version 26.
71//
72// logins
73//   origin_url
74//   action_url
75//   username_element
76//   username_value
77//   password_element
78//   password_value
79//   submit_element
80//   signon_realm        The authority (scheme, host, port).
81//   ssl_valid           SSL status of page containing the form at first
82//                       impression.
83//   preferred           MRU bit.
84//   date_created        This column was added after logins support. "Legacy"
85//                       entries have a value of 0.
86//   blacklisted_by_user Tracks whether or not the user opted to 'never
87//                       remember'
88//                       passwords for this site.
89//
90// autofill
91//   name                The name of the input as specified in the html.
92//   value               The literal contents of the text field.
93//   value_lower         The contents of the text field made lower_case.
94//   pair_id             An ID number unique to the row in the table.
95//   count               How many times the user has entered the string |value|
96//                       in a field of name |name|.
97//
98// autofill_dates        This table associates a row to each separate time the
99//                       user submits a form containing a certain name/value
100//                       pair.  The |pair_id| should match the |pair_id| field
101//                       in the appropriate row of the autofill table.
102//   pair_id
103//   date_created
104//
105// autofill_profiles    This table contains AutoFill profile data added by the
106//                      user with the AutoFill dialog.  Most of the columns are
107//                      standard entries in a contact information form.
108//
109//   label              The label of the profile.  Presented to the user when
110//                      selecting profiles.
111//   unique_id          The unique ID of this profile.
112//   first_name
113//   middle_name
114//   last_name
115//   email
116//   company_name
117//   address_line_1
118//   address_line_2
119//   city
120//   state
121//   zipcode
122//   country
123//   phone
124//   fax
125//
126// credit_cards         This table contains credit card data added by the user
127//                      with the AutoFill dialog.  Most of the columns are
128//                      standard entries in a credit card form.
129//
130//   label              The label of the credit card.  Presented to the user
131//                      when selecting credit cards.
132//   unique_id          The unique ID of this credit card.
133//   name_on_card
134//   type
135//   card_number        Before version 23 stores credit card number, 23 and
136//                      after stores empty string.
137//   expiration_month
138//   expiration_year
139//   verification_code  Before version 23 stores the CVC/CVV/CVV2 card security
140//                      code. After that stores the empty string.
141//   billing_address    A foreign key into the autofill_profiles table.
142//   shipping_address   A foreign key into the autofill_profiles table.
143//     For the following two fields encryption is used. Currently it uses
144//     Encryptor, that does encryption on windows only. As on the other
145//     systems this file is readable by owner only, it is good for now.
146//     For potentially going over the wire other encryption is used, see
147//     chrome/browser/sync/protocol/autofill_specifics.proto
148//   card_number_encrypted Stores encrypted credit card number.
149//   verification_code_encrypted  The CVC/CVV/CVV2 card security code.
150//
151// web_app_icons
152//   url         URL of the web app.
153//   width       Width of the image.
154//   height      Height of the image.
155//   image       PNG encoded image data.
156//
157// web_apps
158//   url                 URL of the web app.
159//   has_all_images      Do we have all the images?
160//
161////////////////////////////////////////////////////////////////////////////////
162
163using base::Time;
164
165namespace {
166
167typedef std::vector<Tuple3<int64, string16, string16> > AutofillElementList;
168
169// Current version number.  Note: when changing the current version number,
170// corresponding changes must happen in the unit tests, and new migration test
171// added.  See |WebDatabaseMigrationTest::kCurrentTestedVersionNumber|.
172const int kCurrentVersionNumber = 27;
173const int kCompatibleVersionNumber = 27;
174const int kUrlIdPosition = 15;
175
176// Keys used in the meta table.
177const char* kDefaultSearchProviderKey = "Default Search Provider ID";
178const char* kBuiltinKeywordVersion = "Builtin Keyword Version";
179
180// The maximum length allowed for form data.
181const size_t kMaxDataLength = 1024;
182
183void BindURLToStatement(const TemplateURL& url, sql::Statement* s) {
184  s->BindString(0, WideToUTF8(url.short_name()));
185  s->BindString(1, WideToUTF8(url.keyword()));
186  GURL favicon_url = url.GetFavIconURL();
187  if (!favicon_url.is_valid()) {
188    s->BindString(2, std::string());
189  } else {
190    s->BindString(2, history::HistoryDatabase::GURLToDatabaseURL(
191                       url.GetFavIconURL()));
192  }
193  if (url.url())
194    s->BindString(3, url.url()->url());
195  else
196    s->BindString(3, std::string());
197  s->BindInt(4, url.safe_for_autoreplace() ? 1 : 0);
198  if (!url.originating_url().is_valid()) {
199    s->BindString(5, std::string());
200  } else {
201    s->BindString(5, history::HistoryDatabase::GURLToDatabaseURL(
202        url.originating_url()));
203  }
204  s->BindInt64(6, url.date_created().ToTimeT());
205  s->BindInt(7, url.usage_count());
206  s->BindString(8, JoinString(url.input_encodings(), ';'));
207  s->BindInt(9, url.show_in_default_list() ? 1 : 0);
208  if (url.suggestions_url())
209    s->BindString(10, url.suggestions_url()->url());
210  else
211    s->BindString(10, std::string());
212  s->BindInt(11, url.prepopulate_id());
213  s->BindInt(12, url.autogenerate_keyword() ? 1 : 0);
214  s->BindInt(13, url.logo_id());
215  s->BindBool(14, url.created_by_policy());
216}
217
218void InitPasswordFormFromStatement(PasswordForm* form, sql::Statement* s) {
219  std::string tmp;
220  string16 decrypted_password;
221  tmp = s->ColumnString(0);
222  form->origin = GURL(tmp);
223  tmp = s->ColumnString(1);
224  form->action = GURL(tmp);
225  form->username_element = s->ColumnString16(2);
226  form->username_value = s->ColumnString16(3);
227  form->password_element = s->ColumnString16(4);
228
229  int encrypted_password_len = s->ColumnByteLength(5);
230  std::string encrypted_password;
231  if (encrypted_password_len) {
232    encrypted_password.resize(encrypted_password_len);
233    memcpy(&encrypted_password[0], s->ColumnBlob(5), encrypted_password_len);
234    Encryptor::DecryptString16(encrypted_password, &decrypted_password);
235  }
236
237  form->password_value = decrypted_password;
238  form->submit_element = s->ColumnString16(6);
239  tmp = s->ColumnString(7);
240  form->signon_realm = tmp;
241  form->ssl_valid = (s->ColumnInt(8) > 0);
242  form->preferred = (s->ColumnInt(9) > 0);
243  form->date_created = Time::FromTimeT(s->ColumnInt64(10));
244  form->blacklisted_by_user = (s->ColumnInt(11) > 0);
245  int scheme_int = s->ColumnInt(12);
246  DCHECK((scheme_int >= 0) && (scheme_int <= PasswordForm::SCHEME_OTHER));
247  form->scheme = static_cast<PasswordForm::Scheme>(scheme_int);
248}
249
250// TODO(jhawkins): This is a temporary stop-gap measure designed to prevent
251// a malicious site from DOS'ing the browser with extremely large profile
252// data.  The correct solution is to parse this data asynchronously.
253// See http://crbug.com/49332.
254string16 LimitDataSize(const string16& data) {
255  if (data.size() > kMaxDataLength)
256    return data.substr(0, kMaxDataLength);
257
258  return data;
259}
260
261void BindAutoFillProfileToStatement(const AutoFillProfile& profile,
262                                    sql::Statement* s) {
263  s->BindString16(0, profile.Label());
264  s->BindInt(1, profile.unique_id());
265
266  string16 text = profile.GetFieldText(AutoFillType(NAME_FIRST));
267  s->BindString16(2, LimitDataSize(text));
268  text = profile.GetFieldText(AutoFillType(NAME_MIDDLE));
269  s->BindString16(3, LimitDataSize(text));
270  text = profile.GetFieldText(AutoFillType(NAME_LAST));
271  s->BindString16(4, LimitDataSize(text));
272  text = profile.GetFieldText(AutoFillType(EMAIL_ADDRESS));
273  s->BindString16(5, LimitDataSize(text));
274  text = profile.GetFieldText(AutoFillType(COMPANY_NAME));
275  s->BindString16(6, LimitDataSize(text));
276  text = profile.GetFieldText(AutoFillType(ADDRESS_HOME_LINE1));
277  s->BindString16(7, LimitDataSize(text));
278  text = profile.GetFieldText(AutoFillType(ADDRESS_HOME_LINE2));
279  s->BindString16(8, LimitDataSize(text));
280  text = profile.GetFieldText(AutoFillType(ADDRESS_HOME_CITY));
281  s->BindString16(9, LimitDataSize(text));
282  text = profile.GetFieldText(AutoFillType(ADDRESS_HOME_STATE));
283  s->BindString16(10, LimitDataSize(text));
284  text = profile.GetFieldText(AutoFillType(ADDRESS_HOME_ZIP));
285  s->BindString16(11, LimitDataSize(text));
286  text = profile.GetFieldText(AutoFillType(ADDRESS_HOME_COUNTRY));
287  s->BindString16(12, LimitDataSize(text));
288  text = profile.GetFieldText(AutoFillType(PHONE_HOME_WHOLE_NUMBER));
289  s->BindString16(13, LimitDataSize(text));
290  text = profile.GetFieldText(AutoFillType(PHONE_FAX_WHOLE_NUMBER));
291  s->BindString16(14, LimitDataSize(text));
292}
293
294AutoFillProfile* AutoFillProfileFromStatement(const sql::Statement& s) {
295  AutoFillProfile* profile = new AutoFillProfile(
296      s.ColumnString16(0), s.ColumnInt(1));
297  profile->SetInfo(AutoFillType(NAME_FIRST),
298                   s.ColumnString16(2));
299  profile->SetInfo(AutoFillType(NAME_MIDDLE),
300                   s.ColumnString16(3));
301  profile->SetInfo(AutoFillType(NAME_LAST),
302                   s.ColumnString16(4));
303  profile->SetInfo(AutoFillType(EMAIL_ADDRESS),
304                   s.ColumnString16(5));
305  profile->SetInfo(AutoFillType(COMPANY_NAME),
306                   s.ColumnString16(6));
307  profile->SetInfo(AutoFillType(ADDRESS_HOME_LINE1),
308                   s.ColumnString16(7));
309  profile->SetInfo(AutoFillType(ADDRESS_HOME_LINE2),
310                   s.ColumnString16(8));
311  profile->SetInfo(AutoFillType(ADDRESS_HOME_CITY),
312                   s.ColumnString16(9));
313  profile->SetInfo(AutoFillType(ADDRESS_HOME_STATE),
314                   s.ColumnString16(10));
315  profile->SetInfo(AutoFillType(ADDRESS_HOME_ZIP),
316                   s.ColumnString16(11));
317  profile->SetInfo(AutoFillType(ADDRESS_HOME_COUNTRY),
318                   s.ColumnString16(12));
319  profile->SetInfo(AutoFillType(PHONE_HOME_WHOLE_NUMBER),
320                   s.ColumnString16(13));
321  profile->SetInfo(AutoFillType(PHONE_FAX_WHOLE_NUMBER),
322                   s.ColumnString16(14));
323
324  return profile;
325}
326
327void BindCreditCardToStatement(const CreditCard& credit_card,
328                               sql::Statement* s) {
329  s->BindString16(0, credit_card.Label());
330  s->BindInt(1, credit_card.unique_id());
331
332  string16 text = credit_card.GetFieldText(AutoFillType(CREDIT_CARD_NAME));
333  s->BindString16(2, LimitDataSize(text));
334  text = credit_card.GetFieldText(AutoFillType(CREDIT_CARD_TYPE));
335  s->BindString16(3, LimitDataSize(text));
336  text.clear();  // No unencrypted cc info.
337  s->BindString16(4, LimitDataSize(text));
338  text = credit_card.GetFieldText(AutoFillType(CREDIT_CARD_EXP_MONTH));
339  s->BindString16(5, LimitDataSize(text));
340  text = credit_card.GetFieldText(AutoFillType(CREDIT_CARD_EXP_4_DIGIT_YEAR));
341  s->BindString16(6, LimitDataSize(text));
342  text.clear();
343  s->BindString16(7, LimitDataSize(text));
344  s->BindInt(8, credit_card.billing_address_id());
345  // We don't store the shipping address anymore.
346  text.clear();
347  s->BindString16(9, LimitDataSize(text));
348  text = credit_card.GetFieldText(AutoFillType(CREDIT_CARD_NUMBER));
349  std::string encrypted_data;
350  Encryptor::EncryptString16(text, &encrypted_data);
351  s->BindBlob(10, encrypted_data.data(),
352              static_cast<int>(encrypted_data.length()));
353  // We don't store the CVV anymore.
354  text.clear();
355  s->BindBlob(11, text.data(), static_cast<int>(text.length()));
356}
357
358CreditCard* CreditCardFromStatement(const sql::Statement& s) {
359  CreditCard* credit_card = new CreditCard(
360      s.ColumnString16(0), s.ColumnInt(1));
361  credit_card->SetInfo(AutoFillType(CREDIT_CARD_NAME),
362                       s.ColumnString16(2));
363  credit_card->SetInfo(AutoFillType(CREDIT_CARD_TYPE),
364                       s.ColumnString16(3));
365  string16 credit_card_number = s.ColumnString16(4);
366  // It could be non-empty prior to version 23. After that it encrypted in
367  // the column 10.
368  if (credit_card_number.empty()) {
369    int encrypted_cc_len = s.ColumnByteLength(10);
370    std::string encrypted_cc;
371    if (encrypted_cc_len) {
372      encrypted_cc.resize(encrypted_cc_len);
373      memcpy(&encrypted_cc[0], s.ColumnBlob(10), encrypted_cc_len);
374      Encryptor::DecryptString16(encrypted_cc, &credit_card_number);
375    }
376  }
377  credit_card->SetInfo(AutoFillType(CREDIT_CARD_NUMBER), credit_card_number);
378  credit_card->SetInfo(AutoFillType(CREDIT_CARD_EXP_MONTH),
379                       s.ColumnString16(5));
380  credit_card->SetInfo(AutoFillType(CREDIT_CARD_EXP_4_DIGIT_YEAR),
381                       s.ColumnString16(6));
382
383  string16 credit_card_verification_code = s.ColumnString16(7);
384  // We don't store the CVV anymore.
385  credit_card->set_billing_address_id(s.ColumnInt(8));
386  // We don't store the shipping address anymore.
387  // Column 10 is processed above.
388  // Column 11 is processed above.
389
390  return credit_card;
391}
392
393}  // namespace
394
395WebDatabase::WebDatabase() {
396}
397
398WebDatabase::~WebDatabase() {
399}
400
401void WebDatabase::BeginTransaction() {
402  db_.BeginTransaction();
403}
404
405void WebDatabase::CommitTransaction() {
406  db_.CommitTransaction();
407}
408
409sql::InitStatus WebDatabase::Init(const FilePath& db_name) {
410  // When running in unit tests, there is already a NotificationService object.
411  // Since only one can exist at a time per thread, check first.
412  if (!NotificationService::current())
413    notification_service_.reset(new NotificationService);
414
415  // Set the exceptional sqlite error handler.
416  db_.set_error_delegate(GetErrorHandlerForWebDb());
417
418  // We don't store that much data in the tables so use a small page size.
419  // This provides a large benefit for empty tables (which is very likely with
420  // the tables we create).
421  db_.set_page_size(2048);
422
423  // We shouldn't have much data and what access we currently have is quite
424  // infrequent. So we go with a small cache size.
425  db_.set_cache_size(32);
426
427  // Run the database in exclusive mode. Nobody else should be accessing the
428  // database while we're running, and this will give somewhat improved perf.
429  db_.set_exclusive_locking();
430
431  if (!db_.Open(db_name))
432    return sql::INIT_FAILURE;
433
434  // Initialize various tables
435  sql::Transaction transaction(&db_);
436  if (!transaction.Begin())
437    return sql::INIT_FAILURE;
438
439  // Version check.
440  if (!meta_table_.Init(&db_, kCurrentVersionNumber, kCompatibleVersionNumber))
441    return sql::INIT_FAILURE;
442  if (meta_table_.GetCompatibleVersionNumber() > kCurrentVersionNumber) {
443    LOG(WARNING) << "Web database is too new.";
444    return sql::INIT_TOO_NEW;
445  }
446
447  // Initialize the tables.
448  if (!InitKeywordsTable() || !InitLoginsTable() || !InitWebAppIconsTable() ||
449      !InitWebAppsTable() || !InitAutofillTable() ||
450      !InitAutofillDatesTable() || !InitAutoFillProfilesTable() ||
451      !InitCreditCardsTable() || !InitTokenServiceTable()) {
452    LOG(WARNING) << "Unable to initialize the web database.";
453    return sql::INIT_FAILURE;
454  }
455
456  // If the file on disk is an older database version, bring it up to date.
457  MigrateOldVersionsAsNeeded();
458
459  return transaction.Commit() ? sql::INIT_OK : sql::INIT_FAILURE;
460}
461
462bool WebDatabase::SetWebAppImage(const GURL& url, const SkBitmap& image) {
463  // Don't bother with a cached statement since this will be a relatively
464  // infrequent operation.
465  sql::Statement s(db_.GetUniqueStatement(
466      "INSERT OR REPLACE INTO web_app_icons "
467      "(url, width, height, image) VALUES (?, ?, ?, ?)"));
468  if (!s)
469    return false;
470
471  std::vector<unsigned char> image_data;
472  gfx::PNGCodec::EncodeBGRASkBitmap(image, false, &image_data);
473
474  s.BindString(0, history::HistoryDatabase::GURLToDatabaseURL(url));
475  s.BindInt(1, image.width());
476  s.BindInt(2, image.height());
477  s.BindBlob(3, &image_data.front(), static_cast<int>(image_data.size()));
478  return s.Run();
479}
480
481bool WebDatabase::GetWebAppImages(const GURL& url,
482                                  std::vector<SkBitmap>* images) {
483  sql::Statement s(db_.GetUniqueStatement(
484      "SELECT image FROM web_app_icons WHERE url=?"));
485  if (!s) {
486    NOTREACHED() << "Statement prepare failed";
487    return false;
488  }
489  s.BindString(0, history::HistoryDatabase::GURLToDatabaseURL(url));
490  while (s.Step()) {
491    SkBitmap image;
492    int col_bytes = s.ColumnByteLength(0);
493    if (col_bytes > 0) {
494      if (gfx::PNGCodec::Decode(
495              reinterpret_cast<const unsigned char*>(s.ColumnBlob(0)),
496              col_bytes, &image)) {
497        images->push_back(image);
498      } else {
499        // Should only have valid image data in the db.
500        NOTREACHED();
501      }
502    }
503  }
504  return true;
505}
506
507bool WebDatabase::SetWebAppHasAllImages(const GURL& url,
508                                        bool has_all_images) {
509  sql::Statement s(db_.GetUniqueStatement(
510      "INSERT OR REPLACE INTO web_apps (url, has_all_images) VALUES (?, ?)"));
511  if (!s) {
512    NOTREACHED() << "Statement prepare failed";
513    return false;
514  }
515  s.BindString(0, history::HistoryDatabase::GURLToDatabaseURL(url));
516  s.BindInt(1, has_all_images ? 1 : 0);
517  return s.Run();
518}
519
520bool WebDatabase::GetWebAppHasAllImages(const GURL& url) {
521  sql::Statement s(db_.GetUniqueStatement(
522      "SELECT has_all_images FROM web_apps WHERE url=?"));
523  if (!s) {
524    NOTREACHED() << "Statement prepare failed";
525    return false;
526  }
527  s.BindString(0, history::HistoryDatabase::GURLToDatabaseURL(url));
528  return (s.Step() && s.ColumnInt(0) == 1);
529}
530
531bool WebDatabase::RemoveWebApp(const GURL& url) {
532  sql::Statement delete_s(db_.GetUniqueStatement(
533      "DELETE FROM web_app_icons WHERE url = ?"));
534  if (!delete_s) {
535    NOTREACHED() << "Statement prepare failed";
536    return false;
537  }
538  delete_s.BindString(0, history::HistoryDatabase::GURLToDatabaseURL(url));
539  if (!delete_s.Run())
540    return false;
541
542  sql::Statement delete_s2(db_.GetUniqueStatement(
543      "DELETE FROM web_apps WHERE url = ?"));
544  if (!delete_s2) {
545    NOTREACHED() << "Statement prepare failed";
546    return false;
547  }
548  delete_s2.BindString(0, history::HistoryDatabase::GURLToDatabaseURL(url));
549  return delete_s2.Run();
550}
551
552bool WebDatabase::RemoveAllTokens() {
553  sql::Statement s(db_.GetUniqueStatement(
554      "DELETE FROM token_service"));
555  if (!s) {
556    NOTREACHED() << "Statement prepare failed";
557    return false;
558  }
559
560  return s.Run();
561}
562
563bool WebDatabase::SetTokenForService(const std::string& service,
564                                     const std::string& token) {
565  // Don't bother with a cached statement since this will be a relatively
566  // infrequent operation.
567  sql::Statement s(db_.GetUniqueStatement(
568      "INSERT OR REPLACE INTO token_service "
569      "(service, encrypted_token) VALUES (?, ?)"));
570  if (!s) {
571    NOTREACHED() << "Statement prepare failed";
572    return false;
573  }
574
575  std::string encrypted_token;
576
577  bool encrypted = Encryptor::EncryptString(token, &encrypted_token);
578  if (!encrypted) {
579    return false;
580  }
581
582  s.BindString(0, service);
583  s.BindBlob(1, encrypted_token.data(),
584             static_cast<int>(encrypted_token.length()));
585  return s.Run();
586}
587
588bool WebDatabase::GetAllTokens(std::map<std::string, std::string>* tokens) {
589  sql::Statement s(db_.GetUniqueStatement(
590      "SELECT service, encrypted_token FROM token_service"));
591  if (!s) {
592    NOTREACHED() << "Statement prepare failed";
593    return false;
594  }
595
596  while (s.Step()) {
597    std::string encrypted_token;
598    std::string decrypted_token;
599    std::string service;
600    service = s.ColumnString(0);
601    bool entry_ok = !service.empty() &&
602                    s.ColumnBlobAsString(1, &encrypted_token);
603    if (entry_ok) {
604      Encryptor::DecryptString(encrypted_token, &decrypted_token);
605      (*tokens)[service] = decrypted_token;
606    } else {
607      NOTREACHED();
608      return false;
609    }
610  }
611  return true;
612}
613
614bool WebDatabase::InitKeywordsTable() {
615  if (!db_.DoesTableExist("keywords")) {
616    if (!db_.Execute("CREATE TABLE keywords ("
617                     "id INTEGER PRIMARY KEY,"
618                     "short_name VARCHAR NOT NULL,"
619                     "keyword VARCHAR NOT NULL,"
620                     "favicon_url VARCHAR NOT NULL,"
621                     "url VARCHAR NOT NULL,"
622                     "show_in_default_list INTEGER,"
623                     "safe_for_autoreplace INTEGER,"
624                     "originating_url VARCHAR,"
625                     "date_created INTEGER DEFAULT 0,"
626                     "usage_count INTEGER DEFAULT 0,"
627                     "input_encodings VARCHAR,"
628                     "suggest_url VARCHAR,"
629                     "prepopulate_id INTEGER DEFAULT 0,"
630                     "autogenerate_keyword INTEGER DEFAULT 0,"
631                     "logo_id INTEGER DEFAULT 0,"
632                     "created_by_policy INTEGER DEFAULT 0)")) {
633      NOTREACHED();
634      return false;
635    }
636  }
637  return true;
638}
639
640bool WebDatabase::InitLoginsTable() {
641  if (!db_.DoesTableExist("logins")) {
642    if (!db_.Execute("CREATE TABLE logins ("
643                     "origin_url VARCHAR NOT NULL, "
644                     "action_url VARCHAR, "
645                     "username_element VARCHAR, "
646                     "username_value VARCHAR, "
647                     "password_element VARCHAR, "
648                     "password_value BLOB, "
649                     "submit_element VARCHAR, "
650                     "signon_realm VARCHAR NOT NULL,"
651                     "ssl_valid INTEGER NOT NULL,"
652                     "preferred INTEGER NOT NULL,"
653                     "date_created INTEGER NOT NULL,"
654                     "blacklisted_by_user INTEGER NOT NULL,"
655                     "scheme INTEGER NOT NULL,"
656                     "UNIQUE "
657                     "(origin_url, username_element, "
658                     "username_value, password_element, "
659                     "submit_element, signon_realm))")) {
660      NOTREACHED();
661      return false;
662    }
663    if (!db_.Execute("CREATE INDEX logins_signon ON logins (signon_realm)")) {
664      NOTREACHED();
665      return false;
666    }
667  }
668
669#if defined(OS_WIN)
670  if (!db_.DoesTableExist("ie7_logins")) {
671    if (!db_.Execute("CREATE TABLE ie7_logins ("
672                     "url_hash VARCHAR NOT NULL, "
673                     "password_value BLOB, "
674                     "date_created INTEGER NOT NULL,"
675                     "UNIQUE "
676                     "(url_hash))")) {
677      NOTREACHED();
678      return false;
679    }
680    if (!db_.Execute("CREATE INDEX ie7_logins_hash ON "
681                     "ie7_logins (url_hash)")) {
682      NOTREACHED();
683      return false;
684    }
685  }
686#endif
687
688  return true;
689}
690
691bool WebDatabase::InitAutofillTable() {
692  if (!db_.DoesTableExist("autofill")) {
693    if (!db_.Execute("CREATE TABLE autofill ("
694                     "name VARCHAR, "
695                     "value VARCHAR, "
696                     "value_lower VARCHAR, "
697                     "pair_id INTEGER PRIMARY KEY, "
698                     "count INTEGER DEFAULT 1)")) {
699      NOTREACHED();
700      return false;
701    }
702    if (!db_.Execute("CREATE INDEX autofill_name ON autofill (name)")) {
703       NOTREACHED();
704       return false;
705    }
706    if (!db_.Execute("CREATE INDEX autofill_name_value_lower ON "
707                     "autofill (name, value_lower)")) {
708       NOTREACHED();
709       return false;
710    }
711  }
712  return true;
713}
714
715bool WebDatabase::InitAutofillDatesTable() {
716  if (!db_.DoesTableExist("autofill_dates")) {
717    if (!db_.Execute("CREATE TABLE autofill_dates ( "
718                     "pair_id INTEGER DEFAULT 0, "
719                     "date_created INTEGER DEFAULT 0)")) {
720      NOTREACHED();
721      return false;
722    }
723    if (!db_.Execute("CREATE INDEX autofill_dates_pair_id ON "
724                     "autofill_dates (pair_id)")) {
725      NOTREACHED();
726      return false;
727    }
728  }
729  return true;
730}
731
732bool WebDatabase::InitAutoFillProfilesTable() {
733  if (!db_.DoesTableExist("autofill_profiles")) {
734    if (!db_.Execute("CREATE TABLE autofill_profiles ( "
735                     "label VARCHAR, "
736                     "unique_id INTEGER PRIMARY KEY, "
737                     "first_name VARCHAR, "
738                     "middle_name VARCHAR, "
739                     "last_name VARCHAR, "
740                     "email VARCHAR, "
741                     "company_name VARCHAR, "
742                     "address_line_1 VARCHAR, "
743                     "address_line_2 VARCHAR, "
744                     "city VARCHAR, "
745                     "state VARCHAR, "
746                     "zipcode VARCHAR, "
747                     "country VARCHAR, "
748                     "phone VARCHAR, "
749                     "fax VARCHAR)")) {
750      NOTREACHED();
751      return false;
752    }
753    if (!db_.Execute("CREATE INDEX autofill_profiles_label_index "
754                     "ON autofill_profiles (label)")) {
755      NOTREACHED();
756      return false;
757    }
758  }
759  return true;
760}
761
762bool WebDatabase::InitCreditCardsTable() {
763  if (!db_.DoesTableExist("credit_cards")) {
764    if (!db_.Execute("CREATE TABLE credit_cards ( "
765                     "label VARCHAR, "
766                     "unique_id INTEGER PRIMARY KEY, "
767                     "name_on_card VARCHAR, "
768                     "type VARCHAR, "
769                     "card_number VARCHAR, "
770                     "expiration_month INTEGER, "
771                     "expiration_year INTEGER, "
772                     "verification_code VARCHAR, "
773                     "billing_address VARCHAR, "
774                     "shipping_address VARCHAR, "
775                     "card_number_encrypted BLOB, "
776                     "verification_code_encrypted BLOB)")) {
777      NOTREACHED();
778      return false;
779    }
780    if (!db_.Execute("CREATE INDEX credit_cards_label_index "
781                     "ON credit_cards (label)")) {
782      NOTREACHED();
783      return false;
784    }
785  }
786  return true;
787}
788
789bool WebDatabase::InitWebAppIconsTable() {
790  if (!db_.DoesTableExist("web_app_icons")) {
791    if (!db_.Execute("CREATE TABLE web_app_icons ("
792                     "url LONGVARCHAR,"
793                     "width int,"
794                     "height int,"
795                     "image BLOB, UNIQUE (url, width, height))")) {
796      NOTREACHED();
797      return false;
798    }
799  }
800  return true;
801}
802
803bool WebDatabase::InitWebAppsTable() {
804  if (!db_.DoesTableExist("web_apps")) {
805    if (!db_.Execute("CREATE TABLE web_apps ("
806                     "url LONGVARCHAR UNIQUE,"
807                     "has_all_images INTEGER NOT NULL)")) {
808      NOTREACHED();
809      return false;
810    }
811    if (!db_.Execute("CREATE INDEX web_apps_url_index ON web_apps (url)")) {
812      NOTREACHED();
813      return false;
814    }
815  }
816  return true;
817}
818
819bool WebDatabase::InitTokenServiceTable() {
820  if (!db_.DoesTableExist("token_service")) {
821    if (!db_.Execute("CREATE TABLE token_service ("
822                     "service VARCHAR PRIMARY KEY NOT NULL,"
823                     "encrypted_token BLOB)")) {
824      NOTREACHED();
825      return false;
826    }
827  }
828  return true;
829}
830
831bool WebDatabase::AddKeyword(const TemplateURL& url) {
832  DCHECK(url.id());
833  // Be sure to change kUrlIdPosition if you add columns
834  sql::Statement s(db_.GetCachedStatement(SQL_FROM_HERE,
835      "INSERT INTO keywords "
836      "(short_name, keyword, favicon_url, url, safe_for_autoreplace, "
837      "originating_url, date_created, usage_count, input_encodings, "
838      "show_in_default_list, suggest_url, prepopulate_id, "
839      "autogenerate_keyword, logo_id, created_by_policy, id) VALUES "
840      "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"));
841  if (!s) {
842    NOTREACHED() << "Statement prepare failed";
843    return false;
844  }
845  BindURLToStatement(url, &s);
846  s.BindInt64(kUrlIdPosition, url.id());
847  if (!s.Run()) {
848    NOTREACHED();
849    return false;
850  }
851  return true;
852}
853
854bool WebDatabase::RemoveKeyword(TemplateURLID id) {
855  DCHECK(id);
856  sql::Statement s(db_.GetUniqueStatement("DELETE FROM keywords WHERE id = ?"));
857  if (!s) {
858    NOTREACHED() << "Statement prepare failed";
859    return false;
860  }
861  s.BindInt64(0, id);
862  return s.Run();
863}
864
865bool WebDatabase::GetKeywords(std::vector<TemplateURL*>* urls) {
866  sql::Statement s(db_.GetUniqueStatement(
867      "SELECT id, short_name, keyword, favicon_url, url, "
868      "safe_for_autoreplace, originating_url, date_created, "
869      "usage_count, input_encodings, show_in_default_list, "
870      "suggest_url, prepopulate_id, autogenerate_keyword, logo_id, "
871      "created_by_policy "
872      "FROM keywords ORDER BY id ASC"));
873  if (!s) {
874    NOTREACHED() << "Statement prepare failed";
875    return false;
876  }
877  while (s.Step()) {
878    TemplateURL* template_url = new TemplateURL();
879    template_url->set_id(s.ColumnInt64(0));
880
881    std::string tmp;
882    tmp = s.ColumnString(1);
883    DCHECK(!tmp.empty());
884    template_url->set_short_name(UTF8ToWide(tmp));
885
886    tmp = s.ColumnString(2);
887    template_url->set_keyword(UTF8ToWide(tmp));
888
889    tmp = s.ColumnString(3);
890    if (!tmp.empty())
891      template_url->SetFavIconURL(GURL(tmp));
892
893    tmp = s.ColumnString(4);
894    template_url->SetURL(tmp, 0, 0);
895
896    template_url->set_safe_for_autoreplace(s.ColumnInt(5) == 1);
897
898    tmp = s.ColumnString(6);
899    if (!tmp.empty())
900      template_url->set_originating_url(GURL(tmp));
901
902    template_url->set_date_created(Time::FromTimeT(s.ColumnInt64(7)));
903
904    template_url->set_usage_count(s.ColumnInt(8));
905
906    std::vector<std::string> encodings;
907    SplitString(s.ColumnString(9), ';', &encodings);
908    template_url->set_input_encodings(encodings);
909
910    template_url->set_show_in_default_list(s.ColumnInt(10) == 1);
911
912    tmp = s.ColumnString(11);
913    template_url->SetSuggestionsURL(tmp, 0, 0);
914
915    template_url->set_prepopulate_id(s.ColumnInt(12));
916
917    template_url->set_autogenerate_keyword(s.ColumnInt(13) == 1);
918
919    template_url->set_logo_id(s.ColumnInt(14));
920
921    template_url->set_created_by_policy(s.ColumnBool(15));
922
923    urls->push_back(template_url);
924  }
925  return s.Succeeded();
926}
927
928bool WebDatabase::UpdateKeyword(const TemplateURL& url) {
929  DCHECK(url.id());
930  // Be sure to change kUrlIdPosition if you add columns
931  sql::Statement s(db_.GetUniqueStatement(
932      "UPDATE keywords "
933      "SET short_name=?, keyword=?, favicon_url=?, url=?, "
934      "safe_for_autoreplace=?, originating_url=?, date_created=?, "
935      "usage_count=?, input_encodings=?, show_in_default_list=?, "
936      "suggest_url=?, prepopulate_id=?, autogenerate_keyword=?, "
937      "logo_id=?, created_by_policy=? WHERE id=?"));
938  if (!s) {
939    NOTREACHED() << "Statement prepare failed";
940    return false;
941  }
942  BindURLToStatement(url, &s);
943  s.BindInt64(kUrlIdPosition, url.id());
944  return s.Run();
945}
946
947bool WebDatabase::SetDefaultSearchProviderID(int64 id) {
948  return meta_table_.SetValue(kDefaultSearchProviderKey, id);
949}
950
951int64 WebDatabase::GetDefaulSearchProviderID() {
952  int64 value = 0;
953  meta_table_.GetValue(kDefaultSearchProviderKey, &value);
954  return value;
955}
956
957bool WebDatabase::SetBuitinKeywordVersion(int version) {
958  return meta_table_.SetValue(kBuiltinKeywordVersion, version);
959}
960
961int WebDatabase::GetBuitinKeywordVersion() {
962  int version = 0;
963  meta_table_.GetValue(kBuiltinKeywordVersion, &version);
964  return version;
965}
966
967bool WebDatabase::AddLogin(const PasswordForm& form) {
968  sql::Statement s(db_.GetUniqueStatement(
969      "INSERT OR REPLACE INTO logins "
970      "(origin_url, action_url, username_element, username_value, "
971      " password_element, password_value, submit_element, "
972      " signon_realm, ssl_valid, preferred, date_created, "
973      " blacklisted_by_user, scheme) "
974      "VALUES "
975      "(?,?,?,?,?,?,?,?,?,?,?,?,?)"));
976  if (!s) {
977    NOTREACHED() << "Statement prepare failed";
978    return false;
979  }
980
981  std::string encrypted_password;
982  s.BindString(0, form.origin.spec());
983  s.BindString(1, form.action.spec());
984  s.BindString16(2, form.username_element);
985  s.BindString16(3, form.username_value);
986  s.BindString16(4, form.password_element);
987  Encryptor::EncryptString16(form.password_value, &encrypted_password);
988  s.BindBlob(5, encrypted_password.data(),
989             static_cast<int>(encrypted_password.length()));
990  s.BindString16(6, form.submit_element);
991  s.BindString(7, form.signon_realm);
992  s.BindInt(8, form.ssl_valid);
993  s.BindInt(9, form.preferred);
994  s.BindInt64(10, form.date_created.ToTimeT());
995  s.BindInt(11, form.blacklisted_by_user);
996  s.BindInt(12, form.scheme);
997  if (!s.Run()) {
998    NOTREACHED();
999    return false;
1000  }
1001  return true;
1002}
1003
1004bool WebDatabase::UpdateLogin(const PasswordForm& form) {
1005  sql::Statement s(db_.GetUniqueStatement(
1006      "UPDATE logins SET "
1007      "action_url = ?, "
1008      "password_value = ?, "
1009      "ssl_valid = ?, "
1010      "preferred = ? "
1011      "WHERE origin_url = ? AND "
1012      "username_element = ? AND "
1013      "username_value = ? AND "
1014      "password_element = ? AND "
1015      "signon_realm = ?"));
1016  if (!s) {
1017    NOTREACHED() << "Statement prepare failed";
1018    return false;
1019  }
1020
1021  s.BindString(0, form.action.spec());
1022  std::string encrypted_password;
1023  Encryptor::EncryptString16(form.password_value, &encrypted_password);
1024  s.BindBlob(1, encrypted_password.data(),
1025             static_cast<int>(encrypted_password.length()));
1026  s.BindInt(2, form.ssl_valid);
1027  s.BindInt(3, form.preferred);
1028  s.BindString(4, form.origin.spec());
1029  s.BindString16(5, form.username_element);
1030  s.BindString16(6, form.username_value);
1031  s.BindString16(7, form.password_element);
1032  s.BindString(8, form.signon_realm);
1033
1034  if (!s.Run()) {
1035    NOTREACHED();
1036    return false;
1037  }
1038  return true;
1039}
1040
1041bool WebDatabase::RemoveLogin(const PasswordForm& form) {
1042  // Remove a login by UNIQUE-constrained fields.
1043  sql::Statement s(db_.GetUniqueStatement(
1044      "DELETE FROM logins WHERE "
1045      "origin_url = ? AND "
1046      "username_element = ? AND "
1047      "username_value = ? AND "
1048      "password_element = ? AND "
1049      "submit_element = ? AND "
1050      "signon_realm = ?"));
1051  if (!s) {
1052    NOTREACHED() << "Statement prepare failed";
1053    return false;
1054  }
1055  s.BindString(0, form.origin.spec());
1056  s.BindString16(1, form.username_element);
1057  s.BindString16(2, form.username_value);
1058  s.BindString16(3, form.password_element);
1059  s.BindString16(4, form.submit_element);
1060  s.BindString(5, form.signon_realm);
1061
1062  if (!s.Run()) {
1063    NOTREACHED();
1064    return false;
1065  }
1066  return true;
1067}
1068
1069bool WebDatabase::RemoveLoginsCreatedBetween(base::Time delete_begin,
1070                                             base::Time delete_end) {
1071  sql::Statement s1(db_.GetUniqueStatement(
1072      "DELETE FROM logins WHERE "
1073      "date_created >= ? AND date_created < ?"));
1074  if (!s1) {
1075    NOTREACHED() << "Statement 1 prepare failed";
1076    return false;
1077  }
1078  s1.BindInt64(0, delete_begin.ToTimeT());
1079  s1.BindInt64(1,
1080               delete_end.is_null() ?
1081                   std::numeric_limits<int64>::max() :
1082                   delete_end.ToTimeT());
1083  bool success = s1.Run();
1084
1085#if defined(OS_WIN)
1086  sql::Statement s2(db_.GetUniqueStatement(
1087      "DELETE FROM ie7_logins WHERE date_created >= ? AND date_created < ?"));
1088  if (!s2) {
1089    NOTREACHED() << "Statement 2 prepare failed";
1090    return false;
1091  }
1092  s2.BindInt64(0, delete_begin.ToTimeT());
1093  s2.BindInt64(1,
1094               delete_end.is_null() ?
1095                   std::numeric_limits<int64>::max() :
1096                   delete_end.ToTimeT());
1097  success = success && s2.Run();
1098#endif
1099
1100  return success;
1101}
1102
1103bool WebDatabase::GetLogins(const PasswordForm& form,
1104                            std::vector<PasswordForm*>* forms) {
1105  DCHECK(forms);
1106  sql::Statement s(db_.GetUniqueStatement(
1107                "SELECT origin_url, action_url, "
1108                "username_element, username_value, "
1109                "password_element, password_value, "
1110                "submit_element, signon_realm, "
1111                "ssl_valid, preferred, "
1112                "date_created, blacklisted_by_user, scheme FROM logins "
1113                "WHERE signon_realm == ?"));
1114  if (!s) {
1115    NOTREACHED() << "Statement prepare failed";
1116    return false;
1117  }
1118
1119  s.BindString(0, form.signon_realm);
1120
1121  while (s.Step()) {
1122    PasswordForm* new_form = new PasswordForm();
1123    InitPasswordFormFromStatement(new_form, &s);
1124
1125    forms->push_back(new_form);
1126  }
1127  return s.Succeeded();
1128}
1129
1130bool WebDatabase::GetAllLogins(std::vector<PasswordForm*>* forms,
1131                               bool include_blacklisted) {
1132  DCHECK(forms);
1133  std::string stmt = "SELECT origin_url, action_url, "
1134                     "username_element, username_value, "
1135                     "password_element, password_value, "
1136                     "submit_element, signon_realm, ssl_valid, preferred, "
1137                     "date_created, blacklisted_by_user, scheme FROM logins ";
1138  if (!include_blacklisted)
1139    stmt.append("WHERE blacklisted_by_user == 0 ");
1140  stmt.append("ORDER BY origin_url");
1141
1142  sql::Statement s(db_.GetUniqueStatement(stmt.c_str()));
1143  if (!s) {
1144    NOTREACHED() << "Statement prepare failed";
1145    return false;
1146  }
1147
1148  while (s.Step()) {
1149    PasswordForm* new_form = new PasswordForm();
1150    InitPasswordFormFromStatement(new_form, &s);
1151
1152    forms->push_back(new_form);
1153  }
1154  return s.Succeeded();
1155}
1156
1157bool WebDatabase::AddFormFieldValues(const std::vector<FormField>& elements,
1158                                     std::vector<AutofillChange>* changes) {
1159  return AddFormFieldValuesTime(elements, changes, Time::Now());
1160}
1161
1162bool WebDatabase::AddFormFieldValuesTime(const std::vector<FormField>& elements,
1163                                         std::vector<AutofillChange>* changes,
1164                                         base::Time time) {
1165  // Only add one new entry for each unique element name.  Use |seen_names| to
1166  // track this.  Add up to |kMaximumUniqueNames| unique entries per form.
1167  const size_t kMaximumUniqueNames = 256;
1168  std::set<string16> seen_names;
1169  bool result = true;
1170  for (std::vector<FormField>::const_iterator
1171       itr = elements.begin();
1172       itr != elements.end();
1173       itr++) {
1174    if (seen_names.size() >= kMaximumUniqueNames)
1175      break;
1176    if (seen_names.find(itr->name()) != seen_names.end())
1177      continue;
1178    result = result && AddFormFieldValueTime(*itr, changes, time);
1179    seen_names.insert(itr->name());
1180  }
1181  return result;
1182}
1183
1184bool WebDatabase::ClearAutofillEmptyValueElements() {
1185  sql::Statement s(db_.GetUniqueStatement(
1186      "SELECT pair_id FROM autofill WHERE TRIM(value)= \"\""));
1187  if (!s) {
1188    NOTREACHED() << "Statement prepare failed";
1189    return false;
1190  }
1191
1192  std::set<int64> ids;
1193  while (s.Step())
1194    ids.insert(s.ColumnInt64(0));
1195
1196  bool success = true;
1197  for (std::set<int64>::const_iterator iter = ids.begin(); iter != ids.end();
1198       ++iter) {
1199    if (!RemoveFormElementForID(*iter))
1200      success = false;
1201  }
1202
1203  return success;
1204}
1205
1206bool WebDatabase::GetIDAndCountOfFormElement(
1207    const FormField& element,
1208    int64* pair_id,
1209    int* count) {
1210  sql::Statement s(db_.GetUniqueStatement(
1211      "SELECT pair_id, count FROM autofill "
1212      "WHERE name = ? AND value = ?"));
1213  if (!s) {
1214    NOTREACHED() << "Statement prepare failed";
1215    return false;
1216  }
1217
1218  s.BindString16(0, element.name());
1219  s.BindString16(1, element.value());
1220
1221  *count = 0;
1222
1223  if (s.Step()) {
1224    *pair_id = s.ColumnInt64(0);
1225    *count = s.ColumnInt(1);
1226  }
1227
1228  return true;
1229}
1230
1231bool WebDatabase::GetCountOfFormElement(int64 pair_id, int* count) {
1232  sql::Statement s(db_.GetUniqueStatement(
1233      "SELECT count FROM autofill WHERE pair_id = ?"));
1234  if (!s) {
1235    NOTREACHED() << "Statement prepare failed";
1236    return false;
1237  }
1238
1239  s.BindInt64(0, pair_id);
1240
1241  if (s.Step()) {
1242    *count = s.ColumnInt(0);
1243    return true;
1244  }
1245  return false;
1246}
1247
1248bool WebDatabase::GetAllAutofillEntries(std::vector<AutofillEntry>* entries) {
1249  DCHECK(entries);
1250  sql::Statement s(db_.GetUniqueStatement(
1251      "SELECT name, value, date_created FROM autofill a JOIN "
1252      "autofill_dates ad ON a.pair_id=ad.pair_id"));
1253
1254  if (!s) {
1255    NOTREACHED() << "Statement prepare failed";
1256    return false;
1257  }
1258
1259  bool first_entry = true;
1260  AutofillKey* current_key_ptr = NULL;
1261  std::vector<base::Time>* timestamps_ptr = NULL;
1262  string16 name, value;
1263  base::Time time;
1264  while (s.Step()) {
1265    name = s.ColumnString16(0);
1266    value = s.ColumnString16(1);
1267    time = Time::FromTimeT(s.ColumnInt64(2));
1268
1269    if (first_entry) {
1270      current_key_ptr = new AutofillKey(name, value);
1271
1272      timestamps_ptr = new std::vector<base::Time>;
1273      timestamps_ptr->push_back(time);
1274
1275      first_entry = false;
1276    } else {
1277      // we've encountered the next entry
1278      if (current_key_ptr->name().compare(name) != 0 ||
1279          current_key_ptr->value().compare(value) != 0) {
1280        AutofillEntry entry(*current_key_ptr, *timestamps_ptr);
1281        entries->push_back(entry);
1282
1283        delete current_key_ptr;
1284        delete timestamps_ptr;
1285
1286        current_key_ptr = new AutofillKey(name, value);
1287        timestamps_ptr = new std::vector<base::Time>;
1288      }
1289      timestamps_ptr->push_back(time);
1290    }
1291  }
1292  // If there is at least one result returned, first_entry will be false.
1293  // For this case we need to do a final cleanup step.
1294  if (!first_entry) {
1295    AutofillEntry entry(*current_key_ptr, *timestamps_ptr);
1296    entries->push_back(entry);
1297    delete current_key_ptr;
1298    delete timestamps_ptr;
1299  }
1300
1301  return s.Succeeded();
1302}
1303
1304bool WebDatabase::GetAutofillTimestamps(const string16& name,
1305                                        const string16& value,
1306                                        std::vector<base::Time>* timestamps) {
1307  DCHECK(timestamps);
1308  sql::Statement s(db_.GetUniqueStatement(
1309      "SELECT date_created FROM autofill a JOIN "
1310      "autofill_dates ad ON a.pair_id=ad.pair_id "
1311      "WHERE a.name = ? AND a.value = ?"));
1312
1313  if (!s) {
1314    NOTREACHED() << "Statement prepare failed";
1315    return false;
1316  }
1317
1318  s.BindString16(0, name);
1319  s.BindString16(1, value);
1320  while (s.Step()) {
1321    timestamps->push_back(Time::FromTimeT(s.ColumnInt64(0)));
1322  }
1323
1324  return s.Succeeded();
1325}
1326
1327bool WebDatabase::UpdateAutofillEntries(
1328    const std::vector<AutofillEntry>& entries) {
1329  if (!entries.size())
1330    return true;
1331
1332  // Remove all existing entries.
1333  for (size_t i = 0; i < entries.size(); i++) {
1334    std::string sql = "SELECT pair_id FROM autofill "
1335                      "WHERE name = ? AND value = ?";
1336    sql::Statement s(db_.GetUniqueStatement(sql.c_str()));
1337    if (!s.is_valid()) {
1338      NOTREACHED() << "Statement prepare failed";
1339      return false;
1340    }
1341
1342    s.BindString16(0, entries[i].key().name());
1343    s.BindString16(1, entries[i].key().value());
1344    if (s.Step()) {
1345      if (!RemoveFormElementForID(s.ColumnInt64(0)))
1346        return false;
1347    }
1348  }
1349
1350  // Insert all the supplied autofill entries.
1351  for (size_t i = 0; i < entries.size(); i++) {
1352    if (!InsertAutofillEntry(entries[i]))
1353      return false;
1354  }
1355
1356  return true;
1357}
1358
1359bool WebDatabase::InsertAutofillEntry(const AutofillEntry& entry) {
1360  std::string sql = "INSERT INTO autofill (name, value, value_lower, count) "
1361                    "VALUES (?, ?, ?, ?)";
1362  sql::Statement s(db_.GetUniqueStatement(sql.c_str()));
1363  if (!s.is_valid()) {
1364    NOTREACHED() << "Statement prepare failed";
1365    return false;
1366  }
1367
1368  s.BindString16(0, entry.key().name());
1369  s.BindString16(1, entry.key().value());
1370  s.BindString16(2, l10n_util::ToLower(entry.key().value()));
1371  s.BindInt(3, entry.timestamps().size());
1372
1373  if (!s.Run()) {
1374    NOTREACHED();
1375    return false;
1376  }
1377
1378  int64 pair_id = db_.GetLastInsertRowId();
1379  for (size_t i = 0; i < entry.timestamps().size(); i++) {
1380    if (!InsertPairIDAndDate(pair_id, entry.timestamps()[i]))
1381      return false;
1382  }
1383
1384  return true;
1385}
1386
1387bool WebDatabase::InsertFormElement(const FormField& element,
1388                                    int64* pair_id) {
1389  sql::Statement s(db_.GetUniqueStatement(
1390      "INSERT INTO autofill (name, value, value_lower) VALUES (?,?,?)"));
1391  if (!s) {
1392    NOTREACHED() << "Statement prepare failed";
1393    return false;
1394  }
1395
1396  s.BindString16(0, element.name());
1397  s.BindString16(1, element.value());
1398  s.BindString16(2, l10n_util::ToLower(element.value()));
1399
1400  if (!s.Run()) {
1401    NOTREACHED();
1402    return false;
1403  }
1404
1405  *pair_id = db_.GetLastInsertRowId();
1406  return true;
1407}
1408
1409bool WebDatabase::InsertPairIDAndDate(int64 pair_id,
1410                                      base::Time date_created) {
1411  sql::Statement s(db_.GetUniqueStatement(
1412      "INSERT INTO autofill_dates "
1413      "(pair_id, date_created) VALUES (?, ?)"));
1414  if (!s) {
1415    NOTREACHED() << "Statement prepare failed";
1416    return false;
1417  }
1418
1419  s.BindInt64(0, pair_id);
1420  s.BindInt64(1, date_created.ToTimeT());
1421
1422  if (!s.Run()) {
1423    NOTREACHED();
1424    return false;
1425  }
1426
1427  return true;
1428}
1429
1430bool WebDatabase::SetCountOfFormElement(int64 pair_id, int count) {
1431  sql::Statement s(db_.GetUniqueStatement(
1432      "UPDATE autofill SET count = ? WHERE pair_id = ?"));
1433  if (!s) {
1434    NOTREACHED() << "Statement prepare failed";
1435    return false;
1436  }
1437
1438  s.BindInt(0, count);
1439  s.BindInt64(1, pair_id);
1440  if (!s.Run()) {
1441    NOTREACHED();
1442    return false;
1443  }
1444
1445  return true;
1446}
1447
1448bool WebDatabase::AddFormFieldValue(const FormField& element,
1449                                    std::vector<AutofillChange>* changes) {
1450  return AddFormFieldValueTime(element, changes, base::Time::Now());
1451}
1452
1453bool WebDatabase::AddFormFieldValueTime(const FormField& element,
1454                                        std::vector<AutofillChange>* changes,
1455                                        base::Time time) {
1456  int count = 0;
1457  int64 pair_id;
1458
1459  if (!GetIDAndCountOfFormElement(element, &pair_id, &count))
1460    return false;
1461
1462  if (count == 0 && !InsertFormElement(element, &pair_id))
1463    return false;
1464
1465  if (!SetCountOfFormElement(pair_id, count + 1))
1466    return false;
1467
1468  if (!InsertPairIDAndDate(pair_id, time))
1469    return false;
1470
1471  AutofillChange::Type change_type =
1472      count == 0 ? AutofillChange::ADD : AutofillChange::UPDATE;
1473  changes->push_back(
1474      AutofillChange(change_type,
1475                     AutofillKey(element.name(), element.value())));
1476  return true;
1477}
1478
1479bool WebDatabase::GetFormValuesForElementName(const string16& name,
1480                                              const string16& prefix,
1481                                              std::vector<string16>* values,
1482                                              int limit) {
1483  DCHECK(values);
1484  sql::Statement s;
1485
1486  if (prefix.empty()) {
1487    s.Assign(db_.GetUniqueStatement(
1488        "SELECT value FROM autofill "
1489        "WHERE name = ? "
1490        "ORDER BY count DESC "
1491        "LIMIT ?"));
1492    if (!s) {
1493      NOTREACHED() << "Statement prepare failed";
1494      return false;
1495    }
1496
1497    s.BindString16(0, name);
1498    s.BindInt(1, limit);
1499  } else {
1500    string16 prefix_lower = l10n_util::ToLower(prefix);
1501    string16 next_prefix = prefix_lower;
1502    next_prefix[next_prefix.length() - 1]++;
1503
1504    s.Assign(db_.GetUniqueStatement(
1505        "SELECT value FROM autofill "
1506        "WHERE name = ? AND "
1507        "value_lower >= ? AND "
1508        "value_lower < ? "
1509        "ORDER BY count DESC "
1510        "LIMIT ?"));
1511    if (!s) {
1512      NOTREACHED() << "Statement prepare failed";
1513      return false;
1514    }
1515
1516    s.BindString16(0, name);
1517    s.BindString16(1, prefix_lower);
1518    s.BindString16(2, next_prefix);
1519    s.BindInt(3, limit);
1520  }
1521
1522  values->clear();
1523  while (s.Step())
1524    values->push_back(s.ColumnString16(0));
1525  return s.Succeeded();
1526}
1527
1528bool WebDatabase::RemoveFormElementsAddedBetween(
1529    base::Time delete_begin,
1530    base::Time delete_end,
1531    std::vector<AutofillChange>* changes) {
1532  DCHECK(changes);
1533  // Query for the pair_id, name, and value of all form elements that
1534  // were used between the given times.
1535  sql::Statement s(db_.GetUniqueStatement(
1536      "SELECT DISTINCT a.pair_id, a.name, a.value "
1537      "FROM autofill_dates ad JOIN autofill a ON ad.pair_id = a.pair_id "
1538      "WHERE ad.date_created >= ? AND ad.date_created < ?"));
1539  if (!s) {
1540    NOTREACHED() << "Statement 1 prepare failed";
1541    return false;
1542  }
1543  s.BindInt64(0, delete_begin.ToTimeT());
1544  s.BindInt64(1,
1545              delete_end.is_null() ?
1546                  std::numeric_limits<int64>::max() :
1547                  delete_end.ToTimeT());
1548
1549  AutofillElementList elements;
1550  while (s.Step()) {
1551    elements.push_back(MakeTuple(s.ColumnInt64(0),
1552                                 s.ColumnString16(1),
1553                                 s.ColumnString16(2)));
1554  }
1555
1556  if (!s.Succeeded()) {
1557    NOTREACHED();
1558    return false;
1559  }
1560
1561  for (AutofillElementList::iterator itr = elements.begin();
1562       itr != elements.end(); itr++) {
1563    int how_many = 0;
1564    if (!RemoveFormElementForTimeRange(itr->a, delete_begin, delete_end,
1565                                       &how_many)) {
1566      return false;
1567    }
1568    bool was_removed = false;
1569    if (!AddToCountOfFormElement(itr->a, -how_many, &was_removed))
1570      return false;
1571    AutofillChange::Type change_type =
1572        was_removed ? AutofillChange::REMOVE : AutofillChange::UPDATE;
1573    changes->push_back(AutofillChange(change_type,
1574                                      AutofillKey(itr->b, itr->c)));
1575  }
1576
1577  return true;
1578}
1579
1580bool WebDatabase::RemoveFormElementForTimeRange(int64 pair_id,
1581                                                const Time delete_begin,
1582                                                const Time delete_end,
1583                                                int* how_many) {
1584  sql::Statement s(db_.GetUniqueStatement(
1585      "DELETE FROM autofill_dates WHERE pair_id = ? AND "
1586      "date_created >= ? AND date_created < ?"));
1587  if (!s) {
1588    NOTREACHED() << "Statement 1 prepare failed";
1589    return false;
1590  }
1591  s.BindInt64(0, pair_id);
1592  s.BindInt64(1, delete_begin.is_null() ? 0 : delete_begin.ToTimeT());
1593  s.BindInt64(2, delete_end.is_null() ? std::numeric_limits<int64>::max() :
1594                                        delete_end.ToTimeT());
1595
1596  bool result = s.Run();
1597  if (how_many)
1598    *how_many = db_.GetLastChangeCount();
1599
1600  return result;
1601}
1602
1603bool WebDatabase::RemoveFormElement(const string16& name,
1604                                    const string16& value) {
1605  // Find the id for that pair.
1606  sql::Statement s(db_.GetUniqueStatement(
1607      "SELECT pair_id FROM autofill WHERE  name = ? AND value= ?"));
1608  if (!s) {
1609    NOTREACHED() << "Statement 1 prepare failed";
1610    return false;
1611  }
1612  s.BindString16(0, name);
1613  s.BindString16(1, value);
1614
1615  if (s.Step())
1616    return RemoveFormElementForID(s.ColumnInt64(0));
1617  return false;
1618}
1619
1620bool WebDatabase::AddAutoFillProfile(const AutoFillProfile& profile) {
1621  sql::Statement s(db_.GetUniqueStatement(
1622      "INSERT INTO autofill_profiles"
1623      "(label, unique_id, first_name, middle_name, last_name, email,"
1624      " company_name, address_line_1, address_line_2, city, state, zipcode,"
1625      " country, phone, fax)"
1626      "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"));
1627  if (!s) {
1628    NOTREACHED() << "Statement prepare failed";
1629    return false;
1630  }
1631
1632  BindAutoFillProfileToStatement(profile, &s);
1633
1634  if (!s.Run()) {
1635    NOTREACHED();
1636    return false;
1637  }
1638
1639  return s.Succeeded();
1640}
1641
1642bool WebDatabase::GetAutoFillProfileForLabel(const string16& label,
1643                                             AutoFillProfile** profile) {
1644  DCHECK(profile);
1645  sql::Statement s(db_.GetUniqueStatement(
1646      "SELECT * FROM autofill_profiles "
1647      "WHERE label = ?"));
1648  if (!s) {
1649    NOTREACHED() << "Statement prepare failed";
1650    return false;
1651  }
1652
1653  s.BindString16(0, label);
1654  if (!s.Step())
1655    return false;
1656
1657  *profile = AutoFillProfileFromStatement(s);
1658
1659  return s.Succeeded();
1660}
1661
1662bool WebDatabase::GetAutoFillProfiles(
1663    std::vector<AutoFillProfile*>* profiles) {
1664  DCHECK(profiles);
1665  profiles->clear();
1666
1667  sql::Statement s(db_.GetUniqueStatement("SELECT * FROM autofill_profiles"));
1668  if (!s) {
1669    NOTREACHED() << "Statement prepare failed";
1670    return false;
1671  }
1672
1673  while (s.Step())
1674    profiles->push_back(AutoFillProfileFromStatement(s));
1675
1676  return s.Succeeded();
1677}
1678
1679bool WebDatabase::UpdateAutoFillProfile(const AutoFillProfile& profile) {
1680  DCHECK(profile.unique_id());
1681  sql::Statement s(db_.GetUniqueStatement(
1682      "UPDATE autofill_profiles "
1683      "SET label=?, unique_id=?, first_name=?, middle_name=?, last_name=?, "
1684      "    email=?, company_name=?, address_line_1=?, address_line_2=?, "
1685      "    city=?, state=?, zipcode=?, country=?, phone=?, fax=? "
1686      "WHERE unique_id=?"));
1687  if (!s) {
1688    NOTREACHED() << "Statement prepare failed";
1689    return false;
1690  }
1691
1692  BindAutoFillProfileToStatement(profile, &s);
1693  s.BindInt(15, profile.unique_id());
1694  bool result = s.Run();
1695  DCHECK_GT(db_.GetLastChangeCount(), 0);
1696  return result;
1697}
1698
1699bool WebDatabase::RemoveAutoFillProfile(int profile_id) {
1700  DCHECK_NE(0, profile_id);
1701  sql::Statement s(db_.GetUniqueStatement(
1702      "DELETE FROM autofill_profiles WHERE unique_id = ?"));
1703  if (!s) {
1704    NOTREACHED() << "Statement prepare failed";
1705    return false;
1706  }
1707
1708  s.BindInt(0, profile_id);
1709  return s.Run();
1710}
1711
1712bool WebDatabase::GetAutoFillProfileForID(int profile_id,
1713    AutoFillProfile** profile) {
1714  sql::Statement s(db_.GetUniqueStatement(
1715      "SELECT * FROM autofill_profiles "
1716      "WHERE unique_id = ?"));
1717  if (!s) {
1718    NOTREACHED() << "Statement prepare failed";
1719    return false;
1720  }
1721
1722  s.BindInt(0, profile_id);
1723  if (s.Step())
1724    *profile = AutoFillProfileFromStatement(s);
1725
1726  return s.Succeeded();
1727}
1728
1729bool WebDatabase::AddCreditCard(const CreditCard& credit_card) {
1730  sql::Statement s(db_.GetUniqueStatement(
1731      "INSERT INTO credit_cards"
1732      "(label, unique_id, name_on_card, type, card_number,"
1733      " expiration_month, expiration_year, verification_code, billing_address,"
1734      " shipping_address, card_number_encrypted, verification_code_encrypted)"
1735      "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"));
1736  if (!s) {
1737    NOTREACHED() << "Statement prepare failed";
1738    return false;
1739  }
1740
1741  BindCreditCardToStatement(credit_card, &s);
1742
1743  if (!s.Run()) {
1744    NOTREACHED();
1745    return false;
1746  }
1747
1748  DCHECK_GT(db_.GetLastChangeCount(), 0);
1749  return s.Succeeded();
1750}
1751
1752bool WebDatabase::GetCreditCardForLabel(const string16& label,
1753                                        CreditCard** credit_card) {
1754  DCHECK(credit_card);
1755  sql::Statement s(db_.GetUniqueStatement(
1756      "SELECT * FROM credit_cards "
1757      "WHERE label = ?"));
1758  if (!s) {
1759    NOTREACHED() << "Statement prepare failed";
1760    return false;
1761  }
1762
1763  s.BindString16(0, label);
1764  if (!s.Step())
1765    return false;
1766
1767  *credit_card = CreditCardFromStatement(s);
1768
1769  return s.Succeeded();
1770}
1771
1772bool WebDatabase::GetCreditCardForID(int credit_card_id,
1773                                     CreditCard** credit_card) {
1774  sql::Statement s(db_.GetUniqueStatement(
1775      "SELECT * FROM credit_cards "
1776      "WHERE unique_id = ?"));
1777  if (!s) {
1778    NOTREACHED() << "Statement prepare failed";
1779    return false;
1780  }
1781
1782  s.BindInt(0, credit_card_id);
1783  if (!s.Step())
1784    return false;
1785
1786  *credit_card = CreditCardFromStatement(s);
1787
1788  return s.Succeeded();
1789}
1790
1791bool WebDatabase::GetCreditCards(
1792    std::vector<CreditCard*>* credit_cards) {
1793  DCHECK(credit_cards);
1794  credit_cards->clear();
1795
1796  sql::Statement s(db_.GetUniqueStatement("SELECT * FROM credit_cards"));
1797  if (!s) {
1798    NOTREACHED() << "Statement prepare failed";
1799    return false;
1800  }
1801
1802  while (s.Step())
1803    credit_cards->push_back(CreditCardFromStatement(s));
1804
1805  return s.Succeeded();
1806}
1807
1808bool WebDatabase::UpdateCreditCard(const CreditCard& credit_card) {
1809  DCHECK(credit_card.unique_id());
1810  sql::Statement s(db_.GetUniqueStatement(
1811      "UPDATE credit_cards "
1812      "SET label=?, unique_id=?, name_on_card=?, type=?, card_number=?, "
1813      "    expiration_month=?, expiration_year=?, verification_code=?, "
1814      "    billing_address=?, shipping_address=?, card_number_encrypted=?, "
1815      "    verification_code_encrypted=? "
1816      "WHERE unique_id=?"));
1817  if (!s) {
1818    NOTREACHED() << "Statement prepare failed";
1819    return false;
1820  }
1821
1822  BindCreditCardToStatement(credit_card, &s);
1823  s.BindInt(12, credit_card.unique_id());
1824  bool result = s.Run();
1825  DCHECK_GT(db_.GetLastChangeCount(), 0);
1826  return result;
1827}
1828
1829bool WebDatabase::RemoveCreditCard(int credit_card_id) {
1830  DCHECK_NE(0, credit_card_id);
1831  sql::Statement s(db_.GetUniqueStatement(
1832      "DELETE FROM credit_cards WHERE unique_id = ?"));
1833  if (!s) {
1834    NOTREACHED() << "Statement prepare failed";
1835    return false;
1836  }
1837
1838  s.BindInt(0, credit_card_id);
1839  return s.Run();
1840}
1841
1842bool WebDatabase::AddToCountOfFormElement(int64 pair_id,
1843                                          int delta,
1844                                          bool* was_removed) {
1845  DCHECK(was_removed);
1846  int count = 0;
1847  *was_removed = false;
1848
1849  if (!GetCountOfFormElement(pair_id, &count))
1850    return false;
1851
1852  if (count + delta == 0) {
1853    if (!RemoveFormElementForID(pair_id))
1854      return false;
1855    *was_removed = true;
1856  } else {
1857    if (!SetCountOfFormElement(pair_id, count + delta))
1858      return false;
1859  }
1860  return true;
1861}
1862
1863bool WebDatabase::RemoveFormElementForID(int64 pair_id) {
1864  sql::Statement s(db_.GetUniqueStatement(
1865      "DELETE FROM autofill WHERE pair_id = ?"));
1866  if (!s) {
1867    NOTREACHED() << "Statement prepare failed";
1868    return false;
1869  }
1870  s.BindInt64(0, pair_id);
1871  if (s.Run()) {
1872    return RemoveFormElementForTimeRange(pair_id, base::Time(), base::Time(),
1873                                         NULL);
1874  }
1875  return false;
1876}
1877
1878void WebDatabase::MigrateOldVersionsAsNeeded(){
1879  // Migrate if necessary.
1880  int current_version = meta_table_.GetVersionNumber();
1881  switch (current_version) {
1882    // Versions 1 - 19 are unhandled.  Version numbers greater than
1883    // kCurrentVersionNumber should have already been weeded out by the caller.
1884    default:
1885      // When the version is too old, we just try to continue anyway.  There
1886      // should not be a released product that makes a database too old for us
1887      // to handle.
1888      LOG(WARNING) << "Web database version " << current_version <<
1889          " is too old to handle.";
1890      return;
1891
1892    case 20:
1893      // Add the autogenerate_keyword column.
1894      if (!db_.Execute("ALTER TABLE keywords ADD COLUMN autogenerate_keyword "
1895                       "INTEGER DEFAULT 0")) {
1896        NOTREACHED();
1897        LOG(WARNING) << "Unable to update web database to version 21.";
1898        return;
1899      }
1900      meta_table_.SetVersionNumber(21);
1901      meta_table_.SetCompatibleVersionNumber(
1902          std::min(21, kCompatibleVersionNumber));
1903      // FALL THROUGH
1904
1905    case 21:
1906      if (!ClearAutofillEmptyValueElements()) {
1907        NOTREACHED() << "Failed to clean-up autofill DB.";
1908      }
1909      meta_table_.SetVersionNumber(22);
1910      // No change in the compatibility version number.
1911
1912      // FALL THROUGH
1913
1914    case 22:
1915      // Add the card_number_encrypted column if credit card table was not
1916      // created in this build (otherwise the column already exists).
1917      // WARNING: Do not change the order of the execution of the SQL
1918      // statements in this case!  Profile corruption and data migration
1919      // issues WILL OCCUR. (see http://crbug.com/10913)
1920      //
1921      // The problem is that if a user has a profile which was created before
1922      // r37036, when the credit_cards table was added, and then failed to
1923      // update this profile between the credit card addition and the addition
1924      // of the "encrypted" columns (44963), the next data migration will put
1925      // the user's profile in an incoherent state: The user will update from
1926      // a data profile set to be earlier than 22, and therefore pass through
1927      // this update case.  But because the user did not have a credit_cards
1928      // table before starting Chrome, it will have just been initialized
1929      // above, and so already have these columns -- and thus this data
1930      // update step will have failed.
1931      //
1932      // The false assumption in this case is that at this step in the
1933      // migration, the user has a credit card table, and that this
1934      // table does not include encrypted columns!
1935      // Because this case does not roll back the complete set of SQL
1936      // transactions properly in case of failure (that is, it does not
1937      // roll back the table initialization done above), the incoherent
1938      // profile will now see itself as being at version 22 -- but include a
1939      // fully initialized credit_cards table.  Every time Chrome runs, it
1940      // will try to update the web database and fail at this step, unless
1941      // we allow for the faulty assumption described above by checking for
1942      // the existence of the columns only AFTER we've executed the commands
1943      // to add them.
1944      if (!db_.Execute("ALTER TABLE credit_cards ADD COLUMN "
1945                       "card_number_encrypted BLOB DEFAULT NULL")) {
1946        LOG(WARNING) << "Could not add card_number_encrypted to "
1947                        "credit_cards table.";
1948      }
1949      if (!db_.Execute("ALTER TABLE credit_cards ADD COLUMN "
1950                       "verification_code_encrypted BLOB DEFAULT NULL")) {
1951        LOG(WARNING) << "Could not add verification_code_encrypted to "
1952                        "credit_cards table.";
1953      }
1954      if (!db_.Execute(
1955              "SELECT card_number_encrypted FROM credit_cards limit 1") ||
1956          !db_.Execute(
1957              "SELECT verification_code_encrypted FROM credit_cards limit 1")) {
1958        NOTREACHED();
1959        LOG(WARNING) << "Unable to update web database to version 23.";
1960        return;
1961      }
1962      meta_table_.SetVersionNumber(23);
1963      // FALL THROUGH
1964
1965    case 23: {
1966      // One-time cleanup for Chromium bug 38364.  In the presence of
1967      // multi-byte UTF-8 characters, that bug could cause AutoFill strings
1968      // to grow larger and more corrupt with each save.  The cleanup removes
1969      // any row with a string field larger than a reasonable size.  The string
1970      // fields examined here are precisely the ones that were subject to
1971      // corruption by the original bug.
1972      const std::string autofill_is_too_big =
1973          "max(length(name), length(value)) > 500";
1974
1975      const std::string credit_cards_is_too_big =
1976          "max(length(label), length(name_on_card), length(type), "
1977          "    length(expiration_month), length(expiration_year), "
1978          "    length(billing_address), length(shipping_address) "
1979          ") > 500";
1980
1981      const std::string autofill_profiles_is_too_big =
1982          "max(length(label), length(first_name), "
1983          "    length(middle_name), length(last_name), length(email), "
1984          "    length(company_name), length(address_line_1), "
1985          "    length(address_line_2), length(city), length(state), "
1986          "    length(zipcode), length(country), length(phone), "
1987          "    length(fax)) > 500";
1988
1989      std::string query = "DELETE FROM autofill_dates WHERE pair_id IN ("
1990          "SELECT pair_id FROM autofill WHERE " + autofill_is_too_big + ")";
1991      if (!db_.Execute(query.c_str())) {
1992        NOTREACHED();
1993        LOG(WARNING) << "Unable to update web database to version 24.";
1994        return;
1995      }
1996      query = "DELETE FROM autofill WHERE " + autofill_is_too_big;
1997      if (!db_.Execute(query.c_str())) {
1998        NOTREACHED();
1999        LOG(WARNING) << "Unable to update web database to version 24.";
2000        return;
2001      }
2002      query = "DELETE FROM credit_cards WHERE (" + credit_cards_is_too_big +
2003          ") OR label IN (SELECT label FROM autofill_profiles WHERE " +
2004          autofill_profiles_is_too_big + ")";
2005      if (!db_.Execute(query.c_str())) {
2006        NOTREACHED();
2007        LOG(WARNING) << "Unable to update web database to version 24.";
2008        return;
2009      }
2010      query = "DELETE FROM autofill_profiles WHERE " +
2011          autofill_profiles_is_too_big;
2012      if (!db_.Execute(query.c_str())) {
2013        NOTREACHED();
2014        LOG(WARNING) << "Unable to update web database to version 24.";
2015        return;
2016      }
2017
2018      meta_table_.SetVersionNumber(24);
2019
2020      // FALL THROUGH
2021    }
2022
2023    case 24:
2024      // Add the logo_id column if keyword table was not created in this build.
2025      if (!db_.Execute("ALTER TABLE keywords ADD COLUMN logo_id "
2026                       "INTEGER DEFAULT 0")) {
2027        NOTREACHED();
2028        LOG(WARNING) << "Unable to update web database to version 25.";
2029        return;
2030      }
2031      meta_table_.SetVersionNumber(25);
2032      meta_table_.SetCompatibleVersionNumber(
2033          std::min(25, kCompatibleVersionNumber));
2034      // FALL THROUGH
2035
2036    case 25:
2037      // Add the created_by_policy column.
2038      if (!db_.Execute("ALTER TABLE keywords ADD COLUMN created_by_policy "
2039                       "INTEGER DEFAULT 0")) {
2040        NOTREACHED();
2041        LOG(WARNING) << "Unable to update web database to version 26.";
2042        return;
2043      }
2044
2045      meta_table_.SetVersionNumber(26);
2046      meta_table_.SetCompatibleVersionNumber(
2047          std::min(26, kCompatibleVersionNumber));
2048      // FALL THROUGH
2049
2050    case 26: {
2051      // Change the credit_cards.billing_address column from a string to an int.
2052      // The stored string is the label of an address, so we have to select the
2053      // unique ID of this address using the label as a foreign key into the
2054      // |autofill_profiles| table.
2055      std::string stmt =
2056          "SELECT credit_cards.unique_id, autofill_profiles.unique_id "
2057          "FROM autofill_profiles, credit_cards "
2058          "WHERE credit_cards.billing_address = autofill_profiles.label";
2059      sql::Statement s(db_.GetUniqueStatement(stmt.c_str()));
2060      if (!s) {
2061        NOTREACHED() << "Statement prepare failed";
2062        return;
2063      }
2064
2065      std::map<int, int> cc_billing_map;
2066      while (s.Step())
2067        cc_billing_map[s.ColumnInt(0)] = s.ColumnInt(1);
2068
2069      // Windows already stores the IDs as strings in |billing_address|. Try to
2070      // convert those.
2071      if (cc_billing_map.empty()) {
2072        std::string stmt =
2073          "SELECT unique_id,billing_address FROM credit_cards";
2074        sql::Statement s(db_.GetUniqueStatement(stmt.c_str()));
2075        if (!s) {
2076          NOTREACHED() << "Statement prepare failed";
2077          return;
2078        }
2079
2080        while (s.Step()) {
2081          int id = 0;
2082          if (base::StringToInt(s.ColumnString(1), &id))
2083            cc_billing_map[s.ColumnInt(0)] = id;
2084        }
2085      }
2086
2087      if (!db_.Execute("CREATE TABLE credit_cards_temp ( "
2088                       "label VARCHAR, "
2089                       "unique_id INTEGER PRIMARY KEY, "
2090                       "name_on_card VARCHAR, "
2091                       "type VARCHAR, "
2092                       "card_number VARCHAR, "
2093                       "expiration_month INTEGER, "
2094                       "expiration_year INTEGER, "
2095                       "verification_code VARCHAR, "
2096                       "billing_address INTEGER, "
2097                       "shipping_address VARCHAR, "
2098                       "card_number_encrypted BLOB, "
2099                       "verification_code_encrypted BLOB)")) {
2100        NOTREACHED();
2101        LOG(WARNING) << "Unable to update web database to version 27.";
2102        return;
2103      }
2104
2105      if (!db_.Execute(
2106          "INSERT INTO credit_cards_temp "
2107          "SELECT label,unique_id,name_on_card,type,card_number,"
2108          "expiration_month,expiration_year,verification_code,0,"
2109          "shipping_address,card_number_encrypted,verification_code_encrypted "
2110          "FROM credit_cards")) {
2111        NOTREACHED();
2112        LOG(WARNING) << "Unable to update web database to version 27.";
2113        return;
2114      }
2115
2116      if (!db_.Execute("DROP TABLE credit_cards")) {
2117        NOTREACHED();
2118        LOG(WARNING) << "Unable to update web database to version 27.";
2119        return;
2120      }
2121
2122      if (!db_.Execute(
2123          "ALTER TABLE credit_cards_temp RENAME TO credit_cards")) {
2124        NOTREACHED();
2125        LOG(WARNING) << "Unable to update web database to version 27.";
2126        return;
2127      }
2128
2129      meta_table_.SetVersionNumber(26);
2130      meta_table_.SetCompatibleVersionNumber(
2131          std::min(26, kCompatibleVersionNumber));
2132      // FALL THROUGH
2133
2134      for (std::map<int, int>::const_iterator iter = cc_billing_map.begin();
2135           iter != cc_billing_map.end(); ++iter) {
2136        sql::Statement s(db_.GetCachedStatement(
2137            SQL_FROM_HERE,
2138            "UPDATE credit_cards SET billing_address=? WHERE unique_id=?"));
2139        if (!s) {
2140          NOTREACHED() << "Statement prepare failed";
2141          return;
2142        }
2143
2144        s.BindInt(0, (*iter).second);
2145        s.BindInt(1, (*iter).first);
2146
2147        if (!s.Run()) {
2148          NOTREACHED();
2149          LOG(WARNING) << "Unable to update web database to version 27.";
2150          return;
2151        }
2152      }
2153
2154      meta_table_.SetVersionNumber(27);
2155      meta_table_.SetCompatibleVersionNumber(
2156          std::min(27, kCompatibleVersionNumber));
2157
2158      // FALL THROUGH
2159    }
2160
2161    // Add successive versions here.  Each should set the version number and
2162    // compatible version number as appropriate, then fall through to the next
2163    // case.
2164
2165    case kCurrentVersionNumber:
2166      // No migration needed.
2167      return;
2168  }
2169}
2170