Why is Dapper parameter in SQL 'WHERE' clause case sensitive?-CodePudding

Problem Description

When using a Dapper parameter in an SQL WHERE-clause, the parameter appears to be case sensitive. However, when I replace the Dapper parameter with a string literal, the WHERE-clause is no longer case sensitive. I have created a simple ASP.NET Core recipe example web API to help illustrate the problem.

In this recipe example, I am using a PostgreSQL database and want to query a recipe table to get a recipe by name. I have made the name column of type citext, which is a case-insensitive string type.

Database Table

A description of the recipe table:

 ------------- -------- ----------- ---------- -------------------- 
|   Column    |  Type  | Collation | Nullable |      Default       |
 ------------- -------- ----------- ---------- -------------------- 
| recipe_id   | uuid   |           | not null | uuid_generate_v4() |
| name        | citext |           | not null |                    |
| description | text   |           |          |                    |
 ------------- -------- ----------- ---------- --------------------

The contents of the recipe table are:

 -------------------------------------- -------------------- ----------------------------------------------------------- 
|              recipe_id               |        name        |                        description                        |
 -------------------------------------- -------------------- ----------------------------------------------------------- 
| 8f749e7a-e192-48df-91af-f319ab608212 | meatballs          | balled up meat                                            |
| f44c696f-a94a-4f17-a387-dd4d42f60ef8 | red beans and rice | yummy new orleans original                                |
| 82c5911b-feec-4854-9073-6a85ea793dc0 | pasta cereal       | couscous and ground meat eaten with a spoon, like cereal! |
 -------------------------------------- -------------------- -----------------------------------------------------------

Query Method

The RecipeController has a GetByName method that accepts the name parameter as part of the URI path. The GetByName method calls the GetByNameAsync method of the RecipeRepository class, which contains the SQL statement in question:

public async Task<Recipe> GetByNameAsync(string name)
{
    string sql = $@"
                    SELECT  *
                    FROM    {nameof(Recipe)}
                    WHERE   {nameof(Recipe)}.{nameof(Recipe.name)} = @{nameof(name)}";
    
    using (IDbConnection connection = Open())
    {
        IEnumerable<Recipe> recipes = await connection.QueryAsync<Recipe>(sql, new {name});

        return recipes.DefaultIfEmpty(new Recipe()).First();
    }
}

Query Responses

If I wanted to query the meatballs recipe by name, and set the name parameter equal to "meatballs", I get the following response:

{
  "recipe_id": "8f749e7a-e192-48df-91af-f319ab608212",
  "name": "meatballs",
  "description": "balled up meat"
}

Setting the name parameter equal to "Meatballs", I get the following response:

{
  "type": "https://tools.ietf.org/html/rfc7231#section-6.5.4",
  "title": "Not Found",
  "status": 404,
  "traceId": "00-5e4e35d5cfec644fc117eaa96e854854-c0490c8ef510f3b1-00"
}

And finally, if I replace the Dapper name parameter with the string literal "Meatballs":

public async Task<Recipe> GetByNameAsync(string name)
{
    string sql = $@"
                    SELECT  *
                    FROM    {nameof(Recipe)}
                    WHERE   {nameof(Recipe)}.{nameof(Recipe.name)} = 'Meatballs'";
    
    using (IDbConnection connection = Open())
    {
        IEnumerable<Recipe> recipes = await connection.QueryAsync<Recipe>(sql, new {name});

        return recipes.DefaultIfEmpty(new Recipe()).First();
    }
}

I get the following response:

{
  "recipe_id": "8f749e7a-e192-48df-91af-f319ab608212",
  "name": "meatballs",
  "description": "balled up meat"
}

Why is this Dapper parameter forcing case-sensitivity? And how can I get around this?

CodePudding user response：

Background

As Jeroen pointed out:

Presumably Dapper isn't doing any such thing and the same thing happens from any language where a parameter is passed as a regular string...

