|
Hi everyone im actually working on an application which migrates the datas that are present in a xml file to my database.. Im coding it on c#.net.. The issue is in my xml file we have customers name which is fetched from the xml file and then inserted into the database.. so most of my customers name have appostrophe(') in their name so during execution of the insert query it throws me odbc exception the insert query that i use is as follows:
insert into EACS_User_1 values('"+uf_Id+"','"+uf_Title+"','"+uf_FirstName+"','"+uf_MiddleName+"','"+uf_LastName+"','"+uf_PhoneNumber+"','"+uf_MobileNumber+"','"+uf_EmailID+"')
here the first name contains appostrophe say for example the name is Moh’d Husam how do i insert such names... Do guide me...
Thanx in advance..
Regards,
Tash
|
|
|
|
|
Hi,
The resolution to your problem and also the approach to avoid sql injection is to use prepared statements. Have a look at this blog (especially the February 18 2006). There you can find an example how to do this in c#.
Regards
Sebastian
|
|
|
|
|
Which blog????
|
|
|
|
|
|
Thanx but i hav a doubt.. actually i googled and i found out that we can use strX.replace("'","''") something like this... but i dont know how to place it in my insert query....
Thanx in advance
Regards,
Tash
|
|
|
|
|
Well e.g. instead of uf_id you write: uf_id.Replace("'","''").
That's it. But again, this is not the way you should insert data into your sql server.
Post the code where you execute the statement and we can show you where to put in the prepared statement.
|
|
|
|
|
XmlDataDocument doc9 = new XmlDataDocument();
doc9.Load(ConfigurationSettings.AppSettings["usr"]);
XmlNodeList nodes9 = doc9.GetElementsByTagName("Row");
foreach(XmlNode node9 in nodes9)
{
if(node9.HasChildNodes && node9.ChildNodes.Count > 0)
{
XmlElement Element9 = (XmlElement) node9;
string u_Id = Element9.GetElementsByTagName("Id")[0].InnerText;
string u_Title = Element9.GetElementsByTagName("Title")[0].InnerText;
string u_FirstName = Element9.GetElementsByTagName("FirstName")[0].InnerText;
string u_MiddleName = Element9.GetElementsByTagName("MiddleName")[0].InnerText;
string u_LastName = Element9.GetElementsByTagName("LastName")[0].InnerText;
string u_FileName = Element9.GetElementsByTagName("FileName")[0].InnerText;
if(u_Id==ugf_UserId)
{
string path4 = ConfigurationSettings.AppSettings["usrfile"]+"//"+u_FileName+".xml";
if (File.Exists(path4))
{
XmlDataDocument doc10 = new XmlDataDocument();
doc10.Load(ConfigurationSettings.AppSettings["usrfile"]+"//"+u_FileName+".xml");
XmlNodeList nodes10 = doc10.GetElementsByTagName("User");
foreach(XmlNode node10 in nodes10)
{
if(node10.HasChildNodes && node10.ChildNodes.Count > 0)
{
XmlElement Element10 = (XmlElement) node10;
string uf_Id = Element10.GetElementsByTagName("Id")[0].InnerText;
string uf_Title = Element10.GetElementsByTagName("Title")[0].InnerText;
string uf_FirstName = Element10.GetElementsByTagName("FirstName")[0].InnerText;
string uf_MiddleName = Element10.GetElementsByTagName("MiddleName")[0].InnerText;
string uf_LastName = Element10.GetElementsByTagName("LastName")[0].InnerText;
string uf_Type = Element10.GetElementsByTagName("Type")[0].InnerText;
string uf_PhoneNumber = Element10.GetElementsByTagName("PhoneNumber")[0].InnerText;
string uf_MobileNumber = Element10.GetElementsByTagName("MobileNumber")[0].InnerText;
string uf_EmailID = Element10.GetElementsByTagName("EmailID")[0].InnerText;
string uf_Branch = Element10.GetElementsByTagName("Branch")[0].InnerText;
string uf_Department = Element10.GetElementsByTagName("Department")[0].InnerText;
string uf_NoOfLoginAttempts = Element10.GetElementsByTagName("NoOfLoginAttempts")[0].InnerText;
string uf_Status = Element10.GetElementsByTagName("Status")[0].InnerText;
if(uf_Id==u_Id)
{
cmd = new OdbcCommand("select UserID from eacs_user_1 where userid ='"+uf_Id+"'" ,con);
rdr = cmd.ExecuteReader();
if(rdr.Read())
{
rdr.Close();
}
else
{
rdr.Close();
Console.WriteLine("{0}",uf_Id);
cmd = new OdbcCommand("insert into EACS_User_1 values('"+uf_Id+"','"+uf_Title+"','"+uf_FirstName.Replace("'","''")+"','"+uf_MiddleName+"','"+uf_LastName+"','"+uf_PhoneNumber+"','"+uf_MobileNumber+"','"+uf_EmailID+"','"+uf_Branch+"','"+uf_Department+"','"+uf_NoOfLoginAttempts+"','"+uf_Status+"','','','','','','')",con);
cmd.ExecuteNonQuery();
}
}
else
{
Console.WriteLine("Please check User file-{0} fields do not match",u_FileName);
}
}
}
}
else
{
Console.WriteLine("User file {0} not present",u_FileName);
}
}
}
}
This is only a part of my code that i have posted...
Thanx in advance..
Regards,
Tash
|
|
|
|
|
Okay, so change the line where you build the command like this:
<br />
<br />
cmd = new OdbcCommand("insert into EACS_User_1 VALUES (@Id,@Title,@FirstName,...)", con);<br />
cmd.Parameters.AddWithValue("@Id", uf_id);<br />
cmd.Parameters.AddWithValue("@Title", uf_Title);<br />
cmd.Parameters.AddWithValue("@FirstName", uf_FirstName);<br />
.<br />
.<br />
.<br />
cmd.ExecuteNonQuery()<br />
<br />
That should do the work.
Regards
Sebastian
|
|
|
|
|
Actually i only find cmd.parameters.add i mean i dont get addwithvalue... should i include anything??? is it because i am using VS2003???
Thanx in advance..
Regards,
Tash
|
|
|
|
|
You are using .NET 2.0, that is why there is no AddWithValue-method. Instead of AddWithValue use Add like this:
cmd.Parameters.Add(new OdbcParameter("@Id", uf_Id));
Regards
Sebastian
|
|
|
|
|
I did as u said but i receive the following Exception:
System.Data.Odbc.OdbcException: ERROR [HY000] [INTERSOLV][ODBC SQL Server driver][SQL Server]Must declare variable '@Id'.
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at ConsoleApplication1.Class1.perform() in c:\documents and settings\900846\my documents\visual studio projects\consoleapplication1\class1.cs:line 635
Thanx in advance...
Regards,
Tash
|
|
|
|
|
Hmmm... try to rewrite to
cmd.Parameters.Add(new OdbcParameter("Id", uf_Id));
|
|
|
|
|
even after making the changes u said i receive xactly the same exception once again... must i change any thing in the insert query???
thanx in advance..
Regards,
Tash
|
|
|
|
|
Ah I remember the good old days. You have to replace all the names with @ within the statement with ?. So your statement should look like this:
...VALUES (?,?,?,...
Be sure that you add the parameters within the same sequence as said in the statement.
|
|
|
|
|
U mean i have to replace @Id by ?
|
|
|
|
|
|
Now My code is as follows:
rdr.Close();
Console.WriteLine("{0}",uf_Id);
cmd = new OdbcCommand("insert into EACS_User_1 values(?,?,?,?,?,?,?,?,?,?,?,?,?)",con);
cmd.Parameters.Add(new OdbcParameter("?",uf_Id));
cmd.Parameters.Add(new OdbcParameter("?",uf_Title));
cmd.Parameters.Add(new OdbcParameter("?",uf_FirstName));
cmd.Parameters.Add(new OdbcParameter("?",uf_MiddleName));
cmd.Parameters.Add(new OdbcParameter("?",uf_LastName));
cmd.Parameters.Add(new OdbcParameter("?",uf_Type));
cmd.Parameters.Add(new OdbcParameter("?",uf_PhoneNumber));
cmd.Parameters.Add(new OdbcParameter("?",uf_MobileNumber));
cmd.Parameters.Add(new OdbcParameter("?",uf_EmailID));
cmd.Parameters.Add(new OdbcParameter("?",uf_Branch));
cmd.Parameters.Add(new OdbcParameter("?",uf_Department));
cmd.Parameters.Add(new OdbcParameter("?",uf_NoOfLoginAttempts));
cmd.Parameters.Add(new OdbcParameter("?",uf_Status));
cmd.ExecuteNonQuery();
And i receive the following exception...
System.Data.Odbc.OdbcException: ERROR [21S01] [INTERSOLV][ODBC SQL Server driver][SQL Server]Insert error: column name or number of supplied values does not match table definition.
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at ConsoleApplication1.Class1.perform() in c:\documents and settings\900846\my documents\visual studio projects\consoleapplication1\class1.cs:line 635
Thanx in advance...
Regards,
Tash
|
|
|
|
|
As the error message is saying the number of ? does not match the number of columns. So you got two options:
1) Write a ? for every column that exists in your table
2) Include the column names in your statement.
I would suggest using the second option, because it is more fail-safe than the first option. Rewrite your statement to include the column names:
INSERT INTO EACS_User_1 (Id, Title, ...) VALUES (...
Regards
Sebastian
|
|
|
|
|
else
{
rdr.Close();
Console.WriteLine("{0}",uf_Id);
cmd = new OdbcCommand("insert into EACS_User_1 values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",con);
cmd.Parameters.Add(new OdbcParameter("?",uf_Id));
cmd.Parameters.Add(new OdbcParameter("?",uf_Title));
cmd.Parameters.Add(new OdbcParameter("?",uf_FirstName));
cmd.Parameters.Add(new OdbcParameter("?",uf_MiddleName));
cmd.Parameters.Add(new OdbcParameter("?",uf_LastName));
cmd.Parameters.Add(new OdbcParameter("?",uf_Type));
cmd.Parameters.Add(new OdbcParameter("?",uf_PhoneNumber));
cmd.Parameters.Add(new OdbcParameter("?",uf_MobileNumber));
cmd.Parameters.Add(new OdbcParameter("?",uf_EmailID));
cmd.Parameters.Add(new OdbcParameter("?",uf_Branch));
cmd.Parameters.Add(new OdbcParameter("?",uf_Department));
cmd.Parameters.Add(new OdbcParameter("?",uf_NoOfLoginAttempts));
cmd.Parameters.Add(new OdbcParameter("?",""));
cmd.Parameters.Add(new OdbcParameter("?",""));
cmd.Parameters.Add(new OdbcParameter("?",""));
cmd.Parameters.Add(new OdbcParameter("?",""));
cmd.Parameters.Add(new OdbcParameter("?",""));
cmd.Parameters.Add(new OdbcParameter("?",""));
cmd.ExecuteNonQuery();
}
But again the appostrophe problem is there.... i get the following exception...
System.Data.Odbc.OdbcException: ERROR [HY000] [INTERSOLV][ODBC SQL Server driver][SQL Server]Error converting client characters into server's character set. Some character(s) could not be converted.
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at ConsoleApplication1.Class1.perform() in c:\documents and settings\900846\my documents\visual studio projects\consoleapplication1\class1.cs:line 640
Thanx in advance..
Regards,
Tash
|
|
|
|
|
Hmm did you checked that the database character set is identical to the one you use within your client app? Check the database char set by using the management studio to administer your sql database.
So you choosed option one and added the columns to your statement. This is not the best way because next time you add a column to the table you have to change your code too...
|
|
|
|
|
u mean the datatype that is assigned to that particular column in my database....
|
|
|
|
|
No, like the error is saying the char set of your client app does not match the server. Check your local encoding with Encoding.Default and the code page used within the database (connect to the server and check the properties of the database with the management studio).
To get the local encoding use Encoding.Default:
http://msdn.microsoft.com/en-us/library/system.text.encoding.default.aspx[^]
|
|
|
|
|
Thanx a lot my issue is solved... actually the name had a symbol (’) and i thought it to be an apostrophe now i replace it with (') in my insert query.. The prepared statement which u specified also works fine... thanx for ur guidance.. Thanx alot...
Regards,
Tash
|
|
|
|
|
public static class Util
{
public static void AddParameters(SqlCommand cmd, params object[] args)
{
for (int i = 0; i < args.Length; i += 2)
{
cmd.Parameters.Add(GetParameter((string)args[i], args[i + 1]));
}
}
public SqlParameter GetParameter(string name, object value)
{
SqlParameter p = new SqlParameter();
p.ParameterName = name;
p.SqlDbType = GetDbType(value);
p.Direction = ParameterDirection.Input;
p.Value = value;
return p;
}
public SqlDbType GetDbType(object value)
{
if (value == null) throw new ArgumentNullException();
if (t == typeof(int)) return SqlDbType.Int;
if (t == typeof(string)) return SqlDbType.NVarChar;
if (t == typeof(DateTime)) return SqlDbType.DateTime;
if (t == typeof(Boolean)) return SqlDbType.Bit;
if (t == typeof(byte[])) return SqlDbType.Image;
if (t == typeof(SqlBinary)) return SqlDbType.Binary;
if (t == typeof(Guid)) return SqlDbType.UniqueIdentifier;
if (t == typeof(Int64)) return SqlDbType.BigInt;
throw new NotSupportedException("Missing support for type " + t.FullName + ".");
}
}
A little bit of code, but you can now easily use it many places, for example like this:
SqlCommand getInsertCommand(string col1, int col2)
{
SqlCommand cmd = new SqlCommand("INSERT [Table] VALUES (@col1, @col2);");
Util.AddParameters(cmd,
"@col1", col1,
"@col2", col2);
return cmd;
}
|
|
|
|
|
The way SeMartens is suggesting is good, but the C# stuff is a little old: "Parameters.Add(...).Value = ..." has been depreciated. You should use "Parameters.AddWithValue(paramName, paramValue)" instead.
All those who believe in psycho kinesis, raise my hand.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
forgot the link: