Correct row deletion for T-SQL string literals

Question

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

BookTitle - NVARCHAR (200)

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

Question: Do I need to avoid only "", or do I even need to avoid \ r \ n? The MySql.Net provider provides a method for excluding string values, is there such a function in the Sql Server.Net Provider?

I probably need equivalent C # escaping for string values.

I am aware of a parameterized command, but in order to minimize the connection of my server with the client, and my values ​​in the IN section are from 20 to 50, it becomes too expensive to run SELECT for each BookTitle value in one call. Most likely, starting a single query and returning all cascading results helps save network resources.

Answer 1

There are more things to avoid than just quotation marks or newline characters. What if there is a binary input (by a hacker)? It is better to use PreparedStatement (in java) or any other equivalent in the target language. Java example:

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

Answer 2

SQL Server will not recognize the sequence \r\n , regardless of whether it was escaped or not.

You will need to do something like this if you want to map \r\n in BookTitle :

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

Answer 3

I had a similar problem when I needed to have an IN in my select query, and the number of elements changed at runtime.

I use a parameterized query in the form of a stored procedure and pass a delimited string containing a list of the things I'm looking for. The crew is automatically processed by the system, there is no need to take unusual steps. It’s better not to limit it to characters that will be found in the text you are looking for (for example, commas). the vertical bar ("|") is likely to work best in many cases.

By the way, make sure that the CRLF in your table is CHAR (13) + CHAR (10), because the opposite path is not \ r \ n, and you will not find it if it is environment. NewLine was part of your search.

This is where the procedure is stored using quick and dirty analysis allowing the use of the table I 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 all the built-in apostrophes, CRLF, etc.) and use a parameterized query. Of course, your saved proc may contain other things besides a delimited list.

Answer 4

You can use table value parameters to pass values ​​for your IN statement. If you are not using a sufficiently new version of visual studio and / or sql server to access the table's value parameters, instead you can pass a single comma-delimited list as a string parameter and then parse this parameter in the table. There are several ways to split the rows into the temp table / table variable. You can split the google function on sql server for several parameters.

Answer 5

Usually, what I would do for such situations passes your information 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 will look like '<ss><s>1</s><s>2</s>...etc...</ss>'