CODEWITHSUNDEEP
sqlms-accessvbaescapingjet
DGM
DGM

Reputation: 26979

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

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?

Upvotes: 2

Views: 3415

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

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?

Upvotes: 1

David-W-Fenton
David-W-Fenton

Reputation: 23067

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

Upvotes: 0

Related Questions