How to parameterize query to prevent SQL injection-CodePudding

Try to parameterize this code.

    string sql = "SELECT COUNT(*) AS count FROM users WHERE username ='"   username   "' AND password='"  password   "';";
    MySqlCommand cmd = new MySqlCommand(sql, conn);

    MySqlDataReader rdr = cmd.ExecuteReader();

CodePudding user response：

string sql = "SELECT COUNT(*) AS count FROM users WHERE username = @username  AND password= @password";
using (MySqlCommand cmd = new MySqlCommand(sql, conn)
{
    cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value =  username;
    cmd.Parameters.Add("@password", MySqlDbType.VarChar).Value =  password;
    int result = (int)cmd.ExecuteScalar();
}

CodePudding user response：

Taken from:

https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

class ParamDemo
{
    static void Main()
    {
        // conn and reader declared outside try
        // block for visibility in finally block
        SqlConnection conn   = null;
        SqlDataReader reader = null;

        string inputCity = "London";
        try
        {
            // instantiate and open connection
            conn =  new 
                SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
            conn.Open();

            // don't ever do this
            // SqlCommand cmd = new SqlCommand(
            // "select * from Customers where city = '"   inputCity   "'";

            // 1. declare command object with parameter
            SqlCommand cmd = new SqlCommand(
                "select * from Customers where city = @City", conn);

            // 2. define parameters used in command object
            SqlParameter param  = new SqlParameter();
            param.ParameterName = "@City";
            param.Value         = inputCity;

            // 3. add new parameter to command object
            cmd.Parameters.Add(param);

            // get data stream
            reader = cmd.ExecuteReader();

            // write each record
            while(reader.Read())
            {
                Console.WriteLine("{0}, {1}", 
                    reader["CompanyName"], 
                    reader["ContactName"]);
            }
        }
        finally
        {
            // close reader
            if (reader != null)
            {
                reader.Close();
            }

            // close connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}