insert into sql db a string that contain special character '

Question

i want to insert to a sql table a string that might contain ' character.

what is my best way to do so ? should i insert a \ before the ' ? here's my command in a c# code:

SqlCommand myCommand = new SqlCommand(
    String.Format(
    "insert into ACTIVE.dbo.Workspaces_WsToRefile values({0},'{1}',getdate())", 
        folderId, 
        NewWorkspaceName), 
     myConnection);

where NewWorkspaceName might contain ' character, so the insert will cause an exception at the moment.

thanks in advanced, hadas.

What happens when NewWorkspaceName is "hi'); DELETE FROM Users; --" ? — drch
– drch, Commented Aug 8, 2012 at 15:19
i know for sure it won't be "hi'); DELETE FROM Users; --" , my only issue is with handeling ' on the string. — dusm
– dusm, Commented Aug 8, 2012 at 15:21

Stephen Gilboy · Accepted Answer · 2012-08-08 15:23:26Z

7

You should be using SqlParameter. http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

    string query = "insert into ACTIVE.dbo.Workspaces_WsToRefile values(@folderID, @newWorkSpace, @createDate)";

using(SqlCommand cmd = new SqlCommand(query, SqlConnection))
{

    SqlParameter param = new SqlParameter("@folderID", folderId);
    param.SqlDbType = SqlDbType.Int;
    cmd.Parameters.Add(param);
    .....
}

edited Aug 8, 2012 at 15:23

answered Aug 8, 2012 at 15:18

Stephen Gilboy

5,8552 gold badges34 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve · Accepted Answer · 2012-08-08 15:33:39Z

3

You have only one option, forget everything else. Use Parametrized queries like this

SqlCommand myCommand = new SqlCommand("insert into ACTIVE.dbo.Workspaces_WsToRefile" + 
                                      " values(@id, @space, getDate()", myConnection);  
myCommand.Parameters.AddWithValue("@id", folderId);
myCommand.Parameters.AddWithValue("@space", NewWorkspaceName);
myCommand.ExecuteNonQuery();

folderID and NewWorkspaceName, are passed to the Sql Engine inside parameters.
This will take care of special characters like quotes.
But you gain another benefit using parametrized queries. You avoid Sql Injection Attacks

edited Aug 8, 2012 at 15:33

answered Aug 8, 2012 at 15:22

Steve

217k22 gold badges242 silver badges296 bronze badges

Comments

marc_s · Accepted Answer · 2012-08-08 15:23:22Z

1

NewWorkspaceName= NewWorkspaceName.Replace("\'","\'\'");

'' is a ' in sql

edited Aug 8, 2012 at 15:23

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 8, 2012 at 15:20

God Dices

513 bronze badges

Comments

Saba · Accepted Answer · 2015-04-02 12:04:10Z

0

You can try this:

string stringToDatabase=Server.HtmlEncode("կҤїАͻBsdҤїА");

This saves 'stringToDatabase' in your database . Then while retreiving

string OriginalText=Server.HtmlDecode(stringFromDatabase);

edited Apr 2, 2015 at 12:04

Saba

3,6662 gold badges24 silver badges35 bronze badges

answered May 11, 2014 at 16:59

Sheba

7367 silver badges16 bronze badges

1 Comment

a_hamm99 Over a year ago

It has to be WebUtility.HtmlEncode(stringFromDatabase) otherwise it does not work

Collectives™ on Stack Overflow

insert into sql db a string that contain special character '

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related