Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
createList(string commaSeparatedElements) {
SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN ("+commaSeparatedElements+")");
I would like to write it using a parameterized query, so every element from the string is checked to prevent against Sql-Injections.
Pseudo-code:
createList(string commaSeparatedElements) {
SqlParameterList elements = new SqlParameterList("@elements", SqlDbType.Int);
SqlParameterList.Values = commaSeparatedElements.split(new Char[1] {','});
SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN (@elements)");
query.Parameters.Add(elements);
Does anything like that exist in C#, or will I have to write it by myself?
EDIT: Thanks for all the answers. As I try not to use code I don't understand (too many bad experiences in the last days), dapper and table-valued parameters, even though they may be perfect for my needs, are off-limits. I just made a loop.
string[] elements = commaSeparatedElements.split(new Char[1] {','});
StringList idParamList = new StringList();
for(int i=0;i<elements.Count;i++) {
query.Parameters.AddWithValue("@element"+i,Convert.ToInt32(elements[i]));
idParamList.Add("@element" + i);
SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN ("+String.Join(",",idParamList)+")");
var ids = new List<int> {1,2,3,4,5};
var rows = conn.Query<SomeType>("select * from table where id in @ids",
new { ids }).ToList();
You'll notice the lack of parenthesis in the in usage; "dapper" spots this, and performs parameter expansion automatically. Plus it handles all the command details for you, including mapping the data back into SomeType instances.
In the above example, it will expand to be equivalent to (approximately):
int ids0 = 1, ids1 = 2, ids2 = 3, ids3 = 4, ids4 = 5;
var rows = conn.Query<SomeType>(
"select * from table where id in (@ids0,@ids1,@ids2,@ids3,@ids4)",
new { ids0, ids1, ids2, ids3, ids4 }).ToList();
SQL Server has one data structure that's ideal for holding multiple values of the same "type" - and that's a table.
Thankfully, in recent years, they introduced Table-Valued Parameters so that you can construct a table in your client code and pass that across to the SQL Server in a natural way, e.g.:
// Assumes connection is an open SqlConnection.
using (connection)
// Create a DataTable with the modified rows.
DataTable addedCategories = CategoriesDataTable.GetChanges(
DataRowState.Added);
// Define the INSERT-SELECT statement.
string sqlInsert =
"INSERT INTO dbo.Categories (CategoryID, CategoryName)"
+ " SELECT nc.CategoryID, nc.CategoryName"
+ " FROM @tvpNewCategories AS nc;"
// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(
sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
"@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";
// Execute the command.
insertCommand.ExecuteNonQuery();
–
and add there the ids you want. It's easy to convert it in a comma seperated list as follows
string listOfIds = string.Join(",", Ids.Select(Id => Id.ToString()).ToArray());
Then you pass this string as a parameter to your parameterized query.
–
/// <summary>
/// Adds a sequence of parameters to an existing parameter collection
/// </summary>
/// <typeparam name="T">Type of parameter values</typeparam>
/// <param name="parameters">Existing parameter collection</param>
/// <param name="pattern">Name pattern of parameters. Must be a valid <see langword="int"/> format string</param>
/// <param name="parameterType">Database type of parameters</param>
/// <param name="length">Length of parameter. 0 for numeric parameters</param>
/// <param name="values">Sequence of values</param>
/// <returns>Comma separated string of parameter names</returns>
public static string AddParameters<T>(SqlParameterCollection parameters,
string pattern,
SqlDbType parameterType,
int length,
IEnumerable<T> values) {
if (parameters == null)
throw new ArgumentNullException("parameters");
if (pattern == null)
throw new ArgumentNullException("pattern");
if (values == null)
throw new ArgumentNullException("values");
if (!pattern.StartsWith("@", StringComparison.CurrentCultureIgnoreCase))
throw new ArgumentException("Pattern must start with '@'");
var parameterNames = new List<string>();
foreach (var item in values) {
var parameterName = parameterNames.Count.ToString(pattern, CultureInfo.InvariantCulture);
parameterNames.Add(parameterName);
parameters.Add(parameterName, parameterType, length).Value = item;
return string.Join(",", parameterNames.ToArray());
It is used like this
string sql = "SELECT col1 " +
"FROM Table " +
"WHERE col2 IN ({@Values}) ";
var paramNames = SqlHelper.AddParameters(command.Parameters,
"@Value0",
SqlDbType.Int,
listOfInts);
command.CommandText = sql.Replace("{@Values}", paramNames);
(Result is a query like SELECT ... IN (@Value0, @Value1, @Value2))
which version of sql server you are using?
You should try out table value parameter if you are using sql server 2008 or later
Check http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters
Pass table valued parameter using ADO.Net
StringBuilder sb = new StringBuilder("Id IN(");
List<SqlParameter> parameters = new List<SqlParameter>();
int i = 1;
foreach (int item in items)
string currentItem = "@Item" + i++.ToString();
sb.Append(currentItem + ",");
parameters.Add(new SqlParameter(currentItem , item));
sb.Remove(sb.Length-1, 1);
sb.Append(")");
I hope this helps
Alternatively you can put a if condition for the parameters. For Example:
if(commaSeparatedElements.Contains("'"))
return;
//Pass parameter & Execute query
