Executing an INSERT command using ASP.net

Question

I am using Visual Studio 2010 to create a simple Website for a college assignment. I am trying to create a contact form that submits the users name, email and message to my database table Messages.

I have created the relevant web service and I know that it is working when I try to GET data from the Table. I am just a little confused as to how I can INSERT data into the table.

Below is the code to my web service. The method I am concerned with is addMessage() I call the method when a button is clicked that is located on the contact.aspx page.

    public class Customers : System.Web.Services.WebService {

    [WebMethod]
    public DataSet getCustomers() {
        SqlConnection conn;
        SqlDataAdapter myDataAdapter;
        DataSet myDataSet;
        string cmdString = "Select * From Customers";
        conn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;AttachDbFilename=C:\\Users\\n00093500\\Desktop\\MMCA2\\APP_DATA\\NORTHWIND.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
        myDataAdapter = new SqlDataAdapter(cmdString, conn);
        myDataSet = new DataSet();
        myDataAdapter.Fill(myDataSet, "Customers");
        return myDataSet;
    }

    [WebMethod]
    public void addMessage(String n, String e, String m)
    {
        SqlConnection conn;
        SqlDataAdapter myDataAdapter;
        SqlCommand myCommand = new SqlCommand("INSERT INTO Messages VALUES("+n+","+e+","+m+")");
        conn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;AttachDbFilename=C:\\Users\\n00093500\\Desktop\\MMCA2\\APP_DATA\\NORTHWIND.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
        //UNSURE WHAT TO DO FROM THIS POINT... CAN I USE myDataAdapter to execute a query?
    }

}

Appreciate any help you guys might have! Thanks

You are right to be concerned. That code is vulnerable to sql injection attacks. It's practically begging to get hacked. — Joel Coehoorn, Dec 02 '13 at 18:18
I don't care about that right now, I'm just learning the bare bones of how to work with ASP. Just need to know how to put the data in the form into my messages table — Javacadabra, Dec 02 '13 at 18:19
@Javacadabra I think that's a poor way to approach Joel's injection concern. It's important, and really not harder than doing it your current way. — Chris Farmer, Dec 02 '13 at 18:20
Parameterized insert; http://stackoverflow.com/a/12939934/246342 — Alex K., Dec 02 '13 at 18:21
@ChrisFarmer If it doesn't involve deviating to far from my original code of course I'll take it onboard, but the reality is I'm trying to integrate the most basic of functionality into a site for a college module that I am taken that will not be looking at the code in that detail. — Javacadabra, Dec 02 '13 at 18:21

score 5 · Accepted Answer · answered Dec 02 '13 at 18:21

[WebMethod]
public void addMessage(String n, String e, String m)
{
    string sql = "INSERT INTO Messages VALUES(@n, @e, @m)";
    using (var conn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;AttachDbFilename=C:\\Users\\n00093500\\Desktop\\MMCA2\\APP_DATA\\NORTHWIND.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"))
    using (var cmd = new SqlCommand(sql, conn))
    {
       //change these three lines to use actual database column types, lengths
       //I'll pretend "e" is a date column just to show an example of how that might look
       cmd.Parameters.Add("@n", SqlDbType.NVarChar, 50).Value = n;
       cmd.Parameters.Add("@e", SqlDbType.DateTime).Value = DateTime.Parse(e);
       cmd.Parameters.Add("@m", SqlDbType.NVarChar, 50).Value = m;

       conn.Open();
       cmd.ExecuteNonQuery();
    }
}

Perfect Joel, makes a lot of sense, thanks for the help! Will accept answer in 5 mins — Javacadabra, Dec 02 '13 at 18:24

Executing an INSERT command using ASP.net

1 Answers1