Replacing single quote within string

Question

I want to replace single quote within a string. this doesn't include beginning & end single quote. for example:

Inputs: this is few example of inputs

 myString = "'O'Nel'"
 myString = "'OR'Nel, Dan' " 
 myString = "'OA'Nel'Test, John; random's t'est'"

Output this is the result I want

 myString = "'O''Nel'"
 myString = "'OR''Nel, Dan' " 
 myString = "'OA''Nel''Test, John; random''s t''est'"

what I tried so far. this code will replace begining & end single quote, which i dont want. I want to keep code simple, maybe there is a buildin function

 myString = myString.Replace("'","''")

 output myString = "''O''Nel''"

Just exclude the beginning and ending of the string using [Substring](https://learn.microsoft.com/en-us/dotnet/api/system.string.substring?view=net-7.0) — LarsTech, Aug 25 '23 at 18:13
Noooooo. _Don't do it._ This is almost always about using the values in an SQL statement, and this is the **completely wrong approach** to the problem. It _WILL_ create meaningful security issues in your code. Instead, learn about parameterized queries. As a bonus, query parameters are also _faster_. — Joel Coehoorn, Aug 25 '23 at 18:40
If the reason is not what is described in the previous comment, you could use `myString = Regex.Replace(myString, "(?!^'|'$)'", "''")` -- If it's related to db storage, then **don't**, ever ,do that. Using Parameters, this very common issues, and others you haven't thought about or found yet, is already resolved — Jimi, Aug 25 '23 at 18:48

Joel Coehoorn · Accepted Answer · 2023-08-28T13:45:09.510

This is almost always about preparing the string for use in an SQL statement. If that's not what's going on here you can disregard this. But if it is, you want to go a completely different direction. You don't need to change the strings at all, and instead should do something more like this:

Dim myString As String = "'OA'Nel'Test, John; random's t'est'"

'This can be const, because it won't ever change
Const SQL As String = " ... WHERE SomeColumn = @myString"

'This is just one way to talk to db, but faster for showing the example
Using cn  As New SqlConnection("connection string"), _
      cmd As New SqlCommand(SQL, cn)

    ' This connects the value in your string variable to the @myString placeholder
    cmd.Parameters.Add("@myString", SqlDbType.NVarChar, 50).Value = myString

    ' Open the connection and run the query
    cn.Open()
    cmd.Execute...()

End Using

Note at no time in the above code (even on the DB server) is the content of myString ever substituted into the SQL statement. The two are kept completely separate throughout, so no possibility of injection exists.

As a bonus, the database can cache the execution plan, which means you can also often get faster DB responses (it doesn't have to rebuild that part each time), and saves work and memory building new strings on the client.

This is one of a few things that's too important to do wrong even for practice, learning, and proof of concept code.

score 0 · Answer 2 · answered Aug 25 '23 at 18:47

If all ypour string have a single quote at the begining and end , you need some basic string functions

If you need the string in a sql statement you can see here were joe alreday wrote teh same answer How do I create a parameterized SQL query? Why Should I? so then you don't need to escape the single quotes at all

Dim mystring As String
mystring = "'O'Nel'"
Debug.Write(mystring & "<-> " 
& "'" & mystring.Substring(1).Substring(0, mystring.Length - 2).Replace("'", "''") & "'")

Output

'O'Nel'<-> 'O''Nel'

Replacing single quote within string

2 Answers2