Other SQL dynamic queries with jRDC2

[Mostez]

The code in this tutorial is based on code example by @Bladimir Silva Toro, thanks for sharing.

MS SQL server provides special stored procedure 'sp_executesql' to execute SQL statements, this stored procedure accepts one string-type parameter which is the SQL query we want to execute. By using this procedure with jRDC2 we will be able to execute any SQL query/nonquery statement. All we need is to add this line to our SQL commands in config properties file on B4J jRDC.

sql.execcall= EXECUTE sp_executesql ?

it's just one parameter call, this parameter is our SQL query/nonquery statement we want to execute, you may rename 'execcall' as you like
in this example we will use two subs one to execute query and the other to execute nonquery SQL statements.

Sub ExecNonQuery(SQLstring As String)As ResumableSub
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("execcall", Array As String(SQLstring))
    Wait For (req.ExecuteCommand(cmd,SQLstring)) JobDone(j As HttpJob)
    If j.Success Then
        'handle OK   
    Else
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release
    Return j.Success
End Sub

Sub ExecQuery (SQLquery As String)As ResumableSub
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("execcall", Array As String(SQLquery))
    Wait For (req.ExecuteQuery(cmd, 0, SQLquery)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        'work with result
        req.PrintTable(res)
    Else
        Log("ERROR: " & j.ErrorMessage)
    End If
    j.Release
    Return j.Success
End Sub

to pass SQL query/nonquery statement to jRDC2 we use code like:

Private Sub Button1_Click
 
    Dim cid As String = "TEST001"
    Dim Qr As String
    Dim cnm As String = "New Customer"
    Dim cmod As String = "Modified Name"
    
    'comment/uncomment blocks To run desired tests
'    Qr = "delete from [test] where CustomerID like " & "'" & cid & "%'"
'    Wait For (CallSub2(Me, "ExecNonQuery",Qr)) Complete (OK As Boolean)
'    Log(OK)
    
    Qr = "insert into [test] ([CustomerID], [CustomerName]) VALUES (" & "'" & cid & "'" & "," &  "'" & cnm & "'" & ")"
    Wait For (CallSub2(Me, "ExecNonQuery",Qr)) Complete (OK As Boolean)
    Log(OK)
    
'    Qr = "update [test] set [CustomerName] = " & "'" & cmod & "'" & " where [CustomerID] = " & "'" & cid & "'"
'    Wait For (CallSub2(Me, "ExecNonQuery",Qr)) Complete (OK As Boolean)
'    Log(OK)
'    
'    Qr = "SELECT * from test where CustomerID = " & "'" & cid & "'"
'    Wait For (CallSub2(Me, "ExecQuery",Qr)) Complete (OK As Boolean)
'    Log(OK)
 
End Sub

 

[Mashiane]

Interesting. Can one use parameter based queries with this? Can you please provide an example? Thanks.

 

[Erel]

Moved to the questions forum.

Such code should never be used:

 Qr = "insert into [test] ([CustomerID], [CustomerName]) VALUES (" & "'" & cid & "'" & "," &  "'" & cnm & "'" & ")"

 

[Mostez]

Such code should never be used:

why such code should never be used?

 

[Erel]

Watch the SQL video tutorial: https://www.b4x.com/etp.html

And code smell #5: https://www.b4x.com/android/forum/t...ommon-mistakes-and-other-tips.116651/#content

 

[Mostez]

Thanks for this important info

 

[Alexander Stolte]

why such code should never be used?

A user can then easily do the following with your data in the database:

DROP TABLE

 

[MrKim]

It is tempting... It makes so many things easier - BUT DON'T DO IT!!!!

 

[Trisha Duke]

A user can then easily do the following with your data in the database:

DROP TABLE

what about doing some checking before things like that get in dynamic sql??
what other command that might cause problem???

 

[MrKim]

what about doing some checking before things like that get in dynamic sql??
what other command that might cause problem???

You can do that, but then you need to write a function or procedure and CHECK it for EVERYTHING that MIGHT be damaging and at that point you might be excluding things that are valid. Think DROP DATABASE, DROP INDEX, DROP TABLE then there is TRUNCATE TABLE and ALTER TABLE then you get into the commands that allow you to access the operating system and run operating system commands and the commands that give you permission to access the operating system.

It is a long list and prone to mistakes. What if your query contains a WHERE clause that includes DROP SHIP? Remember you have to parse everything and analyze it because because DROP TABLE and DROP (edit: it doesn't show when posted but there is a bunch of spaces here) TABLE are just as valid. I have seen code where the worst offenders were testid and it went on for pages.

Here is a page that will give you a 5 minute overview of what you are looking at.
SQL Injection Testing Tutorial (Example and Prevention of SQL Injection Attack) (softwaretestinghelp.com)