insert into database

Question

I've made sure everything pertains to the column types in the database, but I keep getting an SQLCeException. Can anyone tell me what's wrong with this code?

private void ANDPaddDriverButton_Click(object sender, EventArgs e)
{
     string first = ANDPfirstNametextBox.Text;
     string last = ANDPlastNametextBox.Text;
     string mid = textBox5.Text;
     string phone = ANDPphonetextBox.Text;
     string social = ANDPsSNtextBox.Text;
        // EmployeeType="Employee"
     string city = ANDPCityTextbox.Text;
     string state = ANDPStatetextBox.Text;
     string zip = ANDPzipCodetextbox.Text;
     string email = ANDPemailtextBox.Text;
     string address = ANDPaddressTextBox.Text;
     string user = userName.Text;

     DBConn.Open();
     SqlCeCommand cmd = new SqlCeCommand("INSERT INTO [Employee Table] VALUES (" +
            first + "," + last + "," + mid + "," + address + "," + phone + "," + social + ","
                + "Employee" + "," + city + "," + state + "," + zip + "," + email + "," + userName + ")", DBConn);
     cmd.ExecuteNonQuery();
     DBConn.Close();
}

It's got a SQL Injection hole, and a poor table name.

SLaks
– SLaks

2010-12-05 23:28:50 +00:00
Commented Dec 5, 2010 at 23:28 — SLaks
– SLaks, Commented Dec 5, 2010 at 23:28

Alex LE · Accepted Answer · 2010-12-06 00:22:14Z

3

Use Parameters to prevent SQL injection, and the Columns names, because you are relying in the quantity, and order of your table's columns and it will likely change in the future (I'm guessing the column names):

SqlCeCommand cmd = new SqlCeCommand("INSERT INTO [Employee Table] (First, Last, Mid, Address, Phone, Social, Employee, City, State, Zip, Email, UserName) VALUES (@First, @Last, @Mid, @Address, @Phone, @Social, @Employee, @City, @State, @Zip, @Email, @UserName)", DBConn);
cmd.Parameters.AddWithValue("@First", first);
cmd.Parameters.AddWithValue("@Last", last);
cmd.Parameters.AddWithValue("@Mid", mid);
cmd.Parameters.AddWithValue("@Address", address);
cmd.Parameters.AddWithValue("@Phone", phone);
// etc. each column

By the way try to not use spaces in table and columns names ;-)

answered Dec 6, 2010 at 0:22

Alex LE

20.8k4 gold badges32 silver badges30 bronze badges

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

Comments

Lorenzo · Accepted Answer · 2010-12-05 23:28:51Z

2

Your fields of type string/varchar shall be enclosed in single quotes!

SqlCeCommand cmd = new SqlCeCommand("INSERT INTO [Employee Table] VALUES (" +
    "'" + first + "',"

and so on...

Also, as somebody else already commented you're going to greatly expose your code to SQL injection attacks

answered Dec 5, 2010 at 23:28

Lorenzo

29.4k50 gold badges129 silver badges224 bronze badges

7 Comments

SLaks Over a year ago

No. He should use parameters.

Lorenzo Over a year ago

Yes. He should. But that's not the question. He just wanted to know where is the error

Colin Mackay Over a year ago

I think that always answering the question as is is not necessarily the best policy. If he has an obvious security hole and the answer to the question doesn't address that hole then the security hole perpetuates and puts everyone at risk. What if the OP takes the answer and next applies it to code dealing with credit card data. There's a nice easy attack vector right there!

Lorenzo Over a year ago

I completely agree with you both. All the people that commented/answered this question signaled warning of SQL injection. But we cannot know if he's just doing its first insert or if its a simple study exercise or if he's going to write that code on an online application...

Lorenzo Over a year ago

@todd: You're welcome. Glad it helped. Please consider seriously the problem of SQL injection even if this is a basic introduction. ;)

|

SteveCav · Accepted Answer · 2010-12-05 23:32:41Z

0

As Lorenzo said, the string values must be enclosed with single quotes, but please read this page which explains why you shouldn't build a query this way, and shows you how to do it with parameters.

answered Dec 5, 2010 at 23:32

SteveCav

6,7271 gold badge52 silver badges52 bronze badges

Collectives™ on Stack Overflow

insert into database

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related