CODEWITHSUNDEEP
c#asp.netsql
SteveU
SteveU

Reputation: 111

ASP.net / c# SQL code submit via textbox

I'm relativity new to asp.net / c#. I've written a couple of apps, so i can interpret the language but I'm not fluent.

Basically each year we have students arrive into the college and i want to be able to have a textbox where i can drop the SQL code into and it automatically updates the database. Ofcourse this page will be secured.

But something like this.

(,'[email protected]', '12', 'Joe', 'Bloggs', 'Joseph Bloggs', '11JB0666')

So that would be the bit of code of the VALUES in the sql script.

Would this be possible?

I did think i could use something like this...

SqlConnection conn = new
 SqlConnection(ConfigurationManager.ConnectionStrings["ExtensionActivitesConnectionString"].ConnectionString);
    SqlCommand cmd = new SqlCommand(" Insert into dbo.Names (Email,Year,FirstName,LastName,Name,Username) VALUES (@SQL)", conn);
    cmd.Parameters.AddWithValue("@SQL", SqlTextBox.Text);

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

Any help or suggestions would be great thanks.

Upvotes: 2

Views: 1431

Answers (3)

Randolpho
Randolpho

Reputation: 56448

That won't work with a parameterized query like you're doing -- the single quotes will be escaped and the SQL sent to the server will not be valid. That pattern is used to avoid SQL injection attacks.

You would be better off creating a complete form with all the fields you want and passing the individual fields to your SQL statement. Create a separate TextBox for each field you want to insert into your Names table. In my example below, I have named them emailTextBox, yearTextBox, firstnameTextBox, lastnameTextBox, nameTextBox, and usernameTextBox.

Also, although I am providing a very basic example, you should probably validate each field before you actually send the data to the database. You should validate to ensure required fields have values as well as for format -- email for example has a very specific format, and year should probably be a number. For more information on validation, see the following:

http://msdn.microsoft.com/en-us/library/7kh55542.aspx

Example:

var qry = "Insert into dbo.Names (Email,Year,FirstName,LastName,Name,Username) " + 
          "VALUES (@email, @year, @firstname, @lastname, @name, @username)";    
var cmd = new SqlCommand(qry);
cmd.Parameters.AddWithValue("@email", emailTextBox.Text);
cmd.Parameters.AddWithValue("@year", yearTextBox.Text);
cmd.Parameters.AddWithValue("@firstname", firstnameTextBox.Text);
cmd.Parameters.AddWithValue("@lastname", lastnameTextBox.Text);
cmd.Parameters.AddWithValue("@name", nameTextBox.Text);
cmd.Parameters.AddWithValue("@username", usernameTextBox.Text);

Upvotes: 2

Asif Mushtaq
Asif Mushtaq

Reputation: 13150

From your code it is clear that the values are pretty much defined and there is nothing dynamic, so you should provide a proper form to enter the data.

This approach is vary dangerous and is prone to SQL injection.

Upvotes: 1

James Hill
James Hill

Reputation: 61872

This is a terrible and potentially dangerous approach. I suggest that you create a form which collects the information you need, with individual, properly labeled fields.

Since you're only collecting a small amount of data, the form should be very simple. It should include both client-side and server-side validation before you insert the data into your DB.

Upvotes: 8

Related Questions