I am doing validation while add student from the application. If I run following query
SELECT ID FROM Student WHERE Name =' " '+str+' " '
Invalid column name 'str'.
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
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.