Android Question a question about jRDC secured query

[Mostez]

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

 

[ilan]

how does your final query look like?
can you post the code?

 

[josejad]

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.

sql.EmployeeName = SELECT EmployeeName, ID FROM EMPLOYEES WHERE ID = ?

Then, from your app, you call:

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

 

[Erel]

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 also don't understand and don't see any problem with such query.

 

[ilan]

I'm not sure if I understand

I also don't understand

this is why i wanted to see the query.

don't see any problem with such query.

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

"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

 

[Erel]

This cannot happen with jRDC2 as you are sending parameters, not queries. The parameters are passed to parameterized queries.

 

[ilan]

This cannot happen with jRDC2 as you are sending parameters, not queries. The parameters are passed to parameterized queries.

ok, and with sqlite? What would be your suggestion?

 

[Erel]

ok, and with sqlite?

Use parameterized queries. They are supported by all SQL engines.

 

[Erel]

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

 

[Mostez]

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