How to avoid all special characters in Access Jet SQL?
I am trying to change a password using a DDL statement like:
CurrentProject.Connection.Execute "ALTER USER barney PASSWORD "[]!@#$%^ oldpassword"
Yes, this is a nasty password, but someone tried something similar. Note that the initial password quote is not part of the sql syntax here. I need something like mysql_real_escape_string()
but for VBA. Any hints?
source to share
You seem to have pounced on something: you cannot generate a (useful) password containing any of these characters using SQL DDL (note Wikipedia thinks it is SQL DCL ).
Below is the code to reproduce the test scenario:
- creates a new workgroup (in temp folder)
- creates a new database using the working group
- creates a new table with data
- creates a new user with name, password and PID using alphanumeric characters throughout
- grants table privileges to the user
- opens a test connection using new user credentials
- checks that the user can query the table
The code posted works great. However, editing the password in both places (when the user is created and when the test connection is open) to add a non-alpha character (like a quote) throws 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
There is no way to escape the special characters, but it looks like it cannot be done using SQL DDL / DCL, which I think means it cannot be done using ADO at all.
So someone has an alternative for example. DAO?
source to share
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"
This is the usual way to do it. However, this might not do the trick, as the user-level security UI might not accept quotes (I haven't tried it).
source to share