Solved How to handle ' in sql string

[John Sh]

I fave an sql string that takes it's input from a couple of tempvars.
The input for one of the tempvars is Sp. 'Yarawah' from a table field, so the tempvars.value presented to the sql string is "Sp. 'Yarawah'"
The single inverted commas are part of the scientific name.
db.execute crashes with this input.
How do I present this data to the sql string

sQry = "INSERT INTO images ( Genus, SpeciesEpithet, [Image], Collection, Author, sub, infrafamily)  " & _
           "SELECT A.Genus, A.SpeciesEpithet, A.[image], 'Collier' AS exp1, 'Barry Collier' as exp2, A.subspecies, a.infrafamily " & _
           "FROM Collier_Collection as A " & _
           "WHERE A.Genus='" & [TempVars]![Genus] & "' " & _
           "AND A.SpeciesEpithet='" & [TempVars]![Species] & "';"
    oDB.Execute sQry, dbFailOnError

 

[arnelgp]

sQry = "INSERT INTO images ( Genus, SpeciesEpithet, [Image], Collection, Author, sub, infrafamily)  " & _
           "SELECT A.Genus, A.SpeciesEpithet, A.[image], 'Collier' AS exp1, 'Barry Collier' as exp2, A.subspecies, a.infrafamily " & _
           "FROM Collier_Collection as A " & _
           "WHERE A.Genus='" & Replace([TempVars]![Genus],"'","''") & "' " & _
           "AND A.SpeciesEpithet='" & Replace([TempVars]![Species],"'", "''") & "';"
    oDB.Execute sQry, dbFailOnError

 

[John Sh]

Thank you.
I had tried a few variations of that but not that particular one.
John

 

[MarkK]

IMO, a far more robust solution looks like...

Sub DoInsert()
    Const SQL_INSERT = _
        "INSERT INTO images " & _
            "( Genus, SpeciesEpithet, [Image], Collection, Author, sub, infrafamily ) " & _
        "SELECT A.Genus, A.SpeciesEpithet, A.[image], 'Collier', 'Barry Collier', " & _
            "A.subspecies, a.infrafamily " & _
        "FROM Collier_Collection as A " & _
        "WHERE A.Genus = [prmGenus] " & _
           "AND A.SpeciesEpithet = [prmSpecies];"
          
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters("prmGenus") = TempVars!Genus
        .Parameters("prmSpecies") = TempVars!Species
        .Execute dbFailOnError
        .Close
    End With
End Sub

In #2, a double quote in TempVars!Genus would break the SQL all over again. Also, depending on what you put in TempVars!Genus, you could actually modify the structure of the WHERE clause. eg: "Sp. 'Yawara'" Or A.Genus LIKE '*"

 

[John Sh]

In #2, a double quote in TempVars!Genus would break the SQL all over again. Also, depending on what you put in TempVars!Genus, you could actually modify the structure of the WHERE clause. eg: "Sp. 'Yawara'" Or A.Genus LIKE '*"

Thank you.
I have implemented arnelgp's solution and it works seamlessly.

 

[cheekybuddha]

In #2, a double quote in TempVars!Genus would break the SQL all over again.

How so?

 

[Minty]

The parameterised query is by far the best way to handle potential character issues.
Imagine passing in a password - something like
d£t%"_ u|?''p5=

The parameterised query handles it seamlessly.
Replacing things would almost certainly fail to handle this in most cases, and obscures what is being passed in.
You could argue that no one would use those characters, however an encrypted version of the string will use all sorts of weird characters.

 

[John Sh]

A clarifier.
Tempvars!genus will never have quotes, double or single, in the string. Genus is a scientific name and is mostly Latin and never has quotes.
Species is of similar vein, but in cases where the actual species has not been derived and accepted, a species as in my example, with "Sp." is used as an interim with the word in quotes being a location or some other descriptive word to define the specimen from those that might be similar but have been fully identified.

 

[cheekybuddha]

The parameterised query is by far the best way to handle potential character issues.
Imagine passing in a password - something like
d£t%"_ u|?''p5=

The parameterised query handles it seamlessly.

No-one disputes that a parameterised query is excellent for this, but Arnel's code in #2 handles your example just fine too.

 

[June7]

Note, these are not "single inverted commas", they are apostrophes.

 

[cheekybuddha]

Note, these are not "single inverted commas", they are apostrophes.

AKA 'single quote'

 

[June7]

AKA 'single quote'

Which only adds to the confusion. I consider a "single quote" as " and a "double quote" as "".

 

[isladogs]

Which only adds to the confusion. I consider a "single quote" as " and a "double quote" as "".

Really? That's a double quote and a double double quote to many of us!

 

[Ken Sheridan]

Which only adds to the confusion. I consider a "single quote" as " and a "double quote" as "".

I would term that a double quote character, and a contiguous pair of double quote characters. The single quote character can only be termed an apostrophe in the context of its use to signify the omission of one or more characters in a word, not in the context of its use as a delimiter.

 

[isladogs]

@Ken Sheridan
I largely agree except to say that apostrophes are also used to indicate ownership. e.g. both Ken's and don't contain apostrophes.
Taken to an extreme, there are 5 don'ts in my signature line!

Out of interest, how (in words) would you describe """ or """"?

 

[Ken Sheridan]

I'd forgotten about possessive apostrophes. I'd describe multiple sets of double quotes in much the same way as pairs:

A contiguous set of n double quote characters.

BTW the OED makes a firm distinction between the apostrophe character, which it calls a sign, and what it terms a quotation mark. The former is a short superscripted vertical stroke, while the latter is a superscripted inverted comma or pair of inverted commas. It doesn't admit the existence of one or two short superscripted vertical strokes as a quotation mark as far as I can see. The OED, unlike Webster's for instance, is a descriptive rather than proscriptive dictionary, so maybe its time for them to amend the definition.