Android Question JRDC2 - Use SQL code in parameters

[Mattyeux]

Hi,

I use JRDC2 for an Android application with the MYSQL database but i have difficulties for one of my request,
Is it possible to put SQL code in query parameters?

In my case, I want to create "WHERE" code in my B4A because users can add a variable number of filters
For example, we can have "WHERE (date> 2019-01-01 AND date <2019-01-02) OR (date >2019-01-07 AND date< 2019-01-09) OR ..."
I thought the easiest way was to build the sql and send it to the request as a parameter

Something like :

sql.selectwithfilter =SELECT * FROM table WHERE ?

and B4A:

Dim whereCode = "date> 2019-01-01 AND date <2019-01-02) OR (date >2019-01-07 AND date< 2019-01-09)"
Dim cmd As DBCommand = CreateCommand("command", Array(whereCode))

But it does not work

Someone have a solution?

Regards,

 

[aeric]

Dim whereCode = "date> 2019-01-01 AND date <2019-01-02) OR (date >2019-01-07 AND date< 2019-01-09)"

You may need to surround date with single quotes like:

(date > '2019-01-01' AND date < '2019-01-02') OR (date > '2019-01-07' AND date < '2019-01-09')

 

[Erel]

Is it possible to put SQL code in query parameters?

Not without changing jRDC2 server code.

You need to change the query to:

sql.selectwithfilter =SELECT * FROM table WHERE (date > ? AND date < ?) OR (date > ? AND date < ?)

This is how paremeterized queries work.

 

[Mattyeux]

You need to change the query to:

sql.selectwithfilter =SELECT * FROM table WHERE (date > ? AND date < ?) OR (date > ? AND date < ?)

This is how paremeterized queries work.

I know this syntax but i can't do this because i don't know before the request how many parameters i need. It change with users choices.

'it could be this :
(date > ? AND date < ?) OR (date > ? AND date < ?)
'or this :
(date > ? AND date < ?) OR (date > ? AND date < ?) OR (date > ? AND date < ?) OR (date > ? AND date < ?) OR (date > ? AND date < ?)
'or just this
(date > ? AND date < ?)
'or nothing

Not without changing jRDC2 server code.

I suspected that I had to modify the code of the JRDC2 server ... I am thinking of adding a new type of request, something like executeQuery2 but with a concatenation of the command with the parameters
is this the right way?

Thanks for your help

 

[Erel]

is this the right way?

Not unless there is a better option:
Create 10 queries with all the possible patterns. It will take you 2 minutes and it will work.

Using concatenation means that your server is vulnerable to SQL injections.

 

[Mattyeux]

Not unless there is a better option:
Create 10 queries with all the possible patterns. It will take you 2 minutes and it will work.

I think that not possible because i don't have only date but also other filters and the number of pattern could be huge

Using concatenation means that your server is vulnerable to SQL injections.

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

I could add a type of query that can only execute one query (selectwithfilter) and just take an array of parameters as arguments.
like [ (date > '2019-01-01' AND date < '2019-01-02') ,...,...,...,..., (date > '2019-01-07' AND date < '2019-01-09')]

then i build SQL query on the JRDC2 server with security checks like don't have "UPDATE" word or "DELETE" word in parameters.

Thanks for your help

 

[Albert Kallal]

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

 

[Mattyeux]

Thank you very much for your help
I created a dynamics "WHERE" in b4A app, the fixed part is integrated in my JRDC2 Server

Your project is really interesting, this is a good idee. I think in my case i should add "ADDOR" and "ADDAND" methods to the JRDC2 server code.
I will think about it later

Thank you all