2

I'm getting the exception:

Error in Like operator: the string pattern 'Blah [Blah Blah]' is invalid.

It's occurring where I'm using a datatable's Select method like so:

DataRow[] rows = table.Select("X Like '" + y + "'");

I've seen from reading other posts that it's the square brackets. The posts I've read say to escape the squares brackets but most of them focus on the source of the problem being a hard coded string the developer can easily escape.

I'm reading the data in from a database. I could do something like y.replace("[", "\\[") which I'm guessing would fix the issue for square brackets. But then I'm wondering, what if elsewhere in the DB there is a string with an * etc.

How do others deal with this? Can I call something that will make a string safe by escaping all possibly illegal characters or is it a case of manually replacing them with escaped equivalents?

Dan Is Fiddling By Firelight
  • 5,981
  • 17
  • 77
  • 128
Kate
  • 1,556
  • 1
  • 16
  • 33
  • 1
    A [parameterized query](http://stackoverflow.com/q/5468425/464709) would solve this problem as well as other, way more serious ones. – Frédéric Hamidi Oct 28 '13 at 13:48
  • Good point, although I didn't fully explain. The data has previously been read from the Db (using a data reader and populating domain objects). For this particular task (providing data for a third party chart) it needs to be in a datatable so the datatable is constructed in memory and populated from the domain objects properties (via a helper class). The select is being done during this process: it does a select to see if there is already a row in the datatable for a given item oneach iteration through the list. Is there another way to do the select that is more parameterised? – Kate Oct 28 '13 at 13:56
  • I see. To my knowledge, there is no way to pass query parameters to `DataTable.Select()` (which is in itself reason enough to avoid this method like the plague IMHO). If you cannot use `DISTINCT` or similar to eliminate duplicates before populating the `DataTable`, then I'm afraid you will indeed be stuck with unsafe, problematic concatenations. – Frédéric Hamidi Oct 28 '13 at 14:02
  • Hi Frédéric, thanks for your advice anyway: I'll take step back and see if I can find a way to avoid DataTable.Select(). – Kate Oct 28 '13 at 14:15
  • why are people worried about sql injection attacks here, its a simple DataTable object with data in-memory, i can do anything with its Select and nothing can harm the actual db, or i am missing something very imp here? – user734028 Aug 20 '21 at 06:27

1 Answers1

2

In case someone else comes across this, here is the solution I went with. Echoing Frédéric's suggestion above, Jon Skeets solution here Escaping special character in string in C# was also to use something other than DataTable.Select() and so I used his LINQ suggestion.

However, I also came across this answer Correct way to escape characters in a DataTable Filter Expression which does give some code that could be re-used if you did want to implement a way of escaping most possible illegal characters. However, because you don't know what character might be illegal under all conditions I prefered a solution that didn't rely on this technique.

Community
  • 1
  • 1
Kate
  • 1,556
  • 1
  • 16
  • 33