Incorrect SQL Syntax - Unclosed quotation mark after the character string

Question

Now i have string a = "Unclosed quotation mark after the character string '%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC'. Incorrect syntax near '%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC'." for filter

I'm using replace ("'","''") replace ("%","[%]") replace ("[","[[]")

and i have result for string strSQL = select * from studiologs where [Message] like '%Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''. Incorrect syntax near ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''.%'

but result always is null please help me replace this string for filter

thanks all

Answer 1

I think you have missed one more quotation mark at end of query -

select * from studiologs where [Message] like '%Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''%'

or remove that last quotation mark as well, if your string does not have '

select * from studiologs where [Message] like '%Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC%'

Depending on what you are searching exactly

Answer 2

Best thing I would do here is to transfer your SQL query into a procedure, that way the string you give it wont need filtering as the punctuation in the string will not affect the syntax of the query.

So something like this:

USE MYDATABASE
CREATE PROC GET_STUDIO_LOGS
@INPUT_STRING AS NVARCHAR(1024)
AS 
BEGIN
SELECT * FROM STUDIOLOGS WHERE [Message] LIKE '%' + @INPUT_STRING + '%'
END


EXEC GET_STUDIO_LOGS 'Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''. Incorrect syntax near ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''.'

If you use a program to submit the SQL then you can submit the string parameter as it is without any change in punctuation. Doing it natively in SQL you just add another ' (quotemark) to each quotemark that is meant to be part of the string.

If you are trying to escape the % marks you can set an escape character first:

SET ESCAPE '\';
SELECT '\%abc' FROM Table

Try that out, tell me how it goes.