0

In our C# desktop-application we generate a lot of dynamic sql-queries. Now we have some troubles with single quotes in strings. Here's a sample:

INSERT INTO Addresses (CompanyName) VALUES ('Thomas' Imbiss')

My question is: How can I find and replace all single quotes between 2 other single quotes in a string? Unfortunately I can't replace the single quotes when creating the different queries. I can only do that after the full query is created and right before the query gets executed.

I tried this pattern (Regular Expressions): "\w\'\w"

But this pattern doesn't work, because after "s'" there's a space instead of a char.

Improve this question
10
  • 4
    I guess you have much more complicated queries than this one. There is no 100% safe approach here, I am afraid. Unless you find a pattern (in words) that will work for you here, it will be very difficult to come up with any suggestions. Commented Sep 14, 2015 at 9:16
  • 1
    You can use Double quote instead of single quote for giving value. You can use above query like this: INSERT INTO Addresses (CompanyName) VALUES ("Thomas' Imbiss") Commented Sep 14, 2015 at 9:22
  • i would evaluate the costs: your time spent tuning the pattern matching expression following all the cases that will happen and patching all the errors (should you ever catch all of them...) vs using parameters in dynamic sql vs rewriting the application one component/class a time to use stored procedures. Commented Sep 14, 2015 at 9:23
  • If you know what the values are while you're building your strings, a simple myValue.Replace("'", "''") will work (double up single quotes to escape them). Obviously this then needs to be inserted into the query separately, or the outer quotes will get converted too... Commented Sep 14, 2015 at 9:25
  • @stribizhev Sure, this was just an example. Oh, i thought this should be possible with Regex. Commented Sep 14, 2015 at 9:28

1 Answer 1

Reset to default
2

I am sorry to say, there is no solution in approach you expect.

For example, have these columns and values:

  • column A, value ,A',
  • column B, value ,B',

If they are together in column list, you have ',A',',',B','.

Now, where is the boundary between first and second value? It is ambiguous.

You must take action when creating text fields for SQL. Either use SQL parameters or properly escape qoutes and other problematic characters there.

Consider showing the above ambiguous example to managers, pushing the whole problem back as algorithmically unsolvable at your end. Or offer implementing a guess-work and ask them whether they will be happy if content of several text fields can get mixed in some cases like above one.

At time of SQL query creation, if they do not want to start using SQL parameters, the solution for enquoting any input string is as simple as replacing:

string Enquote(string input) { return input.All(c => Strings.AscW(c) < 128) ? "'" : "N'" + input.Replace("'", "''") + "'" }

Of course, it can have problem with deliberately malformed Unicode strings (surrogate pairs to hide ') but it is not normally possible to produce these strings through the user interface. Generally this can be still faster than converting all queries to versions with SQL parameters.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I agree. The more time I work on this problem and think about it, the more problems I am discovering. Thanks for your help!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.