Click here to Skip to main content
15,849,615 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i have a textbox..when iam entering some text with special characters it is giving error...for example., "ari's @re in the'"...it is giving error in sql syntax(since iam displaying that text in a grid view using SQLConnection)

bool result = false;
            DataSet LocalDS = TSDS;
            OdbcConnection ConObj = null;
            OdbcCommand CmdObj = null;
            try
            {
                ConObj = new OdbcConnection(ConnString);
                ConObj.Open();
                if (LocalDS.Tables[0].Rows.Count != 0)
                {
                    DateTime dt;
                    string date;
                    foreach (DataRow dr in TSDS.Tables[0].Rows)
                    {
                        dt = Convert.ToDateTime(dr["Date"]);
                        date = dt.Year + "-" + dt.Month + "-" + dt.Day;
                        CmdObj = new OdbcCommand("insert into Record_table(Emp_name, Dates, Hours, Task , Client , project, Comments, TeamMemberType)" +
                            "values('" + Emp_name + "', '" + date + "'," + dr[4] + ",'" + dr[2] + "' ,'" + dr[0] + "' ,'" + dr[1] + "','" + dr[6] + "','" + dr[5] + "')", ConObj);
                        CmdObj.ExecuteNonQuery();
                        result = true;
                    }
                }
                else { result = false; }
            }
// the comments part is having special characters in its text..that is giving problem
Posted
Updated 24-Jan-14 19:33pm
v2
Comments
Sampath Lokuge 24-Jan-14 9:23am    
Can you put your code snippet ?
ahmed ali mohd 25-Jan-14 0:30am    
bool result = false;
DataSet LocalDS = TSDS;
OdbcConnection ConObj = null;
OdbcCommand CmdObj = null;
try
{
ConObj = new OdbcConnection(ConnString);
ConObj.Open();
if (LocalDS.Tables[0].Rows.Count != 0)
{
DateTime dt;
string date;
foreach (DataRow dr in TSDS.Tables[0].Rows)
{
dt = Convert.ToDateTime(dr["Date"]);
date = dt.Year + "-" + dt.Month + "-" + dt.Day;
CmdObj = new OdbcCommand("insert into Record_table(Emp_name, Dates, Hours, Task , Client , project, Comments, TeamMemberType)" +
"values('" + Emp_name + "', '" + date + "'," + dr[4] + ",'" + dr[2] + "' ,'" + dr[0] + "' ,'" + dr[1] + "','" + dr[6] + "','" + dr[5] + "')", ConObj);
CmdObj.ExecuteNonQuery();
result = true;
}
}
else { result = false; }
}
// the comments part is having special characters in its text..that is giving problem

You're most likely writing in-line SQL

if you write an SQL statement in c# such as:

C#
string SQL = "SELECT * FROM TableA WHERE Field a like '" + input + "'"


It means anything in the input which breaks to quotes is going to create a syntax error.

For this very purpose ADO.Net and parameters allow you to negate the problem of escaping your input values.

C#
IDbCommand cmd = new connection.CreateCommand();
cmd.CommandText = "string SQL = "SELECT * FROM TableA WHERE Field a like @input"";
IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = "@input";
param.DbType = DbTypes.String;
cmd.Parameters.Add(param);

IDataReader reader = cmd.ExecuteReader();


Using this approach where you let ADO manage adding your input through parameters will resolve you problems.

Also, using in-line SQL creates a large security risk. Consider the following input.

';GO;DELETE FROM TableA;GO;

A statement like this inserted using in-line SQL has the potential to do damage. This type of attack is called an SQL Injection Attack

SQL Injection[^]
 
Share this answer
 
Comments
ahmed ali mohd 25-Jan-14 0:24am    
thanks for reply..but how do i include all the special characters?..cant i change in textbox and its properties?
Stephen Hewison 26-Jan-14 4:44am    
A text box doesn't stop you adding special character. What character can't you add to your text box?
ahmed ali mohd 27-Jan-14 4:14am    
@ ' " ....i want to use these
Stephen Hewison 27-Jan-14 4:39am    
I've told you how to do that. Stop writing the value from the textbox directly into your SQL statement and use parameters instead.
Try using Parameterized query , it saves the DB from SQL_injection[^]

C#
CmdObj = new OdbcCommand("insert into Record_table(Emp_name, Dates, Hours, Task , Client , project, Comments, TeamMemberType)" +
                           "values( @Emp_name,@Dates, @Hours, @Task , @Client , @project, @Comments, @TeamMemberType", ConObj)

           CmdObj.Parameters.AddWithValue("@Emp_name", Emp_name);
           CmdObj.Parameters.AddWithValue("@Dates", date);
           CmdObj.Parameters.AddWithValue("@Hours", dr[4]);
           CmdObj.Parameters.AddWithValue("@Task", dr[2]);
           CmdObj.Parameters.AddWithValue("@Client", dr[0]);
           CmdObj.Parameters.AddWithValue("@project", dr[1]);
           CmdObj.Parameters.AddWithValue("@Comments", dr[6]);
           CmdObj.Parameters.AddWithValue("@TeamMemberType", dr[5]);
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900