PDA

View Full Version : Multiple LIKE operators?



theta
09-21-2012, 06:29 AM
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
09-21-2012, 09:16 AM
Brute force and not very elegant


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


Paul

JKwan
09-21-2012, 10:23 AM
Try this:
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

shrivallabha
09-21-2012, 10:44 AM
One more [and non elegant]:
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

snb
09-21-2012, 01:20 PM
if len(replace(replace(replace(replace(replace(SQLString,"CREATE",""),"DELETE",""),"ALTER",""),"INSERT",""),"UPDATE",""))<>len(SQLstring) Then SQLString = ""

GTO
09-22-2012, 05:50 PM
Can we sneak in one more?
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

Paul_Hossler
09-22-2012, 07:46 PM
"Oopsie - value of ......


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

Paul

GTO
09-24-2012, 05:46 AM
Looked for that in On Line Help, but couldn't find it :thumb

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?:doh:

Mark

Paul_Hossler
09-24-2012, 06:18 AM
On Oopsie Resume Next


??

Paul

theta
09-24-2012, 06:33 AM
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
09-24-2012, 07:09 AM
Since we're in Excel:
If Evaluate("COUNT(SEARCH({""CREATE"",""DELETE"",""ALTER"",""INSERT"",""UPDATE""},""" & SQLString & """))") > 0 Then SQLString = ""