Multiple LIKE operators?

[theta]

Hi guys...I am working on a project that involves SQL interaction with a database - mainly using SELECT statements, few JOINS etc.

Before an SQL statement is executed I would like to test it for content. I have seen this done before at an old job, but was unable to save the code. It prevented the user from executing any statement containing a list of commands, but I am not sure how implement it :

SQLString = "SELECT * FROM Customer"

If SQLString LIKE "CREATE,DELETE,ALTER,INSERT,UPDATE" Then SQLString = ""

Obviously this LIKE string is not correct, just showing the intention. I have seen it on a one liner.

[Paul_Hossler]

Brute force and not very elegant

[VBA]
Option Explicit
Sub test()

Dim SQLString As String

SQLString = "SELECT * FROM Customer"
If InStr(SQLString, "CREATE") + InStr(SQLString, "DELETE") + _
InStr(SQLString, "ALTER") + InStr(SQLString, "INSERT") + _
InStr(SQLString, "UPDATE") > 0 Then
SQLString = vbNullString
End If

MsgBox Len(SQLString)

SQLString = "DELETE * FROM Customer"
If InStr(SQLString, "CREATE") + InStr(SQLString, "DELETE") + _
InStr(SQLString, "ALTER") + InStr(SQLString, "INSERT") + _
InStr(SQLString, "UPDATE") > 0 Then
SQLString = vbNullString
End If

MsgBox Len(SQLString)

End Sub
[/VBA]

Paul

[JKwan]

Try this:
[vba]Public Function MatchCriteria2(InpStr As String, Keywords As Variant) As Boolean
Dim InpTemp As String
Dim InpVal As Integer

Dim Keyword As Long

For Keyword = 1 To UBound(Keywords)
If InStr(1, InpStr, Keywords(Keyword), vbTextCompare) Then
InpVal = InStr(1, InpStr, Keywords(Keyword), vbTextCompare)
InpTemp = InpTemp & Mid(InpStr, InpVal, InStr(InpVal, InpStr & " ", " ") - InpVal) & " "
End If
Next Keyword

If InpTemp = "" Then
MatchCriteria2 = False
Else
MatchCriteria2 = True
End If
End Function
Sub Testing()
Dim SQL As String

SQL = "Delete * From ABC"
If MatchCriteria2(SQL, _
Array("Create", _
"Delete", _
"Alter", _
"Insert", _
"Update")) Then
SQL = vbNullString
End If
MsgBox SQL
End Sub
[/vba]

[shrivallabha]

One more [and non elegant]:
[VBA]Sub Test()

SQLString = "SELECT * FROM Customer"

If _
SQLString Like "*CREATE*" Or _
SQLString Like "*DELETE*" Or _
SQLString Like "*ALTER*" Or _
SQLString Like "*INSERT*" Or _
SQLString Like "*UPDATE*" Then
SQLString = vbNullString
End If

MsgBox SQLString

SQLString = "DELETE * FROM Customer"

If _
SQLString Like "*CREATE*" Or _
SQLString Like "*DELETE*" Or _
SQLString Like "*ALTER*" Or _
SQLString Like "*INSERT*" Or _
SQLString Like "*UPDATE*" Then
SQLString = vbNullString
End If

MsgBox SQLString

End Sub
[/VBA]

[snb]

[vba]if len(replace(replace(replace(replace(replace(SQLString,"CREATE",""),"DELETE" ,""),"ALTER",""),"INSERT",""),"UPDATE",""))<>len(SQLstring) Then SQLString = ""
[/vba]

[GTO]

Can we sneak in one more?
[VBA]Option Explicit

Sub example()
Dim SQLString As String
SQLString = "SELECT * FROM Customer"

If SQLString_OK(SQLString, "CREATE", "DELETE", "ALTER", "INSERT", "UPDATE") Then
MsgBox "String is okay, value remains: " & SQLString
Else
MsgBox "Oopsie - value of SQLString is now: " & SQLString
End If

'---OR---

SQLString = "SELECT * FROM Customer"

SQLString_OK SQLString, "CREATE", "DELETE", "ALTER", "INSERT", "UPDATE"

MsgBox "SQLString: " & SQLString

End Sub

Function SQLString_OK(ByRef InputOutputString As String, ParamArray Forbidden() As Variant) As Boolean
Static REX As RegExp
Dim Pattern As String

If REX Is Nothing Then Set REX = CreateObject("VBScript.RegExp")
With REX
.Pattern = Join(Forbidden, "|")
If .Test(InputOutputString) Then
SQLString_OK = False
InputOutputString = vbNullString
Else
SQLString_OK = True
End If
End With
End Function[/VBA]

[Paul_Hossler]

"Oopsie - value of ......

Looked for that in On Line Help, but couldn't find it

Paul

[GTO]

Looked for that in On Line Help, but couldn't find it

Paul

Howdy Pard',

Shucks Paul, one would think the Help files would better document 'Oopsie', but alas, it seems altogether amiss. I am sure it exists, though I have not figured out if it is a Method or Function; but I know it's there, else - how could I experience it so often?

Mark

[Paul_Hossler]

[vba]
On Oopsie Resume Next
[/vba]

??

Paul

[theta]

Great responses - thanks guys

Will play with a few of these methods. I like the array and the regexp approach as they are very easy to expand / contract.

But the one line InStr is very neat and small. Many ways to skin a cat...

[Aflatoon]

Since we're in Excel:
[vba]If Evaluate("COUNT(SEARCH({""CREATE"",""DELETE"",""ALTER"",""INSERT"",""UPDATE ""},""" & SQLString & """))") > 0 Then SQLString = ""[/vba]