Escaping a Single Quotation Within SQL Query

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.

Escape single quote in openquery using dynamic query

You need single quotes around your variables since you are trying to make them string literals. But also complicating it is the fact that you are trying to create a SQL statement in a string that includes another SQL statement in a string. So you need to make your line read like:

And cases.code IN (''''' + @A +''''', ''''' + @B + ''''')

You need two sets of double quotes so that the string literal inside of your string literal is interpreted correctly. Huh? Right. :)

Ultimately you need to build a string that has this valid SQL Syntax in it:

Select * From Openquery(LinkedServerName,'SELECT cases.casenum, user.username, code
            From cases
            Inner join user
            On cases.casenum = user.user_id
            Where cases.date_opened > DateAdd(day,1-datepart(dw,Convert(date,20150501)), Convert(date,20150501))
            And cases.date_opened <= DateAdd(day,8-datepart(dw,Convert(date,20150501)), Convert(date,20150501))
            And cases.code IN (''AAA'', ''BBB'')
            ORDER BY casenum')

You need two quotes around the AAA and BBB in your inner SQL string because it is also SQL code inside a string. So you need double double quotes to get double quotes inside your main string you are building.

How to escape single quotes within single quoted strings

If you really want to use single quotes in the outermost layer, remember that you can glue both kinds of quotation. Example:

 alias rxvt='urxvt -fg '"'"'#111111'"'"' -bg '"'"'#111111'"'"
 #                     ^^^^^       ^^^^^     ^^^^^       ^^^^
 #                     12345       12345     12345       1234

Explanation of how '"'"' is interpreted as just ':

1. ' End first quotation which uses single quotes.
2. " Start second quotation, using double-quotes.
3. ' Quoted character.
4. " End second quotation, using double-quotes.
5. ' Start third quotation, using single quotes.

If you do not place any whitespaces between (1) and (2), or between (4) and (5), the shell will interpret that string as a one long word.

---

Related Topics