Replacing apostrophe in asp.net to prevent SQL error

asp.net sql apostrophe
Jim picture Jim · Sep 19, 2011 · Viewed 9.4k times · Source

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???

Answer

David picture David · Sep 19, 2011

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.

Related questions

Read SQL Table into C# DataTable

I've read a lot of posts about inserting a DataTable into a SQL table, but is there an easy way to pull a SQL table into a .NET DataTable?

Read More
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

I make an outer join and executed successfully in the informix database but I get the following exception in my code: DataTable dt = TeachingLoadDAL.GetCoursesWithEvalState(i, bat); Failed to enable constraints. One or more rows contain values violating non-null, unique, …

Read More
Index (zero based) must be greater than or equal to zero

Hey I keep getting an error: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. My code: OdbcCommand cmd = new OdbcCommand("SELECT FirstName, SecondName, Aboutme FROM User WHERE UserID=1", …

Read More