Problems getting SQL to execute

Question

4.00/5 (1 vote)

See more:

In my project I want to insert data to a table from another table in the database

I used query as follows

C#

String st = "select code from state where name='" + this.statename.Text  + "'";
                            String str = "insert into branch(Code,Name,metro,state)values ('" + this.txtbranchcode.Text + "','" + this.txtbranchname.Text + "','" + this.cmbmetro.SelectedIndex + "','" + st + "')";

but compile time no error
at run time it is showing error as syntax error at Karnataka

here Karnataka means my statename.text

pls correct me error

Posted 14-Jan-10 22:37pm

LukmanulHakeem.T

Updated 13-Mar-15 3:31am

Herman<T>.Instance

v3

Add a Solution

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

johnsyd · Answer 1 · 2010-01-18T18:34:00

I agree with the previous answer that this is a very dangerous approach. If the user is allowed to enter a value into `this.statename.Text` they might type
'; DROP DATABASE; --
The quote closes the open quote making it an empty string, the semicolon terminates the statement and SQL engine then processes the DROP DATABASE as a fresh statement. Ouch!
The `--` following the DROP DATABASE makes the rest of the line a comment. Check out this site: http://michaeldaw.org/sql-injection-cheat-sheet

Anyway...

I think where you're going wrong syntactically is that you are putting single quotes around your select statement in the insert statement, thereby turning it from a select statement into a string literal. I.e.:
values( ..., 'select code from state where name='Karnataka'')
Furthermore, since it already has single quotes in it, it becomes syntactically correct.

My SQL is a bit rusty but I don't think you can have select statements inside VALUES(). I think they have to be literals.
Try this instead. It uses a SELECT to insert into the table:

SQL

String str = "INSERT INTO branch(Code,Name,metro,state)"
+ " SELECT '" + this.txtbranchcode.Text + "','" 
+ this.txtbranchname.Text + "','" 
+ this.cmbmetro.SelectedIndex + "'," 
+ " code FROM state WHERE name='" + this.statename.Text + "'";

Better would be to validate the statename (that it has only letters and no quotes or semicolons etc). Better still would be to send it as a parameterised query - this avoids all the so called SQL injection attacks.

Christian Graus · Answer 2 · 2010-01-14T22:54:00

select code from state where name='Karnataka'

It is kind of nasty that people are entering a state as free text and you're looking up the code that goes with it. You should use the debugger to get the SQL string, then run it in SQL Server to get more detailed errors and/or confirm that the SQL is right. You may need to put things like name in [], because they may be reserved words.

I still think your second line is going to break. Either that, or your database design is really crap. Just like the rest of your code ( I see you still don't care about how secure this code is, or about any sort of basic principles of software design )

I've done some testing, and the SQL I typed in above, which is what should be generated, will work fine on a table called state, with fields called code and name. Perhaps you need to post the scheme of your DB ( by editing your post ). It's clear you have no idea what you're doing, so it's very hard to work out what you're doing wrong exactly, it could be anything.

Problems getting SQL to execute

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0