0

I have some data like name,firstname,surname,std,Rollno.

Using C#, I want to convert this into

('name', 'surname', 'std', 'Rollno')

so that I can use this this data to query into the SQL/MySQL DB like -

SELECT *
FROM Table1 
WHERE UserCommunicationId IN ('name', 'surname', 'std', 'Rollno');

Instead of

SELECT *
FROM Table1 
WHERE UserCommunicationId IN ('name,surname,std,Rollno');
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yogesh Shinde
  • 43
  • 1
  • 5
  • 1
    Have you tried anything? If yes, please show us your attempt. – Prasad Telkikar Dec 13 '21 at 04:58
  • 1
    Please also learn about [SQL-Injection](https://stackoverflow.com/a/35163362/1336590) and how to easily prevent it. – Corak Dec 13 '21 at 05:08
  • Even though your question is not a direct duplicate, the answers there can help you do what you need instead of what you're asking for (which is a security risk). – Zohar Peled Dec 13 '21 at 09:29

3 Answers3

0

You can try below below logic

public static class SQLQueryExtensions
{
    public static string ColumnFormat(this String str)
    {
        return "(" +                           //Include first parenthesis 
              string.Join(", ", str.Split().Select(x => $"'{x}'")) //Add single quote to each column
              + ")";                          //Include last parenthesis
    }
}

You can do it in one line as well,

var inputStr = "name,firstname,surname,std,Rollno";
var result = "(" + string.Join(", ", inputStr.Split().Select(x => $"'{x}'")) + ")";

Try Online

Prasad Telkikar
  • 15,207
  • 5
  • 21
  • 44
0

One approach I can come up is that:

  • Set the whole string into query as a parameter.
  • Split it in a WITH query.
  • LEFT JOIN it in the main query.
  • NOT NULL to check if there's any hit.

I've wrote an example below, but I am Oracle user so I am not sure if these syntax are right, not even tested, just googled around. Only take it as an reference to the explanation of the idea.

WITH RECURSIVE targets (stringBuffer, word) AS (
SELECT
  @Parameter
 ,NULL
UNION ALL
SELECT
  SUBSTRING(stringBuffer, LEAST(LENGTH(SUBSTRING_INDEX(stringBuffer, ',', 1) + 1, LENGTH(stringBuffer)))
 ,SUBSTRING_INDEX(stringBuffer, ',', 1)
WHERE LENGTH(word) > 0
   OR LENGTH(stringBuffer) > 0      -- I am not really sure about these
)

SELECT *
FROM Table1
LEFT JOIN targets ON targets.word = Table1.UserCommunicationId
WHERE targets.word IS NOT NULL;

Then, in C#, set Parameter for your query command in string like this

string s = "name,firstname,surname,std,Rollno";

Edit:

Or, simply:

SELECT *
FROM Table1 
WHERE REGEXP_LIKE(UserCommunicationId, @Parameter)
;

While setting the Parameter in C# as:

string s = "name|firstname|surname|std|Rollno";

Notice that if the keywords can be input by user, you still have the problem where user may enter .+ and it responds every data to them as long as there's no other condition added.

But personally, I think there's a potential issue in your design if you really need an unknown length of IN-CLAUSE in your query. If keywords that can be applied are limited in number, you can, rough but it's my team's current criteria, concat the WHERE section keyword by keyword in C#.

Xiang Wei Huang
  • 336
  • 1
  • 9
0

Use blow logic, will solve your problem.

string inputStr = "name,firstname,surname,std,Rollno";  
string result = string.Join(",", inputStr.Split(',').Select(x => string.Format("'{0}'", x)).ToList());
Output = 'name','firstname','surname','std','Rollno'
rizwan
  • 16
  • 7