Correct String Escaping for T-SQL string literals

Question

I want to use a query as following, I am looking for exact information/link to escape strings

BookTitle is NVARCHAR(200)

SELECT * FROM Books WHERE BookTitle IN ('Mars and Venus', 'Stack''s Overflow \r\n')

Question: Does only "'" needs to be escaped or even \r\n needs to be escaped as well? MySql .Net Provider exposes a method to escape string values, is there any such function in Sql Server .Net Provider?

I probably need C# equivalent escaping for string values.

I am aware of Parameterized Command, but in order to minimize my server to client communication, and my values in IN clause are in number from 20 to 50, it becomes too much network expensive to run SELECTs for each value of BookTitle in one call. Rather running single query and returning all results cascaded helps saving network resources.

This is a tangential comment, but still important. If you're doing this for your own queries in management studio, that's fine. But if you're doing this programmatically — building your query string and escaping in a client-side program — you're doing it wrong. You need a parameterized query. — Joel Coehoorn
– Joel Coehoorn, Commented Aug 12, 2009 at 15:25
Your suggestion that you would need to run 1 select for each value of the parameter is incorrect. Use a Table-Valued-Parameter OR XML OR use a comma-delimited-string and unpack it on the SQL Server side. That will be one query. sommarskog.se/arrays-in-sql-2008.html — Davos
– Davos, Commented Feb 18, 2014 at 1:46

LukeH · Accepted Answer · 2009-08-13 00:15:58Z

5

SQL Server won't recognise the \r\n sequence, whether it's escaped or not.

You'll need to do something like this instead if you want to match the \r\n in BookTitle:

-- \r = CHAR(13)
-- \n = CHAR(10)
SELECT *
FROM Books
WHERE BookTitle IN ('Mars and Venus', 'Stack''s Overflow ' + CHAR(13) + CHAR(10))

edited Aug 13, 2009 at 0:15

answered Aug 12, 2009 at 15:23

LukeH

271k59 gold badges373 silver badges411 bronze badges

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

2 Comments

Akash Kava Over a year ago

Yes I get that, but I need to do this in C# dynamically for each type, like I would need an escape sequence that I can use string.replace for all such values and prepare final query.

LukeH Over a year ago

In that case, don't use string escaping. Use a parameterised query instead.

Daniil · Accepted Answer · 2009-08-12 15:24:21Z

2

There are more things that have to be escaped than just quotes or new line characters. What if there's a binary input (by hacker)? Better use PreparedStatement (in java) or any other equivalent in the target language. Java sample:

PreparedStatement ps = con.prepareStatement("SELECT * FROM Books WHERE BookTitle IN (?, ?)");
ps.setString(1, "Mars and Venus");
ps.setString(2, "Stack's Overflow
and 
");

ResultSet rs = ps.executeQuery();
....

answered Aug 12, 2009 at 15:24

Daniil

1,3882 gold badges19 silver badges30 bronze badges

1 Comment

Akash Kava Over a year ago

Although this is not complete answer, but yes I tried similar thing in c# with little workaround and its acceptable for now.

Cyberherbalist · Accepted Answer · 2009-08-12 17:43:10Z

I've run into a similar problem, where I needed to have a IN in my select query, and the number of elements varied at run time.

I use a parameterized query in the form of a stored procedure and pass in a delimited string containing the list of things I'm looking for. The escaping is automatically handled by the system, no need to take extraordinary steps. Better not make it delimited by characters that will be found in the text you're searching (like commas). a vertical bar ("|") would probably work best in many cases.

By the way, make sure the CRLFs in your table are CHAR(13)+CHAR(10) because the opposite way around isn't \r\n and you wouldn't find it if Environment.NewLine was part of your search.

Here's a stored procedure using a quick and dirty parse resolving to a table that I have used:

CREATE PROCEDURE FindBooks
(
    @list varchar(500)
)
AS

CREATE TABLE #parse_table (item varchar(500))

DECLARE @temp                       VARCHAR(500)
DECLARE @result                     VARCHAR(500)
DECLARE @str                        VARCHAR(500)
DECLARE @pos                        SMALLINT

SET @temp = RTRIM(LTRIM(@list))
SET @pos = 1

WHILE @pos > 0
    BEGIN
    SET @pos = CHARINDEX('|',@temp)
    IF @pos > 0 
       BEGIN
         SET @result = SUBSTRING(@temp,1,@pos - 1)
         SET @temp = RTRIM(LTRIM(SUBSTRING(@temp,@pos+1,LEN(@temp) - @pos)))

         INSERT INTO #parse_table
     SELECT @result
       END
    ELSE
       INSERT INTO #parse_table
       SELECT @temp
END

SELECT * FROM Books WHERE Title in (select * from #parse_table)

Simply create your list of book titles as a simple string (containing whatever embedded apostrophes, CRLFs, and so on) and use a parameterized query. Of course, your stored proc can contain other things besides the delimited list.

Bryant Bowman · Accepted Answer · 2009-08-12 15:39:26Z

0

You could use table value parameters to pass in the the values for your IN statement. If you are not using a new enough version of visual studio and/or sql server to have access to table value parameters, you can instead pass in one comma separated list as string parameter, and then parse the the parameter into a table. There are several methods to split strings into a temp table/table variable. You can google "split function in sql server" for several options.

answered Aug 12, 2009 at 15:39

Bryant Bowman

4231 gold badge5 silver badges8 bronze badges

Comments

John · Accepted Answer · 2009-08-12 15:45:54Z

0

Usually what I'd do for situations like this, is pass your information in as a parameter, but in XML, so you can do something like this:

DECLARE @iDoc INT
EXEC sp_xml_preparedocument @iDoc OUTPUT, @MyXml

SELECT
    *
FROM
    MyTable
WHERE
    MyColumn IN (SELECT [Id] FROM OPENXML(@iDoc,'/ss/s',2) WITH ([Id] INT '.'))

EXEC sp_xml_removedocument @iDoc

in this case, the xml would look like '<ss><s>1</s><s>2</s>...etc...</ss>'

answered Aug 12, 2009 at 15:45

John

17.6k17 gold badges70 silver badges87 bronze badges

2 Comments

Cyberherbalist Over a year ago

A CRLF wouldn't survive the xml-processing, would it? Since it's treated as whitespace you'd have to escape it somehow. How do you escape a CRLF in Xml anyway? I guess with CDATA, but how would that play out in the query?

John Over a year ago

I don't believe whitespace in general needs to be escaped in XML; it's just what it is

Collectives™ on Stack Overflow

Correct String Escaping for T-SQL string literals

5 Answers 5

2 Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related