Escaping ' in Access SQL

Question

I'm trying to do a domain lookup in vba with something like this:

DLookup("island", "villages", "village = '" & txtVillage & "'")

This works fine until txtVillage is something like Dillon's Bay, when the apostrophe is taken to be a single quote, and I get a run-time error.

I've written a trivial function that escapes single quotes - it replaces "'" with "''". This seems to be something that comes up fairly often, but I can't find any reference to a built-in function that does the same. Have I missed something?

score 26 · Accepted Answer · answered Oct 14 '08 at 03:21

26

The "Replace" function should do the trick. Based on your code above:

DLookup("island", "villages", "village = '" & Replace(txtVillage, "'", "''") & "'")

answered Oct 14 '08 at 03:21

Matt

5,052
4
36
54

1

Note that Replace() doesn't seem to exist in older versions of Access (ie. Access 97). – apenwarr Jan 18 '09 at 04:22
Replace() was introduced in Access 2000. – David-W-Fenton Dec 08 '09 at 04:25
1

@David W. Fenton: Just adding a note to disambiguate your use of Access2000, if that's OK with you :) The Replace() function was introduced into VBA6. VBA6 was introduced into the Access UI with effect from Access2000. Access Database Engine SQL does not support a 'replace' function, either natively or via the Jet Expression Services. Using Replace() in SQL only works when using the Access UI (though I admit I know not how), otherwise it will exhibit a syntax error. – onedaywhen Dec 08 '09 at 08:56
...Consequently, it is best to avoid using Replace() in persisted database objects such as Queries/VIEWs/PROCEDUREs and especially Validation Rules and CHECK constraints. – onedaywhen Dec 08 '09 at 08:56

score 3 · Answer 2 · answered Oct 14 '08 at 03:14

3

It's worse than you think. Think about what would happen if someone entered a value like this, and you haven't escaped anything:

'); DROP TABLE [YourTable]

Not pretty.

The reason there's no built in function to simply escape an apostrophe is because the correct way to handle this is to use query parameters. For an Ole/Access style query you'd set this as your query string:

DLookup("island", "village", "village = ? ")

And then set the parameter separately. I don't know how you go about setting the parameter value from vba, though.

answered Oct 14 '08 at 03:14

Joel Coehoorn

399,467
113
570
794

How exactly do you set the paremeter seperately? – StockB Nov 29 '13 at 20:00
Use prepared statements. See http://stackoverflow.com/questions/6572448/msaccess-prepared-statements or http://technet.microsoft.com/en-us/library/aa905910(v=sql.80).aspx. That article is about SQL Server, but I suspect it might work for Access too. – Trevor Dixon Apr 16 '14 at 00:45
Access doesn't allow sequential SQL actions in one statement so SQL injection is harder to do but not impossible. Just that the injection has to result in a single valid SQL action. And domain aggregate function cannot change stored data. – June7 Mar 23 '23 at 19:33

onedaywhen · Answer 3 · 2008-10-14T08:22:05.457

Though the shorthand domain functions such as DLookup are tempting, they have their disadvantages. The equivalent Jet SQL is something like

SELECT FIRST(island)
FROM villages
WHERE village = ?;

If you have more than one matching candidate it will pick the 'first' one, the definition of 'first' is implementation (SQL engine) dependent and undefined for the Jet/ACE engine IIRC. Do you know which one would be first? If you don’t then steer clear of DLookup :)

