title: Sanitizing and preparation of text search string for dapper query
body: <p>We have a table of following structure in <code>postgresql</code> (simplified):</p>
  <pre><code>CREATE TABLE items(
      id bigint NOT NULL DEFAULT nextval('item_id_seq') PRIMARY KEY,
      name varchar(40) not null,
      name_search tsvector GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED
  );
  </code></pre>
  <p>Using <code>C#</code> as the programming language I would like to make a full text search on <code>name_search</code> field. Without full ORM like <code>EntifyFramework</code>.</p>
  <p><code>Dapper</code> is used and the current query/logic looks like this:</p>
  <pre><code>    public async Task&lt;IEnumerable&lt;FeedItemInDb&gt;&gt; GetItems(string searchTerm)
      {
          string searchFilter = &quot;&quot;;
          if (!string.IsNullOrEmpty(searchTerm))
          {
              string searchTermProcessed = $&quot;{searchTerm}:*&quot;;
              searchTermProcessed = searchTermProcessed.Replace(&quot; &quot;, &quot; &amp; &quot;);
  
              searchFilter = $&quot;AND i.name_search @@ to_tsquery('simple', '{searchTermProcessed}')&quot;;
          }
  
          var results = await uow.Connection.QueryAsync&lt;FeedItemInDb&gt;($@&quot;select i.* FROM items i WHERE 1=1 {searchFilter}&quot;, new
          {
              // params here
          });
  
          return results;
      }
  </code></pre>
  <p><strong>It works OK for very trivial cases.</strong> For example search string <code>my test</code> is sanitized to <code>my &amp; test:*</code></p>
  <p><strong>There is one main flaw with this approach</strong> - you have to know in advance all the sanitization rules required for query! As an example, following raw input <code>my :*test</code> ends up with exception:</p>
  <blockquote>
  <p>Npgsql.PostgresException: 42601: syntax error in tsquery: &quot;my &amp; :<em>test:</em>&quot;</p>
  </blockquote>
  <p>I was wondering if there is some kind of package or rules or code in <code>Dapper</code> that would do all of the required sanitization work for me? Similarly to how we can parametrize other values in query...</p>
  <p>Normally, using <code>Dapper</code>, I would expect the code to look like this:</p>
  <pre><code>    public async Task&lt;IEnumerable&lt;FeedItemInDb&gt;&gt; GetItems(string searchTerm)
      {
  
          var results = await uow.Connection.QueryAsync&lt;FeedItemInDb&gt;($@&quot;select i.* FROM items i
      WHERE i.name_search @@ to_tsquery('simple', '@SearchParam:*')&quot;, new
          {
              SearchParam = searchTerm
          });
  
          return results;
      }
  </code></pre>
  <p>But it does not return any results, unfortunately. Nor with <code>to_tsquery('simple', '@SearchParam')</code>.</p>
  <p>In general I just want to know how to solve this problem. How to sanitize the string for the full text search using <code>Dapper</code>. If the user starts passing <code>:,.%&amp;*</code> in a query I would expect my code to start failing if left as is.. Should I maybe prohibit/filter out all special characters from  user input? The problem is I have no idea what I should be filtering out..</p>
  <p><strong>EDIT:</strong>
  <code>to_tsquery('simple', @SearchParam)</code> does seem to work, if I format the search string manually before the search.. So essentially I have the same problem as before. If the string is not properly formatted then <code>sql</code> exception is thrown.. Therefore you have to know and apply in advance all formatting/sanitization rules so the query would not fail. So I still have the same question on how to deal with this situation.</p>