How to escape html string for SQL using C#

Question

I am trying to generate SQL using C# but, it failed to execute on server. There are html inputs in string which causing syntax error.

I have tried to replace single quote(') with twice ('') but it also failed for few inputs like (\\\\'').

Error example:

Actual Input String

'It\'s compact design with stable signal'

Modified SQL Input String

'It\''s compact design with stable signal'

Erroneous SQL

UPDATE products SET LongDescription = 'It\''s compact design with stable signal';

    private static void makeSQLThread(List<Product> products, ref List<string> sqlList)
    {
        foreach (var item in products)
        {
            var colorId = item.ColorId;
            var description = item.Description.Replace("'", "''"); // html input
            var longDescription = item.LongDescription.Replace("'", "''"); // long html input
            var isAvailable = item.isAvailable ? 1 : 0;

            var formatSQl = string.Format("UPDATE products SET ColorId = {0}, Description = '{1}', LongDescription = '{2}', isAvailable = {3} WHERE Id = {4};",
              colorId, description, longDescription, isAvailable, item.Id);

            Console.WriteLine(formatSQl);

            sqlList.Add(formatSQl);
        }
    }

I am saving all these queries in text file and executing on server. How can I do it in safe and better way. I can't use C# commands like SQLCommand etc right now.

Thanks

Linq2SQL or EntityFramework would be my answer (I understand you can't because of `I am saving all these queries in text file and executing on server` but that seems like very bad practice anyway, maybe run a console program as service on the server and have it fetch "jobs" from t he database, that way you don't have to use sql queries and can use Linq2SQL/EF ). And if you want to know whats wrong with your query maybe post the actual query so people know whats wrong — EpicKip, Dec 20 '17 at 12:24
You can use parameterized queries instead of string formatting. And then use this approach: https://stackoverflow.com/a/265261/887149 — Mohayemin, Dec 20 '17 at 12:32
Saving the queries is like a dump tool which make .SQL or text file, I am doing it on local server DB then executing it on Live Server. I know its pitiful but some limitations. :) — Shoaib Ijaz, Dec 20 '17 at 12:34
You don't need to escape anything. DON'T concatenate strings, use parameterized queries. What you posted here is your code, not a dump. DON'T write `string.Format("UPDATE products SET ColorId = {0} ...`. Write `SET ColorId = @colorID` ... and add the parameters. It's actually easier than what you try to do here. Or use a microORM like Dapper to write `var count = connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",new { a=1, b=1 })`. It even works with lists, arrays, so you could write `var count = connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)", myABlist );` — Panagiotis Kanavos, Dec 20 '17 at 12:58
`I can't use C# commands like SQLCommand etc right now.` Why? If you can't use correct/secure C# code, don't use it at all. Export the data to a CSV file with some rare field, line delimiters like §,¦ or ¤ and import the data using the Import Wizard or SSIS. It will be equally safe and probably a lot faster — Panagiotis Kanavos, Dec 20 '17 at 13:03
Why do you say your outpoint is erroneous? It's exactly correct based on your input string. Are you referring to the backslash (\)? What's the actual syntax error you say you are getting and where are you getting it? — Jim Berg, Jul 11 '18 at 16:42

Jim Berg · Answer 1 · 2018-07-11T18:33:52.240

If you're doing a batch update as you seem to want to do, it is much better to do it the way you are planning than to use SQL parameters. It's super slow to make multiple round trips to the server. I recently had to do a batch update of data where I used SQL parameters at first and it took 90 minutes just to do the updates because there were thousands of records. (Writing to Azure DB over 70mbit internet connection). By generating as a single SQL command, as you're trying to do, I was able to write the same exact data in less than 30 seconds.

Parameters are definitely the preferred way if you're doing a small number of updates and inserts, especially if the data you're writing is strings entered by users.

As stated in your question, you cannot use SqlCommand to execute your update command so you can't use SQL parameters. I've made some modifications to your method that should work and added a new method to escape your string. It will also handle NULLs and empty strings which your code will not.

public static string SStr(string s)
{
    return string.IsNullOrEmpty(s) ? "NULL" : "N'" + s.Replace("'","''") + "'";
}

private static void makeSQLThread(List<Product> products, List<string> sqlList)
{
    foreach (var item in products)
    {
        var formatSQl = $"UPDATE products SET ColorId = {item.ColorId}, Description = {SStr(item.Description)}, LongDescription = {item.LongDescription}, isAvailable = {item.isAvailable ? 1 : 0} WHERE Id = {item.Id};";

        Console.WriteLine(formatSQl);

        sqlList.Add(formatSQl);
    }
}

You do not need to pass sqlList by reference because you're adding to it. If you added a value to products, you will actually change the contents of the list you called the method with. The difference is that if you added products = new List<product>(); within your makeSQLThread method, the products object you sent to the method would not be overwritten. If you added sqlList = new List<string>(); the sqlList object you passed to the method would be overwritten.

I didn't realize how long ago that this was asked, but it still deserved an answer better than what was offered in the comments. Just so you web developers out there know, some people are visually impaired and using gray text on a white background can be very difficult for some to see (as in the asked date). I wish web sites offered the ability to select a high contrast color palette.

How to escape html string for SQL using C#

1 Answers1