How do I change the SQL query on the basis of user input in C#?-CodePudding

This is my code where I am receiving the props from ui and it is a boolean value that I am receiving. From the UI, I'm passing true and false:

public void AlterTableColumn(int instanceid, bool IsHidden)
{
    var sqlQuery2 = String.Format("Update {0} set {1} = 1 where Id = {2};", "Mytable", "IsHidden", instanceid);

    string connString = System.Configuration.ConfigurationManager.AppSettings["DBConnection"].ToString();
    // getConnectionStringById(instanceId.ToString());

    using (SqlConnection connection = new SqlConnection(connString))
    {
        connection.Open();

        SqlCommand sql_cmnd = new SqlCommand(sqlQuery2, connection);
        sql_cmnd.CommandType = CommandType.Text;
        sql_cmnd.CommandTimeout = 10000;

        sql_cmnd.ExecuteNonQuery();

        sql_cmnd.Connection.Close();
    }
}

How do I change the sqlQuery2 mentioned above in terms of true or false? Right now it is making 1 in the SQL table for true or false

CodePudding user response：

The way it is written now, the value of sqlQuery2 will be:

Update Mytable set IsHidden = 1 where Id = /*the value of the passed in instanceId*/

You're never using the passed in bool, IsHidden. You're always setting the IsHidden column to 1, regardless of what you pass the IsHidden function parameter.

In order to update the database to 1 or 0 based on the IsHidden parameter you need to set an int based on the value of that parameter. You also need to update the query itself to use the int rather than always setting the value to 1.

int isHiddenDbValue = IsHidden ? 1 : 0;
var sqlQuery2 = string.Format("Update {0} set IsHidden = {1} where Id = {2}",
   "Mytable", isHiddenDbValue, instanceId);

That being said, rather than using string.Format and putting the values in the query directly like you're doing, you should use a parameterized query. This will protect you from SQL injection attacks.

For example:

public void AlterTableColumn(int instanceid, bool IsHidden)
{
    int isHiddenDbValue = IsHidden ? 1 : 0;
    var sqlQuery2 = "Update Mytable set IsHidden = @IsHidden where Id = @Id";
    string connString = System.Configuration.ConfigurationManager.AppSettings["DBConnection"].ToString();
    using (SqlConnection connection = new SqlConnection(connString))
    {
        connection.Open();
        SqlCommand sql_cmnd = new SqlCommand(sqlQuery2, connection);
        sql_cmnd.CommandType = CommandType.Text;
        sql_cmnd.CommandTimeout = 10000;
        sql_cmnd.Parameters.AddWithValue("IsHidden", isHiddenDbValue);
        sql_cmnd.Parameters.AddWithValue("Id", instanceId);
        sql_cmnd.ExecuteNonQuery();
        sql_cmnd.Connection.Close();
    }
}