statement_unittest.cc 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330
  1. // Copyright (c) 2012 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. #include <limits>
  5. #include <string>
  6. #include "base/bind.h"
  7. #include "base/files/file_util.h"
  8. #include "base/files/scoped_temp_dir.h"
  9. #include "base/strings/string_piece_forward.h"
  10. #include "base/test/bind.h"
  11. #include "sql/database.h"
  12. #include "sql/statement.h"
  13. #include "sql/test/scoped_error_expecter.h"
  14. #include "testing/gtest/include/gtest/gtest.h"
  15. #include "third_party/sqlite/sqlite3.h"
  16. namespace sql {
  17. namespace {
  18. class StatementTest : public testing::Test {
  19. public:
  20. ~StatementTest() override = default;
  21. void SetUp() override {
  22. ASSERT_TRUE(temp_dir_.CreateUniqueTempDir());
  23. ASSERT_TRUE(
  24. db_.Open(temp_dir_.GetPath().AppendASCII("statement_test.sqlite")));
  25. }
  26. protected:
  27. base::ScopedTempDir temp_dir_;
  28. Database db_;
  29. };
  30. TEST_F(StatementTest, Assign) {
  31. Statement create;
  32. EXPECT_FALSE(create.is_valid());
  33. create.Assign(db_.GetUniqueStatement(
  34. "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
  35. EXPECT_TRUE(create.is_valid());
  36. }
  37. TEST_F(StatementTest, Run) {
  38. ASSERT_TRUE(db_.Execute(
  39. "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
  40. ASSERT_TRUE(db_.Execute("INSERT INTO rows(a, b) VALUES(3, 12)"));
  41. Statement select(db_.GetUniqueStatement("SELECT b FROM rows WHERE a=?"));
  42. EXPECT_FALSE(select.Succeeded());
  43. // Stepping it won't work since we haven't bound the value.
  44. EXPECT_FALSE(select.Step());
  45. // Run should fail since this produces output, and we should use Step(). This
  46. // gets a bit wonky since sqlite says this is OK so succeeded is set.
  47. select.Reset(/*clear_bound_vars=*/true);
  48. select.BindInt64(0, 3);
  49. EXPECT_FALSE(select.Run());
  50. EXPECT_EQ(SQLITE_ROW, db_.GetErrorCode());
  51. EXPECT_TRUE(select.Succeeded());
  52. // Resetting it should put it back to the previous state (not runnable).
  53. select.Reset(/*clear_bound_vars=*/true);
  54. EXPECT_FALSE(select.Succeeded());
  55. // Binding and stepping should produce one row.
  56. select.BindInt64(0, 3);
  57. EXPECT_TRUE(select.Step());
  58. EXPECT_TRUE(select.Succeeded());
  59. EXPECT_EQ(12, select.ColumnInt64(0));
  60. EXPECT_FALSE(select.Step());
  61. EXPECT_TRUE(select.Succeeded());
  62. }
  63. // Error callback called for error running a statement.
  64. TEST_F(StatementTest, DatabaseErrorCallbackCalledOnError) {
  65. ASSERT_TRUE(db_.Execute(
  66. "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
  67. bool error_callback_called = false;
  68. int error = SQLITE_OK;
  69. db_.set_error_callback(base::BindLambdaForTesting(
  70. [&](int sqlite_error, sql::Statement* statement) {
  71. error_callback_called = true;
  72. error = sqlite_error;
  73. }));
  74. // `rows` is a table with ROWID. https://www.sqlite.org/rowidtable.html
  75. // Since `a` is declared as INTEGER PRIMARY KEY, it is an alias for SQLITE's
  76. // rowid. This means `a` can only take on integer values. Attempting to insert
  77. // anything else causes the error callback handler to be called with
  78. // SQLITE_MISMATCH as error code.
  79. Statement insert(db_.GetUniqueStatement("INSERT INTO rows(a) VALUES(?)"));
  80. ASSERT_TRUE(insert.is_valid());
  81. insert.BindString(0, "not an integer, not suitable as primary key value");
  82. EXPECT_FALSE(insert.Run())
  83. << "Invalid statement should not Run() successfully";
  84. EXPECT_TRUE(error_callback_called)
  85. << "Statement::Run() should report errors to the database error callback";
  86. EXPECT_EQ(SQLITE_MISMATCH, error)
  87. << "Statement::Run() should report errors to the database error callback";
  88. }
  89. // Error expecter works for error running a statement.
  90. TEST_F(StatementTest, ScopedIgnoreError) {
  91. ASSERT_TRUE(db_.Execute(
  92. "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
  93. Statement insert(db_.GetUniqueStatement("INSERT INTO rows(a) VALUES(?)"));
  94. EXPECT_TRUE(insert.is_valid());
  95. insert.BindString(0, "not an integer, not suitable as primary key value");
  96. {
  97. sql::test::ScopedErrorExpecter expecter;
  98. expecter.ExpectError(SQLITE_MISMATCH);
  99. EXPECT_FALSE(insert.Run());
  100. EXPECT_TRUE(expecter.SawExpectedErrors());
  101. }
  102. }
  103. TEST_F(StatementTest, Reset) {
  104. ASSERT_TRUE(db_.Execute(
  105. "CREATE TABLE rows(a INTEGER PRIMARY KEY NOT NULL, b INTEGER NOT NULL)"));
  106. ASSERT_TRUE(db_.Execute("INSERT INTO rows(a, b) VALUES(3, 12)"));
  107. ASSERT_TRUE(db_.Execute("INSERT INTO rows(a, b) VALUES(4, 13)"));
  108. Statement insert(db_.GetUniqueStatement("SELECT b FROM rows WHERE a=?"));
  109. insert.BindInt64(0, 3);
  110. ASSERT_TRUE(insert.Step());
  111. EXPECT_EQ(12, insert.ColumnInt64(0));
  112. ASSERT_FALSE(insert.Step());
  113. insert.Reset(/*clear_bound_vars=*/false);
  114. // Verify that we can get all rows again.
  115. ASSERT_TRUE(insert.Step());
  116. EXPECT_EQ(12, insert.ColumnInt64(0));
  117. EXPECT_FALSE(insert.Step());
  118. insert.Reset(/*clear_bound_vars=*/true);
  119. ASSERT_FALSE(insert.Step());
  120. }
  121. TEST_F(StatementTest, BindInt64) {
  122. // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
  123. // the rows in the same order that they were inserted.
  124. ASSERT_TRUE(db_.Execute(
  125. "CREATE TABLE ints(id INTEGER PRIMARY KEY, i INTEGER NOT NULL)"));
  126. const std::vector<int64_t> values = {
  127. // Small positive values.
  128. 0,
  129. 1,
  130. 2,
  131. 10,
  132. 101,
  133. 1002,
  134. // Small negative values.
  135. -1,
  136. -2,
  137. -3,
  138. -10,
  139. -101,
  140. -1002,
  141. // Large values.
  142. std::numeric_limits<int64_t>::max(),
  143. std::numeric_limits<int64_t>::min(),
  144. };
  145. Statement insert(db_.GetUniqueStatement("INSERT INTO ints(i) VALUES(?)"));
  146. for (int64_t value : values) {
  147. insert.BindInt64(0, value);
  148. ASSERT_TRUE(insert.Run());
  149. insert.Reset(/*clear_bound_vars=*/true);
  150. }
  151. Statement select(db_.GetUniqueStatement("SELECT i FROM ints ORDER BY id"));
  152. for (int64_t value : values) {
  153. ASSERT_TRUE(select.Step());
  154. int64_t column_value = select.ColumnInt64(0);
  155. EXPECT_EQ(value, column_value);
  156. }
  157. }
  158. // Chrome features rely on being able to use uint64_t with ColumnInt64().
  159. // This is supported, because (starting in C++20) casting between signed and
  160. // unsigned integers is well-defined in both directions. This test ensures that
  161. // the casting works as expected.
  162. TEST_F(StatementTest, BindInt64_FromUint64t) {
  163. // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
  164. // the rows in the same order that they were inserted.
  165. static constexpr char kSql[] =
  166. "CREATE TABLE ints(id INTEGER PRIMARY KEY NOT NULL, i INTEGER NOT NULL)";
  167. ASSERT_TRUE(db_.Execute(kSql));
  168. const std::vector<uint64_t> values = {
  169. // Small positive values.
  170. 0,
  171. 1,
  172. 2,
  173. 10,
  174. 101,
  175. 1002,
  176. // Large values.
  177. std::numeric_limits<int64_t>::max() - 1,
  178. std::numeric_limits<int64_t>::max(),
  179. std::numeric_limits<uint64_t>::max() - 1,
  180. std::numeric_limits<uint64_t>::max(),
  181. };
  182. Statement insert(db_.GetUniqueStatement("INSERT INTO ints(i) VALUES(?)"));
  183. for (uint64_t value : values) {
  184. insert.BindInt64(0, static_cast<int64_t>(value));
  185. ASSERT_TRUE(insert.Run());
  186. insert.Reset(/*clear_bound_vars=*/true);
  187. }
  188. Statement select(db_.GetUniqueStatement("SELECT i FROM ints ORDER BY id"));
  189. for (uint64_t value : values) {
  190. ASSERT_TRUE(select.Step());
  191. int64_t column_value = select.ColumnInt64(0);
  192. uint64_t cast_column_value = static_cast<uint64_t>(column_value);
  193. EXPECT_EQ(value, cast_column_value) << " column_value: " << column_value;
  194. }
  195. }
  196. TEST_F(StatementTest, BindBlob) {
  197. // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
  198. // the rows in the same order that they were inserted.
  199. ASSERT_TRUE(db_.Execute(
  200. "CREATE TABLE blobs(id INTEGER PRIMARY KEY NOT NULL, b BLOB NOT NULL)"));
  201. const std::vector<std::vector<uint8_t>> values = {
  202. {},
  203. {0x01},
  204. {0x41, 0x42, 0x43, 0x44},
  205. };
  206. Statement insert(db_.GetUniqueStatement("INSERT INTO blobs(b) VALUES(?)"));
  207. for (const std::vector<uint8_t>& value : values) {
  208. insert.BindBlob(0, value);
  209. ASSERT_TRUE(insert.Run());
  210. insert.Reset(/*clear_bound_vars=*/true);
  211. }
  212. Statement select(db_.GetUniqueStatement("SELECT b FROM blobs ORDER BY id"));
  213. for (const std::vector<uint8_t>& value : values) {
  214. ASSERT_TRUE(select.Step());
  215. std::vector<uint8_t> column_value;
  216. EXPECT_TRUE(select.ColumnBlobAsVector(0, &column_value));
  217. EXPECT_EQ(value, column_value);
  218. }
  219. EXPECT_FALSE(select.Step());
  220. }
  221. TEST_F(StatementTest, BindString) {
  222. // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
  223. // the rows in the same order that they were inserted.
  224. ASSERT_TRUE(db_.Execute(
  225. "CREATE TABLE texts(id INTEGER PRIMARY KEY NOT NULL, t TEXT NOT NULL)"));
  226. const std::vector<std::string> values = {
  227. "",
  228. "a",
  229. "\x01",
  230. std::string("\x00", 1),
  231. "abcd",
  232. "\x01\x02\x03\x04",
  233. std::string("\x01Test", 5),
  234. std::string("\x00Test", 5),
  235. };
  236. Statement insert(db_.GetUniqueStatement("INSERT INTO texts(t) VALUES(?)"));
  237. for (const std::string& value : values) {
  238. insert.BindString(0, value);
  239. ASSERT_TRUE(insert.Run());
  240. insert.Reset(/*clear_bound_vars=*/true);
  241. }
  242. Statement select(db_.GetUniqueStatement("SELECT t FROM texts ORDER BY id"));
  243. for (const std::string& value : values) {
  244. ASSERT_TRUE(select.Step());
  245. EXPECT_EQ(value, select.ColumnString(0));
  246. }
  247. EXPECT_FALSE(select.Step());
  248. }
  249. TEST_F(StatementTest, BindString_NullData) {
  250. // `id` makes SQLite's rowid mechanism explicit. We rely on it to retrieve
  251. // the rows in the same order that they were inserted.
  252. ASSERT_TRUE(db_.Execute(
  253. "CREATE TABLE texts(id INTEGER PRIMARY KEY NOT NULL, t TEXT NOT NULL)"));
  254. Statement insert(db_.GetUniqueStatement("INSERT INTO texts(t) VALUES(?)"));
  255. insert.BindString(0, base::StringPiece(nullptr, 0));
  256. ASSERT_TRUE(insert.Run());
  257. Statement select(db_.GetUniqueStatement("SELECT t FROM texts ORDER BY id"));
  258. ASSERT_TRUE(select.Step());
  259. EXPECT_EQ(std::string(), select.ColumnString(0));
  260. EXPECT_FALSE(select.Step());
  261. }
  262. TEST_F(StatementTest, GetSQLStatementExcludesBoundValues) {
  263. ASSERT_TRUE(db_.Execute(
  264. "CREATE TABLE texts(id INTEGER PRIMARY KEY NOT NULL, t TEXT NOT NULL)"));
  265. Statement insert(db_.GetUniqueStatement("INSERT INTO texts(t) VALUES(?)"));
  266. insert.BindString(0, "John Doe");
  267. ASSERT_TRUE(insert.Run());
  268. // Verify that GetSQLStatement doesn't leak any bound values that may be PII.
  269. EXPECT_EQ(insert.GetSQLStatement(), "INSERT INTO texts(t) VALUES(?)");
  270. EXPECT_EQ(insert.GetSQLStatement().find("VALUES"), 21U);
  271. EXPECT_EQ(insert.GetSQLStatement().find("Doe"), std::string::npos);
  272. // Sanity check that the name was actually committed.
  273. Statement select(db_.GetUniqueStatement("SELECT t FROM texts ORDER BY id"));
  274. ASSERT_TRUE(select.Step());
  275. EXPECT_EQ(select.ColumnString(0), "John Doe");
  276. }
  277. } // namespace
  278. } // namespace sql