[For interest, the answer for Jet/ACE will either be the minimum value based on the clusterd index at the time the database file was last compacted or the first (valid time) inserted value if the database has never been compacted. Clustered index is in turn determined by the PRIAMRY KEY if persent otherwise a UNIQUE constraint or index defined on NOT NULL columns, otherwise the first (valid time) inserted row. What if there is more than one UNIQUE constraint or index defined on NOT NULL columns, which one would be used for clustering? I've no idea! I trust you get the idea that 'first' is not easy to determine, even when you know how!]

I've also seen advice from Microsoft to avoid using domain aggregate functions from an optimization point of view:

Information about query performance in an Access database http://support.microsoft.com/kb/209126

"Avoid using domain aggregate functions, such as the DLookup function... the Jet database engine cannot optimize queries that use domain aggregate functions"

If you choose to re-write using a query you can then take advantage of the PARAMETERS syntax, or you may prefer the Jet 4.0/ACE PROCEDURE syntax e.g. something like

CREATE PROCEDURE GetUniqueIslandName
(
   :village_name VARCHAR(60)
)
AS 
SELECT V1.island_name
  FROM Villages AS V1
 WHERE V1.village_name = :village_name
       AND EXISTS 
       (
        SELECT V2.village_name
          FROM Villages AS V2
         WHERE V2.village_name = V1.village_name
         GROUP 
            BY V2.village_name
        HAVING COUNT(*) = 1
       );

This way you can use the engine's own functionality -- or at least that of its data providers -- to escape all characters (not merely double- and single quotes) as necessary.

score 1 · Answer 4 · answered Jun 18 '13 at 13:58

But then, it should be like this (with one more doublequote each):

sSQL = "SELECT * FROM tblTranslation WHERE fldEnglish=""" & myString & """;"

Or what I prefer:

Make a function to escape single quotes, because "escaping" with "[]" would not allow these characters in your string...

Public Function fncSQLStr(varStr As Variant) As String

If IsNull(varStr) Then
        fncSQLStr = ""
    Else
        fncSQLStr = Replace(Trim(varStr), "'", "''")
    End If

End Function

I use this function for all my SQL-queries, like SELECT, INSERT and UPDATE (and in the WHERE clause as well...)

strSQL = "INSERT INTO tbl" & 
    " (fld1, fld2)" & _
    " VALUES ('" & fncSQLStr(str1) & "', '" & fncSQLStr(Me.tfFld2.Value) & "');"

or

strSQL = "UPDATE tbl" & _
    " SET fld1='" & fncSQLStr(str1) & "', fld2='" & fncSQLStr(Me.tfFld2.Value) & "'" & _
    " WHERE fld3='" & fncSQLStr(str3) & "';"

No, no, no, this couldn't be any more wrong. – Joel Coehoorn Mar 23 '23 at 20:23 — Joel Coehoorn, Mar 23 '23 at 20:23

score 1 · Answer 5 · answered Jan 04 '09 at 09:12

Parametrized queries such as Joel Coehoorn suggested are the way to go, instead of doing concatenation in query string. First - avoids certain security risks, second - I am reasonably certain it takes escaping into engine's own hands and you don't have to worry about that.

score 0 · Answer 6 · edited Apr 04 '13 at 21:33

0

put brackets around the criteria that might have an apostrophe in it.

SOmething like:

DLookup("island", "villages", "village = '[" & txtVillage & "]'")

They might need to be outside the single quotes or just around txtVillage like:

DLookup("island", "villages", "village = '" & [txtVillage] & "'")

But if you find the right combination, it will take care of the apostrophe.

Keith B

edited Apr 04 '13 at 21:33

p.s.w.g

146,324
30
291
331

answered Apr 04 '13 at 21:15

keith b

19
4

Brackets define objects, not string inputs. Tested and failed. This is not a valid answer. – June7 Mar 23 '23 at 19:48

score 0 · Answer 7 · answered Oct 14 '08 at 03:22

I believe access can use Chr$(34) and happily have single quotes/apostrophes inside.
eg

DLookup("island", "villages", "village = " & chr$(34) & nonEscapedString & chr$(34))

Though then you'd have to escape the chr$(34) (")

You can use the Replace function.

Dim escapedString as String

escapedString = Replace(nonescapedString, "'", "''")

score -1 · Answer 8 · answered Oct 14 '08 at 03:16

-1

By the way, here's my EscapeQuotes function

Public Function EscapeQuotes(s As String) As String

    If s = "" Then
        EscapeQuotes = ""
    ElseIf Left(s, 1) = "'" Then
        EscapeQuotes = "''" & EscapeQuotes(Mid(s, 2))
    Else
        EscapeQuotes = Left(s, 1) & EscapeQuotes(Mid(s, 2))
    End If

End Function

answered Oct 14 '08 at 03:16

inglesp

3,299
9
32
30

that's what i was asking for really. i don't use vba much, and i don't find the help files very helpful! cheers. – inglesp Oct 14 '08 at 03:26

score -1 · Answer 9 · edited Dec 31 '11 at 12:28

-1

For who having trouble with single quotation and Replace function, this line may save your day ^o^

Replace(result, "'", "''", , , vbBinaryCompare)

edited Dec 31 '11 at 12:28

Shef

44,808
15
79
90

answered Feb 25 '10 at 02:28

niceboomer

1

score -3 · Answer 10 · edited Jun 14 '13 at 18:47

-3

My solution is much simpler. Originally, I used this SQL expression to create an ADO recordset:

Dim sSQL as String
sSQL="SELECT * FROM tblTranslation WHERE fldEnglish='" & myString & "';"

When myString had an apostrophe in it, like Int'l Electrics, my program would halt. Using double quotes solved the problem.

sSQL="SELECT * FROM tblTranslation WHERE fldEnglish="" & myString & "";"

edited Jun 14 '13 at 18:47

Jared

25,627
7
56
61

answered Jun 14 '13 at 18:26

dubi

1

5

Yeah, so instead of an apostrophe-problem you now have an double-quotes-problem :-/ – Sascha L. Nov 13 '17 at 12:13

Escaping ' in Access SQL

10 Answers10

Linked