Replacing apostrophe in asp.net to prevent SQL error

I have a web-form with a Name field which I want to be able to accept single apostrophes, such as in the name O'Leary, but when trying开发者_运维百科 to push this record to the SQL 2005 server, I get an error. My question is not this. It's that when I attempt to insert the record into the db using this statement...

Dim acctName As String = Replace(txtName.Text, "'", "''")

I get O''Leary in the database instead of O'Leary. Thought SQL was supposed to treat these double single apostrophes as one apostrophe???

You'd be better off using parameterized queries. These will automatically handle the single quotes, and protect you better from SQL Injection.

Inserting the double single quotes (did I say that right?) is a way of escaping the data. It should work, but it's not a best practice.

See this article for a much fuller answer:

http://msdn.microsoft.com/en-us/library/ff648339.aspx

What I'm proposing is step 3.

Edit - I should read the question better

If you're already using parameterized queries, or a stored procedure, and you're setting the value of acctName to the value of a parameter, then you do not need to escape the quotes yourself. That's handled automatically.

It's also handled by several tools, including the Mirosoft Patterns and Practices Database library. That has several commands where you can pass in a statement and array of objects that are used as parameter values -that handles the escaping as well.

If either of those are the case, you can completely eliminate the line of code where you're replacing the values.

Depends how you're INSERTing the data into the database.

If you're using dynamic SQL and building the SQL string yourself, you are responsible for doubling the quotes yourself. But if you're using a parameterized query (as you should be, and probably are) then the engine will take care of that for you and, if you double the quotes yourself, you'll get doubled quotes in the database.

Note, if you started with dynamic SQL and switched to paramterized queries, this issue would suddenly appear at the time you made the change.

Off-the-cuff, without knowing too much detail I'd recommend checking the SET QUOTED_IDENTIFIER setting on the SQL Server. More information can be found here. Let me know if this helps.

It highly depends what query you actually submit. If you submit '' then this is what will be saved. You do need to double the ' but for other reasons (mainly security, but of course also syntax validity).

Please submit the code that you use to submit the query.