Apostrophe replacement in asp.net to prevent SQL error

Question

I have a web form with the "Name" field that I want to be able to accept single apostrophes, for example, the name O'Leary, but when I try to push this entry to the SQL 2005 server, I get an error message, My question is not in that. This is when I try to insert an entry in db using this statement ...

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

I get O''Leary in the database, not O'Leary. Thought SQL should have considered these double single apostrophes as one apostrophe ???

Answer 1

You will be better off using parameterized queries. They automatically process single quotes and protect you from SQL Injection.

Inserting double single quotes (did I say that?) Is a way to avoid data. It should work, but this is not the best practice.

See this article for a much more complete answer:

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

What I suggest is step 3.

Change - better read the question

If you are already using parameterized queries, or a stored procedure, and you set the acctName value to the parameter value, then you do not need to avoid quotes yourself, This is processed automatically.

It is also handled by several tools, including the Mirosoft Patterns and Practices database library. This has several commands where you can pass in an operator and an array of objects that are used as parameter values ​​- which also controls the escaping.

If this is one of those cases, you can completely exclude the line of code in which you replace the values.

Answer 2

Without a cuff, without knowing too much, I would recommend checking the SET QUOTED_IDENTIFIER parameter on SQL Server. More information can be found here . Let me know if this helps.

Answer 3

Depends on how you insert data into the database.

If you use dynamic SQL and build the SQL string yourself, you are responsible for doubling the quotes yourself. But if you use a parameterized query (as it should be, and probably there is), then the mechanism will take care of this for you, and if you double the quotes yourself, you will get double quotes in the database.

Please note: if you started with dynamic SQL and switched to parameterized queries, this problem would suddenly appear while making changes.

Answer 4

It depends on what request you are really sending. If you submit, then this is what will be saved. You need to double the value of ' , but for other reasons (mostly security, but, of course, syntax).

Please send the code that you use to send the request.