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.

Answer 1

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))

Answer 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();
....

Answer 3

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.

Answer 4

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>'

Answer 5

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.