CODEWITHSUNDEEP
c#sqlms-accesssql-injection
Coleman Blaszczykowski
Coleman Blaszczykowski

Reputation: 29

no value given fr one or more required parameters

I'm trying to prevent SQL injections. Am I doing this right? (I'm using MS Access.) Should I still use sqlparameter?

OleDbParameter[] myparm = new OleDbParameter[2];
myparm[0] = new OleDbParameter("@UserID", UserName.Text);
myparm[1] = new OleDbParameter("@Password", encode);

string queryStr = "SELECT * FROM TMUser WHERE UserID=@UserID AND Password=@Password";

OleDbConnection conn = new OleDbConnection(_connStr);
OleDbCommand cmd = new OleDbCommand(queryStr, conn);

conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();

Upvotes: 1

Views: 45

Answers (1)

Ron Beyer
Ron Beyer

Reputation: 11273

Close!

string queryStr = "SELECT * FROM TMUser WHERE UserID=@UserID AND Password=@Password";

OleDbConnection conn = new OleDbConnection(_connStr);
OleDbCommand cmd = new OleDbCommand(queryStr, conn);
cmd.Parameters.AddWithValue("@UserID", UserName.Text);
cmd.Parameters.AddWithValue("@Password", encode);

The parameters are part of the command object and you use the Parameters.AddWithValue method to set the parameter values to what you have defined in the query string.

By the way, you should be using using statements to encapsulate some of your objects, here is what I typically do:

using (OleDbConnection conn = new OleDbConnection(_connStr))
using (OleDbCommand = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT ...";
    cmd.Parameters.AddWithValue(...);

    cmd.ExecuteReader();
    //...
}

That way you don't have to worry about cleaning up resources if something goes wrong inside or closing the connection when you are done.

Upvotes: 1

Related Questions