This indeed was not an issue with Dapper, but an issue with SQL data types. The name column in the recipeExample database does not know the incoming data type is supposed to be of type citext. Therefore, casting the incoming argument to citext is necessary.

As Jeroen also pointed out:

From what I gather Postgres also supports collations, and using a case-insensitive collation on a regular string type is likely to work without conversion of any kind.

Somehow I missed this, but the pgDocs even recommend considering nondeterministic collations instead of using the citext module. After reading up on localization, collations, and watching this YouTube video, I updated the recipe example web api to compare using the citext module with nondeterministic collations.

Database Update

First, I added the case_insensitive collation provided in the PostgreSQL documentation:

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

Then, I updated the recipe table to have two name columns: name_1 of type text using the case_insensitive collation, and name_2 of type citext:

 ------------- -------- ------------------ ---------- -------------------- 
|   Column    |  Type  |    Collation     | Nullable |      Default       |
 ------------- -------- ------------------ ---------- -------------------- 
| recipe_id   | uuid   |                  | not null | uuid_generate_v4() |
| name_1      | text   | case_insensitive | not null |                    |
| name_2      | citext |                  | not null |                    |
| description | text   |                  |          |                    |
 ------------- -------- ------------------ ---------- -------------------- 
Indexes:
    "recipe_pkey" PRIMARY KEY, btree (recipe_id)
    "recipe_name_citext_key" UNIQUE CONSTRAINT, btree (name_2)
    "recipe_name_key" UNIQUE CONSTRAINT, btree (name_1)

Next, I created three Postgres functions to test out the 'Meatballs' query:

The first function queries the name_1 column and takes a text argument
The second function queries the name_2 column and takes a text argument
The third function queries the name_2 column and takes a citext argument


CREATE FUNCTION getrecipe_name1_text(text) RETURNS recipe as $$
    SELECT *
    FROM recipe
    WHERE recipe.name_1 = $1;
$$ LANGUAGE SQL;

CREATE FUNCTION getrecipe_name2_text(text) RETURNS recipe as $$
    SELECT *
    FROM recipe
    WHERE recipe.name_2 = $1;
$$ LANGUAGE SQL;

CREATE FUNCTION getrecipe_name2_citext(citext) RETURNS recipe as $$
    SELECT *
    FROM recipe
    WHERE recipe.name_2 = $1;
$$ LANGUAGE SQL;

Query Tests

Querying the name_1 column with text argument:

recipeexample=# SELECT * FROM getrecipe_name1_text('Meatballs');

 -------------------------------------- ----------- ----------- ---------------- 
|              recipe_id               |  name_1   |  name_2   |  description   |
 -------------------------------------- ----------- ----------- ---------------- 
| 8f749e7a-e192-48df-91af-f319ab608212 | meatballs | meatballs | balled up meat |
 -------------------------------------- ----------- ----------- ---------------- 
(1 row)

Querying the name_2 column with text argument:

recipeexample=# SELECT * FROM getrecipe_name2_text('Meatballs');

 ----------- -------- -------- ------------- 
| recipe_id | name_1 | name_2 | description |
 ----------- -------- -------- ------------- 
|           |        |        |             |
 ----------- -------- -------- ------------- 
(1 row)

Querying the name_2 column with citext argument:

recipeexample=# SELECT * FROM getrecipe_name2_citext('Meatballs');

 -------------------------------------- ----------- ----------- ---------------- 
|              recipe_id               |  name_1   |  name_2   |  description   |
 -------------------------------------- ----------- ----------- ---------------- 
| 8f749e7a-e192-48df-91af-f319ab608212 | meatballs | meatballs | balled up meat |
 -------------------------------------- ----------- ----------- ---------------- 
(1 row)

Conclusion

If the citext module is used, arguments must be cast to citext when querying
If the case_insensitive collation is used, there will be performance penalties and pattern matching operations are not possible