4

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

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Jim
  • 43
  • 1
  • 3

4 Answers4

5

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.

David
  • 72,686
  • 18
  • 132
  • 173
  • 1
    @David there is nothing to indicate he is not using param. queries. In fact, it is likely he is and that is causing the double-quotes. – D'Arcy Rittich Sep 19 '11 at 19:52
  • Hey you nailed with this post. Sorry it took a while to get back to this post. – Jim Sep 19 '11 at 20:24
1

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Menefee
  • 1,475
  • 1
  • 17
  • 26
  • 1
    Actually I owe you an apology and have reversed my vote. Their observed behaviour *could* be explained if the OP is in the situation where the following is true (1) `SET QUOTED_IDENTIFIER OFF` (2) They are creating the query using string concatenation rather than parameters (3) they are using double quotes to delimit their string literals – Martin Smith Sep 19 '11 at 20:06
1

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.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
0

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.

Ofer Zelig
  • 17,068
  • 9
  • 59
  • 93