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

Question

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

Change your `1` to reflect the value of `IsHidden`. Also you're using `IsHidden` as the column name you're wanting to update... FWIW use parameters, helps prevent SQLi and would make this query easier. Finally you added the `mysql` tag, it doesn't appear you are using `mysql` rather `sql`, please remove that tag if not applicable. — Trevor, Feb 14 '22 at 17:15
right now everything is 1 be it false or true , i just need condition for sqlQuery2 — Abhishek Roy, Feb 14 '22 at 17:16
Be aware: your code is subject to [sql injection](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — Ergis, Feb 14 '22 at 17:35

score 1 · Accepted Answer · answered Feb 14 '22 at 17:32

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();
    }
}

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

1 Answers1