Escaping unwanted characters, mainly single quotes --replace function and implementation

Question

I was just testing my database and I realized that I run into problems wherever a text entry in my database contains a ' character (single quote). My solution for now is that before any .execute operations on a string, I call escape(string, "'", " "'" ").

Summarized example below:

qr = "INSERT INTO tblExample VALUES ( " & "'" & me.testparam & "'" & ");"
qr = Replace(qr, "'", " "'" ")
db.execute qr
'also tried  qr = "INSERT INTO tblExample VALUES ( " & "'" & replace(me.testparam,"'"," ") & "'" & ");"

This was what I assumed to be the correct workaround to prevent errors from values such as Tourette's.

There's two problems with this. First of all, it's not working. Second, I have over 50 locations in code throughout my app where I call the statement db.execute qr where qr is a string that could potentially contain a single quote. I need the field in the table to contain the single quote, so I can't just replace it with a space or something similar.

Two part question:

1. Is there a better solution than going through all of my code calling Replace on every string that is to be executed as a query?

2. Why is my current implementation failing? - I still get syntax error in query expression even when escaping the single quote to a space.

Sleepyhead

Have you tried replacing each single ' with '' (two single quotes) ? Not sure about access, but that's how I would normally escape ' in SQL. – Ninurta

Well the thing is, it IS escaping the single quote into a space, but the insert is still not working. No errors...but no data. String before : "Insert into tblTest Values('tourette's');" and after : "insert into tblTest Values('Tourette s');" – Sleepyhead

"Insert into tblTest Values('tourette''s');" is what @Tim is trying to indicate - note the 2 single quotes – Scute

So do you mean "INSERT INTO tblTest Values (" & "'" & replace(me.field,"'","''") & "'" & ");" ? I will try that. edit - The quotes for that are extremely hard to read, but you get the picture. Calling replace with single quote as first param, and two single quotes (not a double quote?) as the second? – Sleepyhead

@Scott Yes - you escape a single quote by doubling it up. – Ninurta

See also: https://mcmap.net/q/531908/-escaping-39-in-access-sql – Ninurta

I reckon you would be better off with a parameter or two: #11403886 – Optometrist

Answer 1

First examine these 2 lines.

"VALUES ( " & "'" & me.testparam & "'" & ");"
"VALUES ( '" & me.testparam & "');"

Both will produce the exact same string. The difference for me is that my brain comprehends the second version faster.

Now, here is what the comments are telling you to do ... replace each single quote in your source string with two single quotes. I added Debug.Print so you can view the finished string in the Immediate window (go there with Ctrl+g) ... you can then see the actual string rather than trying to imagine what it looks like.

qr = "INSERT INTO tblExample VALUES ( '" & _
    Replace(Me.testparam, "'", "''" & "');"
Debug.Print qr
db.Execute qr, dbFailOnError 

Since I assumed db is a DAO.Database object variable, I included the dbFailOnError option. You should include an error handler in your code to deal with any problems dbFailOnError exposes.

When you run into trouble with a VBA function in a query, drop to the Immediate window and test your function expression there. This one triggers a compile error, "Expected: list separator or )":

? Replace("Tourette's", "'", " "'" ")

But this one works:

? Replace("Tourette's", "'", "''")
Tourette''s

I mentioned that because it's useful in general, and also because your title starts with "Escaping unwanted characters, mainly single quotes". So if you want to remove/replace other characters, not just single quotes, experiment in the Immediate window until you find a Replace() expression which works. Then use that expression in your query.

For example, if unwanted characters include line breaks ...

MyString = "foo" & vbCrlf & "bar" : ? MyString
foo
bar
? Replace(MyString, Chr(13) & Chr(10), " ")
foo bar

Note: I used Chr(13) & Chr(10) rather than vbCrlf as the find target because the db engine can use the Chr() function but doesn't know about the named constant (vbCrlf).

Cosh

replace(me.test,"'","''") works. I had previously had double quotes instead of two single quotes, as I was following an example online and misread it. I guess I need to trace through all of my code and call this for every query that I execute in VBA, unless there's an easier workaround. Thanks. – Sleepyhead

For dealing with single quotes, Remou's QueryDef/Parameters suggestion neatly avoids the problem. Apply that wherever you can use it. Elsewhere you could use a custom function to wrap Replace(), but I'm skeptical whether the benefit would justify the effort. Meanwhile I'm still left wondering whether you intended the scope of your question be limited to single quotes as the only special characters. – Cosh

Actually, the scope of my question could extend beyond just single quotes--single quotes is just the specific thing that was giving me problems. I was wondering if there was something like php's mysql_real_escape_string for access that would escape ALL fishy characters. – Sleepyhead

Not exactly. Parameters can avoid some problems. But if you want something closer to mysql_real_escape_string, I'm afraid you'll have to roll your own, or find code someone rolled ... it's not covered in Access' built-in feature set. – Cosh

Alright. It may be more time than it's worth to go through my current project and re-code all my VBA-generated queries to work with parameters rather than concatenation, but in the future, I will implement it as Remou suggested. Are there any specific characters other than single quotes that would likely cause me problems? I'm not really worried about protecting against malicious injections or anythings, as I doubt any of the employees are capable or inclined to do this, but characters like single quotes, slashes, and dashes will probably be used occasionally. – Sleepyhead

Answer 2

Your query is failing because you have not said where to insert :

Dim qd As QueryDef
qr = "INSERT INTO tblExample (AText) VALUES ( [avalue] );"

Set qd = CurrentDB.CreateQueryDef("",qr)
qd.Parameters("avalue").Value = me.testparam
qd.Execute dbFailOnError

Optometrist

Would this method of assigning the parameter instead of concatenating serve to escape the single quote? I followed Hans's implementation above by replacing the single quote with two single quotes and it worked, but your implementation also seems interesting. – Sleepyhead

@Scott Yes. Parameters are generally recommended these days. It also means you do not have to worry about delimiters. – Optometrist

Answer 3

Another method is to define a quote as constant (Const Quote = """") and use that to build SQL Statements. It is not possible to define a quote as Const Quote = Chr(34) as a constant definition can't be based on a function so one has to use four double quotes in a row. The third quote is what you are saving, the second quote is to excape the third quote and the first and last quote are because the value you are assigning is a string.

You will then be able to build SQL statements such as:

SQL = SELECT * FROM tblSyndromes
WHERE Syndrome = " & Quote & "Tourette's" & Quote & ";"

It will no longer matter that there are single quotes in your data.

I don't use parameters as if I upscale my database to sql server and convert my queries to pass-through queries, I can't use parameters. I rarely upscale but I write all my code with that assumption. Also if your query is not working as expected, how do find out what went wrong. If I have a variable called SQL, then I can always print the SQL statement and run it in a new query to see what it does.

Hardheaded