Escaping ' in Access SQL

Escaping ' in Access SQL

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

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

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

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).

Escaping Special Characters for MS Access Query

In addition to using a parameterized query (see Adrian's Answer) you can use a Query Definition with a parameter and call it.

For example you could create a query named qry_InsSomeTable with the following sql

PARAMETERS P_SomeField Text ( 255 );
INSERT INTO tbl_SomeTable (  P_SomeField )
VALUES (P_SomeField );

Then you could call like you would any stored procedure

using(var cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Whatever.mdb")
{
    var cmd = new OleDbCommand();
    cmd.Connection = cn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = cmd.CommandText = "qry_InsSomeTable";
    cmd.Parameters.AddWithValue("P_SomeField", "'^+%&/()=?_ ");

    cn.Open();

    cmd.ExecuteNonQuery(); 

}

Its particularly helpful when you have a table with a bunch of fields

For example

  cmd.CommandText = @"Insert  Into TableWithAlotofFields 
                    (field1, field2, field3, field4, field5,...)
                    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?..)";

  cmd.Parameters.Add("value1");
  cmd.Parameters.Add("value2");
  cmd.Parameters.Add("value3");
  cmd.Parameters.Add("value4");
  cmd.Parameters.Add("value5");

vs.

Query already define in Access as

 PARAMETERS P_field1Text Text ( 255 ), P_field2 Number, P_field3 text(15), ...;
  Insert Into TableWithAlotofFields 
  (field1, field2, field3, field4, field5,...)
  VALUES(P_field1, P_field2, P_field3, P_field4, P_field5,...)

then in c#

 cmd.CommandText = cmd.CommandText = "qry_InsSomeTable";
 cmd.Parameters.AddWithValue("P_field1", "value1");
 cmd.Parameters.AddWithValue("P_field2", "value2");
 cmd.Parameters.AddWithValue("P_field3", "value3");
 cmd.Parameters.AddWithValue("P_field4", "value4");
 cmd.Parameters.AddWithValue("P_field5", "value5");

However as @David-W-Fenton points out the names on the parameters are ignored and only the position is taken into account.
e.g.

This

  cmd.Parameters.AddWithValue("P_field1", "value1");
  cmd.Parameters.AddWithValue("P_field2", "value2");

is not equivalent to

 cmd.Parameters.AddWithValue("P_field2", "value2");
 cmd.Parameters.AddWithValue("P_field1", "value1");

So it seems it mostly stylistic in the differences.

MS Access - Query - LIKE with Escape characters not working

The wildcard character in MS Access is '*', not '%'.

So, try this:

Like '*([1-9])*'

How do I escape all special characters in Access Jet SQL?

You seem to have hit upon something: you cannot create a (usable) password containing any of those characters using SQL DDL (note Wikipedia considers this to be SQL DCL).

Below is some code to reproduce a test scenario:

• creates a new workgroup (in temp
  folder)
• creates a new database using the
  workgroup
• creates a new table with data
• creates a new user with name,
  password and PID using alphanumeric
  characters throughout
• grants privileges on the table to the
  user
• opens a test connection using the new
  user's credentials
• tests that the user can query the
  table

The code as posted works fine. However, editing the password in both places (when the user is created and when the test connection is opened) to add a non-alpha character (e.g. a quote) raises an error in one of those places.

On Error Resume Next
Kill Environ$("temp") & "\MyDatabase.mdb"
Kill Environ$("temp") & "\MyWorkgroup.mdw"
On Error GoTo 0

' Create workgroup and db
Dim cat 
Set cat = CreateObject("ADOX.Catalog")
With cat
  .Create _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Jet OLEDB:Engine Type=4;" & _
      "Data Source=" & _
      Environ$("temp") & "\MyWorkgroup.mdw;" & _
      "Jet OLEDB:Create System Database=-1"
  .Create _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Jet OLEDB:Engine Type=4;" & _
    "Data Source=" & _
    Environ$("temp") & "\MyDatabase.mdb;" & _
    "Jet OLEDB:System Database=" & _
    Environ$("temp") & "\MyWorkgroup.mdw;"

  ' Add table with data and user with privileges
  With .ActiveConnection
    .Execute "CREATE TABLE Test (data_col INTEGER);"
    .Execute "INSERT INTO Test VALUES (55);"
    .Execute "CREATE USER onedaywhen pwd H3sJaZ9k2m;"  ' <-- edit pwd
    .Execute "GRANT ALL PRIVILEGES ON Test TO onedaywhen;"
  End With
End With

' Test user can connect and SELECT
Dim con 
Set con = CreateObject("ADODB.Connection")
With con
  .ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Jet OLEDB:Engine Type=4;" & _
    "Data Source=" & _
    Environ$("temp") & "\MyDatabase.mdb;" & _
    "Jet OLEDB:System Database=" & _
    Environ$("temp") & "\MyWorkgroup.mdw;" & _
    "User ID=onedaywhen;Password=pwd;"  ' <-- edit pwd
  .Open
  MsgBox .Execute("SELECT * FROM Test;")(0)
End With

With no means to escape 'special' characters then it seems this can't be done using SQL DDL/DCL, which I think means it cannot be done using ADO at all.

So, anyone got an alternative e.g. DAO?

Escape input data in SQL queries when using ODBC + Access

I've been trying out random stuff. It seems that odbc_prepare() detects parameters if you use one of these syntaxes (or you even mix them):

• INSERT INTO foo (bar) VALUES (:param)
• INSERT INTO foo (bar) VALUES ([param])

However, odbc_execute() will complain about missing parameters no matter what you feed it with (numeric array, associative array...). And it'll know the exact number of parameters that cannot be found. That makes the whole mechanism completely pointless.

Sad to say, my best solution so far is this:

/**
 * Escape a string to be inserted into Access via ODBC
 */
function odbc_escape_string_access($value){
    $replacements= array(
        "'" => "''",
    );
    return strtr($value, $replacements);
}

It's horrible but I couldn't find anything better.

SQL Escape for access database from C#

Turning ' into '' is enough. Every other character can stay the same.

---

Related Topics