How to use " ' " in a dynamic query in sql server?

Question

I have stored Procedure for Search.

ALTER Proc [dbo].[USP_GETFAQ]
@SortBy Varchar(128)='CreatedDate DESC',            
@Page int=1,              
@RecsPerPage int   =10,            
@Status Char(5)='',          
@Question varchar(500)='',        
@Answer varchar(1000)='' 
As      
DECLARE @SQL VARCHAR(MAX)            
DECLARE @DSQL VARCHAR(MAX)            
DECLARE @whereCondition VARCHAR(1024)            
DECLARE @FirstRec int, @LastRec int            
SET @FirstRec = (@Page - 1) * @RecsPerPage            
SET @LastRec = (@Page * @RecsPerPage + 1)            
Declare @SectionCount int;              
Set NoCount On              
Begin            
 SET @SQL='Select            
   ROW_NUMBER() over( order by  '+@SortBy +') rownum,            
 *  FROM faq where Status <>''D'''          
if @Status !='' and @Status is not null AND @Status!='ALL'          
begin          
SET @SQL+=' AND Status = '''+@Status+''''          
end         
if @Question!=''          
begin          
SET @SQL +=' AND Question like '''+'%'+REPLACE(@Question, '''', '')+'%'+'''' 
end          
if @Answer!=''          
begin          
SET @SQL +=' AND Answer like '''+'%'+REPLACE(@Answer, '''', '')+'%'+''''           
end          
SET @DSQL='SELECT  * from (' + @SQL +') AS tbl'            
print @DSQL            
DECLARE @TEMPResult TABLE(RowNum INT,              
ID uniqueIdentifier,            
Question varchar(500),            
Answer varchar(1000),            
CreatedDate DateTime,            
LastModifiedDate dateTime,            
CreatedByIp varchar(20),            
LastModifiedByIp varchar(20),            
CreatedBy varchar(50),            
ModifiedBy varchar(50),            
[Order] int,            
Status char(5)   
)                          
INSERT INTO @TEMPResult EXEC(@DSQL)              
SELECT (Select Count(*) from @TEMPResult) as Count,ID,SUBSTRING(question, 1, 200)question ,SUBSTRING(Answer, 1,250)Answer,   
 CreatedDate,LastModifiedDate,CreatedByIp ,LastModifiedByIp,CreatedBy,ModifiedBy, [Order], Status   FROM @TEMPResult WHERE RowNum > @FirstRec AND RowNum < @LastRec 
RETURN                        
End

When a question or answer contains "'" i getting error. that synatx is wrong near "'".

What i have tried so far is:

i have replaced the "'" with "''''" before passing the string to stored proc. it run successfully but not returning any record, please help me how can i do it.

You are replacing `'` with nothing in your code. Is that correct? And you print your SQL-String (`print @DSQL`), so what do you get when you run that query manually? What do you get when you run the query in the string `@SQL`? — Ocaso Protal, Oct 17 '13 at 07:21
Not sure I grabbed the use-cases, but you may give a try to replacing `REPLACE(@Answer, '''', '')` with `REPLACE(@Answer, '''', '_')` (and same for the @Answer REPLACE) — jbl, Oct 17 '13 at 07:22

score 0 · Answer 1 · answered Oct 17 '13 at 07:26

Your method will lead to sql injection MSDN SQL injection

Try to use EXEC sp_executesql @SQLString, @ParamDef, @paramList ...

MSDN sp_executesql

Your code:

ALTER Proc [dbo].[USP_GETFAQ]
@SortBy Varchar(128)='CreatedDate DESC',            
@Page int=1,              
@RecsPerPage int   =10,            
@Status Char(5)='',          
@Question varchar(500)='',        
@Answer varchar(1000)='' 
As      
DECLARE @SQL NVARCHAR(MAX)            

DECLARE @FirstRec int, @LastRec int            
SET @FirstRec = (@Page - 1) * @RecsPerPage            
SET @LastRec = (@Page * @RecsPerPage + 1)            
Declare @SectionCount int;              
Set NoCount On              
Begin

 SET @SQL='SELECT  *  from ( 
 Select ROW_NUMBER() over( order by  '+@SortBy +') rownum,
  *  FROM faq where Status <>''D'''          

if @Status !='' and @Status is not null AND @Status!='ALL'          
begin          
SET @SQL+=' AND Status = @Status '          
end         
if @Question!=''          
begin          
    SET @Question = '%'+@Question+'%'
    SET @SQL +=' AND Question like @Question' 
end          
if @Answer!=''          
begin          
    SET @Answer = '%'+@Answer+'%'
    SET @SQL +=' AND Answer like @Answer'           
end          
SET @SQL += ') AS tbl'

print @SQL            

DECLARE @ParamDefinition nvarchar(4000)

SET @ParamDefinition = 
'@Status Char(5),
@Question varchar(500),
@Answer varchar(1000)';

DECLARE @TEMPResult TABLE(RowNum INT,              
ID uniqueIdentifier,            
Question varchar(500),            
Answer varchar(1000),            
CreatedDate DateTime,            
LastModifiedDate dateTime,            
CreatedByIp varchar(20),            
LastModifiedByIp varchar(20),            
CreatedBy varchar(50),            
ModifiedBy varchar(50),            
[Order] int,            
Status char(5)   
)                          
INSERT INTO @TEMPResult 
EXECUTE sp_executesql @SQL, @ParamDefinition
                    ,@Status = @Status
                    ,@Question = @Question
                    ,@Answer = @Answer

SELECT (Select Count(*) from @TEMPResult) as Count,ID,SUBSTRING(question, 1, 200)question ,SUBSTRING(Answer, 1,250)Answer,   
 CreatedDate,LastModifiedDate,CreatedByIp ,LastModifiedByIp,CreatedBy,ModifiedBy, [Order], Status   FROM @TEMPResult WHERE RowNum > @FirstRec AND RowNum < @LastRec 
RETURN                        
End

still the same result and what is the difference between the execution process. its executing fine. but its not returning any result is the problem — Ram Singh, Oct 17 '13 at 07:31
@raman Can you post result of `PRINT @SQL`? About difference - if I `set @Question = 'Hello%'; drop table Users' --` you will loss your data — AlexK, Oct 17 '13 at 07:40

score 0 · Answer 2 · answered Oct 17 '13 at 18:28

0

Use 3 single quotes in a row. Like '''. Do not use any double quotes.

answered Oct 17 '13 at 18:28

FAA

179
11

How to use " ' " in a dynamic query in sql server?

2 Answers2