Please correct my insert query

Question

1.00/5 (1 vote)

See more:

C#

string str = "insert into "+label4.Text+"'(dataid,data) values('"+textBox1.Text+"','"+label2.Text+ "')";
SqlCommand cmd = new SqlCommand(str,con);
cmd.ExecuteNonQuery();
MessageBox.Show("values inserted!");

What I have tried:

when i'm running it this error comes
Incorrect syntax near '='.
Unclosed quotation mark after the character string ')'.

Posted 25-Mar-16 6:03am

Member 10549697

Updated 25-Mar-16 9:37am

Patrice T

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-03-25T06:13:00

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
The chances are that that will cure your problem at the same time.
Especially if you get rid of the spare quote before your open bracket:

C#

string str = "INSERT INTO MyTable (dataid,data) VALUES (@DI, @DT)";
SqlCommand cmd = new SqlCommand(str,con);
cmd.Parameters.AddWithValue("@DI", textBox1.Text);
cmd.Parameters.AddWithValue("@DT", label2.Text);

Dave Kreskowiak · Accepted Answer · 2016-03-25T06:22:00

Solution 2

Google for "C# parameterized sql queries" for the solution to this problem. The only problem with this solution is that you can NOT parameterize the table name. Since you're using a label to hold the table name I don't know why you're ever using a label to hold the name in the first place.

Next, Google for "Sql Injection Attack" to find out why what you're currently doing is so bad. You risk destruction of your database, either inadvertently or intentionally with the code you've written.

Posted 25-Mar-16 6:22am

Dave Kreskowiak

Comments

Member 10549697 26-Mar-16 3:11am

Thank you for the suggestion.the label text is used for name of the table..i'll google for sql injection attack

Dave Kreskowiak 26-Mar-16 11:28am

Why are you getting the name from a LABEL? That's really odd and bad practice.

What you see on screen is a visual representation of the data model in your application. It should never be used AS the data model.

F-ES Sitecore · Accepted Answer · 2016-03-25T07:15:00

Solution 3

Use the debugger to examine the contents of the str variable and it will look like;

SQL

insert into mytable'(dataid,data) values('1','One')

Is that valid SQL? No, you have an unneeded quotation mark (after the table name) which is exactly what the error message is telling you

C#

string str = "insert into "+label4.Text+" (dataid,data) values('"+textBox1.Text+"','"+label2.Text+ "')";

Posted 25-Mar-16 7:15am

F-ES Sitecore

Comments

Richard Deeming 29-Mar-16 8:26am

You've copied the SQL Injection[^] vulnerability from the question.

F-ES Sitecore 29-Mar-16 8:33am

I also copied the awful naming convention as did others :) I was just explaining to him why his code, as pasted, wasn't working.

Richard Deeming 29-Mar-16 8:37am

If you see someone trying to strike the wrong end of a match whilst looking for a gas leak, would you explain which end of the match he should be striking? :)

It's hard enough to get some people to take SQLi seriously, without telling them how to solve their problem the wrong way.

F-ES Sitecore 29-Mar-16 8:44am

The thing about parameterised queries had already been given (mine wasn't the first solution) so there is no point in saying it again, as I stated above I was simply explaining to him why his code wasn't working, to sate his curiosity if nothing else.

Richard Deeming 29-Mar-16 8:46am

Yeah, but guess which answer he'll pay attention to. :)