0

I have 2 SQL statements that query (microsoft full text indexing) that were written for an asp classic site. They contain some quotation marks and visual studio in c# does not accept them the way they are.

I need to change the variable myvalue to @myvalue however I do not know which qoutation marks are needed (or at all since c# doesn't accept) or how to format its use with c#. For example, what to keep and what not to keep or change etc.

SELECT * FROM thistable AS FT_TBL 
INNER JOIN CONTAINSTABLE(thistable, searchindex, '""" & myform & "*""', 50) AS KEY_TBL 
ON FT_TBL.ID = KEY_TBL.[KEY] 
WHERE tropic='current' 
ORDER BY KEY_TBL.RANK DESC

SELECT * FROM thistable AS FT_TBL 
INNER JOIN FREETEXTTABLE(thistable, searchindex, '" & myform & "', 50) AS KEY_TBL 
ON FT_TBL.ID = KEY_TBL.[KEY] 
WHERE tropic='current' 
ORDER BY KEY_TBL.RANK DESC
Cœur
  • 37,241
  • 25
  • 195
  • 267
cjtampa
  • 41
  • 2
  • 11
  • You have more issues than just some quotes here. The ampersand character is how you concatenate strings in vbscript, this is invalid in c#. You use the plus character for this. But really you should be using parameterized queries and not building up a string like this. – Sean Lange Aug 15 '17 at 12:33
  • I inherited these statements. I stated in my question that I need to put the parameter @myform in there instead. I assume changing the second statement to... (thistable, searchindex, '@myform', 50) would handle that one, but the first one I am not sure since there are 2 double qoutes before where the variable goes and the "*""' after where the variable would go. – cjtampa Aug 15 '17 at 12:49
  • This code is totally out of context. This makes it very difficult to help. Are you trying to turn this into a string in c# or something else? – Sean Lange Aug 15 '17 at 12:52
  • Totally out of context? The code works in VBscript. I need it formatted for c#. You do not put plus characters in it. Yes, these statements are meant for a string. I adjusted the second in my last comment, I am only now concerned with the first. – cjtampa Aug 15 '17 at 13:27
  • Yes it is out of context from a code perspective. Typically you would include a little bit of what you have tried so we help. If you are trying to parameterize this which I think you are you don't build up a string. You create the entire query and then add the parameter to your command. You have to remember that I can't see your screen and only know what you post. I get that you want some help here but what you posted is not a valid string in c#. We are left guessing what you want. – Sean Lange Aug 15 '17 at 13:37
  • How about the fact that I have said in main post that am putting in a parameter? and in my second response to you? THESE ARE TO BE PUT INTO STRINGS... for c#, I KNOW ITS NOT AS VALID STRING, hence why I am asking for help to convert it to a valid string where myform will be @myform. – cjtampa Aug 15 '17 at 13:43
  • I understand you are frustrated but yelling at the person trying to help you is not going to help your situation. Perhaps you could take a step back and realize what I am telling you. You want your code as a string but it is more complicated than that because you also have parameters. Showing us the code that you have currently would go a long way to others being able to help. – Sean Lange Aug 15 '17 at 14:02

2 Answers2

0

Something like this...assuming you want to parameterize this instead of continuing the practice of sql injection you inherited.

SELECT * FROM thistable AS FT_TBL 
INNER JOIN FREETEXTTABLE(thistable, searchindex, @myform, 50) AS KEY_TBL 
ON FT_TBL.ID = KEY_TBL.[KEY] 
WHERE tropic='current' 
ORDER BY KEY_TBL.RANK DESC

You would need to make this into a string. And then add the parameter to your sql command.

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
0

You need to escape double quotes. This can be achieved via the convention \" inline or via an @" prefix.

CONTAINSTABLE can be wild-carded, whereas FREETEXTTABLE cannot. Both expect a string contained within single quotes.

Sean Lange's points around SQL injection are well founded and using a parameter will also address the quote issue.

sql1.AppendLine("SELECT* FROM thistable AS FT_TBL ");
sql1.AppendLine("INNER JOIN CONTAINSTABLE(thistable, searchindex, '" + myform + "*', 50) AS KEY_TBL ");
sql1.AppendLine("ON FT_TBL.ID = KEY_TBL.[KEY] ");
sql1.AppendLine("WHERE tropic = 'current' ");
sql1.AppendLine("ORDER BY KEY_TBL.RANK DESC ");

sql2.AppendLine("SELECT * FROM thistable AS FT_TBL ");
sql2.AppendLine("INNER JOIN FREETEXTTABLE(thistable, searchindex, '" + myform + "', 50) AS KEY_TBL");
sql2.AppendLine("ON FT_TBL.ID = KEY_TBL.[KEY] ");
sql2.AppendLine("WHERE tropic = 'current' ");
sql2.AppendLine("ORDER BY KEY_TBL.RANK DESC ");

See this article for more info. Escape double quotes in string

Mr Slim
  • 1,458
  • 3
  • 17
  • 28