Marc Boizeau's blog
Friday, March 25, 2005
Thursday, March 17, 2005
Quotes & parameters
Saw this question on a newsgroup(microsoft.public.data.oledb ) :
"How can legally include " ' " (single quote) in my SQL statement?"
This is a common question and many times it could be a security problem because of SQL injection. Single quote is the SQL string delimiter so you may have to avoid it from you sql code or double it in order to have a valid sql statement.
There is a more elegant way to solve this problem: the use of parameters.
lets say that cnx is a valid connection the syntax in c# for .net will be :
OleDbDataAdapter daRes = new OleDbDataAdapter("SELECT IDT_EMP,EMP_NAME FROM EMP where IDT_EMP = ?",cnx);
daRes.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IDT_EMP", System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(4)), ((System.Byte)(0)), "IDT_EMP", System.Data.DataRowVersion.Current, null));
notice :If you have many parameters all of them will be represent by a "?". You will have to follow the order of the ?s in the SQLstatement when you add your parameters to the oledbcommand parameters collection .
that's all folks
hope this helps !
comments are welcome. ¶ 5:38 AM 0 comments
"How can legally include " ' " (single quote) in my SQL statement?"
This is a common question and many times it could be a security problem because of SQL injection. Single quote is the SQL string delimiter so you may have to avoid it from you sql code or double it in order to have a valid sql statement.
There is a more elegant way to solve this problem: the use of parameters.
lets say that cnx is a valid connection the syntax in c# for .net will be :
OleDbDataAdapter daRes = new OleDbDataAdapter("SELECT IDT_EMP,EMP_NAME FROM EMP where IDT_EMP = ?",cnx);
daRes.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IDT_EMP", System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(4)), ((System.Byte)(0)), "IDT_EMP", System.Data.DataRowVersion.Current, null));
notice :If you have many parameters all of them will be represent by a "?". You will have to follow the order of the ?s in the SQLstatement when you add your parameters to the oledbcommand parameters collection .
that's all folks
hope this helps !
comments are welcome. ¶ 5:38 AM 0 comments
