ASP.net / c# SQL code submit via textbox

Question

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.

I can't advise enough against allowing you to submit SQL directly -- especially to a production or integral system database. You could accomplish the same effect just using a form and fully parameterized sql. I do fully understand your position and goal and although it seems like a quick utility it could really bite you down the road. It is worth the extra effort just to do it safely and there are plenty of simple form->database asp.net ado.net examples around that help you accomplish that. — Quintin Robinson
– Quintin Robinson, Commented Jun 6, 2012 at 15:54

James Hill · Accepted Answer · 2012-06-06 15:53:20Z

8

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.

answered Jun 6, 2012 at 15:53

James Hill

62.2k22 gold badges150 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Randolpho · Accepted Answer · 2012-06-06 16:07:02Z

2

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);

edited Jun 6, 2012 at 16:07

answered Jun 6, 2012 at 15:58

Randolpho

56.5k18 gold badges153 silver badges182 bronze badges

3 Comments

Lynn Crumbling Over a year ago

+1 for following up on James' answer and giving him an example.

Lynn Crumbling Over a year ago

Steve, in case it isn't immediately clear to you, Randolpho's approach uses a different textbox for each input value.

Randolpho Over a year ago

@LynnCrumbling Thanks for pointing that out; I'll edit my answer to make it more clear.

Asif Mushtaq · Accepted Answer · 2012-06-06 15:55:17Z

1

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.

answered Jun 6, 2012 at 15:55

Asif Mushtaq

13.1k3 gold badges36 silver badges43 bronze badges

4 Comments

SteveU Over a year ago

Hi, thanks for your reply. I totally understand what everyone is saying. However in this instance I don't think a form is the correct approach. We would only run this once a year and we would be inserting 120 students names into the database. I would like this task performed by a non admin so I don't really want them going into the SQL database and inserting them that way. Jut thought there might be a way. I know you can do it in the phpmyadmin web page ?

Asif Mushtaq Over a year ago

I think you are worried about the amount of work involved in making a web form, but believe me it should not take more than a day to develop the form including all the validation.

SteveU Over a year ago

It's not the work involved in the form its time to input 120 names

SteveU Over a year ago

What about using a CSV file to upload ? Does anyone know how to do this?

Collectives™ on Stack Overflow

ASP.net / c# SQL code submit via textbox

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related