B4J Question JRDC with variable number of parameters

[marcick]

Is that possible to implement a JRDC command with variable number of parameters ?
I mean something like

SELECT …. FROM …. WHERE name=? OR name=? OR name=? ….

and the number of OR, OR, OR is variable

 

[emexes]

I haven't used JRDC but, if nobody else is going to give it a burl, then I will.

What do your current fixed-number-of-parameters commands look like? Samples with 0, 1, 2 and 3 parameters would be useful.

 

[DarkoT]

My sugestion is that you use only one parameter which will be fullfilled from application with ALL where parameters... Like this

Select * from MyTable where ?

in code declare variable and send id to Rdc:

Sub Test(Where1 As String, Where2 As String, Where3 As String)
    Dim MyWhere As String = $"where MyField = '${Where1}' or MyField2 = '${Where2}' or MyField3 = '$Where3'"$
End Sub

and you need to send MyWhere as parameter to RDC...

 

[emexes]

with ALL where parameters

a sprinkling of this could be useful:

Android Code Snippet - How To Escape The Apostrophe (single quote) And % Sign In a SQLite Query

 

[aeric]

What do you want to achieve?

Search with multiple keywords?

Increase search ability SQLite (Solved)

Below is my b4xMainPage module for my SQLite app #Region Shared Files #CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files" 'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True...

www.b4x.com

 

[marcick]

I haven't used JRDC but, if nobody else is going to give it a burl, then I will.

What do your current fixed-number-of-parameters commands look like? Samples with 0, 1, 2 and 3 parameters would be useful.

Ok. My actual JRDC command on the server is:

