Google.Cloud.BigQuery.V2 (.NET Client) parameterized query with "LIKE" clause

[AMSHub]

Hi, 
I'm trying to write the equivalent of a simple parameterized query with a LIKE clause to be executed via the .NET client Google.Cloud.BigQuery.V2 client.ExecuteQuery functionality.

I can run the query in the BigQuery console with actual text (not parameters)

select Field1, Field2 from FullyQualifiedTable where Field2 like 'something%'

and I can run it using the .NET Client by manually injecting the parameter into the sql string, i.e. NOT parameterized which is not SQL injection safe:

string sql = $"select Field1, Field2 from FullyQualifiedTable where Field2 like '{name}%'"

or I can run a parameterized query with no LIKE clause:

string sql = "select Field1, Field2 from FullyQualifiedTable where Field2 = @name";
var parameters = new List<BigQueryParameter> { new ("name", BigQueryDbType.String, name) };

But as soon as I try to use a BigQueryParameter in conjunction with the "like" clause, it either throws an exception or returns no results, depending on which syntax I attempt.  
ex:

string sql = "select Field1, Field2 from FullyQualifiedTable where Field2 like '@name%'"
var parameters = new List<BigQueryParameter> { 
   new ("name", BigQueryDbType.String, name)
};

I have tried several permutations:

// THings that didn't work:
// like @name
// like @name%
// like '@name%'
// like @name'%'
// like @name ( with parameter definition containing $"{name}%")
// where CONTAINS_SUBSTR(EmployeeName, @name)

and haven't found anything that works.

Is this a bug or is there some specific syntax I just didn't try that will work? 
A LIKE clause is standard SQL and a super basic use case so I feel like this should be supported.

Thanks in advance.

[ms4446]

The issue you're encountering with parameterized queries in Google Cloud BigQuery, particularly with the LIKE operator, arises from a common misunderstanding of how BigQuery handles parameters within SQL queries. This misunderstanding leads to errors when attempting to use parameters with string patterns.

When constructing SQL queries in BigQuery, it is crucial to understand that parameters are designed to replace entire values, not fragments of strings. This principle underlies the challenges you've faced. Let's examine the common mistakes and why they don't work as expected.

1. Direct Parameter Substitution (LIKE @name%) : Directly substituting a parameter in this manner is ineffective because BigQuery does not support partial substitution within a string. Parameters are intended to replace whole values, and attempting to append the wildcard % directly to the parameter results in an invalid query structure.

2. Single Quotes Around Parameters (LIKE '@name%') : Using single quotes around the parameter (@name) treats it as a string literal rather than a variable. This means that @name is interpreted as the exact text @name rather than the value of the parameter, leading to incorrect query results or errors.

3. Using CONTAINS_SUBSTR: While the CONTAINS_SUBSTR function in BigQuery can perform substring searches, it does not address the core issue of parameterization within the LIKE clause. This function is not a direct replacement for LIKE when parameters are involved.

To effectively use a parameter with the LIKE clause in BigQuery, you need to employ concatenation. This involves using the CONCAT function to combine the parameter value with the wildcard character %. This method ensures that the parameter is treated as a variable, and the wildcard is properly appended to form the desired pattern.

Here’s how you can construct a parameterized query with the LIKE clause using concatenation in BigQuery:

using Google.Cloud.BigQuery.V2;
using System;
using System.Collections.Generic;

public class BigQueryExample
{
    public void ExecuteParameterizedQuery(string name)
    {
        string projectId = "your-project-id";
        BigQueryClient client = BigQueryClient.Create(projectId);

        string sql = "SELECT Field1, Field2 FROM `FullyQualifiedTable` WHERE Field2 LIKE CONCAT(@name, '%')";
        var parameters = new List<BigQueryParameter>
        {
            new BigQueryParameter("name", BigQueryDbType.String, name) // Pass just the value 'name'
        };

        var queryOptions = new QueryOptions { UseQueryCache = false };
        BigQueryResults results = client.ExecuteQuery(sql, parameters, queryOptions);

        foreach (var row in results)
        {
            Console.WriteLine($"Field1: {row["Field1"]}, Field2: {row["Field2"]}");
        }
    }
}

In this solution, the CONCAT function is used to concatenate the parameter value @name with the % wildcard. This approach constructs the LIKE pattern correctly within the query. When the query is executed, BigQuery will replace the parameter with the provided value, effectively executing a query similar to Field2 LIKE 'name%'.

Using parameterized queries is not only a best practice for maintaining clean and readable code but also essential for preventing SQL injection attacks. By correctly parameterizing your queries, you ensure that user input is safely handled, thereby protecting your database from malicious exploits.

In conclusion, understanding the correct usage of parameters with the LIKE clause in BigQuery requires recognizing that parameters replace entire values and employing functions like CONCAT to construct patterns. This approach not only solves the immediate issue but also aligns with best practices for secure and effective SQL query construction.