Reputation: 10787
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
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
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