Divyesh Divyesh - 6 months ago 17
SQL Question

SQL query execution difficulty

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?


String SName=txtBox1.Text;

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

SqlConnection connection = null;
SqlDataReader reader = null;
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);

txtBox.Text=" '+str+' "


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.