Rework using parameterized queries C#

Question

There is a lot of tutorials regarding parameterized queries but most of them involve using SqlCommand, the only thing my code accepts is SqlDataAdapter for my SQL command, anytime I try to instantiate it as a SqlCommand I get errors.

TL;DR
I either need guidance on how to rework this using parameterized queries or I need to know why my SqlDataAdapter line doesn't work as an SqlCommand line.

private void btnLogin_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Jake\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;");

    SqlDataAdapter sda = new SqlDataAdapter("Select Count(*) From Login Where Username = '" + txtUsername.Text + "' and Password = '" + txtPassword.Text + "'", con);
    DataTable dt = new DataTable();
    sda.Fill(dt);

    if (dt.Rows[0][0].ToString() == "1")
    {
        this.Hide();
        MessageBox.Show("LOGIN!");
    }
    else
    {
        MessageBox.Show("FAILED!");
    }
}

the sql commend is to run statements like insert and update because you dont need to return nothing. If you need to return something you need a sqldatadadapter. Your query needs sqlparamenters, add them using @ParameterName and add the sql parameter to your sqldata adapter. If you keep your query like this you are vulnerable for sql injections and your code is not secure. — Juan, Sep 24 '15 at 02:51

Juan · Answer 1 · 2015-09-24T12:37:00.150

2

You can use:

   using (SqlDataAdapter sqlDA = new SqlDataAdapter("Select * from user where username = @username and password = @pass", sqlConnect))
    {
        sqlDA.SelectCommand.CommandType = CommandType.Text;
        sqlDA.SelectCommand.Parameters.Add("@username", SqlDbType.Varchar).Value = username;
sqlDA.SelectCommand.Parameters.Add("@pass", SqlDbType.Varchar).Value = password;

        sqlDA.Fill(dataTableVariable);
        return dataTableVariable;
    }

For your password remember to use a hash algorithm

This code is not tested. If you want the best performance you can explore execute scalar.

edited Sep 24 '15 at 12:37

answered Sep 24 '15 at 02:56

Juan

1,352
13
20

Can you elaborate on hash algorithm? Sorry I'm new to this and you've got my attention 8) – Jaekx Sep 24 '15 at 03:03
The hash is one way encryption and is used to encrypt password most of the time. When your system create the user the password is encrypted using your hash algorithm (sha please) and then when the user wants to log in the password provided is converted to a hash a compared with the one stored in the database – Juan Sep 24 '15 at 12:09

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

You can use Parameters.AddWithValue in SqlDataAdapter

    sda.SelectCommand.Parameters.AddWithValue("@ParamName",value);

Check this: c# Using Parameters.AddWithValue in SqlDataAdapter

edited May 23 '17 at 12:22

Community

1
1

answered Sep 24 '15 at 02:56

japzdivino

1,736
3
17
25

You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... – marc_s Sep 24 '15 at 05:13
@marc_s not really, it always depend on the situation, but if needed , he can use `.Parameters.Add("@ParamName", DataTypeHere).Value = ValueHere` – japzdivino Sep 24 '15 at 05:22

score 1 · Answer 3 · answered Sep 24 '15 at 03:01

Try using parametrize query, it will avoids errors and sql injection Once you have the reader you can use HasRow() to check if a record is returned

string conString = "xxxxxxxxxxxxxxxxxxxxxxxx";
using (SqlConnection con = new SqlConnection(conString))
{
    con.Open();
    using (var cmd = new SqlCommand(
    "SELECT * FROM Login Where Username=@Username AND Password=@Password",
    con))
    {
        cmd.Parameters.AddWithValue("@Username", txtUsername.Text);
        cmd.Parameters.AddWithValue("@Password", txtPassword.Text);

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRow())
            {
                if(reader.Read())
                {
                    var username = reader["Username"].ToString();
                }
            }
            else
            {
                //User does not exists
            }
        } 
    }
}

You will need to import some library:

using System;
using System.Data;
using System.Data.SqlClient;

Interesting this is totally new to me, ill have to mess around with this method of handling it, thank you for the help! — Jaekx, Sep 24 '15 at 03:08
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Sep 24 '15 at 05:14
Or since this query returns **one row, one column** only, it would be much simpler to use a `SqlCommand cmd = ......` and then call `cmd.ExecuteScalar()` on it..... — marc_s, Sep 24 '15 at 05:14

Rework using parameterized queries C#

3 Answers3

Linked