A better way to add apostrophes with SQL? - Access VBA

Question

I'm in the middle of creating a notepad application for Access - just to stay sharp. I've created the form for housing the Notepad and several buttons for varied functions. The notepad is saved to a table called tblContents as a Memo - this is because of the limit of 255 characters found with text.

I copied a large amount of text from SO and became aware of the apostrophe problem. You see when saving the text to the table I run a SQL statement which, when adding apostrophes, needs to be of a certain syntax (which I can't remember at this point) in order to run.

To maintain exactly what the user entered, apostrophes and all, is there a way to add this using the same SQL? I don't want to loop through the input and have it remove all the apostrophes.

Here's my code for adding what is input by the user:

'Save the memo
Public Sub SaveMemo()

    'Examine the memo object
With Forms!frmNotepad!memo

    'Set focus to memo in order to get length
    .SetFocus

    If Len(.Text) > 0 Then

        'Save to table
        Dim memoContents As String
        memoContents = .Text

        Dim strSQL As String
        strSQL = "INSERT INTO tblContents (Contents)" & _
                 "VALUES ( '" & memoContents & "' ); "

        'Set the database and execute the SQL
        Dim db As Database
        Set db = CurrentDb

        db.Execute strSQL, dbFailOnError

    Else

        MsgBox ("Nothing to save!")

    End If

End With


End Sub

Gord Thompson · Accepted Answer · 2013-05-14 12:30:54Z

3

If you've been digging around here (and elsewhere) looking for sample code then you may have encountered the term SQL Injection. It is a mechanism whereby user input containing apostrophes (and other funny business) can have surprising and sometimes serious side-effects.

If you are operating within Access itself then you could save yourself some trouble by using a Recordset to update the table. Instead of running an INSERT statement you could use

Dim cdb As DAO.Database, rst As DAO.Recordset
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("tblContents", dbOpenDynaset)
rst.AddNew
rst!Contents = memoContents
rst.Update
rst.Close
Set rst = Nothing
Set cdb = Nothing

That way you don't need to worry about escaping characters or getting tripped up by SQL Injection.

answered May 14, 2013 at 12:30

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Katana24 Over a year ago

I'm going to examine SQL injection in more detail now - but off the top of your head is this way superior to Santosh's?

Gord Thompson Over a year ago

@Katana24 The method recommended by Santosh would protect you from the vast majority of SQL Injection situations, but it is not generally considered to be bulletproof. There can be other ways of "fooling" the database engine into doing something other than what the code writer intended. The nice thing about the Recordset approach (and the similar Parameterized SQL mechanism) is that you (the developer) don't need to "roll your own" protection against all those various injection schemes; the data access methods you call will take care of it for you.

Katana24 Over a year ago

After doing some reading I believe that you are correct in your approach. Would the problem you're referring to be that a malicious string statement could get stored in the table and, if ever attached to a backend server (SQL server), could cause corrupt data or worse (drop the table). Is that what you would be concerned about?

Gord Thompson Over a year ago

@Katana24 Yes, that is one potential problem. Some code may operate on the assumption that any data it gets from inside the database is "safe", when it may not be safe at all. That is why it's becoming increasingly popular among developers to avoid "gluing SQL statements together" wherever possible.

Gord Thompson Over a year ago

@Katana24 You're welcome. For an example of that last point (and perhaps a good chuckle as well), look here.

|

Santosh · Accepted Answer · 2013-05-14 11:29:58Z

3

You may use Replace() function

memoContents = .Text
memoContents  = Replace(memoContents ,"'","''")

answered May 14, 2013 at 11:29

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

Collectives™ on Stack Overflow

A better way to add apostrophes with SQL? - Access VBA

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related