By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

How to confine user SQL to imported tables?

P: n/a
What I'm trying to do is allow advanced users to compose SQL queries to
update imported tables - and only imported tables. So I have a table that
contains all the internal table names (tblTablesInternal) and I loop through
that making the internal table name the pattern to search for in the user's
SQL statement:

Public Function SafeQry(strSql As String) As Boolean
Set rst = db.OpenRecordset("tblTablesInternal")
Set objRgx = CreateObject("VBScript.RegExp")
strSql = LCase(strSql)
SafeQry = True
Do While Not rst.EOF
strTbl = LCase(rst!InternalTable)
objRgx.Pattern = strTbl
Debug.Print "looking for [" & strTbl & "] in [" & strSql & "]"
If objRgx.Test(strSql) Then
SafeQry = False
Exit Do
End If
rst.MoveNext
Loop
End Function

The problem is sometimes an imported table has the same name as an internal
table, and the imported table gets appended with a number - e.g.
'tblUtility1'. So the above Debug.Print output would look like this:

looking for [tblutility] in [update [tblutility1] set entity_id = 464;]

In this case, the RegExp test returns true and the query is flagged as
unsafe when it is not.

Is there a way to perform the RegExp test to match only the whole word -
e.g. 'tblutility'? Is there a better way to confine user SQL statements to
imported tables?

Thanks in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
changing the one line below seems to work
Public Function SafeQry(strSql As String) As Boolean
Set rst = db.OpenRecordset("tblTablesInternal")
Set objRgx = CreateObject("VBScript.RegExp")
strSql = LCase(strSql)
SafeQry = True
Do While Not rst.EOF 'strTbl = LCase(rst!InternalTable)
strTbl = "(\[| )" & LCase(rst!InternalTable) & "(\]| )" objRgx.Pattern = strTbl
Debug.Print "looking for [" & strTbl & "] in [" & strSql & "]"
If objRgx.Test(strSql) Then
SafeQry = False
Exit Do
End If
rst.MoveNext
Loop
End Function

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.