LibSQL: Parse and execute sequential placeholder values

This partially implements SQLite's bind-parameter expression to support
indicating placeholder values in a SQL statement. For example:

    INSERT INTO table VALUES (42, ?);

In the above statement, the '?' identifier is a placeholder. This will
allow clients to compile statements a single time while running those
statements any number of times with different placeholder values.

Further, this will help mitigate SQL injection attacks.

3 files changed, 83 insertions, 4 deletions

diff --git a/Tests/LibSQL/TestSqlExpressionParser.cpp b/Tests/LibSQL/TestSqlExpressionParser.cpp
index ada10d993f..f308e3fc58 100644
--- a/Tests/LibSQL/TestSqlExpressionParser.cpp
+++ b/Tests/LibSQL/TestSqlExpressionParser.cpp
@@ -131,6 +131,19 @@ TEST_CASE(null_literal)
     validate("NULL"sv);
 }
 
+TEST_CASE(bind_parameter)
+{
+    auto validate = [](StringView sql) {
+        auto result = parse(sql);
+        EXPECT(!result.is_error());
+
+        auto expression = result.release_value();
+        EXPECT(is<SQL::AST::Placeholder>(*expression));
+    };
+
+    validate("?"sv);
+}
+
 TEST_CASE(column_name)
 {
     EXPECT(parse(".column_name"sv).is_error());
diff --git a/Tests/LibSQL/TestSqlStatementExecution.cpp b/Tests/LibSQL/TestSqlStatementExecution.cpp
index 87228dad59..cc2fd9e425 100644
--- a/Tests/LibSQL/TestSqlStatementExecution.cpp
+++ b/Tests/LibSQL/TestSqlStatementExecution.cpp
@@ -21,19 +21,19 @@ namespace {
 
 constexpr char const* db_name = "/tmp/test.db";
 
-SQL::ResultOr<SQL::ResultSet> try_execute(NonnullRefPtr<SQL::Database> database, DeprecatedString const& sql)
+SQL::ResultOr<SQL::ResultSet> try_execute(NonnullRefPtr<SQL::Database> database, DeprecatedString const& sql, Vector<SQL::Value> placeholder_values = {})
 {
     auto parser = SQL::AST::Parser(SQL::AST::Lexer(sql));
     auto statement = parser.next_statement();
     EXPECT(!parser.has_errors());
     if (parser.has_errors())
         outln("{}", parser.errors()[0].to_deprecated_string());
-    return statement->execute(move(database));
+    return statement->execute(move(database), placeholder_values);
 }
 
-SQL::ResultSet execute(NonnullRefPtr<SQL::Database> database, DeprecatedString const& sql)
+SQL::ResultSet execute(NonnullRefPtr<SQL::Database> database, DeprecatedString const& sql, Vector<SQL::Value> placeholder_values = {})
 {
-    auto result = try_execute(move(database), sql);
+    auto result = try_execute(move(database), sql, move(placeholder_values));
     if (result.is_error()) {
         outln("{}", result.release_error().error_string());
         VERIFY_NOT_REACHED();
@@ -41,6 +41,12 @@ SQL::ResultSet execute(NonnullRefPtr<SQL::Database> database, DeprecatedString c
     return result.release_value();
 }
 
+template<typename... Args>
+Vector<SQL::Value> placeholders(Args&&... args)
+{
+    return { SQL::Value(forward<Args>(args))... };
+}
+
 void create_schema(NonnullRefPtr<SQL::Database> database)
 {
     auto result = execute(database, "CREATE SCHEMA TestSchema;");
@@ -175,6 +181,59 @@ TEST_CASE(insert_without_column_names)
     EXPECT_EQ(rows_or_error.value().size(), 2u);
 }
 
+TEST_CASE(insert_with_placeholders)
+{
+    ScopeGuard guard([]() { unlink(db_name); });
+
+    auto database = SQL::Database::construct(db_name);
+    EXPECT(!database->open().is_error());
+    create_table(database);
+
+    {
+        auto result = try_execute(database, "INSERT INTO TestSchema.TestTable VALUES (?, ?);");
+        EXPECT(result.is_error());
+        EXPECT_EQ(result.error().error(), SQL::SQLErrorCode::InvalidNumberOfPlaceholderValues);
+
+        result = try_execute(database, "INSERT INTO TestSchema.TestTable VALUES (?, ?);", placeholders("Test_1"sv));
+        EXPECT(result.is_error());
+        EXPECT_EQ(result.error().error(), SQL::SQLErrorCode::InvalidNumberOfPlaceholderValues);
+
+        result = try_execute(database, "INSERT INTO TestSchema.TestTable VALUES (?, ?);", placeholders(42, 42));
+        EXPECT(result.is_error());
+        EXPECT_EQ(result.error().error(), SQL::SQLErrorCode::InvalidValueType);
+
+        result = try_execute(database, "INSERT INTO TestSchema.TestTable VALUES (?, ?);", placeholders("Test_1"sv, "Test_2"sv));
+        EXPECT(result.is_error());
+        EXPECT_EQ(result.error().error(), SQL::SQLErrorCode::InvalidValueType);
+    }
+    {
+        auto result = execute(database, "INSERT INTO TestSchema.TestTable VALUES (?, ?);", placeholders("Test_1"sv, 42));
+        EXPECT_EQ(result.size(), 1u);
+
+        result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable ORDER BY TextColumn;");
+        EXPECT_EQ(result.size(), 1u);
+
+        EXPECT_EQ(result[0].row[0], "Test_1"sv);
+        EXPECT_EQ(result[0].row[1], 42);
+    }
+    {
+        auto result = execute(database, "INSERT INTO TestSchema.TestTable VALUES (?, ?), (?, ?);", placeholders("Test_2"sv, 43, "Test_3"sv, 44));
+        EXPECT_EQ(result.size(), 2u);
+
+        result = execute(database, "SELECT TextColumn, IntColumn FROM TestSchema.TestTable ORDER BY TextColumn;");
+        EXPECT_EQ(result.size(), 3u);
+
+        EXPECT_EQ(result[0].row[0], "Test_1"sv);
+        EXPECT_EQ(result[0].row[1], 42);
+
+        EXPECT_EQ(result[1].row[0], "Test_2"sv);
+        EXPECT_EQ(result[1].row[1], 43);
+
+        EXPECT_EQ(result[2].row[0], "Test_3"sv);
+        EXPECT_EQ(result[2].row[1], 44);
+    }
+}
+
 TEST_CASE(select_from_empty_table)
 {
     ScopeGuard guard([]() { unlink(db_name); });
diff --git a/Tests/LibSQL/TestSqlStatementParser.cpp b/Tests/LibSQL/TestSqlStatementParser.cpp
index 11ff1242cf..e42819a295 100644
--- a/Tests/LibSQL/TestSqlStatementParser.cpp
+++ b/Tests/LibSQL/TestSqlStatementParser.cpp
@@ -752,6 +752,13 @@ TEST_CASE(nested_subquery_limit)
     EXPECT(parse(DeprecatedString::formatted("SELECT * FROM ({});"sv, subquery)).is_error());
 }
 
+TEST_CASE(bound_parameter_limit)
+{
+    auto subquery = DeprecatedString::repeated("?, "sv, SQL::AST::Limits::maximum_bound_parameters);
+    EXPECT(!parse(DeprecatedString::formatted("INSERT INTO table_name VALUES ({}42);"sv, subquery)).is_error());
+    EXPECT(parse(DeprecatedString::formatted("INSERT INTO table_name VALUES ({}?);"sv, subquery)).is_error());
+}
+
 TEST_CASE(describe_table)
 {
     EXPECT(parse("DESCRIBE"sv).is_error());