T-SQL inserting a string into a text datatype column

Question

I have a T-SQL stored procedure where I need to insert the value of a parameter into a text field in a table. The value coming in via parameter is actually a query that I later execute as dynamic SQL. When I enter just a single line without any quotes or other special characters like dash (-), it works fine. But with a quote or special chars, it barfs, since the quotes and special chars throws it off.

My input parameter for the stored procedure is:

@input_query text

My value coming is this:

Select 
    t1.*, t2.name 
from 
    nyc..sellers t1 
right join 
    ark..buyers t2 on t1.id = t2.id
where 
    t1.date = 'period' 
    and t2.period between '2016-01-01' and '2016-12-31' 
    and t2.def = 'u'

When I try to execute the stored procedure like this:

DECLARE @return_value int

EXEC @return_value = [dbo].[booksellers]
     @input_query = N'Select t1.*, t2.name from nyc..sellers t1 right join ark..buyers t2 on
  t1.id = t2.id where t1.date = 'period' and t2.period between '2016-01-01' and '2016-12-31' and t2.def = 'u''

SELECT  'Return Value' = @return_value

I get following error:

INCORRECT SYNTAX NEAR 'period'

I realize it's because the quotes are not escaped and even if they are, the next error will be with the dates not being escaped and so on. But I can't mess with that input query - it needs to be inserted as is. Is there a way I can get it to work?

You know what has to be done. Just do it. single quotes in your string literal need to be doubled up. No other way around it. — Jeremy, Sep 15 '16 at 17:37
This is an example of bad architecture causing problems until it's fixed. If, for some reason, you think it's good to generate the statement in code, simply execute it from code. Don't pass it to a proc to execute it there ... there is no point. If it is in code the app dev would get the error and he would need to fix it there as he should since the statement is invalid. Also, it is not parameterized, which is just lazy, hurts performance and leads to injection vulnerability. Everything about this is just wrong. — btberry, Sep 15 '16 at 17:53
Possible duplicate of [Escape Character in SQL Server](http://stackoverflow.com/questions/5139770/escape-character-in-sql-server) — serverSentinel, Sep 15 '16 at 19:22

serverSentinel · Answer 1 · 2016-09-16T04:04:18.233

The answer is to escape your apostrophes and it has to be done if you're going to assign the text to a variable or insert into a database.

Escaping is handled by the query parser not by the processor. When you look at data that was inserted with escaped apostrophes or retrieve it from the database you get it back with single apostrophes. It does not affect your data.

If I understand you properly you're inserting that query into a table so that later you can query that table and execute those queries. Some sore of queued reporting program?

This proof of concept query proves this is possible WITH ESCAPED APOSTROPHES. I didn't have your schema so I had to change the names of the tables.
It creates the necessary tables and a mock bookstore procedure that inserts the ESCAPED input_query into a table. This is repeated 3 times with 3 different queries. Then I use a temporary table and while loop to pull those queries and execute them.

drop table t1;
create table t1 (
    name varchar( 20 ), 
    id varchar( 20 ), 
    date varchar( 20 ), 
    rid int identity primary key
)
drop table t2;
create table t2 (
    name varchar( 20 ),
    id varchar( 20 ), 
    [period] datetime, 
    def varchar( 10 ),
    rid int identity primary key
)
drop table QueryData;
create table QueryData (
    query nvarchar(max), 
    rid int identity primary key
)
go
---- drop procedure dbo.booksellers; 
go
create procedure dbo.booksellers (
    @input_query nvarchar(max)
)
as
begin
declare @return int
insert into QueryData ( query ) values ( @input_query )

select @return = SCOPE_IDENTITY()
return @return
end
go

insert  into t1 ( name, id, date ) values 
    ( 'row1', 'some', 'period' ), 
    ( 'row2', 'more', 'period' ), 
    ( 'row3', 'even', 'not period' )

insert  into t2 ( name, id, [period], def ) values 
    ( 'period1', 'some', '2016-09-15 06:00', 'u' ), 
    ( 'period2', 'more', '2016-09-15 07:00', 'u' ), 
    ( 'period3', 'less', '2017-09-15 06:00', 'u' ),
    ( 'period1', 'some', '2017-09-15 06:00', 'u' ), 
    ( 'period2', 'more', '2017-09-15 07:00', 'u' ), 
    ( 'period3', 'less', '2017-09-15 06:00', 'u' ),
    ( 'period1', 'some', '2017-09-15 06:00', 'x' ), 
    ( 'period2', 'more', '2017-09-15 07:00', 'x' ), 
    ( 'period3', 'less', '2017-09-15 06:00', 'x' )

DECLARE @return_value int
declare @input_query nvarchar(max)
set @input_query =  N'Select t1.*, t2.name from t1 t1 right join t2 t2 on
  t1.id = t2.id where t1.date = ''period'' and t2.period between ''2016-01-01'' and ''2016-12-31'' and t2.def = ''u'''

EXEC @return_value = [dbo].[booksellers]  @input_query
print 'query'
exec sp_executesql @input_query
SELECT  'Return Value' = @return_value

set @input_query = N'Select t1.*, t2.name from t1 t1 right join t2 t2 on
  t1.id = t2.id where t1.date = ''period'' and t2.period between ''2017-01-01'' and ''2017-12-31'' and t2.def = ''u'''

EXEC @return_value = [dbo].[booksellers]  @input_query
exec sp_executesql @input_query
SELECT  'Return Value' = @return_value

set @input_query = N'Select t1.*, t2.name from t1 t1 right join t2 t2 on
  t1.id = t2.id where t1.date = ''period'' and t2.period between ''2017-01-01'' and ''2017-12-31'' and t2.def = ''x'''
EXEC @return_value = [dbo].[booksellers] @input_query
exec sp_executesql @input_query
SELECT  'Return Value' = @return_value


select 'Queued Queries', * from QueryData
declare @queries table (
    queryId int, 
    rid int identity
)

insert into @queries ( queryId )
    select rid from QueryData

declare @row int
declare @queryId int
declare @queryText nvarchar(max)

select @row = max(rid) from @queries
while @row > 0
begin
    select @queryId = queryId from @queries where rid = @row
    select @queryText = query from QueryData where rid = @queryId
    select @queryText
    exec sp_executesql @input_query
    delete @queries where rid = @row
    set @row = @row -1
end

I can't change it - I need to be able to preserve the integrity of that query string - since I execute it later as part of dynamic sql. — Roger Dodger, Sep 15 '16 at 18:30

T-SQL inserting a string into a text datatype column

1 Answers1