1

Goal: prevent the SQL injection.

I want to pass the column name and table name as dynamically from the select query.

Consider the below method: 

public bool PassColumnTableDynamic(string columnName, string tableName)
{
    string commandText = string.Format(select {0} from {1}, columnName, tableName);

    try
    {
            using (var command = new SqlCommand(commandText, _connection))
            {
               command.ExecuteScalar();
            }
    }
    catch (Exception ex)
    {
        throw new Exception("error",ex);
    }

    return true;
}

The commandText is vulnerable to SQL injection. How can I prevent that?

I have the solution to create a stored procedure and pass it as parameter from this method. But I don't want to create a stored procedure.

Is there anything available in C# code to fix it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Murugan
  • 33
  • 8
  • 1
    Does this answer your question? [How should I pass a table name into a stored proc?](https://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc or more specifically https://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc/1246848#1246848) – RBarryYoung Feb 10 '20 at 16:34
  • No, I don't want to create the stored procedure. – Murugan Feb 11 '20 at 06:06
  • If you want to do it safely, then that's how you do it. You will have to decide whether you don't want to use a stored procedure more than your want your code to be secure. – RBarryYoung Feb 11 '20 at 06:36

1 Answers1

0

if the user can't input those names directly, just inject them like you do...

otherwise there is no direct way, consider using Regex like "^[a-zA-Z]*$" so the user can only insert text

Patrick Beynio
  • 788
  • 1
  • 6
  • 13
  • You may want to look at the Nuget Package Dapper. It makes database access a bit easier.and solves your issue. A guy called Tim Corey has a great YouTube video to get you started. – RudolfJan Feb 10 '20 at 17:38