1 // Copyright 2012 The Chromium Authors
2 // Use of this source code is governed by a BSD-style license that can be
3 // found in the LICENSE file.
8 #include "base/containers/contains.h"
9 #include "base/files/file_util.h"
10 #include "base/files/scoped_temp_dir.h"
11 #include "base/functional/bind.h"
12 #include "base/strings/string_piece_forward.h"
13 #include "base/test/bind.h"
14 #include "sql/database.h"
15 #include "sql/statement.h"
16 #include "sql/test/scoped_error_expecter.h"
17 #include "testing/gtest/include/gtest/gtest.h"
18 #include "third_party/sqlite/sqlite3.h"
23 class StatementTest : public testing::Test {
25 ~StatementTest() override = default;
27 void SetUp() override {
28 ASSERT_TRUE(temp_dir_.CreateUniqueTempDir());
30 db_.Open(temp_dir_.GetPath().AppendASCII("statement_test.sqlite")));
34 base::ScopedTempDir temp_dir_;
38 TEST_F(StatementTest, Assign) {
40 EXPECT_FALSE(create.is_valid());
42 create.Assign(db_.GetUniqueStatement(
43 "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
44 EXPECT_TRUE(create.is_valid());
47 TEST_F(StatementTest, Run) {
48 ASSERT_TRUE(db_.Execute(
49 "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
50 ASSERT_TRUE(db_.Execute("INSERT INTO rows(a, b) VALUES(3, 12)"));
52 Statement select(db_.GetUniqueStatement("SELECT b FROM rows WHERE a=?"));
53 EXPECT_FALSE(select.Succeeded());
55 // Stepping it won't work since we haven't bound the value.
56 EXPECT_FALSE(select.Step());
58 // Run should fail since this produces output, and we should use Step(). This
59 // gets a bit wonky since sqlite says this is OK so succeeded is set.
60 select.Reset(/*clear_bound_vars=*/true);
61 select.BindInt64(0, 3);
62 EXPECT_FALSE(select.Run());
63 EXPECT_EQ(SQLITE_ROW, db_.GetErrorCode());
64 EXPECT_TRUE(select.Succeeded());
66 // Resetting it should put it back to the previous state (not runnable).
67 select.Reset(/*clear_bound_vars=*/true);
68 EXPECT_FALSE(select.Succeeded());
70 // Binding and stepping should produce one row.
71 select.BindInt64(0, 3);
72 EXPECT_TRUE(select.Step());
73 EXPECT_TRUE(select.Succeeded());
74 EXPECT_EQ(12, select.ColumnInt64(0));
75 EXPECT_FALSE(select.Step());
76 EXPECT_TRUE(select.Succeeded());
79 // Error callback called for error running a statement.
80 TEST_F(StatementTest, DatabaseErrorCallbackCalledOnError) {
81 ASSERT_TRUE(db_.Execute(
82 "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
84 bool error_callback_called = false;
85 int error = SQLITE_OK;
86 db_.set_error_callback(base::BindLambdaForTesting(
87 [&](int sqlite_error, sql::Statement* statement) {
88 error_callback_called = true;
92 // `rows` is a table with ROWID. https://www.sqlite.org/rowidtable.html
93 // Since `a` is declared as INTEGER PRIMARY KEY, it is an alias for SQLITE's
94 // rowid. This means `a` can only take on integer values. Attempting to insert
95 // anything else causes the error callback handler to be called with
96 // SQLITE_MISMATCH as error code.
97 Statement insert(db_.GetUniqueStatement("INSERT INTO rows(a) VALUES(?)"));
98 ASSERT_TRUE(insert.is_valid());
99 insert.BindString(0, "not an integer, not suitable as primary key value");
100 EXPECT_FALSE(insert.Run())
101 << "Invalid statement should not Run() successfully";
102 EXPECT_TRUE(error_callback_called)
103 << "Statement::Run() should report errors to the database error callback";
104 EXPECT_EQ(SQLITE_MISMATCH, error)
105 << "Statement::Run() should report errors to the database error callback";
108 // Error expecter works for error running a statement.
109 TEST_F(StatementTest, ScopedIgnoreError) {
110 ASSERT_TRUE(db_.Execute(
111 "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
113 Statement insert(db_.GetUniqueStatement("INSERT INTO rows(a) VALUES(?)"));
114 EXPECT_TRUE(insert.is_valid());
115 insert.BindString(0, "not an integer, not suitable as primary key value");
118 sql::test::ScopedErrorExpecter expecter;
119 expecter.ExpectError(SQLITE_MISMATCH);
120 EXPECT_FALSE(insert.Run());
121 EXPECT_TRUE(expecter.SawExpectedErrors());
125 TEST_F(StatementTest, Reset) {
126 ASSERT_TRUE(db_.Execute(
127 "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
128 ASSERT_TRUE(db_.Execute("INSERT INTO rows(a, b) VALUES(3, 12)"));
129 ASSERT_TRUE(db_.Execute("INSERT INTO rows(a, b) VALUES(4, 13)"));
131 Statement insert(db_.GetUniqueStatement("SELECT b FROM rows WHERE a=?"));
132 insert.BindInt64(0, 3);
133 ASSERT_TRUE(insert.Step());
134 EXPECT_EQ(12, insert.ColumnInt64(0));
135 ASSERT_FALSE(insert.Step());
137 insert.Reset(/*clear_bound_vars=*/false);
138 // Verify that we can get all rows again.
139 ASSERT_TRUE(insert.Step());
140 EXPECT_EQ(12, insert.ColumnInt64(0));
141 EXPECT_FALSE(insert.Step());
143 insert.Reset(/*clear_bound_vars=*/true);
144 ASSERT_FALSE(insert.Step());
147 TEST_F(StatementTest, BindInt64) {
148 // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
149 // the rows in the same order that they were inserted.
150 ASSERT_TRUE(db_.Execute(
151 "CREATE TABLE ints(id INTEGER PRIMARY KEY, i INTEGER NOT NULL)"));
153 const std::vector<int64_t> values = {
154 // Small positive values.
162 // Small negative values.
171 std::numeric_limits<int64_t>::max(),
172 std::numeric_limits<int64_t>::min(),
175 Statement insert(db_.GetUniqueStatement("INSERT INTO ints(i) VALUES(?)"));
176 for (int64_t value : values) {
177 insert.BindInt64(0, value);
178 ASSERT_TRUE(insert.Run());
179 insert.Reset(/*clear_bound_vars=*/true);
182 Statement select(db_.GetUniqueStatement("SELECT i FROM ints ORDER BY id"));
183 for (int64_t value : values) {
184 ASSERT_TRUE(select.Step());
185 int64_t column_value = select.ColumnInt64(0);
186 EXPECT_EQ(value, column_value);
190 // Chrome features rely on being able to use uint64_t with ColumnInt64().
191 // This is supported, because (starting in C++20) casting between signed and
192 // unsigned integers is well-defined in both directions. This test ensures that
193 // the casting works as expected.
194 TEST_F(StatementTest, BindInt64_FromUint64t) {
195 // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
196 // the rows in the same order that they were inserted.
197 static constexpr char kSql[] =
198 "CREATE TABLE ints(id INTEGER PRIMARY KEY NOT NULL, i INTEGER NOT NULL)";
199 ASSERT_TRUE(db_.Execute(kSql));
201 const std::vector<uint64_t> values = {
202 // Small positive values.
211 std::numeric_limits<int64_t>::max() - 1,
212 std::numeric_limits<int64_t>::max(),
213 std::numeric_limits<uint64_t>::max() - 1,
214 std::numeric_limits<uint64_t>::max(),
217 Statement insert(db_.GetUniqueStatement("INSERT INTO ints(i) VALUES(?)"));
218 for (uint64_t value : values) {
219 insert.BindInt64(0, static_cast<int64_t>(value));
220 ASSERT_TRUE(insert.Run());
221 insert.Reset(/*clear_bound_vars=*/true);
224 Statement select(db_.GetUniqueStatement("SELECT i FROM ints ORDER BY id"));
225 for (uint64_t value : values) {
226 ASSERT_TRUE(select.Step());
227 int64_t column_value = select.ColumnInt64(0);
228 uint64_t cast_column_value = static_cast<uint64_t>(column_value);
229 EXPECT_EQ(value, cast_column_value) << " column_value: " << column_value;
233 TEST_F(StatementTest, BindBlob) {
234 // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
235 // the rows in the same order that they were inserted.
236 ASSERT_TRUE(db_.Execute(
237 "CREATE TABLE blobs(id INTEGER PRIMARY KEY NOT NULL, b BLOB NOT NULL)"));
239 const std::vector<std::vector<uint8_t>> values = {
242 {0x41, 0x42, 0x43, 0x44},
245 Statement insert(db_.GetUniqueStatement("INSERT INTO blobs(b) VALUES(?)"));
246 for (const std::vector<uint8_t>& value : values) {
247 insert.BindBlob(0, value);
248 ASSERT_TRUE(insert.Run());
249 insert.Reset(/*clear_bound_vars=*/true);
252 Statement select(db_.GetUniqueStatement("SELECT b FROM blobs ORDER BY id"));
253 for (const std::vector<uint8_t>& value : values) {
254 ASSERT_TRUE(select.Step());
255 std::vector<uint8_t> column_value;
256 EXPECT_TRUE(select.ColumnBlobAsVector(0, &column_value));
257 EXPECT_EQ(value, column_value);
259 EXPECT_FALSE(select.Step());
262 TEST_F(StatementTest, BindBlob_String16Overload) {
263 // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
264 // the rows in the same order that they were inserted.
265 ASSERT_TRUE(db_.Execute(
266 "CREATE TABLE blobs(id INTEGER PRIMARY KEY NOT NULL, b BLOB NOT NULL)"));
268 const std::vector<std::u16string> values = {
269 std::u16string(), std::u16string(u"hello\n"), std::u16string(u"😀🍩🎉"),
270 std::u16string(u"\xd800\xdc00text"), // surrogate pair with text
271 std::u16string(u"\xd8ff"), // unpaired high surrogate
272 std::u16string(u"\xdddd"), // unpaired low surrogate
273 std::u16string(u"\xdc00\xd800text"), // lone low followed by lone high
274 // surrogate and text
275 std::u16string(1024, 0xdb23), // long invalid UTF-16
278 Statement insert(db_.GetUniqueStatement("INSERT INTO blobs(b) VALUES(?)"));
279 for (const std::u16string& value : values) {
280 insert.BindBlob(0, value);
281 ASSERT_TRUE(insert.Run());
282 insert.Reset(/*clear_bound_vars=*/true);
285 Statement select(db_.GetUniqueStatement("SELECT b FROM blobs ORDER BY id"));
286 for (const std::u16string& value : values) {
287 ASSERT_TRUE(select.Step());
288 std::u16string column_value;
289 EXPECT_TRUE(select.ColumnBlobAsString16(0, &column_value));
290 EXPECT_EQ(value, column_value);
292 EXPECT_FALSE(select.Step());
295 TEST_F(StatementTest, BindString) {
296 // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
297 // the rows in the same order that they were inserted.
298 ASSERT_TRUE(db_.Execute(
299 "CREATE TABLE texts(id INTEGER PRIMARY KEY NOT NULL, t TEXT NOT NULL)"));
301 const std::vector<std::string> values = {
305 std::string("\x00", 1),
308 std::string("\x01Test", 5),
309 std::string("\x00Test", 5),
312 Statement insert(db_.GetUniqueStatement("INSERT INTO texts(t) VALUES(?)"));
313 for (const std::string& value : values) {
314 insert.BindString(0, value);
315 ASSERT_TRUE(insert.Run());
316 insert.Reset(/*clear_bound_vars=*/true);
319 Statement select(db_.GetUniqueStatement("SELECT t FROM texts ORDER BY id"));
320 for (const std::string& value : values) {
321 ASSERT_TRUE(select.Step());
322 EXPECT_EQ(value, select.ColumnString(0));
324 EXPECT_FALSE(select.Step());
327 TEST_F(StatementTest, BindString_NullData) {
328 // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
329 // the rows in the same order that they were inserted.
330 ASSERT_TRUE(db_.Execute(
331 "CREATE TABLE texts(id INTEGER PRIMARY KEY NOT NULL, t TEXT NOT NULL)"));
333 Statement insert(db_.GetUniqueStatement("INSERT INTO texts(t) VALUES(?)"));
334 insert.BindString(0, base::StringPiece(nullptr, 0));
335 ASSERT_TRUE(insert.Run());
337 Statement select(db_.GetUniqueStatement("SELECT t FROM texts ORDER BY id"));
338 ASSERT_TRUE(select.Step());
339 EXPECT_EQ(std::string(), select.ColumnString(0));
341 EXPECT_FALSE(select.Step());
344 TEST_F(StatementTest, GetSQLStatementExcludesBoundValues) {
345 ASSERT_TRUE(db_.Execute(
346 "CREATE TABLE texts(id INTEGER PRIMARY KEY NOT NULL, t TEXT NOT NULL)"));
348 Statement insert(db_.GetUniqueStatement("INSERT INTO texts(t) VALUES(?)"));
349 insert.BindString(0, "John Doe");
350 ASSERT_TRUE(insert.Run());
352 // Verify that GetSQLStatement doesn't leak any bound values that may be PII.
353 std::string sql_statement = insert.GetSQLStatement();
354 EXPECT_TRUE(base::Contains(sql_statement, "INSERT INTO texts(t) VALUES(?)"));
355 EXPECT_TRUE(base::Contains(sql_statement, "VALUES"));
356 EXPECT_FALSE(base::Contains(sql_statement, "Doe"));
358 // Sanity check that the name was actually committed.
359 Statement select(db_.GetUniqueStatement("SELECT t FROM texts ORDER BY id"));
360 ASSERT_TRUE(select.Step());
361 EXPECT_EQ(select.ColumnString(0), "John Doe");