Error with encoding apostrophe to and from database

I have an html string that is dynamically created server side.

strHTML = "<td><a href""google.com"" onclick=""SaveToDatabase('" + arrString(0) + "','" + arrString(1) + "')""  

The value of arrString(1) is javascript:OpenDoc('ProductManual.pdf','vbShowDoc')

Error Message: Expected ')开发者_JAVA百科'

How can I encode this value to save in the database, and decode it when retreving it from the database ?

---

simple

you should replace ' to \'

(C#)

arrString(0)=arrString(0).replace("'",@"\'")

---

The problem is you are SaveToDatabase('whatever','javascript:OpenDoc('ProductManual.pdf','vbShowDoc')')""

note the problem with your quotes here. you have an opening quote at 'javascript but your closing quote it taken to be after OpenDoc(' thats not your closing quote, hence use \' You need to encode your quote with \'

Also see: http://forums.asp.net/t/1047952.aspx

Edit: I see Mid787 beat me to it, but I wanted to provide some additional info

---

Any time you read input from the user or any outside source, you should ALWAYS ALWAYS ALWAYS escape it before attempting to build a SQL command from it.

One way to do this is to use prepared statements or stored procedures and use the built-in function calls to set variables. Then the function will take care of any escaping.

If you want or need to hand-build the SQL, write a function to escape it. For most SQL engines, it is sufficient to look for any embedded single quotes and double them, that is, turn

Pat O'Hara said 'Hello'

into

Pat O''Hara said ''Hello''

Some SQL engines have other characters that require escaping, like in Postgres you should also double back-slashes.

It's easy enough to write a function to do this, and then ALWAYS use it.

I've had many conversations with programmers where they say they didn't bother in this case because they didn't think it was likely that anyone would ever enter a quote into this particular field. But usually the reason we're having the conversation is because some one did. Don't analyze it! Just do it! It's like wearing a seat belt. You could sit there and calculate the odds that you'll get in an accident on this particular trip, but why? The wise driver makes it a mindless, automatic act to just always put on his seatbelt, even if all he's doing is moving the car from the driveway to the street. The wise database programmer always escapes input strings, because you never know.

---

If you're trying to pass in a string like

INSERT dbo.table(foo) VALUES('foo'bar');

You need to double up the apostrophes:

INSERT dbo.table(foo) VALUES('foo''bar');

But @John Hartsock is absolutely right: you shouldn't be sending ad hoc concatenated strings to SQL Server. If you use parameters you can avoid this issue altogether.