Android Question a question about jRDC secured query

M

Mostez

Well-Known Member
Licensed User
Longtime User
According to Erel's recommendation this SQL query should not be used with jRDC (dynamic queries) to avoid the chance of SQL injection
SELECT EmployeeName, ID FROM EMPLOYEES WHERE ID = 101

I want to make a query builder to compose SQL query statement to filter data before being populated to B4Xtable.
my idea is to pass 3 parameters to SQL stored procedure via B4J jRDC server
1- Table alias name (table1)
2- List of fields (EmployeeName,ID)
3- Conditions (ID = 101)
the stored procedure will lookup the table alias name (table1) to get real table name(EMPLOYEES ) and rebuild the SQL statement on SERVER using these 3 parameters, then execute it and send result back to application. The composed Select statement is not sent back to application.

is it safe to use this method or there is a better one?

TIA
 
Sort by date Sort by votes

josejad

Expert
Licensed User
Longtime User
Hi:

I'm not sure if I understand.
With jRDC (jRDC2), you don't send any SQL sentences. The sentences are stored in the server and you call the sencence name and pass an array of parameters.

jRDC2 config.properties in SERVER: 
sql.EmployeeName = SELECT EmployeeName, ID FROM EMPLOYEES WHERE ID = ?

Then, from your app, you call:
jRDC2 call from the client APP: 
Sub GetEmployeeName (id As Int)
   Dim req As DBRequestManager = CreateRequest
   Dim cmd As DBCommand = CreateCommand("EmployeeName", Array(id))
   Wait For (req.ExecuteQuery(cmd, 0, Null)) 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
End Sub
 
Upvote 0

ilan

Expert
Licensed User
Longtime User
José J. Aguilar said:
I'm not sure if I understand
Click to expand...

Erel said:
I also don't understand
Click to expand...

this is why i wanted to see the query.


Erel said:
don't see any problem with such query.
Click to expand...

actually the problem starts when you perform the query using parameters the user entered like:

B4X: 
"Select username, useremail, password from table1 where password = " & passtf.text

if the user will enter in the password textfield:
1234 or 1=1

then he will get all users with all password and emails.

Edit: everyone should read this:https://www.w3schools.com/sql/sql_injection.asp
 
Upvote 0
M

Mostez

Well-Known Member
Licensed User
Longtime User
sorry for late reply, just received your replies now, and sorry for my bad explanation
here is the exactly the case I have:
I have about 25 SQL tables, so I have to set the criteria dynamically for each one, I don't want to show table names in code, at the same time I have to send table name and criteria as parameters

as in attached pictures, the browse tab lists what user wants to browse, once clicked I set table alias name, for example it is BROWSE_INDEX_DRIVERS for DRIVERS table, then criteria maker shows up to set the criteria according to user needs
once OK clicked I get 2 vars (vary from table to another and according to what criteria user have made)

2- selected fields names like: SELECT [Company],[DriverID],[DrivingLicense]
3- conditions like: WHERE [DriverID] = 'DRV000004'

1- the first var is table alias(we have it before when user clicked the listview item)

I pass them to jRDC to run stored procedure with 3 parameters
sql.dynamicquery = EXECUTE ExecDynamic ? , ? , ?

the server executes the query and I get result set:

PROCEDURE [dbo].[ExecDynamic] @tablealias nvarchar(100), @selfields nvarchar(max), @criteria nvarchar(max)
declare @sql nvarchar (max);
declare @table nvarchar(100);
set @table = dbo.GetTableNameFromAlias (@tablealias) -- get real table name from alias lookup function
set @sql = @selfields + ' FROM ' + @table + ' ' + @criteria
EXECUTE sp_executesql @sql
 

Attachments

  • Screenshot_20201217-144859.jpg
    157.3 KB · Views: 188
  • Screenshot_20201216-124035.jpg
    56.9 KB · Views: 185
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Solved
Android Question Access database name from client parameter with JRDC ?
Replies
9
Views
2K
PumaCyan
  • Question
Android Question [SOLVED] Invalid parameter index 1 trying to connect to JRDC2
Replies
5
Views
4K
yfleury
O
  • Question
Android Question jRDC2 query problem
Replies
17
Views
5K
oscarsarrio
O
P
  • Question
Android Question Looping queries to JRDC
Replies
8
Views
1K
proinko
P
  • Question
Android Question [SOLVED] Use correct SQL LIKE operator
Replies
6
Views
4K
Bladimir Silva Toro
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…