2

I got some strings that contains a single quote (') like Mayor's Office:

Dim Str = "Insert into EntryTbl(Office, DateCreated, TimeCreated)" & _
          "Values('" & OfficeBox.Text & "', " & _
          "       '" & Now.ToShortDateString & "', " & _
          "       '" & Now.ToString("HH:mm:ss") & "')"

and the officebox.text contains a string Mayor's Office

Glad for any help :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
yoyie yoyie
  • 415
  • 1
  • 8
  • 20
  • 5
    Not related to your question, but storing dates and times as strings is a bad idea. If your RDBMS supports both date and time datatypes, use them. Otherwise use whatever the datatype is called that represents both the date and time. – Dan Bracuk Feb 01 '16 at 04:19
  • 2
    You really should not be building SQL queries using strings - it's a bad practice due to SQL injection attacks. You should use a parameterized query which will automatically escape the single-quote for you. – Enigmativity Feb 01 '16 at 04:37
  • tnx for the info..mate :) – yoyie yoyie Feb 01 '16 at 05:17
  • sir @DanBracuk what do u mean by a bad idea ? does it effect in performance ? sir – yoyie yoyie Feb 02 '16 at 01:54

2 Answers2

7

IMO, parametrized query is better because it prevents SQL injection and it will handle escaping for you(no need to write additional method to handle escaping)

Dim cmd As New SqlCommand("", Conn())
With cmd
     .CommandText = "Insert into tbl(Office, DateCreated, TimeCreated)" & _
                    "Values(@office,@DateCreated,@TimeCreated)"
     .Parameters.AddWithValue("@office", OfficeBox.Text)
     .Parameters.AddWithValue("@DateCreated", Now.ToShortDateString)
     .Parameters.AddWithValue("@TimeCreated", Now.ToString("HH:mm:ss"))
     .ExecuteNonQuery()
End With

Take a look at How do I create a parameterized SQL query? Why Should I? for more informations

Community
  • 1
  • 1
Vivek S.
  • 19,945
  • 7
  • 68
  • 85
  • Please note that there are some concerns with AddWithValue: [AddWithValue is Evil](http://www.dbdelta.com/addwithvalue-is-evil/), [AddWithValue is evil!](http://chrisrickard.blogspot.com/2007/06/addwithvalue-is-evil.html), and [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). All those links give reliable alternatives. – Andrew Morton Apr 06 '20 at 12:24
1

The built in solution is to use 

QUOTENAME(@string)

function to put the quotes.

Emacs User
  • 1,457
  • 1
  • 12
  • 19