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

Question

I'm trying to change a password with a DDL statement like:

CurrentProject.Connection.Execute "ALTER USER barney PASSWORD "[]!@#$%^ oldpassword"

Yes, that's a nasty password, but someone tried something like that. Notice the beginning quote of the password is not part of the sql syntax here. I need something like mysql_real_escape_string() but for VBA. Any hints?

Answer 1

I was able to get the statement to run without error by surrounding the new password with single quotes and doubling up the double quotes.

CurrentProject.Connection.Execute "ALTER USER fred PASSWORD '""[]!@#$%^' oldpassword;"

However, when trying to log on as user fred with that password, Access replied "Not a valid account name or password".

With the exception of the leading double quote, all those characters ([]!@#$%^) worked as a password. The only suggestion I can offer is to instruct your users not to include quotes in their passwords.

Answer 2

You should be able to concatenate in a string using Chr(34):

  Dim strPassword As String

  strPassword = Chr(34) & "[]!@#$%^"
  CurrentProject.Connection.Execute "ALTER USER barney PASSWORD " & strPassword & " oldpassword"

That's the usual way to do this kind of thing. That may not do the trick, though, as the user-level security UI may not accept quotes (I haven't tried it).

Answer 3

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?