SQL query execution difficulty

Question

I am doing validation while add student from the application. If I run following query

SELECT ID FROM Student WHERE Name =' " '+str+' " '

It will generate following error:

Invalid column name 'str'.

And my application going to generate DBException.

How can I solve this problem?

Edit

String SName=txtBox1.Text;

String sql="select id from student where name = ' "+SName.Trim()+" ' ";

SqlConnection connection = null;
SqlDataReader reader = null;
try
{
    connection = GetConnection();
    SqlCommand command = new SqlCommand(sql, connection);
    if (_sqltransection != null)
    {
        command.Transaction = _sqltransection;
    }
    reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}            
catch (SqlException ex)
{
    throw new DBException(ex);
}

Where txtBox.Text=" '+str+' "

You need to add few more single quotes to make it as string else it will be parsed as SELECT ID FROM Student WHERE Name =divyesh — Pரதீப்
– Pரதீப், Commented Jun 27, 2016 at 9:11
What's divyesh? Is it a column name, a variable/parameter or a (failed) string literal? — jarlh
– jarlh, Commented Jun 27, 2016 at 9:12
Why do you need a double quote. You could even run it without the double quotes embedded. — SharK
– SharK, Commented Jun 27, 2016 at 9:14
In my application if user enter string like " ' +divyesh+ ' " in textbox then how can i add more single quotes ?. — Divyesh
– Divyesh, Commented Jun 27, 2016 at 9:14

Alpha75 · Accepted Answer · 2016-06-27 09:20:53Z

2

SELECT ID FROM Student WHERE Name =' " '+'divyesh'+' " '

But have no sense...

Maybe you'll prefer something like:

SELECT ID FROM Student WHERE Name like '%divyesh%'

If you want to add single cuotes in the string:

SELECT '''hello'''

edited Jun 27, 2016 at 9:20

answered Jun 27, 2016 at 9:13

Alpha75

2,2801 gold badge30 silver badges55 bronze badges

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

5 Comments

Divyesh Over a year ago

But how can i modify user input ?

SharK Over a year ago

"SELECT ID FROM Student WHERE Name = '" + txtStudentName.Text.Trim() + "'"

Divyesh Over a year ago

I know it have no sense but in my application if user enter string like " ' +str+ ' " then what should i do ?

SharK Over a year ago

To make it more clearer, "SELECT ID FROM Student WHERE Name = ' " + txtStudentName.Text.Trim() + " ' "

Divyesh Over a year ago

"SELECT ID FROM Student WHERE Name = ' " + txtStudentName.Text.Trim() + " not working Generate same error.

Ciro Corvino · Accepted Answer · 2016-06-27 19:27:30Z

Really, you are sending at your dbms this query

SELECT ID FROM Student WHERE Name ='' divyesh ''

your dbms interprets correctly the sql up to

SELECT ID FROM Student WHERE Name =''

then it finds

divyesh ''

and it tries to know first if divyesh it is a column of Student table, since it isn't raise an error, that you see in the application exception..

To correct this error, you should double quoting your strings passed as filter at your dynamic querys in the app:

SELECT ID FROM Student WHERE Name =' "''+str+''" '

Generally, when coding a dynamic query, your case is being prevented, submitting the "str" filter parameter to a some helper method that check if some single quotes are present and eventually doubles them.

This is a (C#, or Java) trivial solution with respect to what said:

"SELECT ID FROM Student WHERE Name ='" + str.Replace("'", "''") + "' "

But I find that it is very difficult that in a database a "Name" field it is stored between single quotes, so I wonder what are you tring to do..

Anyway what you are doing, using single quote in the filter fields, it is also an element of sql-injection attacks..

You should avoid absolutely that sort of dynamic query, and use in your code some api based on an object model of a sql query statement with parameters, and so pass to the object that "contains" your query the input filters, instantiating parameter objects.

update after clarifications in the PO

String SName = null;

if(!string.IsNullOrEmpty(txtBox1.Text))    
     SName = txtBox1.Text.trim();


String sql="select id from student where name = @paramName";

SqlConnection connection = null;
SqlDataReader reader = null;
try
{
    connection = GetConnection();
    SqlCommand command = new SqlCommand(sql, connection);

    SqlParameter param = new SqlParameter("@paramName", SqlDbType.NVarChar, 50);
    param.Value = SName ?? SqlString.Null;
    command .Parameters.Add(param);

    if (_sqltransection != null)
    {
        command.Transaction = _sqltransection;
    }
    reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}            
catch (SqlException ex)
{
    throw new DBException(ex);
}

please note that the SqlDbType.NVarChar, 50 refers to the data type of your db field "Name" (that I suppose to be a varchar) and so it should be equal in tyope and lenght (for a "name" field should go well 50 chars), anyway, the lower the lenght of a parameter input filter is, better it is

In my application if user enter string like " ' +str+ ' " in textbox then what should i do ?

Andrew Lygin · Accepted Answer · 2016-06-27 09:26:03Z

1

First, your resulting query is invalid since double quotation mark is used for database object names, not for string literals: "divyesh" is a column name, 'divyesh' is a string. You need the second one, so change double quotes to single ones.

Second, never ever use user input to construct an SQL-statement like that. If your user chooses a name "'; DROP DATABASE yourdb; SELECT '", your database is gone. Read more on the topic 'SQL injection'.

edited Jun 27, 2016 at 9:26

answered Jun 27, 2016 at 9:22

Andrew Lygin

6,2271 gold badge35 silver badges38 bronze badges

1 Comment

SharK Over a year ago

True absolutely, SQL Injection

Andrew Bogdanov · Accepted Answer · 2016-06-27 15:18:03Z

1

I agree with previous answer. In this case you must use Prepared Statements. In this page you can find examples

answered Jun 27, 2016 at 15:18

Andrew Bogdanov

213 bronze badges

2 Comments

Ruslan Bes Over a year ago

@Andrew_Bogdanov Welcome to Stack Overflow. I wanted to note, that the "previous" answer may be deleted or selected as an accepted answer and thus bubbled up. If you want to reference specific answer use @ notation with the author's name. Once you got enough reputation you can leave comments with improvements to specific answers. Also answering with a single link isn't a good practice since the link may become invalid. Cheers

Andrew Bogdanov Over a year ago

@RuslanBes Thank for your note. What about @ symbol it is very useful information for me. If link in my answer was broken any reader can use google with words "Prepared Statement" and he found all information.

Collectives™ on Stack Overflow

SQL query execution difficulty

4 Answers 4

5 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related