SQL and ASP.NET WHERE IN generates wrong

Question

I have this line of code:

sql += " AND lc.name IN ('" + String.Join(",", id.type.ToArray()) + "')";

There are two items in id.type and this code generates this:

AND lc.name IN ('towns back to back,towns 3 storey')

Which will not work because it should be like this:

AND lc.name IN ('towns back to back' , 'towns 3 storey')

How can I fix this?

This is vulnerable to SQL Injection attacks. Use parameters instead. — ADyson, Jun 08 '17 at 15:33
@ADyson Yes it's vulnerable, but in this case (IN clause), parameters cannot be used (unfortunately) — Hans Keﬆing, Jun 08 '17 at 16:48
@HansKesting why not? It would be perfectly possible to pull the string into an array, and create a parameter for each item in the array. — ADyson, Jun 08 '17 at 17:22
@HansKesting probably a bit like this: https://stackoverflow.com/a/36458919/5947043 — ADyson, Jun 08 '17 at 17:28

score 0 · Accepted Answer · answered Jun 08 '17 at 16:20

This is not a desirable approach, because it's open to SQL injection. But there are a few things:

You have to accommodate for strings that contain single quotes.
It won't work if you just concatenate. You have to wrap EACH item in single quotes.

Try:

sql += " AND lc.name IN (" + String.Join(",", id.type.ToArray().Select(i=>String.Format(i.Replace("'","''"),"'{0}'")) + ")";

Ram · Answer 2 · 2017-06-08T16:17:05.843

-1

Use parameter to pass input string like

sql += " AND lc.name IN (@inputname)";
sqlcommand.parameters.AddWithValue("@inputname", String.Join("','", id.type.ToArray()))

edited Jun 08 '17 at 16:17

answered Jun 08 '17 at 16:13

Ram

1

IN clauses cannot be parameterized unless there is a fixed number of values. – Xavier J Jun 08 '17 at 16:16

2 Answers2