How to Escape a Single Quote in SQL Server

How do I escape a single quote in SQL Server?

Single quotes are escaped by doubling them up, just as you've shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008:

DECLARE @my_table TABLE (
    [value] VARCHAR(200)
)

INSERT INTO @my_table VALUES ('hi, my name''s tim.')

SELECT * FROM @my_table

Results

value
==================
hi, my name's tim.

Escaping single quote in SQL Server

A word of advice. When testing a dynamic script, first just display it instead of executing it. That way you will be able to see it exactly as it would be seen by the EXEC statement.

Now to the issue. You should keep in mind that you are not passing the variable to SplitValues but are instead concatenating the variable's value into the script. Since the value is varchar, it should be concatenated with quotation marks around it. The absence of them is the only problem really.

The quotes around the second argument, the comma, are escaped correctly in both cases. So, just use either of the methods to add the quotes around the first argument:

• repetition of the quotation mark:

  DECLARE @year varchar(max), @sql varchar(max);
  SET @year = '111,11';
  SET @sql = 'SELECT * FROM SplitValues(''' + @year + ''','','')';
  SELECT @sql;
  

• using CHAR(39):

  DECLARE @year varchar(max), @sql varchar(max);
  SET @year = '111,11';
  SET @sql = 'SELECT * FROM SplitValues(' + CHAR(39) + @year + CHAR(39) + ',' + CHAR(39) + ',' + CHAR(39) + ')';
  SELECT @sql;
  

Obviously, the first method is more compact, but, like I said, both work well, as this SQL Fiddle demo clearly shows.

Note, however, that you could easily escape this issue in the first place, if you pardon the pun. Instead of EXEC (), you could use EXEC sp_executesql, which allows you to use parameters. Here's the same script rewritten to use sp_executesql:

DECLARE @year varchar(max), @delim char(1);
SET @year = '111,11';
SET @delim = ',';
EXEC sp_executesql
  N'SELECT * FROM SplitValues(@year_param,@delim_param)',
  N'@year_param varchar(max), @delim_param char(1)',
  @year,@delim;

As you can see, no need to worry about escaping the quotes: SQL Server takes the trouble of substituting the values correctly, not you.

How do I escape a single quote in dynamic SQL

The best way is to use sp_executesql instead of EXEC and use proper parameter for the @ProductName value.

The rest of the query that can't be parameterized (the name of the table @ProductTable) will remain dynamic string concatenation.

In this case you don't need to escape anything and you are protected against SQL injection.

Something like this:

SET @Command = 
    N'INSERT INTO Products
        (Id
        ,Region
        ,Name
        ,Category
        ,CreatedBy
        ,CreatedOn)
    SELECT
        @ParamId
        ,Region
        ,@ParamProductName
        ,Category
        ,CreatedBy
        ,CreatedOn
    FROM ' + @ProductTable + N' WITH (NOLOCK)
    WHERE ID IS NOT NULL'
    ;

EXEC sp_executesql
    @Command
    ,N'@ParamId int, @ParamProductName nvarchar(255)'
    ,@ParamId = @Id
    ,@ParamProductName = @ProductName
;

Escape single quotes on the fly in T-SQL

To escape XML, you can normally just replace ' with '' with a text editor, although I would only do this if doing a small simple upload as a one-off, where I could check there are no other syntax issues. Do not do this programmatically, especially if your data is untrusted.

You should separate the code and data. This means you don't need to escape anything, as the data is not parsed as part of the code.

You can use variables or parameters for this. When calling a procedure from a client app, use a parameter passing in your XML. For example

CREATE OR ALTER PROCEDURE InsertXML
  @xmlData xml
AS

INSERT SomeTable(XmlData)
VALUES (@xmlData);

If you need to do this programmatically and want to load it from a file, you can use OPENROWSET (BULK, for example

INSERT SomeTable(XMLData)
SELECT CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK 'C:\YourXML.xml', SINGLE_BLOB) AS x;

The file needs to be on the server, not the client.

You can also load files using various tools.

How to escape from a string with single quote inside to do an insert?

SOLUTION:

I was doing the execute method with '{}'.format... but it doesnt work with inserts with such special characters, i changed to cursor.execute("INSERT INTO table VALUES (?,?,?,?,?,?,?,?,?,?)",value1, value2....) and ANY PROBLEM

Thanks anyway for the help

Escape single quote in sql query c#

Just replace single quote (') by double single quote like ''.

so your function will look like:

public SqlDataReader getpartyID(string partyName)
            {
                string query = "EXEC partyIDtoInsert'" +partyName.Replace("'", "''") + "'";
                return new DataAccessLayer().executeQuerys(query);
             }

I hope it will solve your problem. :)

---

Related Topics