Android Question [DBCommand] CreateCommand

CR95 · Aug 10, 2021

In his tutorial "B4X] jRDC2 - B4J implementation of RDC", EREL uses an "object" as parameter for executing an SQL command.

Unfoetunately, I don't arrive to pass a simple string parameter. I get the correct id if I execute

B4X:

Dim cmd As DBCommand = CreateCommand("select_artistid", Null)

with this line in the "config.properties" file :

B4X:

sql.select_artistid=SELECT rowid FROM artiste WHERE ArtisteName = "Sting";

For transferring the parameter as a variable, I tried

B4X:

Dim params() As Object = Array("Sting")
Dim cmd As DBCommand = CreateCommand("select_artistid", params)

with

B4X:

sql.select_artistid=SELECT rowid FROM artiste WHERE ArtisteName = ?;

but it returns a bad rowid.
The Log in B4J Server shows it received

B4X:

Execute: SELECT rowid FROM artiste WHERE ArtisteName = ?;

My understanding is that it cannot work if the question mark in the command received by B4J Server is not replaced by "Sting"
So something is wrong in my parameters list in B4A but what ?
Thanks for the help

MrKim · Aug 16, 2021

CR95 said:
May I come back with a new question (still concerning the parameters sent to the server)
1°) With sqlite3, if I send an sql request, I get a correct response :
View attachment 117848
2°) If my B4A Client send a similar request with ONE question mark in the SQL command, it works

B4X:

Dim params() As String = Array As String("1984") Dim cmd As DBCommand = CreateCommand("select_double", params)

with this line in "config properties"

3°) BUT if I send a request with TWO question marks

B4X:

Dim params() As String = Array As String("TrackTitre","1984") Dim cmd As DBCommand = CreateCommand("select_double", params)

with this line in "config properties"

the server does not find the value and returns nothing
Is there a limitation for the number of question marks with SQLite ?
If you could help

In a situation like this there are two ways to handle it. On the server side you could use a CASE statement ( I don't know if that is available in SQLLite)

B4X:

CASE ?
WHEN 'TrackTitre'  THEN
      SELECT TrackAdresse FROM track WHERE TrackTitre = ?
WHEN 'Case2'
      SELECT TrackAdresse FROM track WHERE Case2 = ?
.
.
END

or you need multiple statements in your config.properties file and you call the right one from your client side depending on the variable.
Either way it is the proper way to do it.
I feel ya. I am an old DB programmer who has been working only with LAN DBs for over 35 years creating code queries is second nature to me but wireless/internet is a whole different animal. What you are trying to do is NOT secure.
SQL injection cheat sheet: 8 best practices to prevent SQL injection | Snyk

Android Question [DBCommand] CreateCommand

CR95

Active Member

MrKim

Well-Known Member

Similar Threads

Android Question [DBCommand] CreateCommand

CR95

Active Member

MrKim

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency