Stored procedure is failing when one of the values contains specials characters

Question

I have a problem with a special character. Because when there is a ' character then the code will fail. How can I make it work with special characters by using a stored procedure below.

        internal bool AddRecord()
        {
             string SQL = "exec SqlInsert ";

             SQL += "'" + _sqlComputer + "', ";
             SQL += "'" + _lastUpdatedBy + "', ";
             SQL += "'" + DateTime.Now + "', ";
             SQL += "'" + _softwareName + "' ";

             return SqlDatabase.Overig(SQL);
        }

Slightly unrelated - don't build SQL queries by appending strings (It makes you more vulnerable to SQL injection). Use an SQLCommand object and add parameters to it. — PhonicUK, May 02 '13 at 12:27
Ok, thank you for your reply. I never did this before. Do you have an example for me? — Jamie, May 02 '13 at 12:28
take a look at http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp - it shows you how to use a stored procedure with parameters. — PhonicUK, May 02 '13 at 12:29
The parameters suggested by @PhonicUK should solve your apostrophe problem as well. Plus, if you have a stored procedure, why do you have sql in your .net application. — Dan Bracuk, May 02 '13 at 12:31

Rajeev Kumar · Accepted Answer · 2013-05-02T13:04:41.900

5

Like this

SqlCommand cmd = new SqlCommand("SqlInsert", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Param1", SqlDbType.VarChar, 25).Value = _sqlComputer ;
cmd.Parameters.Add("@Param2", SqlDbType.VarChar, 50).Value = _lastUpdatedBy ;
cmd.Parameters.Add("@Param3", SqlDbType.DateTime).Value = DateTime.Now
cmd.Parameters.Add("@Param4", SqlDbType.Varchar,50).Value = _softwareName ;

sqlCon.Open();
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
sqlCon.Close();
dr.Dispose();
cmd.Dispose();

Edit As Per Your Requirement

Instead of Passing Values like

SqlDatabase.Overig(SQL);

Do this

SqlDatabase.Overig(_sqlComputer,_lastUpdatedBy,DateTime.Now,_softwareName);

and make changes in Overig method like

Overig(String sqlCom, string UpdatedBy, DateTime dat, string software)
{
SqlCommand cmd = new SqlCommand("SqlInsert", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Param1", SqlDbType.VarChar, 25).Value = sqlCom;
cmd.Parameters.Add("@Param2", SqlDbType.VarChar, 50).Value = UpdatedBy;
cmd.Parameters.Add("@Param3", SqlDbType.DateTime).Value = dat
cmd.Parameters.Add("@Param4", SqlDbType.Varchar,50).Value = software;

sqlCon.Open();
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
sqlCon.Close();
dr.Dispose();
cmd.Dispose();

 }

edited May 02 '13 at 13:04

answered May 02 '13 at 12:32

Rajeev Kumar

4,901
8
48
83

1

+1, even better, if you use the [using](http://msdn.microsoft.com/en-us/library/yh598w02.aspx) statement, for SqlCommand and SqlConnection, – Habib May 02 '13 at 12:34
The SqlCommand I have that in another class. My code just send just one parameter to the other class with one record. So I just need a return. How can I use it when I have the SqlCommand in another class? – Jamie May 02 '13 at 12:36
But the code you are showing in your question. you are passing string to some class. What exactly you wanna do ?? – Rajeev Kumar May 02 '13 at 12:39
You can pass the stored proc parameters to the class you are talking to instead of passing SqlCommand Object and use this code in that class – Rajeev Kumar May 02 '13 at 12:41
I'm not so great in SQL :P Could you help me more with this? Because this is more safe I think – Jamie May 02 '13 at 12:47
I'll test it :) I'll let you know! – Jamie May 02 '13 at 13:07
Does the actually solve OP's problem? the character escape thing? – Jens Kloster May 02 '13 at 13:10
@JensKloster well the way he/she doing was not according to MSDN. – Rajeev Kumar May 02 '13 at 13:12
so define it there.. SqlCon is SqlConnection here – Rajeev Kumar May 02 '13 at 13:14
@RajeevKumar I agree. But it seems out of scope to me. Everyone is always so hyped about SQL injection :) I don't understand why. Yes OP's code is vunarable to SQL Injection, but so what? Thats not why he put in on SO (i think) :) – Jens Kloster May 02 '13 at 13:15
@JensKloster I agree to u. But don't you think we should tell some one right way to do the things. This is for what SO is here. To help others in efficient way.. – Rajeev Kumar May 02 '13 at 13:17
@RajeevKumar In my humble opinion, a good answer must as a very minium solve the problem OP has. An answer may extend to suggestions regarding Design / Security / Architecture. I mean this in a frendly way - I think your answer is good - just not to OP question :) – Jens Kloster May 02 '13 at 13:45
The method you made RajeevKumar will work yes, but the method of JensKloster works too but it much shorter. Just escape them. Only the ' character should be ignore. The other special characters is no problem. I will thank you both! :) – Jamie May 02 '13 at 13:59

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

If you execute the SQL by building a SQLCommand object and adding parameters to it, it'll be easier to use special characters as parameters.

There's a good example of how to do this in this related question: Call a stored procedure with parameter in c#

edited May 23 '17 at 12:15

Community

1
1

answered May 02 '13 at 12:34

PhonicUK

13,486
4
43
62

score 0 · Answer 3 · answered May 02 '13 at 12:31

0

Because when there is a ' character then the code will fail

Then try this:

internal bool AddRecord()
        {
             string SQL = "exec SqlInsert ";

             SQL += "'" + PrepeareForSql(_sqlComputer) + "', ";
             SQL += "'" + PrepeareForSql(_lastUpdatedBy) + "', ";
             SQL += "'" + DateTime.Now + "', ";
             SQL += "'" + PrepeareForSql(_softwareName) + "' ";

             return SqlDatabase.Overig(SQL);
        }

private string PrepeareForSql(string s)
{
   return s.Replace("'","''");
}

answered May 02 '13 at 12:31

Jens Kloster

11,099
5
40
54

Thanks, this is working yes:) Are there more special characters that can make my code fail? – Jamie May 02 '13 at 12:47
@Jamie an execelent quistion :) to be fair - don't know, but [others](http://stackoverflow.com/questions/13712070/special-character-in-sql) have had this problem. I have personally never needed to escape other charaters. – Jens Kloster May 02 '13 at 12:54

score 0 · Answer 4 · answered May 02 '13 at 12:32

A simple way to handle ' is just to add .Replace("'", "''"). This will replace all instances of ' with '' which SQL can handle.

SQL += "'" + _sqlComputer.Replace("'", "''") + "', ";
SQL += "'" + _lastUpdatedBy.Replace("'", "''") + "', ";
SQL += "'" + DateTime.Now + "', ";
SQL += "'" + _softwareName.Replace("'", "''") + "' ";

Stored procedure is failing when one of the values contains specials characters

4 Answers4