I think that not possible because i don't have only date but also other filters and the number of pattern could be huge
I understand ... I don't know how to solve this problem ...
I'm thinking of a solution that could limit the SQL injection vulnerability, tell me what you think
Sure, the trick is to "not" limit the dynamic sql. But you can "limit" sql injection if you still use parameters.
So the "fixed" part of the sql might be say
SELECT * from tblBookings
And THEN in code you add to above a dynamic where clause.
dim strAll as string
strAllSQL = strSQL & " WHERE " & strWhere
rstData = mysql.ExecQuery2(strAllSQL,Params)
So, what is optional then is your "where clause" and the Parameters list.
I been busy, but I do have a "test code class" that helps you build conditions.
(its not really finished yet).
But, it works like this:
Dim SQLBuild As SQLBuilder
SQLBuild.Initialize
SQLBuild.AddOr("BookingDate", date1, date2,"BETWEEN")
SQLBuild.AddOr("BookingDate", date3, date4,"BETWEEN")
SQLBuild.AddOr("BookingDate", date5, date6,"BETWEEN")
' Now above is for "or" - so 3 date ranges.
' lets add a must have city (and clause):
SQLBuild.AddAnd("City","New York",Null,"=")
Dim strSQL As String
strSQL = "SELECT * from tblBooking "
wait for (SQLBuild.LoadData(strSQL,Starter.mysql)) Complete (ok As Boolean)
If ok Then
' standard process cursor/data routine
Do While SQLBuild.rstData.NextRow
' do whatever
Loop
End If
So, you can "hand code" the where clause, or build some kind of "helper" routine to add each parameter and ALSO build the where clause part.
So, you can certainly build up some sql, build up the WHERE clause, and also build up the parameters for the where clause.
The result is you do get/have parameters. (reasonable sql injection protection).
I like this kind of approach, and the "hard part" is all that string concentration stuff in code gets real messy real fast.
And Params REALLY helps by strong data typing for you - so you don't worry about quotes for strings, formatted dates. And then numbers which don't need quotes.
I have attached a rough working class - it needs more work, but it should give you a basic starting routine.
Regards,
Albert D. Kallal
Edmonton, Alberta Canada