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/logins_table.h"
6
7#include <limits>
8#include <string>
9
10#include "app/sql/statement.h"
11#include "base/logging.h"
12#include "chrome/browser/password_manager/encryptor.h"
13#include "webkit/glue/password_form.h"
14
15using webkit_glue::PasswordForm;
16
17namespace {
18
19void InitPasswordFormFromStatement(PasswordForm* form, sql::Statement* s) {
20  std::string tmp;
21  string16 decrypted_password;
22  tmp = s->ColumnString(0);
23  form->origin = GURL(tmp);
24  tmp = s->ColumnString(1);
25  form->action = GURL(tmp);
26  form->username_element = s->ColumnString16(2);
27  form->username_value = s->ColumnString16(3);
28  form->password_element = s->ColumnString16(4);
29
30  int encrypted_password_len = s->ColumnByteLength(5);
31  std::string encrypted_password;
32  if (encrypted_password_len) {
33    encrypted_password.resize(encrypted_password_len);
34    memcpy(&encrypted_password[0], s->ColumnBlob(5), encrypted_password_len);
35    Encryptor::DecryptString16(encrypted_password, &decrypted_password);
36  }
37
38  form->password_value = decrypted_password;
39  form->submit_element = s->ColumnString16(6);
40  tmp = s->ColumnString(7);
41  form->signon_realm = tmp;
42  form->ssl_valid = (s->ColumnInt(8) > 0);
43  form->preferred = (s->ColumnInt(9) > 0);
44  form->date_created = base::Time::FromTimeT(s->ColumnInt64(10));
45  form->blacklisted_by_user = (s->ColumnInt(11) > 0);
46  int scheme_int = s->ColumnInt(12);
47  DCHECK((scheme_int >= 0) && (scheme_int <= PasswordForm::SCHEME_OTHER));
48  form->scheme = static_cast<PasswordForm::Scheme>(scheme_int);
49}
50
51}  // anonymous namespace
52
53bool LoginsTable::Init() {
54  if (!db_->DoesTableExist("logins")) {
55    if (!db_->Execute("CREATE TABLE logins ("
56                      "origin_url VARCHAR NOT NULL, "
57                      "action_url VARCHAR, "
58                      "username_element VARCHAR, "
59                      "username_value VARCHAR, "
60                      "password_element VARCHAR, "
61                      "password_value BLOB, "
62                      "submit_element VARCHAR, "
63                      "signon_realm VARCHAR NOT NULL,"
64                      "ssl_valid INTEGER NOT NULL,"
65                      "preferred INTEGER NOT NULL,"
66                      "date_created INTEGER NOT NULL,"
67                      "blacklisted_by_user INTEGER NOT NULL,"
68                      "scheme INTEGER NOT NULL,"
69                      "UNIQUE "
70                      "(origin_url, username_element, "
71                      "username_value, password_element, "
72                      "submit_element, signon_realm))")) {
73      NOTREACHED();
74      return false;
75    }
76    if (!db_->Execute("CREATE INDEX logins_signon ON logins (signon_realm)")) {
77      NOTREACHED();
78      return false;
79    }
80  }
81
82#if defined(OS_WIN)
83  if (!db_->DoesTableExist("ie7_logins")) {
84    if (!db_->Execute("CREATE TABLE ie7_logins ("
85                      "url_hash VARCHAR NOT NULL, "
86                      "password_value BLOB, "
87                      "date_created INTEGER NOT NULL,"
88                      "UNIQUE "
89                      "(url_hash))")) {
90      NOTREACHED();
91      return false;
92    }
93    if (!db_->Execute("CREATE INDEX ie7_logins_hash ON "
94                      "ie7_logins (url_hash)")) {
95      NOTREACHED();
96      return false;
97    }
98  }
99#endif
100
101  return true;
102}
103
104bool LoginsTable::IsSyncable() {
105  return true;
106}
107
108bool LoginsTable::AddLogin(const PasswordForm& form) {
109  sql::Statement s(db_->GetUniqueStatement(
110      "INSERT OR REPLACE INTO logins "
111      "(origin_url, action_url, username_element, username_value, "
112      " password_element, password_value, submit_element, "
113      " signon_realm, ssl_valid, preferred, date_created, "
114      " blacklisted_by_user, scheme) "
115      "VALUES "
116      "(?,?,?,?,?,?,?,?,?,?,?,?,?)"));
117  if (!s) {
118    NOTREACHED() << "Statement prepare failed";
119    return false;
120  }
121
122  std::string encrypted_password;
123  s.BindString(0, form.origin.spec());
124  s.BindString(1, form.action.spec());
125  s.BindString16(2, form.username_element);
126  s.BindString16(3, form.username_value);
127  s.BindString16(4, form.password_element);
128  Encryptor::EncryptString16(form.password_value, &encrypted_password);
129  s.BindBlob(5, encrypted_password.data(),
130             static_cast<int>(encrypted_password.length()));
131  s.BindString16(6, form.submit_element);
132  s.BindString(7, form.signon_realm);
133  s.BindInt(8, form.ssl_valid);
134  s.BindInt(9, form.preferred);
135  s.BindInt64(10, form.date_created.ToTimeT());
136  s.BindInt(11, form.blacklisted_by_user);
137  s.BindInt(12, form.scheme);
138  if (!s.Run()) {
139    NOTREACHED();
140    return false;
141  }
142  return true;
143}
144
145bool LoginsTable::UpdateLogin(const PasswordForm& form) {
146  sql::Statement s(db_->GetUniqueStatement(
147      "UPDATE logins SET "
148      "action_url = ?, "
149      "password_value = ?, "
150      "ssl_valid = ?, "
151      "preferred = ? "
152      "WHERE origin_url = ? AND "
153      "username_element = ? AND "
154      "username_value = ? AND "
155      "password_element = ? AND "
156      "signon_realm = ?"));
157  if (!s) {
158    NOTREACHED() << "Statement prepare failed";
159    return false;
160  }
161
162  s.BindString(0, form.action.spec());
163  std::string encrypted_password;
164  Encryptor::EncryptString16(form.password_value, &encrypted_password);
165  s.BindBlob(1, encrypted_password.data(),
166             static_cast<int>(encrypted_password.length()));
167  s.BindInt(2, form.ssl_valid);
168  s.BindInt(3, form.preferred);
169  s.BindString(4, form.origin.spec());
170  s.BindString16(5, form.username_element);
171  s.BindString16(6, form.username_value);
172  s.BindString16(7, form.password_element);
173  s.BindString(8, form.signon_realm);
174
175  if (!s.Run()) {
176    NOTREACHED();
177    return false;
178  }
179  return true;
180}
181
182bool LoginsTable::RemoveLogin(const PasswordForm& form) {
183  // Remove a login by UNIQUE-constrained fields.
184  sql::Statement s(db_->GetUniqueStatement(
185      "DELETE FROM logins WHERE "
186      "origin_url = ? AND "
187      "username_element = ? AND "
188      "username_value = ? AND "
189      "password_element = ? AND "
190      "submit_element = ? AND "
191      "signon_realm = ?"));
192  if (!s) {
193    NOTREACHED() << "Statement prepare failed";
194    return false;
195  }
196  s.BindString(0, form.origin.spec());
197  s.BindString16(1, form.username_element);
198  s.BindString16(2, form.username_value);
199  s.BindString16(3, form.password_element);
200  s.BindString16(4, form.submit_element);
201  s.BindString(5, form.signon_realm);
202
203  if (!s.Run()) {
204    NOTREACHED();
205    return false;
206  }
207  return true;
208}
209
210bool LoginsTable::RemoveLoginsCreatedBetween(base::Time delete_begin,
211                                             base::Time delete_end) {
212  sql::Statement s1(db_->GetUniqueStatement(
213      "DELETE FROM logins WHERE "
214      "date_created >= ? AND date_created < ?"));
215  if (!s1) {
216    NOTREACHED() << "Statement 1 prepare failed";
217    return false;
218  }
219  s1.BindInt64(0, delete_begin.ToTimeT());
220  s1.BindInt64(1,
221               delete_end.is_null() ?
222                   std::numeric_limits<int64>::max() :
223                   delete_end.ToTimeT());
224  bool success = s1.Run();
225
226#if defined(OS_WIN)
227  sql::Statement s2(db_->GetUniqueStatement(
228      "DELETE FROM ie7_logins WHERE date_created >= ? AND date_created < ?"));
229  if (!s2) {
230    NOTREACHED() << "Statement 2 prepare failed";
231    return false;
232  }
233  s2.BindInt64(0, delete_begin.ToTimeT());
234  s2.BindInt64(1,
235               delete_end.is_null() ?
236                   std::numeric_limits<int64>::max() :
237                   delete_end.ToTimeT());
238  success = success && s2.Run();
239#endif
240
241  return success;
242}
243
244bool LoginsTable::GetLogins(const PasswordForm& form,
245                            std::vector<PasswordForm*>* forms) {
246  DCHECK(forms);
247  sql::Statement s(db_->GetUniqueStatement(
248                "SELECT origin_url, action_url, "
249                "username_element, username_value, "
250                "password_element, password_value, "
251                "submit_element, signon_realm, "
252                "ssl_valid, preferred, "
253                "date_created, blacklisted_by_user, scheme FROM logins "
254                "WHERE signon_realm == ?"));
255  if (!s) {
256    NOTREACHED() << "Statement prepare failed";
257    return false;
258  }
259
260  s.BindString(0, form.signon_realm);
261
262  while (s.Step()) {
263    PasswordForm* new_form = new PasswordForm();
264    InitPasswordFormFromStatement(new_form, &s);
265
266    forms->push_back(new_form);
267  }
268  return s.Succeeded();
269}
270
271bool LoginsTable::GetAllLogins(std::vector<PasswordForm*>* forms,
272                               bool include_blacklisted) {
273  DCHECK(forms);
274  std::string stmt = "SELECT origin_url, action_url, "
275                     "username_element, username_value, "
276                     "password_element, password_value, "
277                     "submit_element, signon_realm, ssl_valid, preferred, "
278                     "date_created, blacklisted_by_user, scheme FROM logins ";
279  if (!include_blacklisted)
280    stmt.append("WHERE blacklisted_by_user == 0 ");
281  stmt.append("ORDER BY origin_url");
282
283  sql::Statement s(db_->GetUniqueStatement(stmt.c_str()));
284  if (!s) {
285    NOTREACHED() << "Statement prepare failed";
286    return false;
287  }
288
289  while (s.Step()) {
290    PasswordForm* new_form = new PasswordForm();
291    InitPasswordFormFromStatement(new_form, &s);
292
293    forms->push_back(new_form);
294  }
295  return s.Succeeded();
296}
297