https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292021-04-12T06:55:01ZRedmineRedmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1019472021-04-12T06:55:01ZGo MAEDA
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-1 priority-4 priority-default" href="/issues/13347">Feature #13347</a>: Filtering by issue subject with wildcard</i> added</li></ul> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1019492021-04-12T06:55:17ZGo MAEDA
<ul><li><strong>Related to</strong> <i><a class="issue tracker-1 status-5 priority-4 priority-default closed" href="/issues/19786">Defect #19786</a>: '%' and '_' are treated as SQL wildcards in issue filter</i> added</li></ul> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1024002021-05-12T04:20:40ZGo MAEDA
<ul></ul><p>I actually find the current behavior useful. For example, when I search for photos using the "File" filter, I use the query string "dsc%.jpg".</p>
<p>If Redmine prohibits the inclusion of "_" or "%" in the query string, then I want some alternative.</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1031562021-07-07T06:46:51ZJens Krämerjk@jkraemer.net
<ul><li><strong>File</strong> <a href="/attachments/27715">0001-tokenize-query-strings-for-Issue.like-and-Query-sql_.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/27715/0001-tokenize-query-strings-for-Issue.like-and-Query-sql_.patch">0001-tokenize-query-strings-for-Issue.like-and-Query-sql_.patch</a> added</li></ul><p>In general I believe these SQL wild cards are nowhere documented in the Redmine context (please correct me if I'm wrong), and therefore more or less a 'hidden feature' for power users who are familiar with SQL. Given the potential of abuse (and the current inability to search for terms that actually contain these wildcards), I still believe it is the right way to not expose such a low level database feature.</p>
<p>However I get your point and in fact I am right now revisiting this because some Planio power users do miss the SQL wild cards as well :)</p>
<p>The alternative I am experimenting with right now is breaking up the user's query string into tokens like the global search does, and use these to build a query with multiple <code>LIKE</code> clauses that are combined with <code>AND</code>. I'm attaching a preliminary patch that implements this for the <code>Issue.like</code> scope (used by the autocompleter) and the <code>Query#sql_contains</code> method (which should cover all query filters). Do you think that's a viable approach?</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1032882021-07-23T07:26:48ZGo MAEDA
<ul></ul><p>Jens Krämer wrote:</p>
<blockquote>
<p>In general I believe these SQL wild cards are nowhere documented in the Redmine context (please correct me if I'm wrong), and therefore more or less a 'hidden feature' for power users who are familiar with SQL. Given the potential of abuse (and the current inability to search for terms that actually contain these wildcards), I still believe it is the right way to not expose such a low level database feature.</p>
</blockquote>
<p>I agree with you. Actually, I opened <a class="issue tracker-2 status-1 priority-4 priority-default" title="Feature: Filtering by issue subject with wildcard (New)" href="https://www.redmine.org/issues/13347">#13347</a> that reports about it 6 years ago.</p>
<blockquote>
<p>The alternative I am experimenting with right now is breaking up the user's query string into tokens like the global search does, and use these to build a query with multiple <code>LIKE</code> clauses that are combined with <code>AND</code>. I'm attaching a preliminary patch that implements this for the <code>Issue.like</code> scope (used by the autocompleter) and the <code>Query#sql_contains</code> method (which should cover all query filters). Do you think that's a viable approach?</p>
</blockquote>
<p>I tried out the patch and found it is really useful. I think it is a great improvement to be able to do OR searches with multiple keywords, which is currently not possible with the current filters. Although it is a breaking change, it is worth more than that.</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1034132021-07-29T04:59:56ZGo MAEDA
<ul><li><strong>Target version</strong> set to <i>Candidate for next major release</i></li></ul> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1037412021-08-15T05:23:00ZGo MAEDA
<ul><li><strong>Blocks</strong> <i><a class="issue tracker-2 status-5 priority-4 priority-default closed" href="/issues/35764">Feature #35764</a>: Multiple search terms in the "contains" operator of text filters</i> added</li></ul> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1037452021-08-15T08:11:42ZMarius BĂLTEANU
<ul><li><strong>Target version</strong> changed from <i>Candidate for next major release</i> to <i>5.0.0</i></li></ul> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1037572021-08-15T11:44:52ZGo MAEDA
<ul></ul><p>Jens Krämer wrote:</p>
<blockquote>
<p>The alternative I am experimenting with right now is breaking up the user's query string into tokens like the global search does, and use these to build a query with multiple <code>LIKE</code> clauses that are combined with <code>AND</code>. I'm attaching a preliminary patch that implements this for the <code>Issue.like</code> scope (used by the autocompleter) and the <code>Query#sql_contains</code> method (which should cover all query filters). Do you think that's a viable approach?</p>
</blockquote>
<p>I have extracted attachment:0001-tokenize-query-strings-for-Issue.like-and-Query-sql_.patch to a separete issue <a class="issue tracker-2 status-5 priority-4 priority-default closed" title="Feature: Multiple search terms in the "contains" operator of text filters (Closed)" href="https://www.redmine.org/issues/35764">#35764</a>, because it adds a new feature but others fix undesirable behavior as described in the subject.</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1042492021-10-03T19:50:09ZMarius BĂLTEANU
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Closed</i></li><li><strong>Assignee</strong> set to <i>Marius BĂLTEANU</i></li></ul><p>All five patches committed, thanks.</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1042632021-10-05T07:49:38ZGo MAEDA
<ul><li><strong>Status</strong> changed from <i>Closed</i> to <i>Reopened</i></li></ul><p>After applying the patch, some tests fail when the backend database is SQLite. I will post a fix soon.</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1042762021-10-06T14:56:04ZGo MAEDA
<ul><li><strong>File</strong> <a href="/attachments/28131">35073-sqlite-fix.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/28131/35073-sqlite-fix.patch">35073-sqlite-fix.patch</a> added</li></ul><p>Go MAEDA wrote:</p>
<blockquote>
<p>After applying the patch, some tests fail when the backend database is SQLite. I will post a fix soon.</p>
</blockquote>
<p>I am attaching a patch that fixes the issue. There is no default escape character in SQLite, so it must be specified explicitly using an ESCAPE clause.</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1042792021-10-06T15:06:35ZMarius BĂLTEANU
<ul></ul><p>Go MAEDA wrote:</p>
<blockquote>
<p>Go MAEDA wrote:</p>
<blockquote>
<p>After applying the patch, some tests fail when the backend database is SQLite. I will post a fix soon.</p>
</blockquote>
<p>I am attaching a patch that fixes the issue. There is no default escape character in SQLite, so it must be specified explicitly using an ESCAPE clause.</p>
</blockquote>
<p>I don't have a local environment with SQLite to test it. Is it ok to commit the patch directly?</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1042822021-10-07T14:20:30ZGo MAEDA
<ul><li><strong>File</strong> <a href="/attachments/28138">35073-sqlite-fix.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/28138/35073-sqlite-fix.patch">35073-sqlite-fix.patch</a> added</li></ul><p>Updated the fix for SQLite. The previous patch fails when the database is MS SQL Server.</p>
<p>Marius BALTEANU wrote:</p>
<blockquote>
<p>I don't have a local environment with SQLite to test it. Is it ok to commit the patch directly?</p>
</blockquote>
<p>Yes, it can be committed as is. The new patch was tested with all supported databases (PostgreSQL, MySQL, SQLite, and MS SQL Server).</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1042952021-10-09T11:12:03ZMarius BĂLTEANU
<ul><li><strong>Subject</strong> changed from <i>escape values in LIKE statements to prevent injection of placeholders (_ or %)</i> to <i>Escape values in LIKE statements to prevent injection of placeholders (_ or %)</i></li><li><strong>Status</strong> changed from <i>Reopened</i> to <i>Closed</i></li></ul><p>All tests passed now.</p> Redmine - Feature #35073: Escape values in LIKE statements to prevent injection of placeholders (_ or %)https://www.redmine.org/issues/35073?journal_id=1061662022-03-27T03:00:00ZGo MAEDA
<ul><li><strong>Tracker</strong> changed from <i>Patch</i> to <i>Feature</i></li></ul>