CODEWITHSUNDEEP
c#ado.net
robor
robor

Reputation: 3089

How to get SqlParamter replaced in SELECT SqlCommand?

In an existing codebase there is hardcoded SQL and I want to avoid SQL injection.

The below code uses SqlCommand together with SqlParameters. The query does not return any data. However, when I remove the parameters the query returns the correct results.

How can I use SqlParameters with a SELECT statement?

        string atUsername = "@username"; //does not work
        //string atUsername = "Demo1"; //THIS WORKS
        string atPassword = "@password"; //does not work
        //string atPassword = "222"; //THIS WORKS
        string sql = @"SELECT userId, userName, password, status, roleId, vendorId 
        FROM users 
        WHERE username = '" + atUsername + "' AND password = '" + atPassword + "'";

        SqlCommand cmd = new SqlCommand(sql);

        cmd.Parameters.Add(atUsername, SqlDbType.NVarChar, 20);
        cmd.Parameters[atUsername].Value = "Demo1";
        //cmd.Parameters.AddWithValue //also does not work


        cmd.Parameters.Add(atPassword, SqlDbType.NVarChar, 20);
        cmd.Parameters[atPassword].Value = "222";
        //cmd.Parameters.AddWithValue //also does not work

        SqlConnection conn = new SqlConnection(connStr);
        cmd.Connection = conn;
        conn.Open();

        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        sda.Fill(dt);

        Console.WriteLine(dt.Rows != null);
        if (dt.Rows != null)
        {
            Console.WriteLine(dt.Rows.Count);
        }

        conn.Close();
        conn.Dispose();

I have also unsuccessfully tried alternatives using

  1. SqlCommand.ExecuteReader and SqlDataReader
  2. IDisposable pattern

  3. Replace cmd.Parameters.Add(atUsername with

    SqlParameter pUsername = new SqlParameter(); pUsername.ParameterName = atUsername; pUsername.Value = "Demo1"; cmd.Parameters.Add(pUsername);"

PS. I've heard of EntityFramework but I cannot use EF in this case (long story).

Upvotes: 0

Views: 467

Answers (1)

Evk
Evk

Reputation: 101473

The root of your problem is that you use variable names inside string literal:

 WHERE username = '@username' AND password = '@password'

So they are not treated as variable names by sql server. Instead you are searching for user with name "@username" and password "@password". Correct way is:

WHERE username = @username AND password = @password

Upvotes: 3

Related Questions