none
Escaping "curly apostrophe" for a sql statement

    Question

  • Hi,

        I'm trying to escape the curly apostrophe for a sql statement, i.e., Bob’s . I tried using the following

    string str = inputStr.Replace("'","''");

    Of course, this doesn't work.

     

    Thanks!

     

    Saturday, January 29, 2011 5:59 PM

Answers

  • If the apostrophe is curly, then you need not escape it, since will be different from a regular apostrophe.

    As an aside, it is recommended, for several reasons, to parameterize queries insted of just constructing the SQL code. You don't have to escape things, you are protected from SQL Injection and you also use the plan cache better.


    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, February 25, 2011 8:00 AM
    Moderator
  • >       Thanks for your fast replies. However, I need the apostrophe in the query. I need to search for O'Hara. The problem is when you type in an apostrophe in MS Word (curly) and Notepad (not curly), it renders differently. I believe this has something to do with the encoding. I also need to escape in the SQL query otherwise it will break the SQL statement.

    Stop!

    This here is awfully wrong:

    string inputFirstName = inputBox.Text.Replace("'","''");
    string SQLString = "SELECT *" + " FROM Employees" + " where firstName = '" + inputFirstName + "'";

    Never write code like this. The code should look like this:

    string SQLString = "SELECT * FROM dbo.Employees where firstName = @firstname"
    cmd.CommandText = SQLString
    cmd.Parameters.Add("@firstname", SqlDbTypes.VarChar, 30)
    cmd.Parameters("@firstname").Value = intputFirstName

    Do you write code for your own leisure? In the case, the above may pass. But if you write code for a living, as an employee or consultant, inlining parameter values is highly irresponsible, and I am sorry to say, but it is simply not acceptable for a professional to do.

    You may still have to help the user if he types a different character that what's in the database. But that has nothing to do with escaping.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Friday, February 25, 2011 8:23 AM

All replies

  • Hi,
       I'm taking an input from a textbox and using it in a SQL statement. However, the user is copying a name with a curly apostrophe from Microsoft Word. The apostrophe is "curly." For example, the user copys "O’Hara." I'm trying to escape with the Replace function, however this doesn't work.


    string inputFirstName = inputBox.Text.Replace("'","''");
    string SQLString = "SELECT *" + " FROM Employees" + " where firstName = '" + inputFirstName + "'";


    Thanks!

    Saturday, January 29, 2011 6:20 PM
  •     static string ReplaceString()
        {
          string input = "O'Hara";
          return input.Replace("'", String.Empty);
        }
    

    Mark the best replies as answers. "Fooling computers since 1971."

    http://rudedog2.spaces.live.com/default.aspx

    Saturday, January 29, 2011 6:39 PM
  • Hi Rudedog2,

          Thanks for your fast replies. However, I need the apostrophe in the query. I need to search for O'Hara. The problem is when you type in an apostrophe in MS Word (curly) and Notepad (not curly), it renders differently. I believe this has something to do with the encoding. I also need to escape in the SQL query otherwise it will break the SQL statement.

     

    Thanks! 

    Saturday, January 29, 2011 9:28 PM
  • Replace has 2 overloads.  One uses 2 strings while the other uses 2 char parameters.  Run this code on text pasted from each application.

     

            static string ReplaceString()

            {

                string input = "O'Hara";

                foreach ( Char c in input )

                {

                    Console.WriteLine(c.ToString() + " = " + Convert.ToInt32(c).ToString());

                }

                return input.Replace("'", String.Empty);

            }

     

    Compare the values returned for the character.  Then use the char value from Word and replace it with the char value from Notepad in the other overload that takes 2 char parameters.

    Are you using a TextBox or a RichTextBox?


    Mark the best replies as answers. "Fooling computers since 1971."

    http://rudedog2.spaces.live.com/default.aspx

    Saturday, January 29, 2011 10:24 PM
  • Hi MorphByte,

    You need escape the single quotes in sql query, then you can reference the following solution:

    http://it.toolbox.com/wiki/index.php/How_do_I_escape_single_quotes_in_SQL_queries%3F

     

    I think the sql forum is suitable for such question, though you're using the sql statement in winForm application in C# language.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

     

    Best wishes,


    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 25, 2011 5:15 AM
  • If the apostrophe is curly, then you need not escape it, since will be different from a regular apostrophe.

    As an aside, it is recommended, for several reasons, to parameterize queries insted of just constructing the SQL code. You don't have to escape things, you are protected from SQL Injection and you also use the plan cache better.


    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, February 25, 2011 8:00 AM
    Moderator
  • >       Thanks for your fast replies. However, I need the apostrophe in the query. I need to search for O'Hara. The problem is when you type in an apostrophe in MS Word (curly) and Notepad (not curly), it renders differently. I believe this has something to do with the encoding. I also need to escape in the SQL query otherwise it will break the SQL statement.

    Stop!

    This here is awfully wrong:

    string inputFirstName = inputBox.Text.Replace("'","''");
    string SQLString = "SELECT *" + " FROM Employees" + " where firstName = '" + inputFirstName + "'";

    Never write code like this. The code should look like this:

    string SQLString = "SELECT * FROM dbo.Employees where firstName = @firstname"
    cmd.CommandText = SQLString
    cmd.Parameters.Add("@firstname", SqlDbTypes.VarChar, 30)
    cmd.Parameters("@firstname").Value = intputFirstName

    Do you write code for your own leisure? In the case, the above may pass. But if you write code for a living, as an employee or consultant, inlining parameter values is highly irresponsible, and I am sorry to say, but it is simply not acceptable for a professional to do.

    You may still have to help the user if he types a different character that what's in the database. But that has nothing to do with escaping.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Friday, February 25, 2011 8:23 AM
  • Take a research on SQL injection attack. Here is a thread on ASP.NET forum that may help you to understand the danger of not using parameters.

    Avoid SQL injection attacks


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, February 27, 2011 6:10 AM
    Moderator
  • How would you do an IN operation with parameters?
    Thursday, May 05, 2011 3:45 PM
  • For the IN operation with parameters there are many approaches available and the right approach may depend on the SQL Server version you're using (in SQL Server 2008 you can pass table valued parameters to a stored procedure).

    Take a look at this article to learn more

    Arrays & Lists in SQL Server
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, May 05, 2011 3:50 PM
    Moderator