CODEWITHSUNDEEP
asp.netdatabase
yrazlik
yrazlik

Reputation: 10787

How can i write the following query as parametrized query?

I have been hearing about parametrized queries every time I ask a question about database here. It looks like I am not using parametrized queries and my code may suffer from SQL injection. So here is my code: 

public void CreateStudent(int ID, String status, String email, String firstName,     String lastName, String password, String level, String program)
{
  SqlConnection con = new SqlConnection(GetConnectionString());

  string query1 = "insert into StudentTable(Name,Surname,ID,email,level,program,status,password,Type) values ("
   + "'" + firstName + "'" + "," + "'" + lastName + "'" + ","
   + "'" + ID + "'" + "," + "'" + email + "'" + "," + "'" + level + "'" + "," + "'" + program + "'" + "," + "'" + status + "'"
   + "," + "'" + password + "'" + "," + "'" + "Student" + "'" + ")";

  SqlCommand command = new SqlCommand(query1,con);

  int result;
  con.Open();
  result = command.ExecuteNonQuery();
  con.Close();
}

Here is what I have tried:

SqlConnection con = new SqlConnection(GetConnectionString());

string query1 = "insert into StudentTable(Name,Surname,ID,email,level,program,status,password,Type) values(@firstName,@lastName,@ID,@email,@level,@program,@status,@password,Student)";

SqlCommand command = new SqlCommand(query1,con);

command.Parameters.AddWithValue("@firstName", firstName);
command.Parameters.AddWithValue("@lastName", lastName);
command.Parameters.AddWithValue("@ID", ID);
command.Parameters.AddWithValue("@email", email);
command.Parameters.AddWithValue("@level", level);
command.Parameters.AddWithValue("@program", program);
command.Parameters.AddWithValue("@status", status);
command.Parameters.AddWithValue("@password", password);

int result;
con.Open();
result = command.ExecuteNonQuery();
con.Close();

This gives an error saying that Student is an invalid column name. Actually, here I try to use "Student" as a string value to be added to the column Type. Can somebody write this query as a parametrized query so that I can understand it?

Upvotes: 1

Views: 78

Answers (2)

Joe Carr
Joe Carr

Reputation: 313

Check this link

    public void CreateStudent(int ID, String status, String email, String firstName, String lastName, String password, String level, String program)
    {
        SqlConnection con = new SqlConnection(GetConnectionString());

        using (
            SqlCommand command =
                new SqlCommand(
                    @"insert into StudentTable(Name,Surname,ID,email,level,program,status,password,Type) values 
                    (@name, @surname, @id, @email, @level, @program, @status,@password,'Student')",
                    con))
        {
            //
            // Add new SqlParameter to the command.
            //
            command.Parameters.Add(new SqlParameter("name", firstName));
            command.Parameters.Add(new SqlParameter("surname", lastName));
            command.Parameters.Add(new SqlParameter("id", ID));
            command.Parameters.Add(new SqlParameter("email", email));
            command.Parameters.Add(new SqlParameter("level", level));
            command.Parameters.Add(new SqlParameter("program", program));
            command.Parameters.Add(new SqlParameter("status", status));

            int result;
            con.Open();
            result = command.ExecuteNonQuery();
            con.Close();
        }
    }

Upvotes: 0

MDMalik
MDMalik

Reputation: 4001

In that case it should be 'Student'

SqlConnection con = new SqlConnection(GetConnectionString());


string query1 = "insert into StudentTable(Name,Surname,ID,email,level,program,status,password,Type) values(@firstName,@lastName,@ID,@email,@level,@program,@status,@password,'Student')";


SqlCommand command = new SqlCommand(query1,con);

command.Parameters.AddWithValue("@firstName", firstName);
command.Parameters.AddWithValue("@lastName", lastName);
command.Parameters.AddWithValue("@ID", ID);
command.Parameters.AddWithValue("@email", email);
command.Parameters.AddWithValue("@level", level);
command.Parameters.AddWithValue("@program", program);
command.Parameters.AddWithValue("@status", status);
command.Parameters.AddWithValue("@password", password);

int result;
con.Open();
result = command.ExecuteNonQuery();
con.Close();

Upvotes: 3

Related Questions