sql.test=SELECT * FROM `records` WHERE ((`Pwd` = ? OR `Pwd` = ? OR `Pwd` = ?)

And I call it from B4A with this code:

    Dim cmd1 As DBCommand
    cmd1.Initialize
    cmd1.Name = "sql_test"   
    cmd1.Parameters = Array As Object("mark","john","alex")
    reqManager.ExecuteQuery(cmd1, 0, "testSync")

Non I want to do the same thing using 4 or 5 'Pwd', not just 3, without creating another JRDC command

 

[marcick]

My sugestion is that you use only one parameter which will be fullfilled from application with ALL where parameters... Like this

Select * from MyTable where ?

in code declare variable and send id to Rdc:

Sub Test(Where1 As String, Where2 As String, Where3 As String)
    Dim MyWhere As String = $"where MyField = '${Where1}' or MyField2 = '${Where2}' or MyField3 = '$Where3'"$
End Sub

and you need to send MyWhere as parameter to RDC...

I'll give it a try, thanks

 

[aeric]

sql.test=SELECT * FROM `records` WHERE ((`Pwd` = ? OR `Pwd` = ? OR `Pwd` = ?)

Isn't this allow someone to have multiple brute force password attempts to crack the password?

 

[marcick]

Isn't this allow someone to have multiple brute force password attempts to crack the password?

They are not really passwords, just keywords to filter the result of the table.
Nobody should be interested to my stuff ... I hope .....

 

[emexes]

And I call it from B4A with this code:

    Dim cmd1 As DBCommand
    cmd1.Initialize
    cmd1.Name = "sql_test"   
    cmd1.Parameters = Array As Object("mark","john","alex")
    reqManager.ExecuteQuery(cmd1, 0, "testSync")

Precisely which JRDC library or class are you using? ie name and version. Could you please point me to where you downloaded it from?

I probably won't be able to run it but it'll be useful to see the associated help information.

Non I want to do the same thing using 4 or 5 'Pwd', not just 3, without creating another JRDC command

I have a feeling that you'll either have to:

a/ construct the entire command yourself, as one string and including all parameters within it, or

b/ create another JRDC stored command with the required number of "?" parameter placeholders.

 

[marcick]

I have a feeling that you'll either have to:

b/ create another JRDC stored command with the required number of "?" parameter placeholders.

This is what I'm actually doing, but because the number of "?" is not fixed, I can't create a command for each case

I'm using this
https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/

 

[emexes]

And I call it from B4A with this code:

I just realised probably why I'm more confused than usual.

Are we working with B4A or B4J here?

 

[aeric]

I just realised probably why I'm more confused than usual.

Are we working with B4A or B4J here?

View attachment 137778

JRDC Server = B4J
JRDC Clients = B4X

 

[emexes]

I haven't used JRDC but, if nobody else is going to give it a burl, then I will.

I'm starting to realise why nobody else jumped in straight away with a simple answer to an obvious question...

And when I do a general Google search about JRDC variable number parameters, guess whose post appears at the top of the list?!?!

This does not bode well. It is starting to look like the commands are restricted to those specified at the server, presumably to keep a leash on what mischief clients can do to the database.

 

[emexes]

This does not bode well. It is starting to look like the commands are restricted to those specified at the server, presumably to keep a leash on what mischief clients can do to the database.

Partially confirmed by the comment "It is also safer as the SQL commands are set in the server side" at the "jRDC2 - B4J implementation of RDC" post.

So... try @DarkoT's suggestion, except possibly without the first "where" in the parameter string, because the server-side command already has a "where".

 

[OliverA]

No. Parameterized queries are used in order to prevent SQL injection attacks.

Workaround #1 (safest): Create multiple Parameterized queries, one for each quantity of parameters desired (see https://www.b4x.com/android/forum/t...smartphone-to-jrdc2-server.111269/post-693960)

Workaround #2 (you're on your own regarding the security of using this): Create dynamic SQL server side (see https://www.b4x.com/android/forum/threads/variable-table-name-through-jrdc.83262/post-527239). Note: That link shows you how to use the table name as a parameter. That could be adapted to your purpose. You would have to pass jRDC the desired SQL fragment.

 

[aeric]

It is starting to look like the commands are restricted to those specified at the server

Yes

presumably to keep a leash on what mischief clients can do to the database

Yes

The source can be modified to handle special commands in RDCHandler, if you wish.

First, add a new Type (both Client and Server),

Type DBCommand2 (Name As String, Column1 As String, Parameters() As Object)

In Server app, modified Sub Handle in RDCHandler class by adding a new condition for method = "query3" which will call ExecuteQuery3

        If method = "query2" Then
            q = ExecuteQuery2(con, in, resp)
        Else if method = "query3" Then
            q = ExecuteQuery3(con, in, resp)
        Else if method = "batch2" Then
            q = ExecuteBatch2(con, in, resp)

Add a new method ExecuteQuery3,

Private Sub ExecuteQuery3 (con As SQL, in As InputStream,  resp As ServletResponse) As String
    Dim ser As B4XSerializator
    Dim m As Map = ser.ConvertBytesToObject(Bit.InputStreamToBytes(in))
    Dim cmd As DBCommand2 = m.Get("command")
    Dim limit As Int = m.Get("limit")
   
    Dim query As String = Main.rdcConnector1.GetCommand(cmd.Name)
    If query.Contains("WHERE") = False Then    query = query & " WHERE"
    Dim words As List
    words.Initialize
    For Each param In cmd.Parameters
        If words.Size > 0 Then query = query & " OR"
        query = query & $" ${cmd.Column1} LIKE ?"$
        words.Add("%" & param & "%") ' add % symbols
    Next
   
    Dim rs As ResultSet = con.ExecQuery2(query, words)
    If limit <= 0 Then limit = 0x7fffffff 'max int
    Dim jrs As JavaObject = rs
    Dim rsmd As JavaObject = jrs.RunMethod("getMetaData", Null)
    Dim cols As Int = rs.ColumnCount
    Dim res As DBResult
    res.Initialize
    res.columns.Initialize
    res.Tag = Null 'without this the Tag properly will not be serializable.
    For i = 0 To cols - 1
        res.columns.Put(rs.GetColumnName(i), i)
    Next
    res.Rows.Initialize
    Do While rs.NextRow And limit > 0
        Dim row(cols) As Object
        For i = 0 To cols - 1
            Dim ct As Int = rsmd.RunMethod("getColumnType", Array(i + 1))
            'check whether it is a blob field
            If ct = -2 Or ct = 2004 Or ct = -3 Or ct = -4 Then
                row(i) = rs.GetBlob2(i)
            Else if ct = 2 Or ct = 3 Then
                row(i) = rs.GetDouble2(i)
            Else If DateTimeMethods.ContainsKey(ct) Then
                Dim SQLTime As JavaObject = jrs.RunMethodJO(DateTimeMethods.Get(ct), Array(i + 1))
                If SQLTime.IsInitialized Then
                    row(i) = SQLTime.RunMethod("getTime", Null)
                Else
                    row(i) = Null
                End If
            Else
                row(i) = jrs.RunMethod("getObject", Array(i + 1))
            End If
        Next
        res.Rows.Add(row)
    Loop
    rs.Close
    Dim data() As Byte = ser.ConvertObjectToBytes(res)
    resp.OutputStream.WriteBytes(data, 0, data.Length)
    Return "query: " & cmd.Name
End Sub

In config.properties, add a command like:

sql.query_with_variable_parameters=SELECT Field1, Field2, Field3 FROM MyTable

In Client app, inside DBRequestManager class, add a new method ExecuteQuery2

Public Sub ExecuteQuery2(Command As DBCommand2, Limit As Int, Tag As Object) As HttpJob
    Dim ser As B4XSerializator
    Dim data() As Byte = ser.ConvertObjectToBytes(CreateMap("command": Command, "limit": Limit,  "version": VERSION))
    Return SendJob(CreateJob, data, Tag, "query3")
End Sub

Usage:
Pass the array of names as parameters.
Take note on "Field3" as the filter column.

Public Sub GetStudents (parameters() As Object)
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand2 = CreateCommand2("query_with_variable_parameters", "Field3", parameters)
    Wait For (req.ExecuteQuery2(cmd, 0, Null)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        req.PrintTable(res)
    Else
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release
End Sub

I didn't test. Please let me know if this work.

 

[emexes]

The source can be modified to handle special commands in RDCHandler

Your solution looks like a work of art compared to the the skulduggery I had in mind

 

[marcick]

Thank you everybody. I will study all the stuff and decide. But probably yes, easier to create 12 commands, one for each number of parameters I have to pass and don't complicate everything too much .....