0

My database program has a statement like:

variable = "SELECT * from Staff where StaffName = '" & cStaffName & "'"

This works fine until I have a member of staff with ' (apostrophe) in there name as it ends the start apostrophe.

SELECT * from Staff where StaffName = 'O'Conner'

Is there a way around this without replacing the apostrophe in her name?

Tasos K.
  • 7,979
  • 7
  • 39
  • 63
Graham Jones
  • 165
  • 4
  • 19
  • change the ' to '' via replace function, however you should consider parameterized queries to avoid sql injection – ah_hau Oct 01 '14 at 09:11

2 Answers2

2

You just need to use a parameterized query.

Using con = new SqlConnection(....)
Using cmd = new SqlCommand("SELECT * from Staff where StaffName = @name", con)
   con.Open
   cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = cStaffName
   Using reader = cmd.ExecuteReader
     .....
   End Using
End Using
End Using

In this scenario you add a parameter to the SqlCommand parameters collection. The command text has no more a string concatenation but contains a parameter placeholder (@name). The parameter itself contains the value you want to pass to your query. In this way there is no problem with quotes embedded in the value.
You also get the extra benefit to avoid any kind of Sql Injection problem with the user input

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Arr I see, like that's the way I do Insert and Update Commands. I'll give it ago, thanks. – Graham Jones Oct 01 '14 at 11:19
  • Hope it helps. Note also the usage of the Using Statement. This is another life-saver because it close and dispose the objects in the using block, also in case of Exceptions – Steve Oct 01 '14 at 12:31
0
variable = "SELECT * from Staff where StaffName = '" & Replace(cStaffName, "'", "\'") & "'"
Ciper123
  • 121
  • 1
  • 13